前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >用SpringBoot+MySql+JPA实现对数据库的增删改查和分页

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

作者头像
泰斗贤若如
发布2019-06-18 19:02:32
1.9K0
发布2019-06-18 19:02:32
举报
文章被收录于专栏:泰斗贤若如

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

     JPA是Java Persistence API的简称,中文名Java持久层API,是JDK 5.0注解或XML描述对象-关系表的映射关系,并将运行期的实体对象持久化到数据库中。   使用Springboot和jpa对数据库进行操作时,能够大大减少我们的工作量,在jpa中,已经在底层封装好了增删查的功能和sql语句,可以使我们进行快速开发

项目流程

一、新建一个项目

 二、配置文件

代码语言:javascript
复制
#数据源配置
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
代码语言:javascript
复制
<?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实体类

代码语言:javascript
复制
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实体类

代码语言:javascript
复制
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接口

代码语言:javascript
复制
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接口

代码语言:javascript
复制
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接口

代码语言:javascript
复制
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实现类

代码语言:javascript
复制
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接口

代码语言:javascript
复制
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实现类

代码语言:javascript
复制
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类

代码语言:javascript
复制
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类

代码语言:javascript
复制
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

代码语言:javascript
复制
<!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

代码语言:javascript
复制
<!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测试

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2019-05-02 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 使用SpringBoot+Mysql+JPA实现对数据库的增删改查和分页
  • 项目流程
    • 一、新建一个项目
      •  二、配置文件
        • 三、Mysql数据库
          • 四、后端实现
            •  student实体类
              •   user实体类
                • StudentDao接口
                  • UserDao接口
                    • StudentService接口
                      • StudentServiceImpl实现类
                        • UserService接口
                          • UserServiceImpl实现类
                            • StudentController类
                              • UserController类
                                • 五、前端分页实现
                                  •  index.html
                                    • add.html
                                      • 六、页面显示
                                        • 七、Postman测试
                                          • User测试
                                            • Student测试
                                            领券
                                            问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档