打造一款适合自己的快速开发框架-通用查询设计及实现

前言

一般来说所有的系统都离不开查询，常规的做法都是前端传实体属性，后端通过实体的属性作为条件进行查询。此种方式会使用到Mybatis的动态sql拼接where条件。在遇到查询需求不是很明朗需要经常改动的情况下，可能并不是很方便。本快速开发框架在查询上使用了一种比较特殊的方式，就是定义一个通用的查询规范，由前端根据需要，调整查询条件。该方式可能和Mybatis-Plus Wrapper有点像，但是可能就没他做的那么重。有兴趣的同学可以去了解一下。

设计说明

相关操作

操作名	说明
EQ	等于=
NE	不等于<>
GT	大于>
GE	大于等于>=
LT	小于<
LE	小于等于<=
BT	between 值1 and 值2
NBT	not between 值1 and 值2
LIKE	like '%值%'
NLIKE	not like '%值%'
LLIKE	like '%abc'
RLIKE	like 'abc%'
IN	in(值1,值2)
NIN	not in(值1,值2)

入参样例

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

自动拼接的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)

目前只实现了简单的单表查询，复杂的查询还是需要自己编写sql。

开始编码

目录结构

├── mldong-admin  管理端接口
	├── src/main/java
		├──	com.mldong.modules.sys
			├── controller
				└── SysUserController.java
			├──	dto
				└──	SysUserPageParam.java
			└──	service
				├── impl
					└── SysUserServiceImpl.java
				└── SysUserService.java
├── mldong-common  工具类及通用代码
	├── src/main/java
		├──	com.mldong.common
			├── base
				├── OperateTypeEnum.java
				├──	QueryModel.java
				└──	PageParam.java 
			└── tk
				└── ConditionUtil.java
├── mldong-generator  代码生成器

核心文件说明

• mldong-common/src/main/java/com/mldong/common//OperateTypeEnum.java

操作类型枚举定义

package com.mldong.common.base;
/**
 * 操作类型
 * @author mldong
 *
 */
public enum OperateTypeEnum {
	EQ("等于","="),
	NE("不等于","<>"),
	GT("大于",">"),
	GE("大于等于",">="),
	LT("小于","<"),
	LE("小于等于","<="),
	BT("区间范围","between and"),
	NBT("非区间范围","not between and"),
	LIKE("模糊","like '%aa%'"),
	LLIKE("左模糊","like '%a'"),
	RLIKE("右模糊","like 'a%'"),
	IN("包含","in"),
	NIN("不包含","not in")
	;
	OperateTypeEnum(String name,String desc) {
		this.name = name;
		this.desc = desc;
	}
	private String name;
	private String desc;
	public String getName() {
		return name;
	}
	public String getDesc() {
		return desc;
	}

• mldong-common/src/main/java/com/mldong/common/base/WhereParam.java

自定义查询模型，用于接收前端单个查询条件的实体类

package com.mldong.common.base;

import io.swagger.annotations.ApiModelProperty;

import com.mldong.common.base.OperateTypeEnum;
/**
 * 自定义查询实体
 * @author mldong
 *
 */
public class WhereParam {
	/**
	 * 操作类型
	 */
	@ApiModelProperty(value="操作类型",required=true)
	private OperateTypeEnum operateType;
	/**
	 * 属性名
	 */
	@ApiModelProperty(value="属性名",required=true)
	private String propertyName;
	/**
	 * 属性值
	 */
	@ApiModelProperty(value="属性值",required=true)
	private Object propertyValue;
	
	public OperateTypeEnum getOperateType() {
		return operateType;
	}
	public void setOperateType(OperateTypeEnum operateType) {
		this.operateType = operateType;
	}
	public String getPropertyName() {
		return propertyName;
	}
	public void setPropertyName(String propertyName) {
		this.propertyName = propertyName;
	}
	public Object getPropertyValue() {
		return propertyValue;
	}
	public void setPropertyValue(Object propertyValue) {
		this.propertyValue = propertyValue;
	}
}

• mldong-common/src/main/java/com/mldong/common/tk/ConditionUtil.java

tk的查询条件构建

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条件工具封装
 * @author mldong
 *
 */
public class ConditionUtil {
	private ConditionUtil() {}
	/**
	 * 通过查询构型构造tk查询条件
	 * @param clazz
	 * @param list
	 * @return
	 */
	public static Condition buildCondition(Class<?> clazz,List<WhereParam> 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;
			}
		}
		return condition;
	}
}

• mldong-common/src/main/java/com/mldong/common/PageParam.java

分页查询实体基类

package com.mldong.common.base;

import io.swagger.annotations.ApiModelProperty;

import java.util.List;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;

/**
 * 分页查询基类
 * @author mldong
 *
 * @param <T>
 */
public class PageParam<T> {
	/**
	 * 每几页
	 */
	@ApiModelProperty(value="每几页")
	private int pageNum;
	/**
	 * 每页大小
	 */
	@ApiModelProperty(value="每页大小")
    private int pageSize;
	public int getPageNum() {
		return pageNum;
	}
	@ApiModelProperty(value="自定义查询参数集合")
	private List<WhereParam> whereParams;

	
	public List<WhereParam> getWhereParams() {
		return whereParams;
	}
	public void setWhereParams(List<WhereParam> whereParams) {
		this.whereParams = whereParams;
	}
	public void setPageNum(int pageNum) {
		this.pageNum = pageNum;
	}
	public int getPageSize() {
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
	public <T> Page<T> buildPage() {
        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);
    }
}

• mldong-admin/src/main/java/com/mldong/modules/sys/service/impl/SysUserServiceImpl.java

查询调用代码片段

@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);
	}

• mldong-admin/src/main/java/com/mldong/modules/sys/service/impl/SysUserServiceImpl.java

控制层代码片段

/**
	 * 分页查询用户列表
	 * @param param
	 * @return
	 */
	@PostMapping("list")
	@ApiOperation(value="分页查询用户列表", notes="分页查询用户列表")
	public CommonResult<CommonPage<SysUser>> list(@RequestBody SysUserPageParam param) {
		return CommonResult.success("查询用户成功",sysUserService.list(param));
	}

• mldong-admin/src/main/java/com/mldong/modules/sys/dto/SysUserPageParam.java

  新增的分页查询实体，继承基类，又可自行扩展，对应的代码生成模板为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="用户分页查询实体")
public class SysUserPageParam extends PageParam<SysUser> {
	
}

小结

本文的通用查询是基于tk的单表查询做的，只是在接收参数那层做了封装，只满足了一些基础的单表查询需求。复杂的查询可以通过查询实体扩展参数接收，然后新建对应的dao层进行自定义。

项目源码地址

• 后端

gitee.com/mldong/mldo…

• 前端

gitee.com/mldong/mldo…

相关文章

打造一款适合自己的快速开发框架-先导篇

打造一款适合自己的快速开发框架-后端脚手架搭建

打造一款适合自己的快速开发框架-集成mapper

打造一款适合自己的快速开发框架-集成swaggerui和knife4j

打造一款适合自己的快速开发框架-通用类封装之统一结果返回、统一异常处理

打造一款适合自己的快速开发框架-业务错误码规范及实践

打造一款适合自己的快速开发框架-框架分层及CURD样例

打造一款适合自己的快速开发框架-mapper逻辑删除及枚举类型规范

打造一款适合自己的快速开发框架-数据校验之Hibernate Validator

打造一款适合自己的快速开发框架-代码生成器原理及实现