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> { }