使用idea编辑器Spring boot在MySQL数据库当中的增删改查操作

最新推荐文章于 2025-04-04 10:00:00 发布

平平常常一般牛

最新推荐文章于 2025-04-04 10:00:00 发布

阅读量471

点赞数

分类专栏： Spring Boot 文章标签： sql java spring html5 mysql

折扇戏美人

本文链接：https://blog.csdn.net/CSDN_java1005/article/details/106118563

版权

Spring Boot 专栏收录该内容

36 篇文章

订阅专栏

Spring boot在MySQL数据库当中的增删改查操作

前言

我这里用的是spring boot + thymeleaf

-Pom.xml 依赖

<?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 https://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.2.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.sdbairui</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</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-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-thymeleaf -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
            <version>2.2.7.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>


        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.9</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-devtools -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <version>2.2.7.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Entity包中的Student类 package com.sdbairui.demo.Entity;

package com.sdbairui.demo.Entity;

import javax.persistence.*;
import java.util.Date;
@Table/*声明此对象映射到数据库的数据表，通过它可以为实体指定表(talbe)*/
@Entity(name="student")
/*@Entity 表明该类 (Student) 为一个实体类，它默认对应数据库中的表名是student*/
public class Student {
    @Id     /*@Id 注解可将实体Bean中某个属性定义为主键 */
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    /*@GeneratedValue注解存在的意义主要就是为一个实体生成一个唯一标识的主键*/
    private int id;
    private String name;
    private int score;
    private Date birthday;
    private int sid;

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

Classss类

package com.sdbairui.demo.Entity;

import javax.persistence.*;

@Table
@Entity(name="classes")
public class Classes {
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private int cid;
    private int cname;

    public int getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }

    public int getCname() {
        return cname;
    }

    public void setCname(int cname) {
        this.cname = cname;
    }
}

配置信息（连接数据库）application.yml

Spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver

Server:
  port: 8080

DAO层（便于进行数据库的操作）Classes类接口

package com.sdbairui.demo.Dao;

import com.sdbairui.demo.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;
import java.util.Map;

public interface IStudent extends JpaRepository<Student,Integer> {
    @Query(value="SELECT * FROM student INNER JOIN classes ON student.sid=classes.cid",nativeQuery=true)
    /*
    * @Query:注解sql语句的
    * */
    List<Map<String,Object>> findCname();

    @Query(value="SELECT * FROM student INNER JOIN classes ON student.sid=classes.cid where id=?1 ",nativeQuery=true)
    List<Map<String,Object>> findById(int id);

    @Query(value="select * from student inner join classes  on student .sid=classes.cid where name like concat('%',:name,'%') ",nativeQuery=true)
    List<Map<String,Object>> findName(@Param("name")String name);
    /* @Param注解单一属性，便于取值*/
}

Student类的接口

package com.sdbairui.demo.Dao;

import com.sdbairui.demo.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;
import java.util.Map;

public interface IStudent extends JpaRepository<Student,Integer> {
    @Query(value="SELECT * FROM student INNER JOIN classes ON student.sid=classes.cid",nativeQuery=true)
    /*
    * @Query:注解sql语句的
    * */
    List<Map<String,Object>> findCname();

    @Query(value="SELECT * FROM student INNER JOIN classes ON student.sid=classes.cid where id=?1 ",nativeQuery=true)
    List<Map<String,Object>> findById(int id);

    @Query(value="select * from student inner join classes  on student .sid=classes.cid where name like concat('%',:name,'%') ",nativeQuery=true)
    List<Map<String,Object>> findName(@Param("name")String name);
    /* @Param注解单一属性，便于取值*/
}

Service服务层–ClassesService类

package com.sdbairui.demo.Service;

import com.sdbairui.demo.Dao.IClasses;
import com.sdbairui.demo.Entity.Classes;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service  /*指明咱们这个文件是业务层，是服务层指明咱们这个文件是业务层，是服务层*/

public class ServiceClasses {

    @Resource /*@Resource：自动注入的注解：就相当给Dao层实例化。*/

    IClasses classes;
    public List<Classes> finAll(){
        return classes.findAll();
    }

}

StudentService类

package com.sdbairui.demo.Service;

import com.sdbairui.demo.Dao.IStudent;
import com.sdbairui.demo.Entity.Student;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@Service
public class ServiceStudent {
    @Resource
    IStudent studentDao;
    public List<Map<String,Object>> findCname(){
        return studentDao.findCname();
    }

    public void doCreate(Student student){
        studentDao.save(student);
    }

    public void doDelete(int id){
        studentDao.deleteById(id);
    }

    public List<Map<String,Object>> findById(int id){
        return studentDao.findById(id);
   }

    public List<Map<String,Object>> findName(String name){
        return studentDao.findName(name);
    }
}

Controller控制层-StudentController类

package com.sdbairui.demo.Controller;

import com.sdbairui.demo.Entity.Classes;
import com.sdbairui.demo.Entity.Student;
import com.sdbairui.demo.Service.ServiceClasses;
import com.sdbairui.demo.Service.ServiceStudent;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Controller/*@Controller：用于定义控制器类，在spring项目中由控制器负责将用户发来的URL请求转发到对应的服务接口（service层）*/
@RequestMapping("/student")/*@RequestMapping：提供路由信息，负责URL到Controller中的具体函数的映射。*/
/*@RestController：用于标注控制层组件(如struts中的action)，@ResponseBody和@Controller的合集。 返回信息*/
public class StudentController {
    @Autowired          /*Autowired：自动导入依赖的bean*/
    ServiceStudent serviceStudent;
    @Autowired
    ServiceClasses serviceClasses;

