package jnpf.onlinedev.util;
|
|
import cn.hutool.core.util.ObjectUtil;
|
import jnpf.database.model.query.SuperQueryJsonModel;
|
import jnpf.emnus.SearchMethodEnum;
|
import jnpf.model.visualjson.FieLdsModel;
|
import jnpf.model.visualjson.config.ConfigModel;
|
import jnpf.permission.model.authorize.OnlineDynamicSqlModel;
|
import lombok.Data;
|
import org.mybatis.dynamic.sql.*;
|
|
import java.util.ArrayList;
|
import java.util.List;
|
|
@Data
|
public class OnlineQuerySqlUtils {
|
|
/**
|
* 运算符
|
*/
|
private SearchMethodEnum symbol;
|
/**
|
* 逻辑拼接符号
|
*/
|
private boolean and;
|
/**
|
* 组件标识
|
*/
|
private String jnpfKey;
|
/**
|
* 字段key
|
*/
|
private String vModel;
|
/**
|
* 自定义的值
|
*/
|
private Object fieldValue;
|
/**
|
* 自定义的值2
|
*/
|
private Object fieldValueTwo;
|
|
private VisitableCondition<Object> sqlCondition = null;
|
private BindableColumn<Object> sqlColumn = null;
|
private List<AndOrCriteriaGroup> groupList = new ArrayList<>();
|
private boolean isSqlServer = false;
|
private boolean isOracle = false;
|
private boolean isAddMatchLogic = false;
|
|
|
private List<String> dataList = new ArrayList<>();
|
|
public List<AndOrCriteriaGroup> getSuperSql(List<SuperQueryJsonModel> conditionList, List<OnlineDynamicSqlModel> sqlModelList, String databaseProductName, String matchLogic) {
|
isSqlServer = databaseProductName.equalsIgnoreCase("Microsoft SQL Server");
|
isOracle = databaseProductName.equalsIgnoreCase("oracle");
|
isAddMatchLogic = SearchMethodEnum.AND.getSymbol().equalsIgnoreCase(matchLogic);
|
List<AndOrCriteriaGroup> groupQueryList = new ArrayList<>();
|
OnlineProductSqlUtils.superList(conditionList);
|
for (SuperQueryJsonModel queryJsonModel : conditionList) {
|
List<FieLdsModel> fieLdsModelList = queryJsonModel.getGroups();
|
String logic = queryJsonModel.getLogic();
|
and = SearchMethodEnum.AND.getSymbol().equalsIgnoreCase(logic);
|
List<AndOrCriteriaGroup> groupListAll = new ArrayList<>();
|
for (FieLdsModel fieLdsModel : fieLdsModelList) {
|
ConfigModel config = fieLdsModel.getConfig();
|
sqlCondition = null;
|
sqlColumn = null;
|
groupList = new ArrayList<>();
|
jnpfKey = config.getJnpfKey();
|
symbol = SearchMethodEnum.getSearchMethod(fieLdsModel.getSymbol());
|
vModel = fieLdsModel.getVModel();
|
fieldValue = fieLdsModel.getFieldValueOne();
|
fieldValueTwo = fieLdsModel.getFieldValueTwo();
|
dataList = fieLdsModel.getDataList();
|
String tableName = ObjectUtil.isNotEmpty(config.getRelationTable()) ? config.getRelationTable() : config.getTableName();
|
OnlineDynamicSqlModel onlineDynamicSqlModel = sqlModelList.stream().filter(sql -> sql.getTableName().equals(tableName)).findFirst().orElse(null);
|
if (onlineDynamicSqlModel != null) {
|
getSymbolWrapper(onlineDynamicSqlModel);
|
groupListAll.addAll(groupList);
|
}
|
}
|
if (!groupListAll.isEmpty()) {
|
if (isAddMatchLogic) {
|
groupQueryList.add(SqlBuilder.and(DerivedColumn.of("1"), SqlBuilder.isEqualTo(and ? 1 : 2), groupListAll.toArray(new AndOrCriteriaGroup[groupListAll.size()])));
|
} else {
|
groupQueryList.add(SqlBuilder.or(DerivedColumn.of("1"), SqlBuilder.isEqualTo(and ? 1 : 2), groupListAll.toArray(new AndOrCriteriaGroup[groupListAll.size()])));
|
}
|
}
|
}
|
return groupQueryList;
|
}
|
|
private void getSymbolWrapper(OnlineDynamicSqlModel onlineDynamicSqlModel) {
|
SqlTable sqlTable = onlineDynamicSqlModel.getSqlTable();
|
sqlColumn = sqlTable.column(vModel);
|
List<AndOrCriteriaGroup> list = new ArrayList<>();
|
switch (symbol) {
|
case IS_NULL:
|
sqlCondition = SqlBuilder.isNull();
|
list.add(SqlBuilder.and(sqlTable.column(vModel), sqlCondition));
|
break;
|
case IS_NOT_NULL:
|
sqlCondition = SqlBuilder.isNotNull();
|
list.add(SqlBuilder.and(sqlTable.column(vModel), sqlCondition));
|
break;
|
case EQUAL:
|
sqlCondition = SqlBuilder.isEqualTo(fieldValue);
|
list.add(SqlBuilder.and(sqlTable.column(vModel), sqlCondition));
|
break;
|
case NOT_EQUAL:
|
sqlCondition = SqlBuilder.isNotEqualTo(fieldValue);
|
list.add(SqlBuilder.and(sqlTable.column(vModel), sqlCondition));
|
break;
|
case GREATER_THAN:
|
sqlCondition = SqlBuilder.isGreaterThan(fieldValue);
|
list.add(SqlBuilder.and(sqlTable.column(vModel), sqlCondition));
|
break;
|
case LESS_THAN:
|
sqlCondition = SqlBuilder.isLessThan(fieldValue);
|
list.add(SqlBuilder.and(sqlTable.column(vModel), sqlCondition));
|
break;
|
case GREATER_THAN_OR_EQUAL:
|
sqlCondition = SqlBuilder.isGreaterThanOrEqualTo(fieldValue);
|
list.add(SqlBuilder.and(sqlTable.column(vModel), sqlCondition));
|
break;
|
case LESS_THAN_OR_EQUAL:
|
sqlCondition = SqlBuilder.isLessThanOrEqualTo(fieldValue);
|
list.add(SqlBuilder.and(sqlTable.column(vModel), sqlCondition));
|
break;
|
case LIKE:
|
if (isSqlServer) {
|
fieldValue = String.valueOf(fieldValue).replace("\\[", "[[]");
|
}
|
sqlCondition = SqlBuilder.isLike("%" + fieldValue + "%");
|
list.add(SqlBuilder.and(sqlTable.column(vModel), sqlCondition));
|
break;
|
case NOT_LIKE:
|
if (isSqlServer) {
|
fieldValue = String.valueOf(fieldValue).replace("\\[", "[[]");
|
}
|
sqlCondition = SqlBuilder.isNotLike("%" + fieldValue + "%");
|
list.add(SqlBuilder.and(sqlTable.column(vModel), sqlCondition));
|
break;
|
case INCLUDED:
|
case NOT_INCLUDED:
|
getInWrapper(sqlTable, list);
|
break;
|
case BETWEEN:
|
sqlCondition = SqlBuilder.isBetween(fieldValue).and(fieldValueTwo);
|
list.add(SqlBuilder.and(sqlTable.column(vModel), SqlBuilder.isBetween(fieldValue).and(fieldValueTwo)));
|
break;
|
default:
|
break;
|
}
|
if (!list.isEmpty()) {
|
int n = 1;
|
if (symbol.equals(SearchMethodEnum.INCLUDED)) {
|
n = 2;
|
}
|
if (and) {
|
groupList.add(SqlBuilder.and(DerivedColumn.of("1"), SqlBuilder.isEqualTo(n), list.toArray(new AndOrCriteriaGroup[list.size()])));
|
} else {
|
groupList.add(SqlBuilder.or(DerivedColumn.of("1"), SqlBuilder.isEqualTo(n), list.toArray(new AndOrCriteriaGroup[list.size()])));
|
}
|
}
|
}
|
|
private void getInWrapper(SqlTable sqlTable, List<AndOrCriteriaGroup> list) {
|
for (String value : dataList) {
|
if (isSqlServer) {
|
value = String.valueOf(value).replace("\\[", "[[]");
|
}
|
if (SearchMethodEnum.INCLUDED.equals(symbol)) {
|
sqlCondition = SqlBuilder.isLike("%" + value + "%");
|
list.add(SqlBuilder.or(sqlTable.column(vModel), SqlBuilder.isLike("%" + value + "%")));
|
} else {
|
sqlCondition = SqlBuilder.isNotLike("%" + value + "%");
|
list.add(SqlBuilder.and(sqlTable.column(vModel), SqlBuilder.isNotLike("%" + value + "%")));
|
}
|
}
|
}
|
|
}
|
