Spring Boot 和 MyBatis 对 MySQL 数据库进行 CRUD 操作

在这篇文章中，我们将使用 Spring Boot 和 MyBatis 对 MySQL 数据库进行 CRUD 操作。MyBatis 是一个 Java持久性框架，它使用注释或 XML 描述符将对象与存储过程或 SQL 语句耦合在一起。

准备

我们将根据上一篇文章中的设置和代码开始配置。

项目结构

在我们开始之前，项目结构如下所示：

├── java
│   └── merikanto
│       └── demo
│           ├── controller
│           │   ├── HelloController.java
│           │   ├── JdbcController.java
│           │   └── MyBatisController.java
│           ├── dao
│           │   └── UserDao.java
│           ├── entity
│           │   └── User.java
│           └── DemoApplication.java
└── resources
    ├── application.properties
    └── mapper
        └── UserDao.xml

MySQL 设置与上一篇文章中提到的相同。还请确保包括两个类HelloController和JdbcController.

Pom 依赖

将以下内容添加到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.5.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>merikanto</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.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
        
		<!-- 引入 MyBatis 场景启动器，包含其自动配置类及 MyBatis 3 相关依赖 -->
		<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>
		</dependency>
        
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
    
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

application.properties

application.properties在下面配置文件resources：

spring.datasource.url=jdbc:mysql://localhost:3306/demo_springboot?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=

mybatis.mapper-locations=classpath:mapper/*Dao.xml

添加@MapperScan

添加@MapperScan到主类：

package merikanto.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.mybatis.spring.annotation.MapperScan;

@SpringBootApplication
@MapperScan("merikanto.demo.dao")
public class DemoApplication {

	public static void main(String[] args) {
		System.out.println("[+] STARTING Spring Boot...");
		SpringApplication.run(DemoApplication.class, args);
	}
}

MyBatis 的 CRUD

在关于Hibernate 配置的帖子中，我提到了 CRUD 的含义：创建、读取、更新、删除。

现在我们已经完成了准备工作，我们可以继续设置 MyBatis。

用户实体

首先我们创建User类用户entity包。

package merikanto.demo.entity;

public class User {

    private Integer id;
    private String name;
    private String password;

    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 String getPassword() {
        return password; }

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

用户道

然后我们UserDao在 package 下添加dao，定义 CRUD 操作的接口：

package merikanto.demo.dao;

import merikanto.demo.entity.User;

import java.util.List;

public interface UserDao {
  
	// @return
    List<User> findAllUsers();

	// @param User
	// @return
    int insertUser(User User);

	// @param User
	// @return
    int updUser(User User);

	// @param id
	// @return
    int delUser(Integer id);
}

用户道.xml

UserDao.xml在下面添加/resources/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="merikanto.demo.dao.UserDao">
    <resultMap type="merikanto.demo.entity.User" id="UserResult">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="password" column="password"/>
    </resultMap>

    <select id="findAllUsers" resultMap="UserResult">
        select id,name,password from user
        order by id desc
    </select>


    <insert id="insertUser" parameterType="merikanto.demo.entity.User">
        insert into user(name,password)
        values(#{name},#{password})
    </insert>

    <update id="updUser" parameterType="merikanto.demo.entity.User">
        update user
        set
        name=#{name},password=#{password}
        where id=#{id}
    </update>

    <delete id="delUser" parameterType="int">
        delete from user where id=#{id}
    </delete>

</mapper>

CRUD 的 MyBatisController

MyBatisController在 CRUD下添加controller。

package merikanto.demo.controller;

import merikanto.demo.dao.UserDao;
import merikanto.demo.entity.User;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

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

@RestController
public class MyBatisController {

    @Resource
    UserDao userDao;

    // === READ === 
    @GetMapping("/users/mybatis/queryAll")
    public List<User> queryAll() {
        return userDao.findAllUsers();
    }

    // === CREATE === 
    @GetMapping("/users/mybatis/insert")
    public Boolean insert(String name, String password) {
        if (StringUtils.isEmpty(name) || StringUtils.isEmpty(password)) {
            return false;
        }
        User user = new User();
        user.setName(name);
        user.setPassword(password);
        return userDao.insertUser(user) > 0;
    }

    // === UPDATE === 
    @GetMapping("/users/mybatis/update")
    public Boolean insert(Integer id, String name, String password) {
        if (id == null || id < 1 || StringUtils.isEmpty(name) || StringUtils.isEmpty(password)) {
            return false;
        }
        User user = new User();
        user.setId(id);
        user.setName(name);
        user.setPassword(password);
        return userDao.updUser(user) > 0;
    }

    // === DELETE === 
    @GetMapping("/users/mybatis/delete")
    public Boolean insert(Integer id) {
        if (id == null || id < 1) {
            return false;
        }
        return userDao.delUser(id) > 0;
    }
}

测试

在浏览器中使用以下命令来测试 CRUD：

/users/mybatis/insert?name=merikanto&password=123	# Create

/users/mybatis/insert?name=mybatis&password=123	# Create

/users/mybatis/queryAll	# Read

/users/mybatis/update?id=2&name=mybatis&password=456	# Update

/users/mybatis/delete?id=2	# Delete