    @RequestMapping("/toIndex")
    public String toIndex(Model model){
        List<Map<String,Object>>list=serviceStudent.findCname();
        model.addAttribute("student",list);
        return "index";
    }
    @RequestMapping("/toAdd")
    public String toAdd(Model model){
        List<Classes> list=serviceClasses.finAll();
        model.addAttribute("classes",list);
    return "Student_Add";
    }
    @RequestMapping("/doAdd")
    public String doAdd(HttpServletRequest request) throws Exception {
        String name=request.getParameter("name");
        int score=Integer.parseInt(request.getParameter("score"));
        Date birthday=new SimpleDateFormat("yyyy-MM-dd").parse(request.getParameter("birthday"));
        int sid=Integer.parseInt(request.getParameter("sid"));
        Student student=new Student();
        student.setName(name);
        student.setScore(score);
        student.setBirthday(birthday);
        student.setSid(sid);
        serviceStudent.doCreate(student);
        return "redirect:/student/toIndex";
    }
    @RequestMapping("/doDelete")
    public String doDelete(HttpServletRequest request){
        int id=Integer.parseInt(request.getParameter("id"));
        serviceStudent.doDelete(id);
        return "redirect:/student/toIndex";
    }
    @RequestMapping("/toUpdate")
    public String toUpdate(HttpServletRequest request,Model model){
        int id=Integer.parseInt(request.getParameter("id"));
        HttpSession session=request.getSession();
        session.setAttribute("id",id);
        List<Map<String,Object>>list=serviceStudent.findById(id);
        List<Classes> list1=serviceClasses.finAll();
        model.addAttribute("classes",list1);
        model.addAttribute("student",list);
        return "Student_update";

    }
    @RequestMapping("/doUpdate")
    public String doUpdate(HttpServletRequest request) throws ParseException {
        int id=(int)request.getSession().getAttribute("id");
        String name=request.getParameter("name");
        int score=Integer.parseInt(request.getParameter("score"));
        Date birthday=new SimpleDateFormat("yyyy-MM-dd").parse(request.getParameter("birthday"));
        int sid=Integer.parseInt(request.getParameter("sid"));
        Student student=new Student();
        student.setId(id);
        student.setName(name);
        student.setScore(score);
        student.setBirthday(birthday);
        student.setSid(sid);
        serviceStudent.doCreate(student);
        return "redirect:/student/toIndex";
    }
    @RequestMapping("/findName")
    public String findName(HttpServletRequest request,Model model){
        String name=request.getParameter("name");
        List<Map<String,Object>>list=serviceStudent.findName(name);
        model.addAttribute("student",list);
        List<Classes>list1=serviceClasses.finAll();
        model.addAttribute("classes",list1);
        return "Student_findName";
    }
}

显示层

index.html 查询全部界面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<table border="1" width="80%"height="80%">
    <tr >
        <td colspan="6">
            <form action="/student/findName">
                <input type="text" name="name">
                <input type="submit"value="查询">
            </form>
        </td>
    </tr>
    <tr>
        <td>编号</td>
        <td>姓名</td>
        <td>成绩</td>
        <td>生日</td>
        <td>班级</td>
        <td>操作</td>
    </tr>
    <tr th:each="student:${student}">
        <td th:text="${student.id}"></td>
        <td th:text="${student.name}"></td>
        <td th:text="${student.score}"></td>
        <td th:text="${student.birthday}"></td>
        <td th:text="${student.cname}"></td>
        <td>
            <a href="/student/toAdd">添加</a>
            <a th:href="@{/student/doDelete(id=${student.id})}">删除</a>
            <a th:href="@{/student/toUpdate(id=${student.id})}">修改</a>
        </td>
    </tr>
</table>
</body>
</html>

Student_Add 添加页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>

<form action="/student/doAdd">
    姓名：<input type="text" name="name"><br>
    成绩：<input type="text" name="score"><br>
    生日：<input type="Date" name="birthday"><br>
    班级：<select name="sid" id="">
             <option th:each="classes:${classes}" th:value="${classes.cid}" th:text="${classes.cname}"></option>
         </select>
    <br><input type="submit" value="提交">
</form>
</body>
</html>

Student_findName 根据姓名的模糊查询

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<table border="1" width="80%"height="80%">
    <tr >
        <td colspan="6">
            <form action="/student/findName">
                <input type="text" name="name">
                <input type="submit"value="查询">
            </form>
        </td>
    </tr>
    <tr>
        <td>编号</td>
        <td>姓名</td>
        <td>成绩</td>
        <td>生日</td>
        <td>班级</td>
        <td>操作</td>
    </tr>
    <tr th:each="student:${student}">
        <td th:text="${student.id}"></td>
        <td th:text="${student.name}"></td>
        <td th:text="${student.score}"></td>
        <td th:text="${student.birthday}"></td>
        <td th:text="${student.cname}"></td>
        <td>
            <a href="/student/toAdd">添加</a>
            <a th:href="@{/student/doDelete(id=${student.id})}">删除</a>
            <a th:href="@{/student/toUpdate(id=${student.id})}">修改</a>
        </td>
    </tr>
</table>
</body>
</html>

Student_update 修改页面获取信息后进行修改

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form action="/student/doUpdate" th:each="student:${student}">
    姓名：<input type="text" name="name" th:value="${student.name}"><br>
    成绩：<input type="text" name="score" th:value="${student.score}"><br>
    生日：<input type="Date" name="birthday" th:value="${student.birthday}"><br>
    班级：<select name="sid" id="">
    <option th:each="classes:${classes}" th:value="${classes.cid}" th:text="${classes.cname}"></option>
</select>
    <br><input type="submit" value="提交">
</form>
</body>
</html>