MybatisPlus Advanced Guide
preface
At present, most companies in the market will use MybatisPlus to develop, but when encountering some complex sql, we will still choose xml to write sql. This paper intends to mine the advanced functions of MybatisPlus to realize relatively complex sql and customization of sql.
Attachment: please refer to the official documents for the use of the basic part of MybatisPlus MybatisPlus documentation
Update and query of some entity fields
Query some entity field values
// first kind select(String... sqlSelect) // Class II select(Predicate<TableFieldInfo> predicate) select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)
The first method directly queries the corresponding fields
select("id", "name", "age") // General query
select(PatientDO::getPatientName, PatientDO::getPinyin) // lambda query
The second method is to filter the query fields (except the primary key). If the input parameter does not contain class, the entity attribute in the wrapper needs to have a value before calling! The last repeated call of these two methods shall prevail
select(i -> i.getProperty().startsWith("marco"))
Update some entity field values
// first kind set(String column, Object val) set(boolean condition, String column, Object val) // Class II setSql(boolean condition, String sql)
The first type is to set and update entity fields one by one
default void updatePatientName(PatientDO patientDO) {
update(patientDO, new LambdaUpdateWrapper<PatientDO>()
.set(PatientDO::getPatientName, patientDO.getPatientName()));
};
The second type is similar to writing sql directly, but the value needs to be written dead, which is not recommended
setSql(true, "patientName = 'marco'")
The above two methods are applicable to blank a field
Custom SQL
When some scenarios require customized sql for all methods, mybatis plus provides the following two methods
For example, I now need to define a common BaseMapper method, and other mappers inherit BaseMapper
baseMapper.getAll(Wrappers.<PatientDO>lambdaQuery().eq(PatientDO::isDeleted, 1));
Annotation method
@Select("select * from patient ${ew.customSqlSegment}")
List<PatientDO> getAll(@Param(Constants.WRAPPER) Wrapper wrapper);
Xml mode
<select id="getAll" resultType="MysqlData">
SELECT * FROM entity ${ew.customSqlSegment}
</select>
Chain CURD (for understanding only)
The previous queries are to create a condition constructor, and then query with the condition constructor as the parameter through mapper or service. For example, LambdaQueryChainWrapper combines the previous two sentences into one to complete the conditional query.
Chain query
// Chain query general
QueryChainWrapper<T> query();
// Chained query lambda. Note: Kotlin is not supported
LambdaQueryChainWrapper<T> lambdaQuery();
// Example:
query().eq("column", value).one();
lambdaQuery().eq(Entity::getId, value).list();
Chain update
// Chain change normal
UpdateChainWrapper<T> update();
// Chain change lambda. Note: Kotlin is not supported
LambdaUpdateChainWrapper<T> lambdaUpdate();
// Example:
update().eq("column", value).remove();
lambdaUpdate().eq(Entity::getId, value).update(entity);
Note: to create LambdaQueryChainWrapper object, you need to pass mapper object. Another disadvantage is that chained query only has list() and one() methods to obtain data. All returned are entity objects or collections of entity objects, and there is no corresponding Map() query method.
@Autowired
private CustomerMapper customerMapper;
public void selectByCustomerName() {
List<CustomerDO> list = new LambdaQueryChainWrapper<CustomerDO>(customerMapper)
.select(CustomerDO::getId, Customer::getName)
.like(CustomerDO::getName, "marco").list();
BaseMapper extends general Sql
Step 1: define the method of general sql statement
/**
* Delete all
*/
public class DeleteAll extends AbstractMethod {
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
/* Execute SQL, dynamic SQL reference class SqlMethod */
String sql = "delete from " + tableInfo.getTableName();
/* mapper Consistent interface method name */
String method = "deleteAll";
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
return this.addDeleteMappedStatement(mapperClass, method, sqlSource);
}
}
Step 2: define and inject MySQL injector
/**
* Custom Sql injection
*
* @author nieqiurong 2018/8/11 20:23.
*/
public class MySqlInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
//Add custom method
methodList.add(new DeleteAll());
/**
* The following 3 are built-in options
* The first 2 support field filtering functions
*/
// Example: do not specify the fields filled in by update
methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
methodList.add(new AlwaysUpdateSomeColumnById());
methodList.add(new LogicDeleteByIdWithFill());
return methodList;
}
}
@Configuration
public class MybatisPlusConfig {
@Bean
public MySqlInjector sqlInjector() {
return new MySqlInjector();
}
}
Step 3: customize the method interface of sql
public interface MyBaseMapper<T> extends BaseMapper<T> {
/**
* There are 4 method s defined below, of which 3 are built-in options
*/
int insertBatchSomeColumn(List<T> entityList);
int alwaysUpdateSomeColumnById(@Param(Constants.ENTITY) T entity);
int deleteByIdWithFill(T entity);
/**
* The following is self defined
*/
int deleteAll();
}
Injecting complex queries using @ SelectProvider
Define query criteria PatientSelectProvider
Single parameter
public class PatientSelectProvider {
public String selectById(String patientId) {
return new SQL(){{
SELECT("patient_id, patient_name");
FROM("patient");
WHERE("patient_id ="+ patientId);
}}.toString();
}
}
Reference Provider in Mapper
@SelectProvider(value = PatientSelectProvider.class, method = "selectById") PatientDO selectByPatientId();
Note: when the parameters passed in mapper are decorated with @ param annotation, the Map object must be used in the xxprovider class to receive parameters.
Multiple parameters
public String selectUserById(Map<String, Object> para){
return new SQL(){{
SELECT("patient_id, patient_name");
FROM("patient");
WHERE("patient_id=" + para.get("patientId"));
if(StringUtils.isNotBlank((String)para.get("patientName"))){
WHERE("patientName=" + para.get("patientName"));
}
}}.toString();
}
Note: the sql writing method at this time does not need to use and to connect in the spliced sql. The where source code has been spliced into the where method as follows
private static final String AND = ") \nAND (";
private static final String OR = ") \nOR (";
Functional sql programming
func
func(Consumer<Children> consumer) func(boolean condition, Consumer<Children> consumer)
func method (it is mainly convenient to use different methods to continuously chain when if... else is called)
func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})
or
// Splicing or() or(boolean condition) // nesting or(Consumer<Param> consumer) or(boolean condition, Consumer<Param> consumer)
Splice OR
eq("id",1).or().eq("name","marco")
// --Equivalent to - > id = 1 or name = 'Marco'
Note: actively calling or means that the next method is not connected with and! (if or is not called, and connection is used by default.)
Nested OR
or(i -> i.eq("name", "marco").ne("status", "1"))
// --Equivalent to - > or (name = 'Marco' and status < > '1')
and
and(Consumer<Param> consumer) and(boolean condition, Consumer<Param> consumer)
and(i -> i.eq("name", "marco").ne("status", "1"))
// --Equivalent to - > and (name = 'Marco' and status < > '1')
apply
apply(String applySql, Object... params) apply(boolean condition, String applySql, Object... params)
matters needing attention:
This method can be used for the params of the dynamic input parameters of the database function, which corresponds to the {index} part inside the previous applySql. In this way, there will be no sql injection risk. On the contrary, there will be ~ (the last method is recommended)
apply("id = 1")
// --Equivalent to - > id = 1
apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
// --Equivalent to - > date_ format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")
// --Equivalent to - > date_ format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
last
last(String lastSql) last(boolean condition, String lastSql)
matters needing attention:
It can only be called once. The last call shall prevail. There is a risk of sql injection. Please use it with caution
// Common methods are as follows
last("limit 1")
FieldFill annotation
For the fields to be added and filled, add
@TableField(fill = FieldFill.INSERT) private Date createTime
For the fields to be added or filled, add
@TableField(fill = FieldFill.INSERT_UPDATE) private Date updateTime
Linked table query
It is recommended to use xml to query linked tables. Of course, you can also use the following methods to query linked tables
Annotation method
//Multi table joint query by condition orderID
@Select("select t1.*,t2.user_name,t2.nick_name from orders t1 LEFT JOIN users t2 ON t1.user_id =t2.id WHERE t1.user_id= #{id}")
List<Map<String,Object>> orderUserList(Page<Map<String,Object>> page, String id);
}
External extension method of SelectProvider
First, define the SelectProvider entity
public String selectByPatientId(Long patientId) {
return new SQL(){{
SELECT("p.patient_id, p.patient_name, pc.mobile");
FROM("patient p");
LEFT_OUTER_JOIN("patient_contacts pc on p.patient_id = pc.patient_id");
WHERE("p.patient_id = " + patientId);
}}.toString();
}
Next, reference the Provider in Mapper
@SelectProvider(value = PatientSelectProvider.class, method = "selectByPatientId") PatientDO selectByPatientId(Long patientId);
Optimistic lock plug-in
Implementation method of optimistic lock: when taking out the record, when obtaining the current version update, when carrying this version to execute the update,
set version = newVersion where version = oldVersion
If the version is wrong, the update fails. The optimistic lock configuration needs two steps. Remember two steps
1. Plug in configuration
First, inject the optimisticlocker interceptor
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor() {
return new OptimisticLockerInterceptor();
}
2. Annotation entity field @ Version must be!
@Version private Integer version;
Special note:
Only int,Integer,long,Long,Date,Timestamp,LocalDateTime integer types are supported
newVersion = oldVersion + 1 newVersion will be written back to entity, only supported
updateById(id) and update(entity, wrapper) methods
Under the update(entity, wrapper) method, the wrapper cannot be reused!!!
int id = 100;
int version = 2;
User u = new User();
u.setId(id);
u.setVersion(version);
...
u.setXXX(xxx);
if(userService.updateById(u)){
System.out.println("Update successfully");
}else{
System.out.println("Update failed due to modified by others");
}
// --Equivalent to - > Update TBL_ user set name = 'update',version = 3 where id = 100 and version = 2