SpringBoot 2.x series: Integration with Mybatis

An overview of

In the previous chapter, we introduced how Spring Boot integrated Mybatis to complete database access. In the actual project development process, there are four commonly used combinations, which are as follows:

MyBatis + XML
MyBatis + annotation
MyBatisPlus
TkMyBatis

In the last article, we provided a primer on the first two methods, which can be found at juejin.cn/post/692241… Next, we continue to introduce MyBatisPlus and TkMyBatis

MyBatis-Plus

As a Java development engineer, I must be familiar with the famous MyBatis-Plus. The author’s idea is “born to simplify the development”. The practical use of MyBatis has greatly simplified the development and improved the development efficiency. Official website: https://mybatis.plus/

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

In this section, we will automatically configure mybatis- Plus using mybatis-plus-boot-starter. At the same time, demonstrate how to use MyBatis-Plus to implement various CRUD operations.

Introduction of depend on

<! 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>

<! -- Automatic configuration of MyBatis Plus
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.2.0</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: ‘

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 - Plus configuration content
mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
  global-config:
    db-config:
      id-type: auto # ID Primary key increment
      logic-delete-value: 1 # Logical deleted value (default: 1)
      logic-not-delete-value: 0 # Logical undeleted value (default: 0)
  mapper-locations: classpath*:mapper/*.xml
  type-aliases-package: com.dragon.mybatisplus.entity
Copy the code

The configuration class

Create MybatisPlusConfig config class in config package and add the following configuration:
```
/ * * *@author lilong
 */
@MapperScan("com.dragon.mybatisplus.mapper")
@Configuration
public class MybatisPlusConfig {}Copy the code
```
Since this is a starting demo, here we only need to configure the package scan path of Mybatis

Write the code

Writing entity classes

@Data
@TableName(value = "user")
public class User {
    /** * primary key */
    private Integer id;
    /** * User name */
    private String username;
    /** * Password */
    private String password;
    /** * create time */
    private Date createTime;
    /** * Whether to delete */
    @TableLogic
    private Integer deleted;
}
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. Mybatisplus. Create interface UserMapper mapper package, this time we need to inherit MyBatis mapper, the basis of – Plus

Com. Baomidou. Mybatisplus. Core. Mapper. BaseMapper interface, the code is as follows:

/ * * *@author lilong
 */
@Repository
public interface UserMapper extends BaseMapper<User> {
    /** * select ** from username@param username
     * @return* /
    default User selectByUsername(@Param("username") String username) {
        LambdaQueryWrapper<User> wrapper = new QueryWrapper<User>().lambda();
        returnselectOne(wrapper.eq(User::getUsername, username)); }}Copy the code

MyBatis + XML comparing to this way, we found that through inheritance com. Baomidou. Mybatisplus. Core. The mapper. BaseMapper interface, single table for database CRUD MyBatis – Plus automatically completed for us, The analysis source code is as follows:

public interface BaseMapper<T> extends Mapper<T> {
    int insert(T entity);

    int deleteById(Serializable id);

    int deleteByMap(@Param("cm") Map<String, Object> columnMap);

    int delete(@Param("ew") Wrapper<T> wrapper);

    int deleteBatchIds(@Param("coll") Collection<? extends Serializable> idList);

    int updateById(@Param("et") T entity);

    int update(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper);

    T selectById(Serializable id);

    List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);

    List<T> selectByMap(@Param("cm") Map<String, Object> columnMap);

    T selectOne(@Param("ew") Wrapper<T> queryWrapper);

    Integer selectCount(@Param("ew") Wrapper<T> queryWrapper);

    List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);

    List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> queryWrapper);

    List<Object> selectObjs(@Param("ew") Wrapper<T> queryWrapper);

    IPage<T> selectPage(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);

    IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);
}
Copy the code

By looking at the source code, we found that Mybatis- Plus automatically helps us to generate the method is quite comprehensive, generally speaking, when developing business code, the most time-consuming and boring operation is the single table CURD, Mybatis- Plus can greatly simplify our development.

For #selectByUsername(@param (“username”) String username), . We use the com baomidou. Mybatisplus. Core. The conditions. The query. QueryWrapper < T > structure relatively flexible conditions, so some dynamic SQL we don’t need to write in the XML.

The code is as follows:
```
default User selectByUsername(@Param("username") String username) {
        LambdaQueryWrapper<User> wrapper = new QueryWrapper<User>().lambda();
        return selectOne(wrapper.eq(User::getUsername, username));
    }
