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 数据库。

1. mysql> create database test;

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

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

选择如下依赖项：

Spring Web
MyBatis Framework
JDBC API
MySQL Driver

点击 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 类。

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

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>

测试

构建并启动这个项目。

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

1. 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)
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

在 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

在 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

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

4. 测试更新用户

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

在 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

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

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