用SpringBoot+MySql+JPA实现对数据库的增删改查和分页 - 好文

使用SpringBoot+Mysql+JPA实现对数据库的增删改查和分页

JPA是Java Persistence API的简称，中文名Java持久层API，是JDK
5.0注解或XML描述对象－关系表的映射关系，并将运行期的实体对象持久化到数据库中。

使用Springboot和jpa对数据库进行操作时，能够大大减少我们的工作量，在jpa中，已经在底层封装好了增删查的功能和sql语句，可以使我们进行快速开发

项目流程

一、新建一个项目

二、配置文件

#数据源配置 spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/stu?useSSL=false&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.username=root spring.datasource.password=root
spring.datasource.initialSize=20 spring.datasource.minIdle=50
spring.datasource.maxActive=500 #上下文配置 server.port=8888
server.servlet.context-path=/stu #配置jpa #帮我们自动生成表结构
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql= true
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
　　

<?xml version="1.0" encoding="UTF-8"?> <project
xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion> <parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.4.RELEASE</version> <relativePath/>  </parent> <groupId>com.kude.stu</groupId>
<artifactId>kudestu</artifactId> <version>0.0.1-SNAPSHOT</version>
<name>kudestu</name> <description>Demo project for Spring Boot</description>
<properties> <java.version>1.8</java.version> </properties> <dependencies>
<dependency> <groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>
<dependency> <groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope>
</dependency> <dependency> <groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId> <scope>runtime</scope>
</dependency> <dependency> <groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId> <scope>test</scope>
</dependency>  <dependency> <groupId>com.alibaba</groupId>
<artifactId>druid</artifactId> <version>1.1.10</version> </dependency>  <dependency> <groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.10</version> </dependency> </dependencies> <build> <plugins>
<plugin> <groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build>
</project>
　　

三、Mysql数据库

新建一个数据库stu,创建student表和user表

四、后端实现

student实体类
package com.kude.stu.kudestu.stu.entity; import javax.persistence.*; import
javax.xml.soap.Name; @Entity @Table(name = "student") public class Student {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id;
private String name; private int age; private String sex; @Override public
String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' +
", age=" + age + ", sex='" + sex + '\'' + '}'; } public Integer getId() {
return id; } public void setId(Integer id) { this.id = id; } public String
getName() { return name; } public void setName(String name) { this.name = name;
} public int getAge() { return age; } public void setAge(int age) { this.age =
age; } public String getSex() { return sex; } public void setSex(String sex) {
this.sex = sex; } public Student() { } }
　　user实体类
package com.kude.stu.kudestu.stu.entity; import javax.persistence.*; @Entity
@Table(name="user") public class User { @Id @GeneratedValue(strategy =
GenerationType.IDENTITY) private int id; private String username; private
String password; public User() { } @Override public String toString() { return
"User{" + "id=" + id + ", username='" + username + '\'' + ", password='" +
password + '\'' + '}'; } public int getId() { return id; } public void
setId(int id) { this.id = id; } public String getUsername() { return username;
} public void setUsername(String username) { this.username = username; } public
String getPassword() { return password; } public void setPassword(String
password) { this.password = password; } }
　　

StudentDao接口
package com.kude.stu.kudestu.stu.dao; import
com.kude.stu.kudestu.stu.entity.Student; import
org.springframework.data.jpa.repository.JpaRepository; import
org.springframework.data.jpa.repository.Query; import
org.springframework.data.repository.query.Param; import java.util.List; public
interface StudentDao extends JpaRepository<Student,Integer> { Student
findStuById(Integer id); @Query(name = "findStuByName",nativeQuery = true,value
= "select * from student where name=:name ") List<Student>
findStuByName(@Param("name") String name); }
　　

UserDao接口
package com.kude.stu.kudestu.stu.dao; import
com.kude.stu.kudestu.stu.entity.User; import
org.springframework.data.jpa.repository.JpaRepository; import
org.springframework.data.jpa.repository.Query; import
org.springframework.data.repository.query.Param; public interface UserDao
extends JpaRepository<User,Integer> { /** * 根据id查询 */ User findUserById(int
id); @Query(name = "login",nativeQuery = true,value = "select * from user where
username=:username and password=:password") User login(@Param("username")
String username,@Param("password") String password); }
　　

