About the configuration and use of MyBatis

Although Spring Data JPA is widely popular abroad, MyBatis is still more commonly used in China

Integrate MyBatis

Step 1: Create a new Spring Boot project and introduce the MyBatis Starter and MySQL Connector dependencies in pom.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>

Note about the version of mybatis-spring-boot-starter:

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

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

Step 2: Configure the connection configuration of mysql in application.properties as described before using the jdbc module and the jpa module 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.Driver

Of course, you can also not use the default data source (HikariCP). If you want to use Druid as the database connection pool, you can refer to "Spring Boot 2.x: Using Domestic Database Connection Pool Druid" one article.

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

The specific creation command is 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 User of the 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 the User table: UserMapper. Define two data operations in the interface, one insert and one query, 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);
	}

}

Annotation configuration instructions

Let's use several different ways to pass parameters to implement the insertion operation implemented in the previous article, and learn some annotations commonly used in MyBatis.

Use @Param

In the previous integration example, we have used this simplest way of passing parameters, 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 age corresponds to #{age} in SQL.

Use Map

The following code uses a Map<String, Object> 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);

For the parameters required in the Insert statement, we only need to fill in the content of the same name in the map, as shown in the following code:

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

user target audience

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} and #{age} in the statement correspond to the name and age properties in the User object, respectively.

CRUD

MyBatis provides different annotations for configuration for different database operations. In the previous example, @Insert is demonstrated. The following is an example of a set of basic additions, deletions, 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);
}

return result binding

Relatively small changes are made to addition, deletion, and modification operations. For the "check" operation, we often need to perform operations such as multi-table association, summary calculation, etc., then the result of the query is often no longer a simple entity object, and often needs to return a wrapper class that is different from the database entity. In this case, you can use the @Results and @Result annotations to bind, as follows:

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

XML format configuration description

Step 1: Add mapper's scan package configuration to the main application class:

@MapperScan("com.didispace.chapter36.mapper")
@SpringBootApplication
public class Chapter36Application {

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

}

Step 2: Create the Mapper definition of the User table under the Mapper package specified in the first step:

public interface UserMapper {

    User findByName(@Param("name") String name);

    int insert(@Param("name") String name, @Param("age") Integer age);

}

Step 3: Specify the location of the xml configuration through the mybatis.mapper-locations parameter in the configuration file:

mybatis.mapper-locations=classpath:mapper/*.xml

Step 4: Create the mapper configuration of the User table in the xml configuration directory specified in the third step:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.didispace.chapter36.mapper.UserMapper">
    <select id="findByName" resultType="com.didispace.chapter36.entity.User">
        SELECT * FROM USER WHERE NAME = #{name}
    </select>

    <insert id="insert">
        INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})
    </insert>
</mapper>

Here, the usage of MyBatis in the annotation mode has been changed to the configuration mode of XML.