使用 SpringBoot 和 MyBatis 构建一个 CRUD 示例

本篇文档将指导你如何使用 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 0.6，在 MySQL 客户端新建一个命名为 test 数据库。

  1. mysql> create database test;

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

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

image-20221026152318567

选择如下依赖项：

  • Spring Web
  • MyBatis Framework
  • JDBC API
  • MySQL Driver

image-20221026152447954

点击 Create 创建项目。依赖项列在 pom.xml 文件中。通常你无需修改任何东西。

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  4.     <modelVersion>4.0.0</modelVersion>
  5.     <groupId>com.example</groupId>
  6.     <artifactId>mybatis-demo</artifactId>
  7.     <version>0.0.1-SNAPSHOT</version>
  8.     <name>mybatis-demo</name>
  9.     <description>mybatis-demo</description>
  11.     <properties>
  12.         <java.version>1.8</java.version>
  13.         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  14.         <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  15.         <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
  16.     </properties>
  18.     <dependencies>
  19.         <dependency>
  20.             <groupId>org.springframework.boot</groupId>
  21.             <artifactId>spring-boot-starter-jdbc</artifactId>
  22.         </dependency>
  23.         <dependency>
  24.             <groupId>org.springframework.boot</groupId>
  25.             <artifactId>spring-boot-starter-web</artifactId>
  26.         </dependency>
  27.         <dependency>
  28.             <groupId>org.mybatis.spring.boot</groupId>
  29.             <artifactId>mybatis-spring-boot-starter</artifactId>
  30.             <version>2.1.4</version>
  31.         </dependency>
  33.         <dependency>
  34.             <groupId>mysql</groupId>
  35.             <artifactId>mysql-connector-java</artifactId>
  36.             <scope>runtime</scope>
  37.         </dependency>
  38.         <dependency>
  39.             <groupId>org.springframework.boot</groupId>
  40.             <artifactId>spring-boot-starter-test</artifactId>
  41.             <scope>test</scope>
  42.             <exclusions>
  43.                 <exclusion>
  44.                     <groupId>org.junit.vintage</groupId>
  45.                     <artifactId>junit-vintage-engine</artifactId>
  46.                 </exclusion>
  47.             </exclusions>
  48.         </dependency>
  49.     </dependencies>
  51.     <dependencyManagement>
  52.         <dependencies>
  53.             <dependency>
  54.                 <groupId>org.springframework.boot</groupId>
  55.                 <artifactId>spring-boot-dependencies</artifactId>
  56.                 <version>${spring-boot.version}</version>
  57.                 <type>pom</type>
  58.                 <scope>import</scope>
  59.             </dependency>
  60.         </dependencies>
  61.     </dependencyManagement>
  63.     <build>
  64.         <plugins>
  65.             <plugin>
  66.                 <groupId>org.apache.maven.plugins</groupId>
  67.                 <artifactId>maven-compiler-plugin</artifactId>
  68.                 <version>3.8.1</version>
  69.                 <configuration>
  70.                     <source>1.8</source>
  71.                     <target>1.8</target>
  72.                     <encoding>UTF-8</encoding>
  73.                 </configuration>
  74.             </plugin>
  75.             <plugin>
  76.                 <groupId>org.springframework.boot</groupId>
  77.                 <artifactId>spring-boot-maven-plugin</artifactId>
  78.                 <version>2.3.7.RELEASE</version>
  79.                 <configuration>
  80.                     <mainClass>com.example.mybatisdemo.MybatisDemoApplication</mainClass>
  81.                 </configuration>
  82.                 <executions>
  83.                     <execution>
  84.                         <id>repackage</id>
  85.                         <goals>
  86.                             <goal>repackage</goal>
  87.                         </goals>
  88.                     </execution>
  89.                 </executions>
  90.             </plugin>
  91.         </plugins>
  92.     </build>
  94. </project>

