We’ve already covered two ways to access a relational database in Spring Boot:

  • Using the spring – the boot – starter – JDBC
  • Using the spring – the boot – starter – data – jpa

Although Spring Data JPA is widely popular abroad, it is still the world of MyBatis in China. Therefore, today we will talk about how to integrate MyBatis in Spring Boot to complete the add, delete, change and check operation of the relational database.

Integration of MyBatis

Step 1: Create a New Spring Boot project and introduce MyBatis Starter and MySQL Connector dependencies in PUM. XML as follows:

<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> < version > 2.1.1 < / version > < / dependency > < the dependency > < groupId > mysql < / groupId > <artifactId>mysql-connector-java</artifactId> </dependency>Copy the code

Note about mybatis-spring-boot-starter:

  • X 2.1.Version: MyBatis 3.5+, Java 8+, Spring Boot 2.1+
  • X 2.0.Version for: MyBatis 3.5+, Java 8+, Spring Boot 2.0/2.1
  • X 1.3.Version for: MyBatis 3.4+, Java 6+, Spring Boot 1.5

Among them, the 2.1.x and 1.3.x versions are still being maintained.

Step 2: Configure the connection configuration for mysql in application.properties as described earlier using the JDBC and JPA modules to connect to the database

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.DriverCopy the code

If you want to use Druid as your database connection pool, see Spring Boot 2.x: Using Druid as your database connection pool.

Mysql > select * from User where id(BIGINT), age(INT), name(VARCHAR);

The following commands are used to create the vm:

CREATE TABLE `User` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ciCopy the code

Create table User mapping object User:

@Data @NoArgsConstructor public class User { private Long id; private String name; private Integer age; public User(String name, Integer age) { this.name = name; this.age = age; }}Copy the code

Step 5: Create an operation interface for the User table: UserMapper. Define two data operations, one insert and one query, in the interface for subsequent unit test validation.

@Mapper
public interface UserMapper {

    @Select("SELECT * FROM USER WHERE NAME = #{name}")
    User findByName(@Param("name") String name);

    @Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
    int insert(@Param("name") String name, @Param("age") Integer age);

}
Copy the code

Step 6: Create the Spring Boot main class

@SpringBootApplication public class Chapter35Application { public static void main(String[] args) { SpringApplication.run(Chapter35Application.class, args); }}Copy the code

Step 7: Create unit tests. The specific test logic is as follows:

  • Insert name=AAA, age=20, select name=AAA, age=20
  • After the test, the data is rolled back to ensure that the test unit runs in an independent data environment
@Slf4j @RunWith(SpringRunner.class) @SpringBootTest public class Chapter35ApplicationTests { @Autowired private UserMapper userMapper; @Test @Rollback public void test() throws Exception { userMapper.insert("AAA", 20); User u = userMapper.findByName("AAA"); Assert.assertEquals(20, u.getAge().intValue()); }}Copy the code

Note Configuration notes

Here are some common comments in MyBatis to learn about the insertion operation implemented in the previous article through several different methods of parameter passing.

Using the @ Param

We have already used this simplest method of passing parameters in the previous integration example as follows:

@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
int insert(@Param("name") String name, @Param("age") Integer age);Copy the code

The name defined in @param corresponds to #{name} in SQL and age corresponds to #{age} in SQL.

Using the Map

The following code uses a Map object as a container for passing parameters:

@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER})")
int insertByMap(Map<String, Object> map);Copy the code

For the parameters needed in the Insert statement, we simply fill the map with the same name, as shown in the following code:

Map<String, Object> map = new HashMap<>();
map.put("name", "CCC");
map.put("age", 40);
userMapper.insertByMap(map);Copy the code

Using the object

In addition to Map objects, we can also use plain Java objects as parameters to query conditions. For example, we can use User objects directly:

@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
int insertByUser(User user);Copy the code

#{name} and #{age} correspond to the name and age attributes of the User object, respectively.

Add and delete

MyBatis provides different annotations for different database operations. In the previous example, we demonstrated @insert. Here is a set of basic additions, subtractions, and changes for the User table:

public interface UserMapper {

    @Select("SELECT * FROM user WHERE name = #{name}")
    User findByName(@Param("name") String name);

    @Insert("INSERT INTO user(name, age) VALUES(#{name}, #{age})")
    int insert(@Param("name") String name, @Param("age") Integer age);

    @Update("UPDATE user SET age=#{age} WHERE name=#{name}")
    void update(User user);

    @Delete("DELETE FROM user WHERE id =#{id}")
    void delete(Long id);
}Copy the code

After completing a set of add, delete, change, and check, let’s try the following unit test to verify that the above is correct:

@Transactional @RunWith(SpringRunner.class) @SpringBootTest public class ApplicationTests { @Autowired private UserMapper userMapper; @test@rollback public void testUserMapper() throws Exception {// Insert a piece of data and select it to verify usermapper. insert("AAA", 20); User u = userMapper.findByName("AAA"); Assert.assertEquals(20, u.getAge().intValue()); U. scetage (30); // Update a data and select to verify u. scetage (30); userMapper.update(u); u = userMapper.findByName("AAA"); Assert.assertEquals(30, u.getAge().intValue()); // Delete this data and select verify usermapper.delete (u.getid ()); u = userMapper.findByName("AAA"); Assert.assertEquals(null, u); }}Copy the code

Return result binding

For add, delete, change relatively small changes. For the “check” operation, we often need to be more table, lump sum operation, such as for the Results of the query often is no longer a simple entity object, often need to return an unlike database entity wrapper classes, so this kind of situation, can pass @ Results and @ Result annotations for binding, specific as follows:

@Results({
    @Result(property = "name", column = "name"),
    @Result(property = "age", column = "age")
})
@Select("SELECT name, age FROM user")
List<User> findAll();Copy the code

In the code above, the property attribute in @result corresponds to the member name in the User object, and column corresponds to the field name in the SELECT. In this configuration, the id attribute is not detected, and only the name and age objects corresponding to User are mapped, so that the following unit test can verify that the detected ID is NULL, and other attributes are not null:

@Test @Rollback public void testUserMapper() throws Exception { List<User> userList = userMapper.findAll(); for(User user : userList) { Assert.assertEquals(null, user.getId()); Assert.assertNotEquals(null, user.getName()); }}Copy the code

This article focuses on some of the most commonly used methods, and more on the use of annotations can be found in the documentation. The next article will cover the traditional use of XML to configure SQL.

Click through to the Summary Directory

Code sample

For an example of this article, see the chapter3-5 directory in the repository below:

  • Github:github.com/dyc87112/Sp…
  • Gitee:gitee.com/didispace/S…

If you think this article is good, welcome Star support, your attention is my motivation!

Welcome to pay attention to my official account: Program ape DD, for exclusive learning resources and daily dry goods push.

If you are interested in my feature content, you can also follow my blog: didispace.com