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

一：环境配置

idea 2018 3.4
jdk 1.8
maven 3.53
mysql 5.7

由于此文是从草稿箱中捞出来的，是当年一位老师带我上手Spring Boot写的，年代久远，sql文件和源码我都已经找不到了，我也懒得对着pojo再建，而且发现倚赖中和配置文件中有一些用不到的东西，就先放那吧，不影响运行?

二：项目结构

三：配置文件

1. properties

#数据源配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName=com.mysql.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=/kude

#配置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

#redis的配置
# Redis_config
# Redis数据库索引（默认为0）
spring.redis.database=0
# Redis服务器地址
spring.redis.host=127.0.0.1
# Redis服务器连接端口
spring.redis.port=6379
# Redis服务器连接密码（默认为空）
spring.redis.password=123456
# 连接超时时间（毫秒）
spring.redis.timeout=3600
# 连接池最大连接数（使用负值表示没有限制）
spring.redis.jedis.pool.max-active=8
# 连接池最大阻塞等待时间（使用负值表示没有限制）
spring.redis.jedis.pool.max-wait=-1
# jedis超时
spring.redis.jedis.shutdown-timeout=100
# 连接池中的最大空闲连接
spring.redis.jedis.pool.max-idle=8
# 连接池中的最小空闲连接
spring.redis.jedis.pool.min-idle=0

2.pom 倚赖

<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/redis.clients/jedis -->
        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
            <version>2.9.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
            <version>2.4.2</version>
        </dependency>

        <!--springboot操作redis的组件-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>

        <!--添加糊涂工具类-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.5.6</version>
        </dependency>

        <!--javamail依赖用来发送电子邮件-->
        <dependency>
            <groupId>javax.mail</groupId>
            <artifactId>mail</artifactId>
            <version>1.4.7</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.4</version>
        </dependency>

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.10</version>
        </dependency>
    </dependencies>

四：entity层

1. Student

package com.example.sdudent_02_spring.example.entity;

import javax.persistence.*;

@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 Student() {
    }

    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;
    }
}

2. User

package com.example.sdudent_02_spring.example.entity;

import javax.persistence.*;

@Entity
@Table(name = "user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String username;
    private String password;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer 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;
    }

    public User() {
    }
}

五:dao层

1. StudentDao

package com.example.sdudent_02_spring.example.dao;

import com.example.sdudent_02_spring.example.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> {

    /*
    * 需要自定义部分方法，与复杂sql语句
    * */
    Student findStudentById(Integer id);

    @Query(name = "findStuByName",nativeQuery = true,value =
            "select * from student where name=:name")
    List<Student> findStuByName(@Param("name") String name);

}

2. UserDao

package com.example.sdudent_02_spring.example.dao;

import com.example.sdudent_02_spring.example.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> {

    User findUserById(Integer 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);

}

六 service层

1. 接口 StudentService

package com.example.sdudent_02_spring.example.service;

import com.example.sdudent_02_spring.example.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 sid);
    Student findStuById(Integer id);
    List<Student> findStuByName(String name);

    /**
     * 分页查询所有数据
     * @param page 当前页
     * @param pageSize 每页记录数
     * @return
     */
    Page<Student> findAll(int page, int pageSize);

}

2. 实现接口类StudentServiceImpl

package com.example.sdudent_02_spring.example.service;

import com.example.sdudent_02_spring.example.dao.StudentDao;
import com.example.sdudent_02_spring.example.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 sid) {
        studentDao.deleteById(sid);
    }

    @Override
    public Student findStuById(Integer sid) {
        return studentDao.findStudentById(sid);
    }

    @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);
    }
}

3. 接口UserService

package com.example.sdudent_02_spring.example.service;

import com.example.sdudent_02_spring.example.entity.User;
import org.springframework.data.domain.Page;

import java.util.List;

public interface UserService {

    List<User> findAll();
    User findUserById(Integer id);
    User save(User user);
    User update(User user);
    void delete(int id);
    User login(String username, String password);
    Page<User> findByPage(int page, int limit);

}

4. 接口实现类UserServiceImpl

package com.example.sdudent_02_spring.example.service;

