SpringBoot 和 MyBatis 基础示例

本篇文档将指导你如何使用 SpringBootMybatisIntellij 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,按需命名项目名称。

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>
  10. <properties>
  11. <java.version>1.8</java.version>
  12. <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  13. <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  14. <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
  15. </properties>
  16. <dependencies>
  17. <dependency>
  18. <groupId>org.springframework.boot</groupId>
  19. <artifactId>spring-boot-starter-jdbc</artifactId>
  20. </dependency>
  21. <dependency>
  22. <groupId>org.springframework.boot</groupId>
  23. <artifactId>spring-boot-starter-web</artifactId>
  24. </dependency>
  25. <dependency>
  26. <groupId>org.mybatis.spring.boot</groupId>
  27. <artifactId>mybatis-spring-boot-starter</artifactId>
  28. <version>2.1.4</version>
  29. </dependency>
  30. <dependency>
  31. <groupId>mysql</groupId>
  32. <artifactId>mysql-connector-java</artifactId>
  33. <scope>runtime</scope>
  34. </dependency>
  35. <dependency>
  36. <groupId>org.springframework.boot</groupId>
  37. <artifactId>spring-boot-starter-test</artifactId>
  38. <scope>test</scope>
  39. <exclusions>
  40. <exclusion>
  41. <groupId>org.junit.vintage</groupId>
  42. <artifactId>junit-vintage-engine</artifactId>
  43. </exclusion>
  44. </exclusions>
  45. </dependency>
  46. </dependencies>
  47. <dependencyManagement>
  48. <dependencies>
  49. <dependency>
  50. <groupId>org.springframework.boot</groupId>
  51. <artifactId>spring-boot-dependencies</artifactId>
  52. <version>${spring-boot.version}</version>
  53. <type>pom</type>
  54. <scope>import</scope>
  55. </dependency>
  56. </dependencies>
  57. </dependencyManagement>
  58. <build>
  59. <plugins>
  60. <plugin>
  61. <groupId>org.apache.maven.plugins</groupId>
  62. <artifactId>maven-compiler-plugin</artifactId>
  63. <version>3.8.1</version>
  64. <configuration>
  65. <source>1.8</source>
  66. <target>1.8</target>
  67. <encoding>UTF-8</encoding>
  68. </configuration>
  69. </plugin>
  70. <plugin>
  71. <groupId>org.springframework.boot</groupId>
  72. <artifactId>spring-boot-maven-plugin</artifactId>
  73. <version>2.3.7.RELEASE</version>
  74. <configuration>
  75. <mainClass>com.example.mybatisdemo.MybatisDemoApplication</mainClass>
  76. </configuration>
  77. <executions>
  78. <execution>
  79. <id>repackage</id>
  80. <goals>
  81. <goal>repackage</goal>
  82. </goals>
  83. </execution>
  84. </executions>
  85. </plugin>
  86. </plugins>
  87. </build>
  88. </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
  7. # Database connection url, modify to MatrixOne address and port, with paratemers
  8. 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
  9. # Database username and password
  10. spring.datasource.username=dump
  11. spring.datasource.password=111
  12. # Mybatis mapper location
  13. mybatis.mapper-locations=classpath:mapping/*xml
  14. # Mybatis entity package
  15. mybatis.type-aliases-package=com.example.mybatisdemo.entity
  16. # Web application port
  17. server.port=8080

编写代码

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

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

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

image-20221026155656694

1. UserController.java

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

2. User.java

  1. package com.example.mybatisdemo.entity;
  2. import org.springframework.web.bind.annotation.RequestMapping;
  3. import org.springframework.web.bind.annotation.RequestMethod;
  4. public class User {
  5. private Integer id;
  6. private String username;
  7. private String password;
  8. private String address;
  9. public User(Integer id, String username, String password, String address) {
  10. this.id = id;
  11. this.username = username;
  12. this.password = password;
  13. this.address = address;
  14. }
  15. public Integer getId() {
  16. return id;
  17. }
  18. public String getUsername() {
  19. return username;
  20. }
  21. public String getPassword() {
  22. return password;
  23. }
  24. public String getAddress() {
  25. return address;
  26. }
  27. public void setId(Integer id) {
  28. this.id = id;
  29. }
  30. public void setUsername(String username) {
  31. this.username = username;
  32. }
  33. public void setPassword(String password) {
  34. this.password = password;
  35. }
  36. public void setAddress(String address) {
  37. this.address = address;
  38. }
  39. @Override
  40. public String toString() {
  41. return "User{" +
  42. "id=" + id +
  43. ", username='" + username + '\'' +
  44. ", password='" + password + '\'' +
  45. ", address='" + address + '\'' +
  46. '}';
  47. }
  48. }

3. UserMapper.java

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

4. UserService.java

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

5. MyBatisDemoApplication.java

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

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

测试

构建并启动这个项目。

image-20221026161226923

当出现下面的消息示例时,表示应用程序已经正常启动,你可以打开浏览器并发送 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

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
  4. Database changed
  5. mysql> show tables;
  6. +----------------+
  7. | tables_in_test |
  8. +----------------+
  9. | user |
  10. +----------------+
  11. 1 row in set (0.00 sec)
  12. mysql> show create table user;
  13. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  14. | Table | Create Table |
  15. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  16. | user | CREATE TABLE `user` (
  17. `id` INT NOT NULL AUTO_INCREMENT,
  18. `username` VARCHAR(255) DEFAULT null,
  19. `password` VARCHAR(255) DEFAULT null,
  20. `address` VARCHAR(255) DEFAULT null,
  21. PRIMARY KEY (`id`)
  22. ) |
  23. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  24. 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)