Create a suitable for their own rapid development framework – universal query design and implementation

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