Previously, we have introduced two ways to access relational databases in Spring Boot:

• Using spring boot starter JDBC
• Using spring boot starter data JPA

Although Spring Data JPA is widely popular abroad, it is still the world of MyBatis in China. Therefore, in today's article, we will specifically talk about how to integrate MyBatis in Spring Boot to complete the addition, deletion, modification and query of relational databases.

Integrate MyBatis

Step 1: create a new Spring Boot project in POM The Starter and MySQL Connector dependencies of MyBatis are introduced into XML, as follows:

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

about
For the version of mybatis spring boot starter, please note:

• 2.1.x version is applicable to: MyBatis 3.5 +, Java 8 +, Spring Boot 2.1+
• 2.0.x version is applicable to: MyBatis 3.5 +, Java 8 +, Spring Boot 2.0/2.1
• 1.3.x version is applicable to: MyBatis 3.4 +, Java 6 +, Spring Boot 1.5

Among them, 2.1.1 is still being maintained X version and 1.3 X version.

Step 2: the same as the previous introduction of using jdbc module and jpa module to connect to the database, in application Configure mysql connection configuration in properties

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

Of course, you can also use the default data source. If you want to use Druid as the database connection pool, please refer to the article Spring Boot 2.x: using domestic database connection pool Druid.

Step 3: create a table for testing in Mysql, such as User table, which contains id(BIGINT), age(INT) and name(VARCHAR) fields.

The specific creation commands are as follows:

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_ci

Step 4: create the mapping object of User table:

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

Step 5: create the operation interface of User table: UserMapper. Two data operations, one insert and one query, are defined in the interface for subsequent unit test verification.

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

}

Step 6: create the Spring Boot main class

@SpringBootApplication
public class Chapter35Application {

    public static void main(String[] args) {
        SpringApplication.run(Chapter35Application.class, args);
    }

}

Step 7: create a unit test. The specific test logic is as follows:

• Insert a record with name=AAA and age=20, then query according to name=AAA and judge whether age is 20
• Roll back the data after the test to ensure that the data environment for each run of the test unit is independent

@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());
    }

}

Annotation configuration description

The following simultaneous interpreting methods are used to implement the insertion operation in the previous paper, so as to learn some notes commonly used in MyBatis.

Use @ Param

In the previous integration example, we have used the simplest parameter transfer method, as follows:

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

This method is easy to understand. The name defined in @ Param corresponds to #{name} in SQL, and the age corresponds to #{age} in SQL.

Using Map

The following code uses the map < string, Object > object as the container for passing parameters:

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

For the parameters required in the Insert statement, we only need to fill in 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);

Use object

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

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

In this way, #{name}, #{age} in the statement correspond to the name and age attributes in the User object respectively.

Add, delete, modify and query

MyBatis provides different annotations to configure different database operations. In the previous example, @ Insert was demonstrated. The following is a set of basic additions, deletions and modifications to the User table as an example:

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

After completing a set of additions, deletions and modifications, we might as well try the following unit tests to verify the correctness of the above operations:

@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 for verification
        userMapper.insert("AAA", 20);
        User u = userMapper.findByName("AAA");
        Assert.assertEquals(20, u.getAge().intValue());
        // update a piece of data and select it for verification
        u.setAge(30);
        userMapper.update(u);
        u = userMapper.findByName("AAA");
        Assert.assertEquals(30, u.getAge().intValue());
        // Delete this data and select validation
        userMapper.delete(u.getId());
        u = userMapper.findByName("AAA");
        Assert.assertEquals(null, u);
    }
}

Return result binding

There is relatively little change in addition, deletion and modification operations. For the "query" operation, we often need to perform multi table Association, summary calculation and other operations, so the query Result is often no longer a simple entity object, and we often need to return a packaging class different from the database entity. In this case, we can bind it through @ Results and @ Result annotations, as follows:

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

In the above code, the property attribute in @ Result corresponds to the member name in the User object, and the column corresponds to the field name from the SELECT. In this configuration, the id attribute is deliberately not found, and only the name and age objects in the corresponding User are mapped. In this way, the following unit test can be used to verify that the found id is null, while 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());
    }
}

This article mainly introduces some of the most commonly used methods. For more use of other annotations, please refer to the documentation. In the next article, we will introduce how to use XML to configure the traditional use of SQL.

By the way, I'm currently working on Java development. If you are learning Java, understand java, and are eager to become a qualified java development engineer, and lack of basic introductory video tutorials in the process of learning Java, you can pay attention to and confide in me: 01. obtain. I have a full set of video tutorials on the latest Java basics here.