Use mybatis with TK Mybatis for database operation

This is a spring cloud project. mybatis uses TK Myabtis general mapper summary blog

Use TK Mybatis is very simple, but there are some pits. Now let's talk about introducing mybatis, which is also very simple

1. First, introduce the dependency of mybatis, jdbc and mysql connecteor into the Controller module (i.e. the main class module of spring boot)

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

2. Then add the configuration of datesource in the configuration file of spring boot, and create mybatis config in the resource folder XML. If you want to configure mybatis in it, you can do it without configuration

My profile is application properties

#Database url
spring.datasource.url= url
#Database user name
spring.datasource.username= username
#Database password
spring.datasource.password= password
#mysql driver
spring.datasource.driverClassName=com.mysql.jdbc.Driver

3. Finally, add the mybatis dependency in the dao module, and then you can use myabtis. However, if you want to use it now, you need to add the mappers element in the mybatis configuration file to scan the mapper, or use the @ MapperScan annotation in the spring boot main class to configure the scanned mapper package

The next step is to use TK mybatis

4. Introduce TK in Controller module and dao module Mybatis dependency, and then create the mapper package of dao module. If paging query is needed, introduce paging query dependency in dao module

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.9</version>
</dependency>

5. Mark the @ MapperScan annotation on the spring boot main class to scan the package where the mapper is located. Note that the annotation used is TK mybatis. spring. annotation. The @ MappperScan annotation of MapperScan is not the MapperSacn annotation under mybatis package

6. Next is TK Use of mybatis general mapper

Generally speaking, you create a CommonMapper first, and then inherit CommonMapper and customize some operations. Of course, you can not do custom operations, TK The general mapper of mybatis itself has defined many operations, mainly including single and add / delete query according to example, but for modification, there is only the method of modifying according to id, so batch modification needs to be customized, but the methods provided by the general mapper are generally sufficient

User is an entity class that maps to the user table of the database

CommonMapper

public interface CommonMapper<T> extends Mapper<T>, MySqlMapper<T> {
}

Note that common mapper cannot be injected directly, for example:

//Doing so will result in an exception
@Autowired
protected CommonMapper<User> userMapper;

UserMapper

@Component
public interface UserMapper extends CommonMapper<User> {
//Some custom operations
}

Some common uses are not listed, and some use:

Paging query by criteria:

private static final Integer MIN_PAGE_SIZE = 5;
private static final Integer MAX_PAGE_SIZE = 50;
private static final Integer DEFAULT_PAGE_SIZE = 10;
private static final String SORT_STYLE = "updated_time desc";
@Autowired
private UserMapper userMapper;
public Page<User> queryUser(Query query) {
    Integer size = query.getPageSize();
    Integer page = query.getPage();
    /**
     * Judge the legitimacy of page number and page size, and set the page number and page size of page query
     * */
    if(queryPaperVO.getPage() == null){
        page = 1;
    }
    if(size == null || size < MIN_PAGE_SIZE || size > MAX_PAGE_SIZE){
        size = DEFAULT_PAGE_SIZE;
    }
    PageMethod.startPage(page,size);
    /**
     * Non null verification and query based on non null conditions
     * */
    Example example = new Example(User.class);
    Example.Criteria ec = example.createCriteria();
    example.setOrderByClause(SORT_STYLE);
    //For multi condition query, use spring's StringUtils for non null verification
    if(!StringUtils.isEmpty(query.getName())){
        ec.andLike("name",SYMBOL+query.getName()+SYMBOL);
    }
    if(queryPaperVO.getCompanyId() != null){
        ec.andEqualTo("companyId",query.getCompanyId());
    }
    if(queryPaperVO.getOrgId() != null){
        ec.andEqualTo(ORG_ID,query.getOrgId());
    }
    
   return  (Page<User>) userMapper.selectByExample(example);
}

Batch delete

@Override
public Boolean deleteUsers(List<User> users) {
	//Set the id list, and use collection and other traversable structures
    List<Long> userId = new ArrayList<>();
    for(User user : users){
        userIds.add(user.getId());
    }
    Example example = new Example(User.class);
    Example.Criteria userEC = example.createCriteria();
    userEC.andIn("id",userId);
    return paperSubjectAnswerMapper.deleteByExample(example) > 0;
}

Some points needing attention:

1. If the database table name is different from the entity class name, you need to mark @ Table(name = "table name") on the entity class

2. You need to mark @ Id annotation on Id (primary key), otherwise the * byprimarykey method of general mapper will not find the corresponding record

@Table(name = "t_user")
public class User {
    @Id
    private Long id;
    private String name;
}

3. TK is used for batch insertion of general mapper mybatis. mapper. common. special. Insertlistmapper. At this time, the database needs to set the primary key self increment or the entity class is marked with the primary key growth strategy as self increment

If you want to use the id you set as the primary key, you need to use TK mybatis. mapper. additional. insert. insetListMapper of insertlistmapper

import paper.pojo.entity.User;
import org.springframework.stereotype.Component;
import tk.mybatis.mapper.additional.insert.InsertListMapper;

@Component
public interface UserListMapper extends InsertListMapper<User> {
}