springboot+mybatis 实现对数据库的增删改查

一、插入数据库

0、在pom.xml文件里面导入mysql驱动

 <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

在pom.xml里面导入mybatis依赖

      <!--导入mybatis依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--导入mybatis依赖-->

1、连接数据库的配置

在application.properties里面

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc
spring.datasource.url=jdbc:mysql://localhost:3306/idea?serverTimezone=UTC&characterEncoding=utf-8

2、创建表

3、创建bean类

package mybatiscrud.model;

public class user {
    private int id;
    private String username;
    private String password;
    private String tell;

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

    public void setPassword(String password) {
        this.password = password;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setTell(String tell) {
        this.tell = tell;
    }

    public int getId() {
        return id;
    }

    public String getPassword() {
        return password;
    }

    public String getUsername() {
        return username;
    }

    public String getTell() {
        return tell;
    }
}

4、dao层

package com.example.mybatiscrud.dao;
import com.example.mybatiscrud.model.user;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserDao {
    //插入
     int insert(user u);
    //删除
    int delete(int id);
    //修改
    int update(user u);
    //查询all
    List<user> query();
    //条件查询
    List<user> queryby(String u);
}

5、服务层

package com.example.mybatiscrud.service;
import com.example.mybatiscrud.dao.UserDao;
import com.example.mybatiscrud.model.user;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
    @Autowired
    private UserDao ud;
    public int insert(user u){
        return ud.insert(u);
    };
    //删除
    public int delete(int id){
        return ud.delete(id);
    };
    //修改
   public int update(user u){
       return ud.update(u);
   };
    //查询all
    public List<user> query(){
        return ud.query();
    };
    //条件查询
    public user queryby(int c){
        System.out.println("service:"+c);
        return ud.queryby(c);
    };
}

6、控制层

package com.example.mybatiscrud.control;
import com.example.mybatiscrud.model.user;
import com.example.mybatiscrud.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import java.util.List;
@Controller
@RequestMapping("/user")
public class UserControl {
    /*author:tjn
      date:2020.09.11
       arm:springboot+mybatis的增删改查询
     */
    @Autowired
    private UserService us;
    //增加
    @RequestMapping("/insert")
    public String adduser(@RequestBody user u){
        int i=us.insert(u);
        return "index";
    }
    //删除
    @RequestMapping("/delete")
    public String deleteuser(@RequestParam("id")  int id){
        us.delete(id);
        //页面跳转到查询所有的页面
        return "redirect:/user/query";
    }

    //修改
    @RequestMapping("/update")
    public String update(user u){
         us.update(u);
        return "redirect:/user/query";
    }

    //查询所有
    @RequestMapping("/query")
    public String queryall(Model model){
        List<user> list=us.query();
        model.addAttribute("list",list);
        return "/all";
    }

    //条件查询
    @RequestMapping("/qby")
    public String query(@RequestParam int id,Model model){
        user u=us.queryby(id);
        //把这个user显示在页面上面
        model.addAttribute("user",u);
        return "/update";
    }
    //
}

7、mybatis操作数据库的代码

注意这个里面的id 名称要与那个dao层接口类的方法名称一致，不然会报错Invalid bound statement (not found):

