springboot如何对本地数据库增删改查_用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

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

org.springframework.boot

spring-boot-starter-parent

2.1.4.RELEASE

com.kude.stu

kudestu

0.0.1-SNAPSHOT

kudestu

Demo project for Spring Boot

1.8

org.springframework.boot

spring-boot-starter-data-jpa

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-devtools

runtime

mysql

mysql-connector-java

runtime

org.springframework.boot

spring-boot-starter-test

test

com.alibaba

druid

1.1.10

com.github.pagehelper

pagehelper-spring-boot-starter

1.2.10

org.springframework.boot

spring-boot-maven-plugin

三、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 findStuById(Integer id);

@Query(name = "findStuByName",nativeQuery = true,value = "select * from student where name=:name ")

List 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 {

/**

* 根据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 findStuByName(String name);

/**

* 分页查询所有数据

* @param page 当前页

* @param pageSize 每页记录数

* @return

*/

Page 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 findStuByName(String name) {

return studentDao.findStuByName(name);

}

@Override

public Page 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 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 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 findStuByName(@PathVariable String name){

return studentService.findStuByName(name);

}

/**

* 分页查询

* @param page

*

* @return

*/

@GetMapping("/query")

public Page 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 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

var page = 1;

var tp = 0;

$(function(){

showData();

});

function upPage(){

if(page>1){

page--;

showData();

}

}

function downPage(){

if(page

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 = "

"

ID姓名年龄性别操作";

for(var i=0;i

var stu = rel[i];

htmlStr += "

"+stu.id+""+stu.name+""+stu.age+""+stu.sex+""+

"

修改  删除";

}

htmlStr += "

";

$("#show").html(htmlStr);

}

});

}

学生管理系统

---

添加学生

上一页  下一页

add.html

添加学生

---

姓名：

年龄：

性别：

男

女

六、页面显示

七、Postman测试

User测试

Student测试

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

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