import com.example.sdudent_02_spring.example.dao.UserDao;
import com.example.sdudent_02_spring.example.entity.User;
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.data.domain.Sort;
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(Integer id) {
        return userDao.findUserById(id);
    }


    @Override
    public User save(User user) {
        return userDao.save(user);
    }

    @Override
    public User update(User user) {
        return userDao.save(user);
    }

    @Override
    public void delete(int id) {
        userDao.deleteById(id);
    }

    @Override
    public User login(String username, String password) {
        return userDao.login(username,password);
    }

    @Override
    public Page<User> findByPage(int page, int pageSize) {
        Pageable pageable = PageRequest.of(page,pageSize,new Sort(Sort.Direction.ASC,"id"));
        return userDao.findAll(pageable);
    }
}

六：controller层

1. studentController

package com.example.sdudent_02_spring.example.controller;
import com.example.sdudent_02_spring.example.entity.Student;
import com.example.sdudent_02_spring.example.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
     * @return
     */
    @GetMapping("/del/{id}")
    public String del(@PathVariable int id){
        studentService.delete(id);
        return "yes";
    }

    @GetMapping("/findByName")
    public List<Student> findByName(String name,HttpServletResponse response){
        response.setHeader("Access-Control-Allow-Origin","*");
        return studentService.findStuByName(name);
    }

    @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,3);
    }
}

2. UserController

package com.example.sdudent_02_spring.example.controller;

import com.example.sdudent_02_spring.example.entity.User;
import com.example.sdudent_02_spring.example.service.UserService;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.util.List;

/**
 * 处理用户信息的控制层
 * @author zt
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    /**
     * 查询用户的方法
     */
    @RequestMapping("/findAll")
    public List<User> findAll(){
        return userService.findAll();
    }

    /**
     * 根据ID查询用户
     */
    @RequestMapping("/query")
    public User findById(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);
    }

    /**
     * 分页查询
     */
    @RequestMapping("/page")
    public Page<User> findByPage(Integer p, HttpServletResponse response){
        response.setHeader("Access-Control-Allow-Origin","*");
        if(p==null || p<=0){
            p = 0;
        }else{
            p-=1;
        }
        return userService.findByPage(p,2);
    }

    /**
     * 修改
     */
    @RequestMapping("/updata")
    public User update(User user){
        return userService.update(user);
    }

    /**
     * 删除
     */
    @RequestMapping("/del")
    public String del(int id){
        userService.delete(id);
        return "yes";
    }

}

七：前端界面与实现

1. add.html

<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<title></title>
	</head>
	<body>
		<h1>添加学生</h1>
		<hr>
		<form action="http://localhost:8888/kude/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>

2.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;
			
			
			/*
			 DOM加载完成后执行的回调函数，并且只会执行一次。
			 */
			$(function(){
				showData();
			});
			
			function upPage(){
				if(page>1){
					page--;
					showData();
				}
				
			}
			
			function downPage(){
				if(page<tp){
					page++;
					showData();
				}
				
			}
			
			function showData(){
				/* jQuery ajax() 方法 */
				$.ajax({
					url:'http://localhost:8888/kude/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>&nbsp;<a href='http://localhost:8888/kude/s/del/"+stu.id+"'>删除</a></td></tr>";
						}
						
						htmlStr += "</table>";
						
						$("#show").html(htmlStr);
					}
				});
			}
			
			
			/* 显示查询结果 */
			function fun() {
				var val = $("#textId").val();
				$.ajax({
						url:'http://localhost:8888/kude/s/findByName?name='+val,
						success:function(result){
							/* var rel = result.content;
							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>&nbsp;<a href='http://localhost:8888/kude/s/del/"+stu.id+"'>删除</a></td></tr>";
							}
							
							htmlStr += "</table>"; */
							
							$("#showFind").html(result);
						}
				})
			}
		</script>
		
	</head>
	<body>
		
		<h1 style="text-align: center;">学生管理系统</h1>
		<hr>
	
		<div id="findStudent" style="width: 80%,height = 100px;">
			<input type="text" id="textId">
			<input type="button" value="查找" onclick="fun()">
		</div>
						
		<!-- 显示查询结果 -->
		<div id="showFind">				
		</div>
		
		
		<div id="show">
			
		</div>
		
		<a href="javascript:upPage();">上一页</a>&nbsp;&nbsp;<a href="javascript:downPage();">下一页</a>
		<br>
		<a href="add.html">添加学生</a>
	</body>
</html>