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

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;
  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>

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