StudentService接口
package com.kude.stu.kudestu.stu.service; import
com.kude.stu.kudestu.stu.entity.Student; import
org.springframework.data.domain.Page; import java.util.List; public interface
StudentService { Student save(Student student); Student update(Student
student); void delete(Integer id); Student findStuById(Integer id);
List<Student> findStuByName(String name); /** * 分页查询所有数据 * @param page 当前页 *
@param pageSize 每页记录数 * @return */ Page<Student> findAll(int page,int
pageSize); }
　　

StudentServiceImpl实现类
package com.kude.stu.kudestu.stu.service; import
com.kude.stu.kudestu.stu.dao.StudentDao; import
com.kude.stu.kudestu.stu.entity.Student; import
org.springframework.beans.factory.annotation.Autowired; import
org.springframework.data.domain.Page; import
org.springframework.data.domain.PageRequest; import
org.springframework.data.domain.Pageable; import
org.springframework.stereotype.Service; import java.util.List; @Service public
class StudentServiceImpl implements StudentService { @Autowired private
StudentDao studentDao; @Override public Student save(Student student) { return
studentDao.save(student); } @Override public Student update(Student student) {
return studentDao.save(student); } @Override public void delete(Integer id) {
studentDao.deleteById(id); } @Override public Student findStuById(Integer id) {
return studentDao.findStuById(id); } @Override public List<Student>
findStuByName(String name) { return studentDao.findStuByName(name); } @Override
public Page<Student> findAll(int page, int pageSize) { Pageable pageable =
PageRequest.of(page,pageSize); return studentDao.findAll(pageable); } }
　　

UserService接口
package com.kude.stu.kudestu.stu.service; import
com.kude.stu.kudestu.stu.entity.User; import java.util.List; public interface
UserService { List<User> findAll(); User findUserById(int id); User save(User
user); User update(User user); User login(String username,String password); }
　　

UserServiceImpl实现类
package com.kude.stu.kudestu.stu.service; import
com.kude.stu.kudestu.stu.dao.UserDao; import
com.kude.stu.kudestu.stu.entity.User; import
org.springframework.beans.factory.annotation.Autowired; import
org.springframework.stereotype.Service; import java.util.List; @Service public
class UserServiceImpl implements UserService { @Autowired private UserDao
userDao; @Override public List<User> findAll() { return userDao.findAll(); }
@Override public User findUserById(int id) { return userDao.findUserById(id); }
@Override public User save(User user) { return userDao.save(user); } @Override
public User login(String username, String password) { return
userDao.login(username,password); } @Override public User update(User user) {
return null; } }
　　

StudentController类
package com.kude.stu.kudestu.stu.controller; import
com.kude.stu.kudestu.stu.entity.Student; import
com.kude.stu.kudestu.stu.service.StudentService; import
org.springframework.beans.factory.annotation.Autowired; import
org.springframework.data.domain.Page; import
org.springframework.web.bind.annotation.*; import
javax.servlet.http.HttpServletResponse; import java.util.List; @RestController
@RequestMapping("/s") public class StudentController { @Autowired private
StudentService studentService; /** * 添加学生 * @param student 要添加的学生对象 * @return
*/ @PostMapping("/add") public Student save(Student student){ return
studentService.save(student); } /** * 修改学生 * @param student 要修改的学生对象 * @return
*/ @PostMapping("/update") public Student update(Student student){ return
studentService.save(student); } /** * 删除学生 * @param id 要删除学生对象的id */
@GetMapping("/del/{id}") public String del(@PathVariable Integer id){
studentService.delete(id); return "yes"; } /** * 通过姓名查询学生信息 * @param name
要查询的学生的姓名 * @return */ @GetMapping("/findByName/{name}") public List<Student>
findStuByName(@PathVariable String name){ return
studentService.findStuByName(name); } /** * 分页查询 * @param page * * @return */
@GetMapping("/query") public Page<Student> findByPage(Integer page,
HttpServletResponse response){
response.setHeader("Access-Control-Allow-Origin","*"); if(page==null||page<=0){
page=0; }else { page -=1; } return studentService.findAll(page,5); } }
　　

