SpringBoot 2.x series: Integration with Mybatis

An overview of

In the last section, we introduced how to access the database by Spring Boot. First, we introduced JDBC and how to access the database through the native JDBC API. We saw through the Demo that the use of native JDBC is very troublesome, we need to maintain the core object of JDBC, transaction management, etc. We then introduced the encapsulated JdbcTemplate utility class provided by Spring Boot. Although JdbcTemplate simplifies common code logic through template design patterns, there are still many inconveniences to use. Such as:

SQL statements are hard coded into code to modify SQL is not convenient
The mapping of paraphis and parameter Settings is also hard-coded and not conducive to subsequent maintenance
Dynamic SQL statement assembly, both troublesome and prone to error
.

In this article, we introduce a new ORM framework — MyBatis. The former of MyBatis is the iBatis project of Apache Foundation. Due to various reasons,MyBatis was separated from Apache in 2010 and changed its name to MyBatis. MyBatis migrated the source code to GitHub.

There are several ways to use MyBatis in combination, which are as follows:

MyBatis + XML
MyBatis + annotation
MyBatisPlus
TkMyBatis

During the development of the project in the last two years, our team used the combination of MyBatis +XML, and the CURD operation of each table in the development process required handwritten codes and SQL. In order to reduce the development workload, we introduced MyBatis Generator, which reduced the development workload. However, it also resulted in a lot of useless code in the project.

In the last project, I introduced tkMyBatis with the PageHelper plug-in, which greatly improved the development efficiency and did not introduce useless code fragments.

At present, the company uses MyBatisPlus in this project. Compared with tkMyBatis, MyBatisPlus provides more practical functions, such as supporting physical deletion and configuring multiple data sources

Now, let’s get started with each of the four ways listed above.

MyBatis+XML

Example code corresponding to the warehouse address: github.com/dragon8844/…

Introduction of depend on

Introduce dependencies into the pom.xml file:

<! Automatic configuration of database connection pool
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<! -- In this example, we use MySQL -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<! -- Implement automatic configuration of MyBatis
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>

<! Easy to verify with unit tests -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

<! -- Lombok simplified code -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
Copy the code

Add the configuration

Application configuration

Create the application configuration file application.yml in the Resources directory and add the following configuration information:

spring:
  # datasource configures the datasource
  datasource:
    url: jdbc:mysql://localhost:3306/test? useSSL=false&useUnicode=true&characterEncoding=UTF-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root

# mybatis config content
mybatis:
  config-location: classpath:mybatis-config.xml MyBatis config file path
  mapper-locations: classpath:mapper/*.xml Configure the Mapper XML address
  type-aliases-package: com.dragon.mybatisxml.entity Configure the database entity package path
 
Copy the code

The mybatis configuration file, mybatis-config. XML file, also specifies the path to the SQL file, mapper directory, class root path

The mybatis configuration

Create mybatis-config. XML under resources and add the following configuration:


        
<! DOCTYPEconfiguration PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <! Convert fields using hump nomenclature -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <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>
Copy the code

Create the MyBatisConfig config config class under the config package and add the following configuration:

  / * * *@author lilong
   */
  @MapperScan(basePackages = "com.dragon.mybatisxml.mapper")
  @Configuration
  public class MybatisConfig {}Copy the code

This configuration class specifies the package path for the Mapper interface

Write the code

Writing entity classes

@Data
public class User {
    /** * primary key */
    private Integer id;
    /** * User name */
    private String username;
    /** * Password */
    private String password;
    /** * create time */
    private Date createTime;
}
Copy the code

DDL statement for entity class:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `username` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Username',
  `password` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'password',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  `deleted` tinyint(1) DEFAULT NULL COMMENT 'Deleted or not 0- Not deleted; 1 - delete '.PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Copy the code

Write the Mapper class

Com. Dragon. Mybatisxml. Mapper UserMapper package under create interfaces

