spring boot增删改查示例

准备工作

这里我们使用spring boot和thymeleaf编写一个增删改查的示例。

创建一个spring boot模块,里面勾选上Lombok,web,Thymeleaf,mysql,mybatis。

这里还是使用之前的t_user表,下面是建表语句:

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

这里使用mybatis generator生成相应的文件,我们把mapper文件放到resource目录下,这样就不用在pom文件中添加标签将xml文件编译到target目录下了。

mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.monkey1024.crud.mapper.StudentMapper">

    <!--查询多条数据-->
    <!--resultType是数据返回放到哪个类型中-->
    <select id="selectAllStudent" resultType="com.monkey1024.crud.bean.Student">
        SELECT id,name,age,score FROM t_student
    </select>

    <select id="selectStudentById" resultType="com.monkey1024.crud.bean.Student">
        SELECT id,name,age,score FROM t_student WHERE id=#{id}
    </select>


    <!--修改-->
    <update id="updateStudent">
        UPDATE t_student SET name=#{name},age=#{age},score=#{score} WHERE id=#{id}
    </update>

    <!--删除-->
    <delete id="deleteStudent">
        DELETE FROM t_student Where id=#{id}
    </delete>

    <!--新增-->
    <insert id="addStudent">
        INSERT INTO t_student(name,age,score) VALUES (#{name},#{age},#{score})
        <selectKey resultType="int" keyProperty="id" order="AFTER">
            SELECT @@identity
        </selectKey>
    </insert>

</mapper>

dao

@Mapper//在dao接口上加入Mapper注解,这样springboot就可以扫描到
public interface StudentMapper {
    List<Student> selectAllStudent();

    Student selectStudentById(Integer id);

    void addStudent(Student student);

    void updateStudent(Student student);

    void deleteStudent(Integer id);
}

service实现类

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentMapper studentMapper;


    @Override
    public List<Student> selectAllStudent() {

        return studentMapper.selectAllStudent();
    }

    @Override
    public Student selectStudentById(Integer id) {

        return studentMapper.selectStudentById(id);
    }

    @Override
    public void addStudent(Student student) {
        studentMapper.addStudent(student);

    }

    @Override
    public void updateStudent(Student student) {
        studentMapper.updateStudent(student);
    }

    @Override
    public void deleteStudent(Integer id) {
        studentMapper.deleteStudent(id);
    }
}

controller

@Controller
public class StudentController {

    @Autowired
    private StudentService studentService;

    /**
     * 查询全部数据
     * @param model
     * @return
     */
    @RequestMapping("/index")
    public String index(Model model) {
        List<Student> studentList = studentService.selectAllStudent();
        model.addAttribute("studentList", studentList);

        return "index";
    }


    /**
     * 去修改页面
     * @param id
     * @param model
     * @return
     */
    @RequestMapping("/student/toUpdate")
    public String toUpdate(@RequestParam(value = "id") Integer id,Model model) {

        Student student = studentService.selectStudentById(id);

        model.addAttribute("student", student);

        return "student";

    }

    /**
     * 去添加页面
     * @return
     */
    @RequestMapping("/student/toAdd")
    public String toAdd(){
        return "student";
    }

    /**
     * 添加或修改
     * @param student
     * @return
     */
    @RequestMapping("/student/addOrUpdate")
    public String addOrUpdate(Student student) {
        if (student.getId() == null){
            //添加
            studentService.addStudent(student);
        }else {
            //修改
            studentService.updateStudent(student);
        }

        return "redirect:/index";
    }

    /**
     * 删除
     * @param id
     * @return
     */
    @RequestMapping("/student/delete")
    public String delete(@RequestParam("id") Integer id){
        studentService.deleteStudent(id);

        return "redirect:/index";
    }
}

yml配置文件

spring:
  datasource:
    username: root
    password: 111111
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
    type: com.alibaba.druid.pool.DruidDataSource
  thymeleaf:
    cache: false
    mode: LEGANCYHTML5

mybatis:
  type-aliases-package: com.monkey1024.bean
  mapper-locations: classpath:mappers/*.xml

#打印日志
logging:
  level:
   com.monkey1024.mapper: debug

前台index.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8"/>
    <link rel="stylesheet" href="/css/bootstrap.min.css"/>
    <script src="/js/jquery.min.js"></script>
    <script src="/js/bootstrap.min.js"></script>
    <title>Title</title>
</head>

<body style="'margin:50px;'">
<div><a href="/student/toAdd">添加用户</a></div>
<table class="table table-striped">
    <thead>
    <tr>
        <th>序号</th>
        <th>姓名</th>
        <th>年龄</th>
        <th>分数</th>
    </tr>
    </thead>

    <tbody>
    <tr th:each="stu:${studentList}">
        <td th:text="${stuStat.count}">1</td>
        <td th:text="${stu.name}">jack</td>
        <td th:text="${stu.age}">11</td>
        <td th:text="${stu.score}">98</td>
        <td>
            <a th:href="@{/student/toUpdate(id=${stu.id})}">修改</a>
            <a th:href="@{/student/delete(id=${stu.id})}">删除</a>
        </td>
    </tr>

    </tbody>
</table>

</body>
</html>

前台Student.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8"/>
    <link rel="stylesheet" href="/css/bootstrap.min.css"/>
    <script src="/js/jquery.min.js"></script>
    <script src="/js/bootstrap.min.js"></script>
    <title>Title</title>
</head>

<body th:style="'margin:50px;'">

<form action="/student/addOrUpdate" class="form-horizontal" role="form" method="post">
    <div class="form-group">
        <label for="name" class="col-sm-2 control-label">姓名</label>
        <div class="col-sm-10">
            <input type="text" class="form-control" id="name" name="name" placeholder="请输入姓名" th:value="${student?.name}"/>
        </div>
    </div>
    <div class="form-group">
        <label for="age" class="col-sm-2 control-label">年龄</label>
        <div class="col-sm-10">
            <input type="text" class="form-control" id="age" name="age" placeholder="请输入年龄" th:value="${student?.age}"/>
        </div>
    </div>
    <div class="form-group">
        <label for="score" class="col-sm-2 control-label">分数</label>
        <div class="col-sm-10">
            <input type="text" class="form-control" id="score" name="score" placeholder="请输入分数" th:value="${student?.score}"/>
        </div>
    </div>

    <div class="form-group">
        <div class="col-sm-offset-2 col-sm-10">
            <input type="hidden" id="id" name="id" th:value="${student?.id}"/>
            <button type="submit" class="btn btn-default">提 交</button>
        </div>
    </div>
</form>

</body>
</html>