preface
Generally speaking, all systems are inseparable from query. The conventional approach is to transmit entity attributes at the front end and query through entity attributes as conditions at the back end. This approach uses Mybatis’ dynamic SQL concatenation of WHERE conditions. This may not be very convenient in cases where the query requirements are not very clear and require frequent changes. This rapid development framework uses a special way in the query, which is to define a general query specification, and adjust the query conditions according to the needs of the front end. This may be similar to the Mybatis-Plus Wrapper, but it may not be as heavy. Those of you who are interested can go and check it out.
Design specification
The relevant operation
The operation name | instructions |
---|---|
EQ | Equal to = |
NE | Is not the same as < > |
GT | More than > |
GE | The value is greater than or equal to >= |
LT | < < |
LE | Less than or equal to <= |
BT | Between values 1 and 2 |
NBT | Not between The value is 1 and 2 |
LIKE | Like ‘% % values’ |
NLIKE | Not like ‘% value %’ |
LLIKE | like ‘%abc’ |
RLIKE | like ‘abc%’ |
IN | In (value 1, value 2) |
NIN | Not in(value 1, value 2) |
Into the sample
{
"pageNum": 1."pageSize": 15."whereParams": [{"operateType": "LIKE"."propertyName": "userName"."propertyValue": "admin"
},
{
"operateType": "BT"."propertyName": "createTime"."propertyValue": ["2020-01-01"."2020-06-06"] {},"operateType": "EQ"."propertyName": "isLocked"."propertyValue": 2}}]Copy the code
Automatic concatenation SQL
SELECT id,user_name,real_name,avatar,email,mobile_phone,telephone,password.salt,sex,is_locked,create_time,update_time,is_deleted FROM sys_user WHERE is_deleted = 2 and ( ( user_name like ? and create_time between ? and ? and is_locked = ? ) ) LIMIT ?
-- %admin%(String), 2020-01-01(String), 2020-06-06(String), 2(Integer), 15(Integer)
Copy the code
At present, only a simple single table query, complex query or need to write their OWN SQL.
Start coding
The directory structure
├─ ├─ SRC /main/ Java ├─ com.mlDong.modules. Sys ├─ Controller ├─ Dto └ ─ ─ SysUserPageParam. Java └ ─ ─ service ├ ─ ─ impl └ ─ ─ SysUserServiceImpl. Java └ ─ ─ SysUserService. Java ├ ─ ─ mldong - common ├─ SRC /main/ Java ├─ ├─ Java ├─ Java ├─ SRC /main/ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ Java ├─ PageParam. Java ├── Java ├─ Java ├─ Java ├─Copy the code
Core File Description
mldong-common/src/main/java/com/mldong/common//OperateTypeEnum.java
Operation type enumeration definition
package com.mldong.common.base;
/** * Operation type *@author mldong
*
*/
public enum OperateTypeEnum {
EQ("Equal"."="),
NE("Not equal"."< >"),
GT("More than".">"),
GE("Greater than or equal to"."> ="),
LT("Less than"."<"),
LE("Less than or equal to"."< ="),
BT("Interval range"."between and"),
NBT("Non-interval range"."not between and"),
LIKE("Fuzzy"."like '%aa%'"),
LLIKE("Left blur"."like '%a'"),
RLIKE("Blurred right"."like 'a%'"),
IN("Contains"."in"),
NIN("Not included"."not in"); OperateTypeEnum(String name,String desc) {this.name = name;
this.desc = desc;
}
private String name;
private String desc;
public String getName(a) {
return name;
}
public String getDesc(a) {
return desc;
}
Copy the code
mldong-common/src/main/java/com/mldong/common/base/WhereParam.java
A custom query model for entity classes that receive a single query condition from the front end
package com.mldong.common.base;
import io.swagger.annotations.ApiModelProperty;
import com.mldong.common.base.OperateTypeEnum;
/** * Custom query entity *@author mldong
*
*/
public class WhereParam {
/** * Operation type */
@ApiModelProperty(value="Operation type",required=true)
private OperateTypeEnum operateType;
/** * Attribute name */
@ApiModelProperty(value="Attribute name",required=true)
private String propertyName;
/** * Attribute value */
@ApiModelProperty(value="Attribute value",required=true)
private Object propertyValue;
public OperateTypeEnum getOperateType(a) {
return operateType;
}
public void setOperateType(OperateTypeEnum operateType) {
this.operateType = operateType;
}
public String getPropertyName(a) {
return propertyName;
}
public void setPropertyName(String propertyName) {
this.propertyName = propertyName;
}
public Object getPropertyValue(a) {
return propertyValue;
}
public void setPropertyValue(Object propertyValue) {
this.propertyValue = propertyValue; }}Copy the code
mldong-common/src/main/java/com/mldong/common/tk/ConditionUtil.java
Tk query condition construction
package com.mldong.common.tk;
import java.util.List;
import tk.mybatis.mapper.entity.Condition;
import tk.mybatis.mapper.entity.Example.Criteria;
import com.mldong.common.base.WhereParam;
/** * tk conditional tool package *@author mldong
*
*/
public class ConditionUtil {
private ConditionUtil(a) {}
/** * Construct tk query condition * by querying configuration@param clazz
* @param list
* @return* /
public static Condition buildCondition(Class
clazz,List
list)
{
Condition condition = new Condition(clazz);
Criteria criteria = condition.createCriteria();
for(WhereParam model: list) {
switch (model.getOperateType()) {
case EQ:
criteria.andEqualTo(model.getPropertyName(), model.getPropertyValue());
break;
case NE:
criteria.andNotEqualTo(model.getPropertyName(), model.getPropertyValue());
break;
case GT:
criteria.andGreaterThan(model.getPropertyName(), model.getPropertyValue());
break;
case GE:
criteria.andGreaterThanOrEqualTo(model.getPropertyName(), model.getPropertyValue());
break;
case LT:
criteria.andLessThan(model.getPropertyName(), model.getPropertyValue());
break;
case LE:
criteria.andLessThanOrEqualTo(model.getPropertyName(), model.getPropertyValue());
break;
case BT:
List<Object> listObject = (List<Object>) model.getPropertyValue();
criteria.andBetween(model.getPropertyName(), listObject.get(0),listObject.get(1));
break;
case NBT:
listObject = (List<Object>) model.getPropertyValue();
criteria.andNotBetween(model.getPropertyName(), listObject.get(0),listObject.get(1));
break;
case LIKE:
criteria.andLike(model.getPropertyName(), "%"+model.getPropertyValue()+"%");
break;
case LLIKE:
criteria.andLike(model.getPropertyName(), "%"+model.getPropertyValue());
break;
case RLIKE:
criteria.andLike(model.getPropertyName(), model.getPropertyValue()+"%");
break;
case IN:
listObject = (List<Object>) model.getPropertyValue();
criteria.andIn(model.getPropertyName(), listObject );
break;
case NIN:
listObject = (List<Object>) model.getPropertyValue();
criteria.andNotIn(model.getPropertyName(), listObject );
default:
break; }}returncondition; }}Copy the code
mldong-common/src/main/java/com/mldong/common/PageParam.java
Paging query entity base classes
package com.mldong.common.base;
import io.swagger.annotations.ApiModelProperty;
import java.util.List;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
/** * paged query base class *@author mldong
*
* @param <T>
*/
public class PageParam<T> {
/** ** every few pages */
@ApiModelProperty(value="Every few pages")
private int pageNum;
/** * Size per page */
@ApiModelProperty(value="Page size")
private int pageSize;
public int getPageNum(a) {
return pageNum;
}
@ApiModelProperty(value="Custom Query Parameter Set")
private List<WhereParam> whereParams;
public List<WhereParam> getWhereParams(a) {
return whereParams;
}
public void setWhereParams(List<WhereParam> whereParams) {
this.whereParams = whereParams;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageSize(a) {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public <T> Page<T> buildPage(a) {
return buildPage(false);
}
public <T> Page<T> buildPage(boolean count) {
if(this.pageNum == 0) {
this.pageNum = 1;
}
if(this.pageSize==0) {
this.pageSize=15;
}
return PageHelper.startPage(this.pageNum, this.pageSize, count); }}Copy the code
mldong-admin/src/main/java/com/mldong/modules/sys/service/impl/SysUserServiceImpl.java
Query invocation code snippet
@Override
public CommonPage<SysUser> list(SysUserPageParam param) {
Page<SysUser> page =param.buildPage(true);
List<WhereParam> queryModelList = param.getWhereParams();
if(null == queryModelList || queryModelList.isEmpty()) {
SysUser user = new SysUser();
sysUserMapper.select(user);
} else {
sysUserMapper.selectByCondition(ConditionUtil.buildCondition(SysUser.class, queryModelList)); }
return CommonPage.toPage(page);
}
Copy the code
mldong-admin/src/main/java/com/mldong/modules/sys/service/impl/SysUserServiceImpl.java
Control layer code snippet
/** ** page query user list *@param param
* @return* /
@PostMapping("list")
@ApiOperation(value="Paging query user list", notes="Paging query user list")
public CommonResult<CommonPage<SysUser>> list(@RequestBody SysUserPageParam param) {
return CommonResult.success("User query succeeded",sysUserService.list(param));
}
Copy the code
-
mldong-admin/src/main/java/com/mldong/modules/sys/dto/SysUserPageParam.java
The new paging query entity inherits the base class and can be extended by itself. The corresponding code generation template is pageparam.ftl.
package com.mldong.modules.sys.dto;
import io.swagger.annotations.ApiModel;
import com.mldong.common.base.PageParam;
import com.mldong.modules.sys.entity.SysUser;
@ApiModel(description="User paging query entity")
public class SysUserPageParam extends PageParam<SysUser> {}Copy the code
summary
The general query in this paper is based on tk single table query, only in the layer of receiving parameters to do encapsulation, only to meet some basic single table query requirements. Complex queries can be received by querying entity extension parameters and then customized by creating the corresponding DAO layer.
Project source code address
- The back-end
Gitee.com/mldong/mldo…
- The front end
Gitee.com/mldong/mldo…
Related articles
Create a suitable for their own rapid development framework – the pilot
Build a suitable for their own rapid development framework – back-end scaffolding
Build a fast development framework for yourself – integrated Mapper
Build a fast development framework for yourself – integration with Swaggerui and KNIfe4J
Build a suitable for their own rapid development framework – universal class packaging unified result return, unified exception handling
Create a quick development framework for yourself – business error code specifications and practices
Build a quick development framework for yourself – framework layering and CURD sample
Create a suitable for their own rapid development framework – Mapper logical deletion and enumeration type specification
Create a suitable framework for rapid development – Hibernate Validator data verification
Create a suitable for their own rapid development framework – code generator principle and implementation