Spring Boot:Mybatis Minimalist Configuration

Posted by Paul Arnold on Mon, 30 Sep 2019 03:13:53 +0200

Spring Boot (7): Mybatis Minimalist Configuration

1. Preface

The ORM framework is designed to simplify database operations in programming. After so many years of development, there are basically two remaining, one is Hibernate, which claims to be able to write no SQL, and the other is Mybaties, which are very SQL-friendly. Each has its own characteristics and can be developed in an enterprise system on demand.Flexible use.It's interesting to see that traditional businesses like Hibernate, while the Internet industry often uses Mybatis.

Hibernate's feature is that all SQL is generated in Java code, and you don't need to jump out of the program to write (see) SQL. It has the integrity of programming. Spring Data Jpa is the mode that develops to the top. You can basically generate the corresponding SQL based on the method name. If you don't know anything about it, you can read the first two articles by the author.Article Spring Boot (3): ORM Framework JPA and Connection Pool Hikari and Spring Boot: QueryDSL with wings for JPA.

Mybatis was cumbersome in its early stages, requiring configuration files, entity classes, Dao layer mapping associations, and a wide range of other configurations.Of course, Mybatis also found this disadvantage. Initially, generator was developed to automatically produce entity classes, configuration files, and Dao layer codes based on table results, which can reduce a part of the development; later, a lot of optimization was done to use annotations, automatically manage the Dao layer and configuration files, etc., to the top.That's the pattern we're going to talk about today. mybatis-spring-boot-starter is Spring Boot + Mybatis, which can either completely annotate that you don't need a configuration file or simply configure it to get started.

2. Engineering Practice

Start by creating the parent project spring-boot-mybatis and introducing the global dependency package as follows:

List of codes: spring-boot-mybatis/pom.xml

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.0</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

mybatis-spring-boot-starter: Current latest version is 2.1.0

2.1 Minimalist xml

Create subproject spring-boot-mybatis-xml

2.1.1 Profile

The application.yml configuration file is as follows:

List of codes: spring-boot-mybatis/spring-boot-mybatis-xml/src/main/resources/application.yml

server:
  port: 8080
spring:
  application:
    name: spring-boot-mybatis-xml
  datasource:
    url: jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      auto-commit: true
      minimum-idle: 2
      idle-timeout: 60000
      connection-timeout: 30000
      max-lifetime: 1800000
      pool-name: DatebookHikariCP
      maximum-pool-size: 5
