SpringBoot and MyBatis CRUD demo

SpringBoot and MyBatis CRUD demo

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.

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

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

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.

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

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)