Copy the code
```
QueryWrapper already provides a number of concatenation methods, which can be used in MyBatis-Plus documentation – Conditional Constructor

Unit testing

Take a unit test and the code looks like this:

@SpringBootTest
@Slf4j
class UserMapperTest {

    @Resource
    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(13);
        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.setId(13);
        user.setUsername("Bill");
        user.setPassword("111111");
        Integer count = userMapper.updateById(user);
        log.info("count:{}", count);
    }

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

tkMybatis

I don’t use tkMybatis very much in my actual work. I basically directly transition from Mybatis + XML to Mybatis -Plus, and only one project uses tkMybatis. Tk is the abbreviation of Toolkit, which means toolkit. It is mainly a combination of two open source projects

Mapper: Provides the generic MyBatis Mapper.
Mybatis-PageHelper: Provides Mybatis pagination plugin.

More information can be found at mybatis. IO /

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

In this section, we will use tkMyBatis to implement our various CRUD operations.

Introduction of depend on

In the POM.xml file, introduce related dependencies, which are slightly more like the following:

<! 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>

<! Automatic configuration of Mapper -->
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.1.5</version>
</dependency>

<! PageHelper = PageHelper = PageHelper = PageHelper
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.5</version>
    <exclusions>
        <exclusion>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </exclusion>
    </exclusions>
</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

The introduction ofmybatis-spring-boot-starterRely on automated configuration of MyBatis.
The introduction ofmapper-spring-boot-starterDependency, automated configuration of Mapper.
The introduction ofpagehelper-spring-boot-starterDependency, automatic configuration of PageHelper.

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.tkmybatis.entity Configure the database entity package path

Mapper configuration content
mapper:
  not-empty: true # whether a null field is determined during INSERT and UPDATE operations. The < if test = 'XXX! = null' />
  identity: MYSQL

# PageHelper configuration content
# specific parameters, see https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
pagehelper:
  helperDialect: mysql The pagination plugin automatically detects the current database link and automatically selects the appropriate pagination method. You can configure the helperDialect property to specify which dialect to use for paging plug-ins.
  reasonable: true The default value is false. When this parameter is set to true, the first page is queried when pageNum<=0, and the last page is queried when pageNum> Pages exceeds the total number. If the default value is false, query is performed based on parameters.
  supportMethodsArguments: true # Support paging through Mapper interface parameters, default value is false, paging plug-in will be from the query method parameter value, automatically according to the above params configuration of the value of the field, find the appropriate value will automatically paging
Copy the code

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.tkmybatis.mapper")
@Configuration
public class MybatisConfig {}Copy the code
```
To specify the basic package path of Mybatis scan package

Write the code

Writing entity classes

@Data
@Table(name = "user")
public class User {
    /** * primary key */
    // Indicates the primary key ID of the field
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY, generator = "JDBC")
    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'.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. Tkmybatis. Create interface UserMapper mapper package, in the same way as MyBatis – Plus, this time we need to inherit tkmybatis mapper, the basis of

Tk.mybatis.mapper.com mon. Mapper interface, the code is as follows:

/ * * *@author lilong
 */
@Repository
public interface UserMapper extends Mapper<User> {

    /** * select ** from username@param username
     * @return* /
    default User selectByUsername(@Param("username") String username){
        Example example = new Example(User.class);
        // Create Criteria and set the query Criteria for username
        example.createCriteria().andEqualTo("username", username);
        // Execute the query
        return selectOneByExample(example);
    }

    /** * query by creation time *@param createTime
     * @return* /
    default List<User> selectByCreateTime(@Param("createTime") Date createTime) {
        Example example = new Example(User.class);
        // Create Criteria and set the create_time query condition
        example.createCriteria().andGreaterThan("createTime", createTime);
        returnselectByExample(example); }}Copy the code

In MyBatis-Plus, we assemble dynamic SQL using QueryWrapper. In TkMyBatis, it provides Example and Criteria classes.
How PageHelper is used for paging will be shown in the unit tests

Unit testing

@SpringBootTest
@Slf4j
class UserMapperTest {

    @Resource
    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 selectByPrimaryKey(a) {
        User user = userMapper.selectByPrimaryKey(14);
        log.info("user:{}", user.toString());
    }

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

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

    @Test
    void deleteByPrimaryKey(a) {
        Integer count = userMapper.deleteByPrimaryKey(14);
        log.info("count:{}", count);
    }

    @Test / / use more, you can refer to https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
    public void testSelectPageByCreateTime(a) {
        // Set paging
        PageHelper.startPage(1.10);
        Date createTime = new Date(2018 - 1990, Calendar.FEBRUARY, 24); // Temporary Demo
        // Perform a list query
        // PageHelper will automatically initiate the number of pages query, set to PageHelper
        List<User> users = userMapper.selectByCreateTime(createTime); / / the actual return is com. Making. Pagehelper) Page proxy objects
        // Convert to a PageInfo object and print pages
        PageInfo<User> page = newPageInfo<>(users); System.out.println(page.getTotal()); }}Copy the code

Paging logic in # testSelectPageByCreateTime () method, we have already implemented.

summary

Now SpringBoot integration of Mybaits introductory tutorial has ended, we recall, integration of Mybatis there are a total of 4 combinations, respectively:

MyBatis + XML
MyBatis + annotation
MyBatisPlus
TkMyBatis

XML is relatively primitive, requiring a large number of HANDWRITTEN CRUD SQL, the development efficiency is not too high, requiring a large number of handwritten SQL.

Annotation this way, although you can omit the XML file writing, but the SQL written in Java code, code maintenance, readability is poor, also can not avoid handwritten a large number of SQL.

The recommendation is to use MyBatisPlus, for single table CRUD can automatically help to generate, the official documentation is more systematic and comprehensive.

As for TkMyBatis, positioning is the toolbox of MyBatis, which is composed of two open source projects, general Mapper and MyBatis -PageHelper, which can also provide the same functions of MyBatisPlus. The official documents are slightly lacking at present.

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-Plus

Introduction of depend on

Add the configuration

Write the code

Unit testing

tkMybatis

Introduction of depend on

Add the configuration

Write the code

Unit testing

summary

One last word

Related Posts

Git Git

How about primary key ID in distributed cluster table

Java concurrent with the JUC document ultimatum