上一篇博客我们已经了解到了基于tkmybatis封装常用的增删改查接口【链接地址】,接下来我们来讲一下怎么封装通用的统计接口。
1 MyDefineGroupByMapper
import org.apache.ibatis.annotations.SelectProvider;
import tk.mybatis.mapper.annotation.RegisterMapper;
import tk.mybatis.mapper.entity.GroupByExample;
import tk.mybatis.mapper.provider.MyDefineGroupByProvider;
import java.util.List;
import java.util.Map;
/**
* @description:
* @author:wanzh
* @date:2021/6/29
*/
@RegisterMapper
public interface MyDefineGroupByMapper<T> {
@SelectProvider(type = MyDefineGroupByProvider.class, method = "dynamicSQL")
List<Map<String,Object>> selectGroupByExample(GroupByExample example);
}
2 MyDefineGroupByProvider
import org.apache.ibatis.mapping.MappedStatement;
import tk.mybatis.mapper.mapperhelper.GroupBySqlHelper;
import tk.mybatis.mapper.mapperhelper.MapperHelper;
import tk.mybatis.mapper.mapperhelper.MapperTemplate;
import tk.mybatis.mapper.mapperhelper.SqlHelper;
/**
* @description:
* @author:wanzh
* @date:2021/6/29
*/
public class MyDefineGroupByProvider extends MapperTemplate {
public MyDefineGroupByProvider(Class<?> mapperClass, MapperHelper mapperHelper) {
super(mapperClass, mapperHelper);
}
public String selectGroupByExample(MappedStatement ms) {
Class<?> entityClass = getEntityClass(ms);
setResultType(ms, entityClass);
//开始拼sql
StringBuilder sql = new StringBuilder("SELECT ");
sql.append(GroupBySqlHelper.getGroupByColumns(entityClass));
sql.append(SqlHelper.fromTable(entityClass, tableName(entityClass)));
sql.append(SqlHelper.exampleWhereClause());
sql.append(GroupBySqlHelper.getGroupBy());
sql.append(SqlHelper.exampleOrderBy(entityClass));
sql.append(SqlHelper.exampleForUpdate());
return sql.toString();
}
@Override
protected void setResultType(MappedStatement ms, Class<?> entityClass) {
//一定要重写该方法,不然会返回当前实体类所有字段
}
}
3 GroupBySqlHelper
/**
* @description:
* @author:wanzh
* @date:2021/6/28
*/
public class GroupBySqlHelper {
public static String getGroupByColumns(Class<?> entityClass){
StringBuilder sb = new StringBuilder();
sb.append("<choose>");
sb.append("<when test=\"_parameter.selectedColumns != null \">");
sb.append("${_parameter.selectedColumns}");
sb.append("</when>");
sb.append("<otherwise>");
sb.append(SqlHelper.exampleSelectColumns(entityClass));
sb.append("</otherwise>");
sb.append("</choose>");
return sb.toString();
}
public static String getGroupBy(){
StringBuilder sb = new StringBuilder();
sb.append("<if test=\"_parameter.groupByClause != null \">");
sb.append(" GROUP BY ${_parameter.groupByClause}");
sb.append("</if>");
return sb.toString();
}
}
4 GroupByExample
public class GroupByExample extends Example {
private String groupByClause;
private String selectedColumns;
public GroupByExample(Class<?> entityClass) {
super(entityClass);
}
public GroupByExample(Class<?> entityClass, boolean exists) {
super(entityClass, exists);
}
public GroupByExample(Class<?> entityClass, boolean exists, boolean notNull) {
super(entityClass, exists, notNull);
}
public String getGroupByClause() {
return groupByClause;
}
public String getSelectedColumns() {
return selectedColumns;
}
public void setSelectedColumns(String selectedColumns) {
this.selectedColumns = selectedColumns;
}
public void setGroupByClause(String groupByClause) {
this.groupByClause = groupByClause;
}
}
5 RequestParamUtils
public class RequestParamUtils {
private static final String FIELD_SPILT = "_";
private static final String SQL_LIKE_CONTACT_STR = "%";
private static final String REQUEST_PARAM_ERROR = "请求参数格式无效";
/**
* 处理请求参数
* @param obj
* @return
*/
public static GroupByExample handlerGroupRequestParam(Object obj) {
Map<String, String> propertyMap = EntityHelper.getColumns(obj.getClass()).stream().collect(Collectors.toMap(EntityColumn::getProperty, EntityColumn::getColumn));
GroupByExample example = new GroupByExample(obj.getClass());
GroupByExample.Criteria criteria = example.createCriteria();
HttpServletRequest request =
((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
Map<String, String[]> parameterMap = new HashMap(request.getParameterMap());
String[] orders = parameterMap.remove("sortField");
String[] isDesc = parameterMap.remove("isDesc");
String[] groupFields = parameterMap.remove("groupByField");
String[] groupSelectFields = parameterMap.remove("groupSelectField");
boolean flag = false;
for (Iterator iter = parameterMap.entrySet().iterator(); iter.hasNext();) {
Map.Entry element = (Map.Entry) iter.next();
String key = String.valueOf(element.getKey());
String[] values = (String[])element.getValue();
if(key.contains(FIELD_SPILT) && handlerRequestParam(key, values, criteria)){
flag = true;
}
}
if(!flag){
example = new GroupByExample(obj.getClass());
}
orderRequestParam(example, orders, isDesc);
groupRequestParam(example, groupFields, groupSelectFields,propertyMap);
return example;
}
public static void orderRequestParam(Example example,String[] orderArr,String[] isDescArr){
int len = isDescArr == null ? 0 : isDescArr.length;
if(orderArr != null){
for(int i = 0; i < orderArr.length; i++){
Boolean isDesc = Boolean.parseBoolean(i < len ? isDescArr[i] : "false");
if(isDesc){
example.orderBy(orderArr[i]).desc();
}else{
example.orderBy(orderArr[i]).asc();
}
}
}
}
private static void groupRequestParam(GroupByExample example, String[] groupFields, String[] groupSelectFields, Map<String, String> propertyMap) {
if(groupFields == null || groupFields.length == 0){
throw new BaseException("统计字段不能为空");
}
List<String> fieldList = new ArrayList<>();
StringBuilder groupByClauseSb = new StringBuilder();
StringBuilder groupByFieldSb = new StringBuilder();
for(String str : groupFields){
if(!propertyMap.containsKey(str)){
throw new BaseException("当前实体类不包含此属性:"+str);
}
fieldList.add(propertyMap.get(str));
groupByClauseSb.append(","+propertyMap.get(str));
groupByFieldSb.append(","+propertyMap.get(str)+" AS "+str);
}
example.setGroupByClause(groupByClauseSb.toString().substring(1));
if(groupSelectFields != null && groupSelectFields.length > 0){
for(int i = 0; i < groupSelectFields.length; i++){
String groupSelectField = groupSelectFields[i];
String[] split = groupSelectField.split(FIELD_SPILT);
if(split.length < 2){
throw new BaseException("存在不合法的统计字段:"+groupSelectField);
}
String property = split[0];
if(!propertyMap.containsKey(property)){
throw new BaseException("当前实体类不包含此属性:"+property);
}
String type = split[1];
GroupType instance = GroupType.getInstance(type);
if(instance == null){
throw new BaseException("存在不合法的统计字段:"+groupSelectField);
}
if(split.length == 3){
groupByFieldSb.append(","+type+"("+propertyMap.get(property)+") as "+ split[2]);
}else{
groupByFieldSb.append(","+type+"("+propertyMap.get(property)+") as "+ type.toLowerCase());
}
}
}
example.setSelectedColumns(groupByFieldSb.toString().substring(1));
}
/**
* 处理请求参数
* @param obj
* @return
*/
public static Example handlerRequestParam(Object obj) {
Example example = new Example(obj.getClass());
Example.Criteria criteria = example.createCriteria();
HttpServletRequest request =
((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
Map<String, String[]> parameterMap = new HashMap(request.getParameterMap());
String[] orders = parameterMap.remove("sortField");
String[] isDescs= parameterMap.remove("isDesc");
boolean flag = false;
for (Iterator iter = parameterMap.entrySet().iterator(); iter.hasNext();) {
Map.Entry element = (Map.Entry) iter.next();
String key = String.valueOf(element.getKey());
String[] values = (String[])element.getValue();
if(key.contains(FIELD_SPILT) && handlerRequestParam(key, values, criteria)){
flag = true;
}
}
if(!flag){
example = new Example(obj.getClass());
}
orderRequestParam(example, orders, isDescs);
return example;
}
private static boolean handlerRequestParam(String key, String[] values,Example.Criteria criteria){
try {
int index = key.lastIndexOf(FIELD_SPILT);
String field = key.substring(0,index);
String opVal = key.substring(index + 1);
int size = criteria.getCriteria().size();
if(opVal.length() > 1){
String type = opVal.substring(0, 1);
opVal = opVal.substring(1);
QueryOP queryOP = QueryOP.getByVal(opVal);
Optional<List<String>> lists = Optional.ofNullable(queryOP)
.map(QueryOP::supports)
.map(Arrays::asList);
if(lists.isPresent() && lists.get().contains(type)){
if(queryOP != QueryOP.IS_NULL && queryOP != QueryOP.NOTNULL){
if(!isNotNull(values)){
return false;
}
}
switch (queryOP){
case EQUAL:
criteria.andEqualTo(field, getValue(values[0], type));
break;
case NOT_EQUAL:
criteria.andNotEqualTo(field, getValue(values[0], type));
break;
case LESS:
criteria.andLessThan(field, getValue(values[0], type));
break;
case GREAT:
criteria.andGreaterThan(field, getValue(values[0], type));
break;
case LESS_EQUAL:
criteria.andLessThanOrEqualTo(field, getValue(values[0], type));
break;
case GREAT_EQUAL:
criteria.andGreaterThanOrEqualTo(field, getValue(values[0], type));
break;
case LIKE:
criteria.andLike(field,SQL_LIKE_CONTACT_STR+getValue(values[0], type)+SQL_LIKE_CONTACT_STR);
break;
case LEFT_LIKE:
criteria.andLike(field,SQL_LIKE_CONTACT_STR+getValue(values[0], type));
break;
case RIGHT_LIKE:
criteria.andLike(field,getValue(values[0], type)+SQL_LIKE_CONTACT_STR);
break;
case IS_NULL:
criteria.andIsNull(field);
break;
case NOTNULL:
criteria.andIsNull(field);
break;
case IN:
criteria.andIn(field,getValues(values,type));
break;
case NOT_IN:
criteria.andNotIn(field,getValues(values,type));
break;
case BETWEEN:
criteria.andBetween(field, getValue(values[0], type), getValue(values[1], type));
break;
case NOT_BETWEEN:
criteria.andNotBetween(field, getValue(values[0], type), getValue(values[1], type));
break;
case JSONB_EXIST:
criteria.andJSONExist(field,getValue(values[0], type));
break;
default:
}
}
}
return criteria.getCriteria().size() > size;
}catch (Exception e){
log.error(REQUEST_PARAM_ERROR,e);
return false;
}
}
private static Object getValue(String val , String type){
switch (type){
case "N":
return Long.parseLong(val);
case "D":
return DateUtil.parse(val).toJdkDate();
case "B":
return Boolean.parseBoolean(val);
}
return val;
}
private static List getValues(String[] values , String type){
List list = new ArrayList();
for(String str : values){
list.add(getValue(str, type));
}
return list;
}
public static boolean isNotNull(String[] values){
if(values == null || values.length == 0 || (values.length == 1 && values[0].equals(""))){
return false;
}
return true;
}
}
7 BaseController
//在BaseController新增方法,BaseController参考上篇文章
@PostMapping("/findGroupByExample")
public RestResponse<List> findGroupByExample(T t) {
GroupByExample example = handlerGroupRequestParam(t);
List<Map<String,Object>> list = baseService.findGroupByExample(example);
return RestResponse.buildSuccessData(list);
}
8. TestController
@RestController
public class TestController {
@Autowired
private TaskWorkOrderMapper taskWorkOrderMapper;
@PostMapping("testGroupBy")
public RestResponse testGroupBy(TaskWorkOrder taskWorkOrder){
GroupByExample example = RequestParamUtils.handlerGroupRequestParam(taskWorkOrder);
List<Map<String,Object>> list = taskWorkOrderMapper.selectGroupByExample(example);
return RestResponse.buildSuccessData(list);
}
}
9 GroupBy
public enum GroupType {
COUNT,
SUM,
MAX,
MIN;
public static GroupType getInstance(String name){
if(StringUtils.isEmpty(name)){
return null;
}
GroupType[] values = values();
for(GroupType val : values){
if(val.name().equals(name)){
return val;
}
}
return null;
}
}