Build a SpringBoot+MyBatis CRUD demo with MatrixOne

This tutorial completes a simple Demo based on SpringBoot+Mybatis+MatrixOne+Intellij IDEA , and realizes the basic CRUD function.

Before you start

A brief introduction about these softwares concerned:

  • MyBatis: It is a popular persistence framework that can customize SQL and support for complex reports and advanced mappings. We only need to focus on the SQL itself in our project.
  • Intellij IDEA: IntelliJ IDEA is a popular IDE for Java developers. It has a lot of plugins that can enhance our efficiency.
  • Maven: Maven is a powerful management tool in Java that can automatically download and import Jar file according to the configuration in the pom.xml file. This feature reduces the conflicts between different versions of Jar files.
  • Spring: Spring is one of the most popular frameworks in Java and more and more enterprise is using the Spring framework to build their project. Spring Boot is built on top of the conventional spring framework. So, it provides all the features of spring and is yet easier to use than spring.

Set up environment

1. Install and Launch MatrixOne

Follow the installation instruction and launch MatrixOne 0.6. Create a database test by MySQL client.

  1. mysql> create database test;

2. Create a new Spring Boot Project Using IntelliJ IDEA

Choose Spring Initializer, and name the project as you want.

image-20221026152318567

Choose Spring Web, MyBatis Framework, JDBC API and MySQL Driver as dependencies for this project.

image-20221026152447954

Click Create, the project will be created. The dependencies are listed in the pom.xml file. Usually you don’t need to modify anything.

  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. Modify the application.properties file

Under resources folder, the MatrixOne connection need to be configured in application.properties file.

  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

Write Code

After setting up the environment, we write code to implement a simple CRUD application. After finishing coding, you’ll have a project structure as below. You can create these packages and java class in advance. We will code the Create, Update, Insert, Delete, Select operations for this demo application.

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>

Test

Build and launch this project.

image-20221026161226923

When you see the following messages, the application is well launched and you can open your browser and send HTTP request.

  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. Test Create Table

Launch your browser and type the following url:

  1. http://localhost:8080/test/create

image-20221026161929338

In MySQL client, we can verify if the table has been successfully created.

  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. Test Add User

Launch your browser and type the following url:

  1. http://localhost:8080/test/add?username=tom&password=123456&address=shanghai

image-20221026162317800

In MySQL client, we can verify if the record is added.

  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. Test Select User

Launch your browser and type the following url:

  1. http://localhost:8080/test/selectUserByid?id=1

image-20221026162455058

We can directly get the queried result from the browser.

4. Test Update User

Launch your browser and type the following url:

  1. http://localhost:8080/test/update/username=tom&password=654321&address=beijing

image-20221026162613066

In MySQL client, we can verify if the record is updated.

  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. Test Delete User

Launch your browser and type the following url:

  1. http://localhost:8080/test/delete?id=1

image-20221026162756460

In MySQL client, we can verify if the record is deleted.

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