<?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.example.mybatiscrud.dao.UserDao">
    <select id="query" resultType="com.example.mybatiscrud.model.user">
        select * from user;
    </select>
    <select id="queryby" parameterType="integer" resultType="com.example.mybatiscrud.model.user">
        select * from user where id=#{id};
    </select>
    <update id="update" parameterType="com.example.mybatiscrud.model.user" >
        update user set username=#{username},password=#{password},tell=#{tell} where id=#{id};
    </update>
    <delete id="delete" parameterType="integer" >
        delete from user where id=#{id};
    </delete>
    <insert id="insert" parameterType="com.example.mybatiscrud.model.user" >
        insert into user(username,password,tell) values (#{username},#{password},#{tell});
    </insert>
</mapper>

配置这个mapper文件的位置

##配置一下mybatis的映射文件的位置
mybatis.mapper-locations=classpath:/mapper/*.xml

在入口类加上扫描bean类（就是与数据库表中属性对于的那个java类）

package com.example.mybatiscrud;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.example.mybatiscrud.model")
public class MybatiscrudApplication {
    public static void main(String[] args) {
        SpringApplication.run(MybatiscrudApplication.class, args);
    }
}

8、html页面的编写

首页

注意：我使用了thymeleaf模板，在第二行加上xmlns:th="http://www.thymeleaf.org" 语法才会生效

<!DOCTYPE html>
<html lang="utf-8" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>springboot+mybatis的增删改查</title>
</head>
<body>
<script src="https://cdn.staticfile.org/jquery/2.1.1/jquery.min.js"></script>
<h1>springboot+mybatis的增删改查</h1>
    <input type="text" name="username" placeholder="用户名" id="username"/><br>
    <input type="password" name="password" placeholder="" id="password"/><br>
    <input type="text" name="tell" placeholder="手机号码" id="tell"/><br>
    <button onclick="reg()">注册</button><br/>
<a href="http://localhost:8080/user/query/">查询所有信息</a><br/>
</body><script>
    function reg() {
        var username = $("#username").val();
        var password = $("#password").val();
        var tell = $("#tell").val();
        alert(tell);
        var data={
            "username": username,
            "password": password,
            "tell": tell
        };
        $.ajax({
            type: "post",　　　　　　　　　　//以POST的方式发送请求
            url: "http://localhost:8080/user/insert/",
            data: JSON.stringify(data),　　　　　//上面拼凑的链接及参数
            contentType: "application/json",
            dataType: "json",
            success: function (data) {},
        });
    }
</script>
</html>

显示表中所有数据的页面

<!DOCTYPE html >
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>所有用户信息</title>
</head>
<body>
<table>
    <tr><td>用户id</td><td>用户名</td><td>密码</td><td>手机号码</td><td>修改</td><td>删除</td></tr>
    <tr th:each="u,state:${list}">
        <td th:text="${u.id}"></td>
        <td th:text="${u.username}"></td>
        <td th:text="${u.password}"></td>
        <td th:text="${u.tell}"></td>
        <td ><a th:href="@{/user/qby(id=${u.id})}">更改</a></td>
        <td><a th:href="@{/user/delete(id=${u.id})}">删除</a></td>
    </tr>
</table>
</body>
<script>

</script>
</html>

修改页面

这里需要注意name="属性名称要与表中的属性一致" 不然会报错，thymeleaf模板不匹配

<!DOCTYPE html>
<html lang="utf-8" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>修改信息页面</title>
</head>
<body>
<form method="post" th:action="@{/user/update/}">
    id:<input type="hidden" name="id"   th:value="${user?.id}" />
    name:<input type="text" name="username" th:value="${user?.username}"/><br/>
    pass:<input type="text" name="password" th:value="${user?.password}"/><br/>
    tell:<input type="text" name="tell" th:value="${user?.tell}"/><br/>
    <input type="submit" value="确定修改"/>
</form>
</body>
</html>

页面效果如下

填写注册的信息，然后点击注册

数据库表中就会多出一行数据

点击查询所有信息

点击一行的更改

随意更改信息，点击确定更改

页面就会更新数据库信息

点击删除，就会出现

演示完毕，现在把application.properties文件完整的展示出来

##设置可以访问html页面
spring.thymeleaf.prefix=classpath:/templates/
##设置连接数据库的代码
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/idea?serverTimezone=UTC&characterEncoding=utf-8
##配置一下mybatis的映射文件的位置
mybatis.mapper-locations=classpath:/mapper/*.xml

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.3.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.example</groupId>
    <artifactId>mybatiscrud</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>mybatiscrud</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>


    <dependencies>

        <!--thymeleaf-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!--thymeleaf-->

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--连接数据库-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--连接数据库-->

       <!--导入mybatis依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--导入mybatis依赖-->

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </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>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.2</version>
        </dependency>
    </dependencies>

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

</project>

耗时两天，终于能够来龙去脉的了解了每一个环节的作用，环环相扣，感觉自己对springboot+mybatis有所了解了！

原文链接：https://blog.csdn.net/tian_jiangnan/article/details/108511070