UserController类
package com.kude.stu.kudestu.stu.controller; import
com.kude.stu.kudestu.stu.entity.User; import
com.kude.stu.kudestu.stu.service.UserService; import
org.springframework.beans.factory.annotation.Autowired; import
org.springframework.web.bind.annotation.PostMapping; import
org.springframework.web.bind.annotation.RequestMapping; import
org.springframework.web.bind.annotation.RequestMethod; import
org.springframework.web.bind.annotation.RestController; import java.util.List;
/** *处理用户信息的控制层 * @author 张玉贤 * */ @RestController @RequestMapping("/user")
public class UserController { @Autowired private UserService userService; /**
*查询用户的方法 * */ @RequestMapping(value = "/findAll",method = RequestMethod.POST)
public List<User> findAll(){ return userService.findAll(); } /** * 根据id查询用户 */
@RequestMapping(value = "/findById") public User findUserById(int id){ return
userService.findUserById(id); } /** * 注册用户 */ @RequestMapping(value =
"/reg",method = RequestMethod.POST) public User reg(User user){ return
userService.save(user); } /** * 用户登录 */ @PostMapping("/login") public User
login(String username,String password){ return
userService.login(username,password); } }
　　

五、前端分页实现

新建一个项目

百度搜索菜鸟教程并打开

在js目录下新建一个jquery.js

将所有内容全选复制粘贴进js目录下的jquery.js中保存即可

index.html
<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title></title> <script
src="js/jquery.js" type="text/javascript" charset="utf-8"></script> <script
type="text/javascript"> var page = 1; var tp = 0; $(function(){ showData(); });
function upPage(){ if(page>1){ page--; showData(); } } function downPage(){
if(page<tp){ page++; showData(); } } function showData(){ $.ajax({
url:'http://localhost:8888/stu/s/query?page='+page, success:function(result){
var rel = result.content; tp = result.totalPages; var htmlStr = "<table
width='80%' algin='center' border='1'>"+
"<tr><th>ID</th><th>姓名</th><th>年龄</th><th>性别</th><th>操作</th></tr>"; for(var
i=0;i<rel.length;i++){ var stu = rel[i]; htmlStr +=
"<tr><td>"+stu.id+"</td><td>"+stu.name+"</td><td>"+stu.age+"</td><td>"+stu.sex+"</td>"+
"<td><a href=''>修改</a> <a
href='http://localhost:8888/stu/s/del/"+stu.id+"'>删除</a></td></tr>"; } htmlStr
+= "</table>"; $("#show").html(htmlStr); } }); } </script> </head> <body> <h1
style="text-align: center;">学生管理系统</h1> <hr> <a href="add.html">添加学生</a> <div
id="show"> </div> <a href="javascript:upPage();">上一页</a> <a
href="javascript:downPage();">下一页</a> </body> </html>
　　

add.html
<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title></title> </head>
<body> <h1>添加学生</h1> <hr> <form action="http://localhost:8888/stu/s/add"
method="post"> <p> 姓名：<input type="text" name="name" /> </p> <p> 年龄：<input
type="text" name="age" /> </p> <p> 性别： <input type="radio" name="sex" value="男"
checked />男 <input type="radio" name="sex" value="女" />女 </p> <p> <input
type="submit" value="保存" /> </p> </form> </body> </html>
　　

六、页面显示

七、Postman测试

User测试

Student测试

测试全部通过，很开心，很激动！！！！

认真看过此篇的小伙伴，如果对你有收获，请点击旁边的小手推荐一下，如果有误，欢迎指正，谢谢！

版权声明：此篇为本人原创，转载请标明出处https://www.cnblogs.com/zyx110/
<https://www.cnblogs.com/zyx110/>

热门工具换一换