3. 修改 application.properties 文件

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

  1. # Application Name
  2. spring.application.name=MyBatisDemo
  3. # Database driver
  4. spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  5. # Data Source name
  6. spring.datasource.name=defaultDataSource
  8. # Database connection url, modify to MatrixOne address and port, with paratemers
  9. 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
  10. # Database username and password
  11. spring.datasource.username=dump
  12. spring.datasource.password=111
  14. # Mybatis mapper location
  15. mybatis.mapper-locations=classpath:mapping/*xml
  16. # Mybatis entity package
  17. mybatis.type-aliases-package=com.example.mybatisdemo.entity
  18. # Web application port
  19. server.port=8080

编写代码

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

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

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

image-20221026155656694

1. UserController.java

  1. package com.example.mybatisdemo.controller;
  3. import com.example.mybatisdemo.entity.User;
  4. import com.example.mybatisdemo.service.UserService;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.web.bind.annotation.RequestMapping;
  7. import org.springframework.web.bind.annotation.RequestMethod;
  8. import org.springframework.web.bind.annotation.ResponseBody;
  9. import org.springframework.web.bind.annotation.RestController;
  11. @RestController
  12. @RequestMapping("/test")
  13. public class UserController {
  15.     String tableName = "user";
  16.     @Autowired
  17.     private UserService userService;
  19.     @RequestMapping(value = "/create", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
  20.     @ResponseBody
  21.     public String createTable(){
  22.         return userService.createTable(tableName);
  23.     }
  25.     @RequestMapping(value = "/selectUserByid", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
  26.     @ResponseBody
  27.     public String GetUser(User user){
  28.         return userService.Sel(user).toString();
  29.     }
  31.     @RequestMapping(value = "/add", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
  32.     public String Add(User user){
  33.         return userService.Add(user);
  34.     }
  36.     @RequestMapping(value = "/update", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
  37.     public String Update(User user){
  38.         return userService.Update(user);
  39.     }
  41.     @RequestMapping(value = "/delete", produces = "application/json;charset=UTF-8", method = RequestMethod.GET)
  42.     public String Delete(User user){
  43.         return userService.Delete(user);
  44.     }
  45. }

2. User.java

  1. package com.example.mybatisdemo.entity;
  3. import org.springframework.web.bind.annotation.RequestMapping;
  4. import org.springframework.web.bind.annotation.RequestMethod;
  6. public class User {
  7.     private Integer id;
  8.     private String username;
  9.     private String password;
  10.     private String address;
  12.     public User(Integer id, String username, String password, String address) {
  13.         this.id = id;
  14.         this.username = username;
  15.         this.password = password;
  16.         this.address = address;
  17.     }
  19.     public Integer getId() {
  20.         return id;
  21.     }
  23.     public String getUsername() {
  24.         return username;
  25.     }
  27.     public String getPassword() {
  28.         return password;
  29.     }
  31.     public String getAddress() {
  32.         return address;
  33.     }
  35.     public void setId(Integer id) {
  36.         this.id = id;
  37.     }
  39.     public void setUsername(String username) {
  40.         this.username = username;
  41.     }
  43.     public void setPassword(String password) {
  44.         this.password = password;
  45.     }
  47.     public void setAddress(String address) {
  48.         this.address = address;
  49.     }
  51.     @Override
  52.     public String toString() {
  53.         return "User{" +
  54.                 "id=" + id +
  55.                 ", username='" + username + '\'' +
  56.                 ", password='" + password + '\'' +
  57.                 ", address='" + address + '\'' +
  58.                 '}';
  59.     }
  60. }

3. UserMapper.java

  1. package com.example.mybatisdemo.mapper;
  3. import com.example.mybatisdemo.entity.User;
  4. import org.apache.ibatis.annotations.Param;
  5. import org.springframework.stereotype.Repository;
  7. @Repository
  8. public interface UserMapper {
  10.     int createTable(@Param("tableName") String tableName);
  12.     User Sel(@Param("user")User user);
  14.     int Add(@Param("user")User user);
  16.     int Update(@Param("user")User user);
  18.     int Delete(@Param("user")User user);
  20. }

4. UserService.java

  1. package com.example.mybatisdemo.service;
  3. import com.example.mybatisdemo.entity.User;
  4. import com.example.mybatisdemo.mapper.UserMapper;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.stereotype.Service;
  8. @Service
  9. public class UserService {
  10.     @Autowired
  11.     UserMapper userMapper;
  13.     public String createTable(String table){
  14.         int a = userMapper.createTable(table);
  15.         if (a == 1) {
  16.             return "Create table failed";
  17.         } else {
  18.             return "Create table successfully";
  19.         }
  20.     }
  22.     public User Sel(User user) {
  23.         return userMapper.Sel(user);
  24.     }
  26.     public String Add(User user) {
  27.         int a = userMapper.Add(user);
  28.         if (a == 1) {
  29.             return "Add user successfully";
  30.         } else {
  31.             return "Add user failed";
  32.         }
  33.     }
  35.     public String Update(User user) {
  36.         int a = userMapper.Update(user);
  37.         if (a == 1) {
  38.             return "Update user successfully";
  39.         } else {
  40.             return "Update user failed";
  41.         }
  42.     }
  44.     public String Delete(User user) {
  45.         int a = userMapper.Delete(user);
  46.         if (a == 1) {
  47.             return "Delete user successfully";
  48.         } else {
  49.             return "Delete user failed";
  50.         }
  51.     }
  53. };

5. MyBatisDemoApplication.java

  1. package com.example.mybatisdemo;
  3. import org.mybatis.spring.annotation.MapperScan;
  4. import org.springframework.boot.SpringApplication;
  5. import org.springframework.boot.autoconfigure.SpringBootApplication;
  7. @MapperScan("com.example.mybatisdemo.mapper")
  8. @SpringBootApplication
  9. public class MyBatisDemoApplication {
  10.     public static void main(String[] args) {
  11.         SpringApplication.run(MyBatisDemoApplication.class, args);
  12.     }
  13. }

6. UserMapper.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  4. <mapper namespace="com.example.mybatisdemo.mapper.UserMapper">
  6.     <resultMap id="BaseResultMap" type="com.example.mybatisdemo.entity.User">
  7.         <result column="id" jdbcType="INTEGER" property="id"/>
  8.         <result column="userName" jdbcType="VARCHAR" property="username"/>
  9.         <result column="passWord" jdbcType="VARCHAR" property="password"/>
  10.         <result column="realName" jdbcType="VARCHAR" property="address"/>
  11.     </resultMap>
  13.     <update id="createTable" parameterType="string">
  14.         CREATE TABLE ${tableName} (
  15.             `id` int(11) NOT NULL AUTO_INCREMENT,
  16.             `username` varchar(255) DEFAULT NULL,
  17.             `password` varchar(255) DEFAULT NULL,
  18.             `address` varchar(255) DEFAULT NULL,
  19.             PRIMARY KEY (`id`)
  20.         );
  21.     </update>
  23.     <select id="Sel" resultType="com.example.mybatisdemo.entity.User">
  24.         select * from user where 1=1
  25.         <if test="user.id != null">
  26.             AND id = #{user.id}
  27.         </if>
  28.     </select>
  30.     <insert id="Add" parameterType="com.example.mybatisdemo.entity.User">
  31.         INSERT INTO user
  32.         <trim prefix="(" suffix=")" suffixOverrides=",">
  33.             <if test="user.username != null">
  34.                 username,
  35.             </if>
  36.             <if test="user.password != null">
  37.                 password,
  38.             </if>
  39.             <if test="user.address != null">
  40.                 address,
  41.             </if>
  42.         </trim>
  43.         <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
  44.             <if test="user.username != null">
  45.                 #{user.username,jdbcType=VARCHAR},
  46.             </if>
  47.             <if test="user.password != null">
  48.                 #{user.password,jdbcType=VARCHAR},
  49.             </if>
  50.             <if test="user.address != null">
  51.                 #{user.address,jdbcType=VARCHAR},
  52.             </if>
  53.         </trim>
  54.     </insert>
  56.     <update id="Update" parameterType="com.example.mybatisdemo.entity.User">
  57.         UPDATE user
  58.         <set>
  59.             <if test="user.username != null">
  60.                 username = #{user.username},
  61.             </if>
  62.             <if test="user.password != null">
  63.                 password = #{user.password},
  64.             </if>
  65.             <if test="user.address != null">
  66.                 address = #{user.address},
  67.             </if>
  68.         </set>
  69.         WHERE
  70.         id=#{user.id}
  71.     </update>
  73.     <delete id="Delete"  parameterType="com.example.mybatisdemo.entity.User">
  74.         DELETE FROM user WHERE id = #{user.id}
  75.     </delete>
  77. </mapper>

测试

构建并启动这个项目。

image-20221026161226923

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

  1. 2022-10-26 16:13:24.030  INFO 60253 --- [           main] c.e.mybatisdemo.MyBatisDemoApplication   : Starting MyBatisDemoApplication on nandeng-macbookpro.local with PID 60253 (/Users/nandeng/IdeaProjects/MyBatisDemo/target/classes started by nandeng in /Users/nandeng/IdeaProjects/MyBatisDemo)
  2. 2022-10-26 16:13:24.035  INFO 60253 --- [           main] c.e.mybatisdemo.MyBatisDemoApplication   : No active profile set, falling back to default profiles: default
  3. 2022-10-26 16:13:25.415  INFO 60253 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
  4. 2022-10-26 16:13:25.421  INFO 60253 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
  5. 2022-10-26 16:13:25.421  INFO 60253 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.41]
  6. 2022-10-26 16:13:25.476  INFO 60253 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
  7. 2022-10-26 16:13:25.477  INFO 60253 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1390 ms
  8. 2022-10-26 16:13:26.020  INFO 60253 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
  9. 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 ''
  10. 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

image-20221026161929338

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

  1. mysql> use test;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. mysql> show tables;
  7. +----------------+
  8. | tables_in_test |
  9. +----------------+
  10. | user           |
  11. +----------------+
  12. 1 row in set (0.00 sec)
  14. mysql> show create table user;
  15. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  16. | Table | Create Table                                                                                                                                                                                  |
  17. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  18. | user  | CREATE TABLE `user` (
  19. `id` INT NOT NULL AUTO_INCREMENT,
  20. `username` VARCHAR(255) DEFAULT null,
  21. `password` VARCHAR(255) DEFAULT null,
  22. `address` VARCHAR(255) DEFAULT null,
  23. PRIMARY KEY (`id`)
  24. ) |
  25. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  26. 1 row in set (0.01 sec)

2. 测试增加用户

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

image-20221026162317800

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

  1. mysql> select * from user;
  2. +------+----------+----------+----------+
  3. | id   | username | password | address  |
  4. +------+----------+----------+----------+
  5. |    1 | tom      | 123456   | shanghai |
  6. +------+----------+----------+----------+
  7. 1 row in set (0.00 sec)

3. 测试查询用户

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

image-20221026162455058

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

4. 测试更新用户

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

image-20221026162613066

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

  1. mysql> select * from user;
  2. +------+----------+----------+---------+
  3. | id   | username | password | address |
  4. +------+----------+----------+---------+
  5. |    1 | tom      | 654321   | beijing |
  6. +------+----------+----------+---------+
  7. 1 row in set (0.00 sec)

5. 测试删除用户

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

image-20221026162756460

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

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