用SpringBoot+MySql+JPA实现对数据库的增删改查和分页
使用SpringBoot+Mysql+JPA实现对数据库的增删改查和分页
JPA是Java Persistence API的简称,中文名Java持久层API,是JDK 5.0注解或XML描述对象-关系表的映射关系,并将运行期的实体对象持久化到数据库中。
使用Springboot和jpa对数据库进行操作时,能够大大减少我们的工作量,在jpa中,已经在底层封装好了增删查的功能和sql语句,可以使我们进行快速开发
项目流程
一、新建一个项目
二、配置文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | #数据源配置 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | <?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/> <!-- lookup parent from repository --> </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> <!-- druid数据库连接池--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version> 1.1 . 10 </version> </dependency> <!-- https: //mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter --> <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实体类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | 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实体类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | 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接口
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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接口
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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接口
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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实现类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | 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接口
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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实现类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | 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类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | 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类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | <!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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | <!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/

作者:泰斗贤若如
微信公众号:去有风的地方飞翔
Github:https://github.com/zyx110
有事微信:zyxt1637039050
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接,否则保留追究法律责任的权利。

我不能保证我所说的都是对的,但我能保证每一篇都是用心去写的,我始终认同: “分享的越多,你的价值增值越大”,我们一同在分享中进步,在分享中成长,越努力越幸运。再分享一句话“十年前你是谁,一年前你是谁,甚至昨天你是谁,都不重要。重要的是,今天你是谁,以及明天你将成为谁。”
人生赢在转折处,改变从现在开始!
支持我的朋友们记得点波推荐哦,您的肯定就是我前进的动力。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 周边上新:园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源!
· 分享 3 个 .NET 开源的文件压缩处理库,助力快速实现文件压缩解压功能!
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试?测试工程师会被淘汰吗?