SpringBoot 和 MyBatis 基础示例

SpringBoot 和 MyBatis 基础示例

本篇文档将指导你如何使用 SpringBoot、Mybatis 和 Intellij IDEA 构建一个简单的应用程序，并实现 CRUD（创建、读取、更新、删除）功能。

开始之前

本篇教程涉及到的软件介绍如下：

MyBatis：MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。我们只需要关注项目中的 SQL 本身。
Intellij IDEA：IntelliJ IDEA 是一种商业化销售的 Java 集成开发环境（Integrated Development Environment，IDE）工具软件。它所拥有诸多插件，可以提高我们的工作效率。
Maven：Maven 是 Java 中功能强大的项目管理工具，可以根据 pom.xml 文件中的配置自动下载和导入 Jar 文件。这个特性减少了不同版本 Jar 文件之间的冲突。
Spring：Spring 是 Java 中最流行的框架之一，越来越多的企业使用 Spring 框架来构建他们的项目。Spring Boot 构建在传统的 Spring 框架之上。因此，它提供了 Spring 的所有特性，而且比 Spring 更易用。

配置环境

1. 安装构建 MatrixOne

按照步骤介绍完成安装单机版 MatrixOne，在 MySQL 客户端新建一个命名为 test 数据库。

mysql> create database test;

2. 使用 IntelliJ IDEA 创建一个新的 Spring Boot 项目

选择 Spring Initializer，按需命名项目名称。

选择如下依赖项：

Spring Web
MyBatis Framework
JDBC API
MySQL Driver

点击 Create 创建项目。依赖项列在 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>
    <groupId>com.example</groupId>
    <artifactId>mybatis-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis-demo</name>
    <description>mybatis-demo</description>
    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <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.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</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>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.3.7.RELEASE</version>
                <configuration>
                    <mainClass>com.example.mybatisdemo.MybatisDemoApplication</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

3. 修改 application.properties 文件

进入到 resources 文件目录下，配置 application.properties 文件，完成 MatrixOne 连接。