@Repository
public interface UserMapper {
    /**
     * 新增
     *
     * @param user
     * @return* /
    Integer insert(User user);
    /** * query ** by ID@param id
     * @return* /
    User selectById(Integer id);
    /** * select ** from username@param username
     * @return* /
    User selectByUsername(String username);
    /** * update ** by ID@param user
     * @return* /
    Integer updateById(User user);
    /** * delete ** based on ID@param id
     * @return* /
    Integer deleteById(Integer id);
}
Copy the code

In the Resources /mapper path, create the usermapper.xml configuration file. The code is as follows:


        
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dragon.mybatisxml.mapper.UserMapper">
    <sql id="FIELDS">
        id, username, password, create_time
    </sql>
    <insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user (
        username, password, create_time
        ) VALUES (
        #{username}, #{password}, #{createTime}
        )
    </insert>
    <select id="selectById" parameterType="Integer" resultType="User">
        SELECT
        <include refid="FIELDS"/>
        FROM user
        WHERE id = #{id}
    </select>
    <select id="selectByUsername" parameterType="String" resultType="User">
        SELECT
        <include refid="FIELDS"/>
        FROM user
        WHERE username = #{username}
        LIMIT 1
    </select>
    <update id="updateById" parameterType="User">
        UPDATE user
        <set>
            <if test="username ! = null">
                , username = #{username}
            </if>
            <if test="password ! = null">
                , password = #{password}
            </if>
        </set>
        WHERE id = #{id}
    </update>
    <delete id="deleteById" parameterType="Integer">
        DELETE FROM user WHERE id = #{id}
    </delete>
</mapper>
Copy the code

For the vast majority of queries, we return uniform fields, so you can define SQL segments using the < SQL /> tag. For the query with large performance or query fields, select the required fields.
For reading, use uppercase for database keywords. For example, SELECT, WHERE, and so on

Unit testing

To create the UserMapperTest test class, let’s test each operation of a simple UserMapper. The code is as follows:

@SpringBootTest
@Slf4j
class UserMapperTest {
    @Resource
    private UserMapper userMapper;
    @Test
    void insert(a) {
        User user = new User();
        user.setUsername("Zhang");
        user.setPassword("123456");
        user.setCreateTime(new Date());
        Integer count = userMapper.insert(user);
        log.info("count:{}", count);
    }
    @Test
    void selectById(a) {
        User user = userMapper.selectById(8);
        log.info("user:{}", user.toString());
    }
    @Test
    void selectByUsername(a) {
        User user = userMapper.selectByUsername("Zhang");
        log.info("user:{}", user.toString());
    }
    @Test
    void updateById(a) {
        User user = new User();
        user.setUsername("Bill");
        user.setPassword("111111");
        Integer count = userMapper.updateById(user);
        log.info("count:{}", count);
    }
    @Test
    void deleteById(a) {
        Integer count = userMapper.deleteById(8);
        log.info("count:{}", count); }}Copy the code

MyBatis Generator MyBatis Generator MyBatis Generator MyBatis Generator MyBatis Generator

MyBatis + annotation

MyBatis can also support the use of annotations to write SQL, but in the actual project development process, it is not recommended to do so, on the one hand, SQL in Java code is difficult to typesetting, in addition to writing complex SQL, Mapper interface will be very messy. In general, although you can no longer write SQL XML files, it is strongly not recommended to use, and I also asked some friends, they rarely use this combination, but in view of the content, I still wrote a Demo

Example code corresponding to the warehouse address: github.com/dragon8844/…

Differences in part

application.yml

You can remove the Mapper-locations configuration items from the applica. yml configuration file.
UserMapper.xml

You can delete the configuration file of the SQL in the Resources /mapper path and declare the SQL to the UserMapper interface with annotations.

Write the code

In com. Dragon. Create interface UserMapper mybatisannotation package, at the same time, the way we use annotations to the SQL statement to the appropriate method, the code is as follows:

@Repository
public interface UserMapper {
    /**
     * 新增
     *
     * @param user
     * @return* /
    @Insert("INSERT INTO user(username, password, create_time) VALUES(#{username}, #{password}, #{createTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    Integer insert(User user);
    /** * query ** by ID@param id
     * @return* /
    @Select("SELECT username, password, create_time FROM user WHERE id = #{id}")
    User selectById(Integer id);
    /** * select ** from username@param username
     * @return* /
    @Select("SELECT username, password, create_time FROM user WHERE username = #{username}")
    User selectByUsername(String username);
    /** * update ** by ID@param user
     * @return* /
    @Update(value = { "" })
    Integer updateById(User user);
    /** * delete ** based on ID@param id
     * @return* /
    @Insert("DELETE FROM user WHERE id = #{id}")
    Integer deleteById(Integer id);
}
Copy the code

Single-table CRUD is ok with annotations, but complex SQL can look messy. Especially when dealing with dynamic SQL, script tags need to be introduced to process, which is very difficult to write and read.

Unit testing

Take a unit test and the code looks like this:

@Slf4j
@SpringBootTest
class UserMapperTest {

    @Resource
    private UserMapper userMapper;

    @Test
    void insert(a) {
        User user = new User();
        user.setUsername("Zhang");
        user.setPassword("123456");
        user.setCreateTime(new Date());
        Integer count = userMapper.insert(user);
        log.info("count:{}", count);
    }

    @Test
    void selectById(a) {
        User user = userMapper.selectById(12);
        log.info("user:{}", user.toString());
    }

    @Test
    void selectByUsername(a) {
        User user = userMapper.selectByUsername("Zhang");
        log.info("user:{}", user.toString());
    }

    @Test
    void updateById(a) {
        User user = new User();
        user.setUsername("Bill");
        user.setPassword("111111");
        Integer count = userMapper.updateById(user);
        log.info("count:{}", count);
    }

    @Test
    void deleteById(a) {
        Integer count = userMapper.deleteById(12);
        log.info("count:{}", count); }}Copy the code

summary

There will be a lot of content after all the four ways of using MyBatis are introduced, so this article will first introduce two ways, namely, MyBatis+XML and MyBatis+ annotation, through which we have a general understanding of the basic use of MyBatis. In the next chapter, we will introduce MyBatisPlus and TkMyBatis. These two frameworks have made certain encapsulation and extension on the basis of MyBatis to reduce redundant codes. At the same time, it can greatly improve our development efficiency. We will see you next

One last word

If this article is helpful to you, or inspired, help pay attention to it, your support is the biggest motivation I insist on writing, thank you for your support.

In addition, pay attention to the public number: black lighthouse, focus on Java back-end technology sharing, covering Spring, Spring the Boot, SpringCloud, Docker, Kubernetes middleware technology, etc.

SpringBoot 2.x series: Integration with Mybatis

An overview of

MyBatis+XML

Introduction of depend on

Add the configuration

Write the code

Unit testing

MyBatis + annotation

Differences in part

Write the code

Unit testing

summary

One last word

Related Posts

Diagram forward and reverse proxies

MySQL > create a database

2020.0.x version -20. Start a Eureka Server cluster