mybatis:
  type-aliases-package: com.springboot.springbootmybatisxml.model
  config-location: classpath:mybatis/mybatis-config.xml
  mapper-locations: classpath:mybatis/mapper/*.xml

Use hikari as the database connection pool here
Spring Boot automatically loads the spring.datasource. * configuration, the data source is automatically injected into the sqlSessionFactory, and the sqlSessionFactory is automatically injected into the Mapper.
Here you need to specify the address of the underlying configuration file and the entity class mapping file

The mybatis-config.xml configuration file is as follows:

List of codes: spring-boot-mybatis/spring-boot-mybatis-xml/src/main/resources/mybatis/mybatis-config.xml

<configuration>
    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer" />
        <typeAlias alias="Long" type="java.lang.Long" />
        <typeAlias alias="HashMap" type="java.util.HashMap" />
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
        <typeAlias alias="ArrayList" type="java.util.ArrayList" />
        <typeAlias alias="LinkedList" type="java.util.LinkedList" />
    </typeAliases>
</configuration>

2.1.2 Mapper map file

List of codes: spring-boot-mybatis/spring-boot-mybatis-xml/src/main/resources/mybatis/mapper/UserMapper.xml

<mapper namespace="com.springboot.springbootmybatisxml.mapper.UserMapper" >

    <resultMap id="BaseResultMap" type="com.springboot.springbootmybatisxml.model.User" >
        <id column="id" property="id" jdbcType="VARCHAR" />
        <result column="nick_name" property="nickName" jdbcType="VARCHAR" />
        <result column="age" property="age" jdbcType="INTEGER" />
        <result column="create_date" property="createDate" jdbcType="TIME"/>
    </resultMap>

    <sql id="Base_Column_List" >
        id, nick_name, age, create_date
    </sql>

    <select id="getAll" resultMap="BaseResultMap"  >
        SELECT
            <include refid="Base_Column_List" />
        FROM user
    </select>

    <select id="getUser" parameterType="java.lang.String" resultMap="BaseResultMap" >
        SELECT
            <include refid="Base_Column_List" />
        FROM
            user
        WHERE id = #{id}
    </select>

    <insert id="insertUser" parameterType="com.springboot.springbootmybatisxml.model.User">
        <selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE">
            select uuid() as id from dual
        </selectKey>
       INSERT INTO
               user
               (id, nick_name, age, create_date)
           VALUES
               (#{id}, #{nickName}, #{age}, #{createDate})
    </insert>

    <update id="updateUser" parameterType="com.springboot.springbootmybatisxml.model.User">
        UPDATE
            user
        SET
        <if test="nickName != null">nick_name = #{nickName},</if>
        <if test="age != null">age = #{age},</if>
        <if test="createDate != null">create_date = #{createDate}</if>
        WHERE
            id = #{id}
    </update>

    <delete id="deleteUser" parameterType="java.lang.String">
       DELETE FROM
                user
       WHERE
                id = #{id}
    </delete>

</mapper>

namespace: the corresponding interface needs to be configured
Simple CRUD operations implemented
Use UUID as primary key when adding data
Dynamic conditions can be judged using the <if>tag

2.1.3 Mapper Layer Code

Code List: spring-boot-mybatis/spring-boot-mybatis-xml/src/main/java/com/spring boot/spring boot mybatis xml/mapper/UserMapper.java

public interface UserMapper {

    List<User> getAll();

    User getUser(String id);

    Long insertUser(User user);

    Long updateUser(User user);

    Long deleteUser(String id);
}

Just define the interface method here, and mybaties will automatically call the code in the xml Mapping file for us.

2.1.4 Start main class

Code List: spring-boot-mybatis/spring-boot-mybatis-xml/src/main/java/com/spring boot/spring boot mybatis xml/SpringBootMybatisXmlApplication.java

@SpringBootApplication
@MapperScan("com.springboot.springbootmybatisxml.mapper")
public class SpringBootMybatisXmlApplication {

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

}

Configuring @MapperScan on the startup main class or adding the annotation @Mapper directly on the Mapper class will work the same way.However, it is recommended to configure @MapperScan on the startup main class, otherwise annotating each Mapper class can be cumbersome and easy to miss.

2.2 No Profile Annotation Version

2.2.1 Configuration

The configuration file application.yml is as follows:

Code List:

mybatis:
  type-aliases-package: com.springboot.springbootmybatisannotation.model

The rest is consistent with the above, and mybatis configuration requires only this one to be sufficient

2.2.2 Mapper class

The core of the annotation version is this class, in which all SQL is contained. The code is as follows:

Code List:

public interface UserMapper {

    @Select("select * from user")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "nickName", column = "nick_name"),
            @Result(property = "age", column = "age"),
            @Result(property = "createDate", column = "create_date")
    })
    List<User> getAll();

    @Select("SELECT * FROM user WHERE id = #{id}")
    @Results({
            @Result(property = "nickName", column = "nick_name")
    })
    User getUser(String id);

    @Insert("INSERT INTO user(id, nick_name, age, create_date) VALUES(#{id}, #{nickName}, #{age}, #{createDate})")
    @SelectKey(keyProperty = "id", resultType = String.class, before = true, statement = "select uuid() as id from dual")
    Long insertUser(User user);

    @Update("UPDATE user SET nick_name = #{nickName}, age = #{age} WHERE create_date = #{createDate}")
    Long updateUser(User user);

    @Delete("DELETE FROM user WHERE id = #{id}")
    Long deleteUser(String id);
}

@Select is a comment to the query class and is used by all queries
@Result modifies the returned result set, and the associated entity class property corresponds to the database field one-to-one. If the entity class property and the database property name are consistent, this property is not required to modify.
@Insert is inserted into the database for use, and direct incoming entity classes automatically resolve attributes to their corresponding values
@Update is responsible for modifications or can pass in objects directly
@delete is responsible for deleting

Note: Using #and $symbols is different

#{}

Using #{} means using a precompiled statement, that is, a preparedStatement when using jdbc, or a placeholder if there are parameters in the sql statement.

${}

sql when using ${} is not treated as a string, what is it, such as the statement above: select from table1 where id=${id} When calling this statement the console prints: select from table1 where id=2, assuming the parameter value passed is 2

The above description shows the difference between the two methods. It is best to use #{} instead, which prevents sql injection and is precompiled, using ${} only when the original output is required.

3. Summary

The two modes have their own features. The annotated version is suitable for simple and fast mode. In fact, like the current popular micro-service mode, a micro-service will correspond to its own database, and the demand for multi-table join queries will be greatly reduced, which will become more and more suitable for this mode.In addition, Hibernate's support for single tables is excellent. Why not use Spring Boot JPA + QueryDSL?

Xml configuration mode is more suitable for large projects, can write SQL smoothly and vividly, can generate SQL flexibly and dynamically, and is easy to adjust SQL.