# Application Name
spring.application.name=MyBatisDemo
# Database driver
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# Data Source name
spring.datasource.name=defaultDataSource
# Database connection url, modify to MatrixOne address and port, with paratemers
spring.datasource.url=jdbc:mysql://127.0.0.1:6001/test?characterSetResults=UTF-8&continueBatchOnError=false&useServerPrepStmts=true&alwaysSendSetIsolation=false&useLocalSessionState=true&zeroDateTimeBehavior=CONVERT_TO_NULL&failoverReadOnly=false&serverTimezone=Asia/Shanghai&socketTimeout=30000
# Database username and password
spring.datasource.username=dump
spring.datasource.password=111
# Mybatis mapper location
mybatis.mapper-locations=classpath:mapping/*xml
# Mybatis entity package
mybatis.type-aliases-package=com.example.mybatisdemo.entity
# Web application port
server.port=8080

编写代码

完成环境配置后，我们编写代码来实现一个简单的 CRUD 应用程序。

在完成编写编码后，你将拥有一个如下所示的项目结构。你可以预先创建这些包和 java 类。

我们将为这个演示应用程序编写创建、更新、插入、删除和选择操作。

1. UserController.java

package com.example.mybatisdemo.controller;
import com.example.mybatisdemo.entity.User;
import com.example.mybatisdemo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/test")
public class UserController {
    String tableName = "user";
    @Autowired
    private UserService userService;
    @RequestMapping(value = "/create", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
    @ResponseBody
    public String createTable(){
        return userService.createTable(tableName);
    }
    @RequestMapping(value = "/selectUserByid", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
    @ResponseBody
    public String GetUser(User user){
        return userService.Sel(user).toString();
    }
    @RequestMapping(value = "/add", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
    public String Add(User user){
        return userService.Add(user);
    }
    @RequestMapping(value = "/update", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
    public String Update(User user){
        return userService.Update(user);
    }
    @RequestMapping(value = "/delete", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
    public String Delete(User user){
        return userService.Delete(user);
    }
}

2. User.java

package com.example.mybatisdemo.entity;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
public class User {
    private Integer id;
    private String username;
    private String password;
    private String address;
    public User(Integer id, String username, String password, String address) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.address = address;
    }
    public Integer getId() {
        return id;
    }
    public String getUsername() {
        return username;
    }
    public String getPassword() {
        return password;
    }
    public String getAddress() {
        return address;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

3. UserMapper.java

package com.example.mybatisdemo.mapper;
import com.example.mybatisdemo.entity.User;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
@Repository
public interface UserMapper {
    int createTable(@Param("tableName") String tableName);
    User Sel(@Param("user")User user);
    int Add(@Param("user")User user);
    int Update(@Param("user")User user);
    int Delete(@Param("user")User user);
}

4. UserService.java

package com.example.mybatisdemo.service;
import com.example.mybatisdemo.entity.User;
import com.example.mybatisdemo.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserService {
    @Autowired
    UserMapper userMapper;
    public String createTable(String table){
        int a = userMapper.createTable(table);
        if (a == 1) {
            return "Create table failed";
        } else {
            return "Create table successfully";
        }
    }
    public User Sel(User user) {
        return userMapper.Sel(user);
    }
    public String Add(User user) {
        int a = userMapper.Add(user);
        if (a == 1) {
            return "Add user successfully";
        } else {
            return "Add user failed";
        }
    }
    public String Update(User user) {
        int a = userMapper.Update(user);
        if (a == 1) {
            return "Update user successfully";
        } else {
            return "Update user failed";
        }
    }
    public String Delete(User user) {
        int a = userMapper.Delete(user);
        if (a == 1) {
            return "Delete user successfully";
        } else {
            return "Delete user failed";
        }
    }
};

5. MyBatisDemoApplication.java

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

6. UserMapper.xml

<?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.mybatisdemo.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.example.mybatisdemo.entity.User">
        <result column="id" jdbcType="INTEGER" property="id"/>
        <result column="userName" jdbcType="VARCHAR" property="username"/>
        <result column="passWord" jdbcType="VARCHAR" property="password"/>
        <result column="realName" jdbcType="VARCHAR" property="address"/>
    </resultMap>
    <update id="createTable" parameterType="string">
        CREATE TABLE ${tableName} (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `username` varchar(255) DEFAULT NULL,
            `password` varchar(255) DEFAULT NULL,
            `address` varchar(255) DEFAULT NULL,
            PRIMARY KEY (`id`)
        );
    </update>
    <select id="Sel" resultType="com.example.mybatisdemo.entity.User">
        select * from user where 1=1
        <if test="user.id != null">
            AND id = #{user.id}
        </if>
    </select>
    <insert id="Add" parameterType="com.example.mybatisdemo.entity.User">
        INSERT INTO user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="user.username != null">
                username,
            </if>
            <if test="user.password != null">
                password,
            </if>
            <if test="user.address != null">
                address,
            </if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="user.username != null">
                #{user.username,jdbcType=VARCHAR},
            </if>
            <if test="user.password != null">
                #{user.password,jdbcType=VARCHAR},
            </if>
            <if test="user.address != null">
                #{user.address,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>
    <update id="Update" parameterType="com.example.mybatisdemo.entity.User">
        UPDATE user
        <set>
            <if test="user.username != null">
                username = #{user.username},
            </if>
            <if test="user.password != null">
                password = #{user.password},
            </if>
            <if test="user.address != null">
                address = #{user.address},
            </if>
        </set>
        WHERE
        id=#{user.id}
    </update>
    <delete id="Delete"  parameterType="com.example.mybatisdemo.entity.User">
        DELETE FROM user WHERE id = #{user.id}
    </delete>
</mapper>

测试

构建并启动这个项目。

当出现下面的消息示例时，表示应用程序已经正常启动，你可以打开浏览器并发送 HTTP 请求。

2022-10-26 16:13:24.030  INFO 60253 --- [           main] c.e.mybatisdemo.MyBatisDemoApplication   : Starting MyBatisDemoApplication on username-macbookpro.local with PID 60253 (/Users/username/IdeaProjects/MyBatisDemo/target/classes started by username in /Users/username/IdeaProjects/MyBatisDemo)
2022-10-26 16:13:24.035  INFO 60253 --- [           main] c.e.mybatisdemo.MyBatisDemoApplication   : No active profile set, falling back to default profiles: default
2022-10-26 16:13:25.415  INFO 60253 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2022-10-26 16:13:25.421  INFO 60253 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2022-10-26 16:13:25.421  INFO 60253 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.41]
2022-10-26 16:13:25.476  INFO 60253 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2022-10-26 16:13:25.477  INFO 60253 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1390 ms
2022-10-26 16:13:26.020  INFO 60253 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2022-10-26 16:13:26.248  INFO 60253 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2022-10-26 16:13:26.272  INFO 60253 --- [           main] c.e.mybatisdemo.MyBatisDemoApplication   : Started MyBatisDemoApplication in 2.669 seconds (JVM running for 3.544)

1. 测试新建表

打开你的的浏览器并输入网址：http://localhost:8080/test/create

在 MySQL 客户端中，验证表是否已成功创建。

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)
mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
`id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) DEFAULT null,
`password` VARCHAR(255) DEFAULT null,
`address` VARCHAR(255) DEFAULT null,
PRIMARY KEY (`id`)
) |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

2. 测试增加用户

打开你的浏览器并输入网址：http://localhost:8080/test/add?username=tom&password=123456&address=shanghai

在 MySQL 客户端中，使用如下命令验证是否成功添加记录：

mysql> select * from user;
+------+----------+----------+----------+
| id   | username | password | address  |
+------+----------+----------+----------+
|    1 | tom      | 123456   | shanghai |
+------+----------+----------+----------+
1 row in set (0.00 sec)

3. 测试查询用户

打开你的浏览器并输入网址：http://localhost:8080/test/selectUserByid?id=1

你可以直接从浏览器中获取查询结果。

4. 测试更新用户

打开你的浏览器并输入网址：http://localhost:8080/test/update/username=tom&password=654321&address=beijing

在 MySQL 客户端中，使用如下命令验证是否成功更新记录：

mysql> select * from user;
+------+----------+----------+---------+
| id   | username | password | address |
+------+----------+----------+---------+
|    1 | tom      | 654321   | beijing |
+------+----------+----------+---------+
1 row in set (0.00 sec)

5. 测试删除用户

打开你的浏览器并输入网址：http://localhost:8080/test/delete?id=1

在 MySQL 客户端中，使用如下命令验证是否成功删除记录：

mysql> select * from user;
Empty set (0.00 sec)