package com.walker.jdbc;
|
|
import com.fasterxml.jackson.databind.node.ArrayNode;
|
import com.walker.infrastructure.utils.JsonUtils;
|
import com.walker.jdbc.dao.PersistenceException;
|
import com.walker.jdbc.util.StringUtils;
|
|
import java.io.Serializable;
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.HashMap;
|
import java.util.IdentityHashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
/**
|
* <pre>
|
* 该类有两个用处，一是绑定页面查询条件，二是生成and形式的查询条件。
|
* Contorller中：
|
* @ModelAttribute("queryVo") QueryVo queryVo
|
* Service中：
|
* sql = sql.append(queryVo.generateAndSql()); 生成and形式的sql语句
|
* queryVo.getQueryMap() 生成对应的sql语句参数
|
* 如需非页面中的查询条件，可使用putLike,putEqual方法自行添加查询条件
|
* 页面中：
|
* input type="text" name="likeItem['rz.username']" value="${queryVo.likeItem['rz.username']}"
|
* 其中likeItem表示模糊查询，equalItem表示精确查询。
|
* 如果要使用别名可用 别名.字段名
|
* 注：暂不支持字段名别名的情况
|
* </pre>
|
*
|
* @author 张亮
|
* @author 时克英 修改
|
* @date 2022-10-10
|
*/
|
public class QueryVo implements Serializable {
|
// 序列化版本号
|
private static final long serialVersionUID = 1L;
|
|
public static final String SPACE = " ";
|
|
/**
|
* 常用sql语句组成片段
|
*/
|
private static final String PERCENT = "%";
|
private static final String LIKE = " like ";
|
private static final String AND = " and ";
|
private static final String OR = " or ";
|
|
private static final String EQUAL = " = ";
|
private static final String UNEQUAL = " != ";
|
private static final String IN = " in ";
|
private static final String NOTIN = " not in ";
|
private static final String GREATER = " >= ";
|
private static final String LESS = " <= ";
|
private static final String WHERE = " where ";
|
private static final String ORDER_BY = " order by ";
|
/**
|
* 等于、相似、大于、小于、不等于、包含、不包含对象
|
*/
|
private Map<String, Object> equalItem;
|
private Map<String, Object> likeItem;
|
private Map<String, Object> greaterItem;
|
private Map<String, Object> lessItem;
|
private Map<String, Object> unequalItem;
|
private Map<String, Object> includeItem;
|
private Map<String, Object> notincludeItem;
|
|
//默认添加后缀
|
private Map<String, Object> defalutSuffixItem;
|
private Integer sfzsgd;
|
|
/**
|
* 记录非重复变量
|
*/
|
private Map<String, Object> countMap;
|
private List keyList;
|
/**
|
* 自定义sql
|
*/
|
private StringBuilder extSql;
|
/**
|
* 自定义order by sql
|
*/
|
private StringBuilder orderBySql;
|
/**
|
* 排序用字段名，正序倒序
|
*/
|
private String qvSortName;
|
private String qvSortOrder;
|
/**
|
* 列选择的所有数据
|
*/
|
private String toggleColumnValue = "";
|
/**
|
* 生成的查询语句用值
|
*/
|
private Map<String, Object> queryMap;
|
/**
|
* 保存页面查询条件字符串
|
*/
|
private String serializeQuery = "";
|
/**
|
* 临时排除查询条件
|
*/
|
private Map<String, Object> excludeQueryVO;
|
|
private List<Map<String,Object>> labelCount;
|
|
|
public QueryVo() {
|
equalItem = new SaveIdentityHashMap<>();
|
likeItem = new SaveIdentityHashMap<>();
|
greaterItem = new SaveIdentityHashMap<>();
|
lessItem = new SaveIdentityHashMap<>();
|
countMap = new SaveIdentityHashMap<>();
|
unequalItem = new SaveIdentityHashMap<>();
|
includeItem = new SaveIdentityHashMap<>();
|
notincludeItem = new SaveIdentityHashMap<>();
|
defalutSuffixItem = new HashMap<>();
|
excludeQueryVO = new HashMap<>();
|
keyList = new ArrayList<>();
|
labelCount = new ArrayList<>();
|
queryMap = new HashMap<>();
|
extSql = new StringBuilder();
|
orderBySql = new StringBuilder();
|
qvSortName = null;
|
qvSortOrder = null;
|
}
|
|
/**
|
* 生成and格式的sql，默认不带where
|
*
|
* @return
|
* @author 张亮
|
*/
|
public StringBuffer generateAndSql() {
|
return generateAndSql(false);
|
}
|
|
/**
|
* 生成and格式的sql，可选择是否带where
|
*
|
* @return
|
* @author 张亮
|
*/
|
public StringBuffer generateAndSql(boolean generateWhere) {
|
StringBuffer whereQuery = new StringBuffer(SPACE);
|
boolean paramEmpty = true;
|
paramEmpty = genSql(equalItem, whereQuery, paramEmpty, EQUAL, false, generateWhere, keyList, countMap);
|
paramEmpty = genSql(likeItem, whereQuery, paramEmpty, LIKE, true, generateWhere, keyList, countMap);
|
paramEmpty = genSql(greaterItem, whereQuery, paramEmpty, GREATER, false, generateWhere, keyList, countMap);
|
paramEmpty = genSql(lessItem, whereQuery, paramEmpty, LESS, false, generateWhere, keyList, countMap);
|
paramEmpty = genSql(unequalItem, whereQuery, paramEmpty, UNEQUAL, false, generateWhere, keyList, countMap);
|
paramEmpty = genSql(includeItem, whereQuery, paramEmpty, IN, false, generateWhere, keyList, countMap);
|
paramEmpty = genSql(notincludeItem, whereQuery, paramEmpty, NOTIN, false, generateWhere, keyList, countMap);
|
//附加sql不为空才生成
|
if (!extSql.toString().equals(com.walker.infrastructure.utils.StringUtils.EMPTY_STRING)) {
|
if (generateWhere && paramEmpty) {
|
//需要生成where没有参数
|
whereQuery.append(WHERE).append(extSql);
|
} else {
|
//不需要生成where没有参数
|
whereQuery.append(AND).append(extSql);
|
}
|
}
|
if(excludeQueryVO.size()==0) {//如果有排除分组函数，不执行排序
|
if (StringUtils.isNotEmpty(qvSortName)) {
|
if (StringUtils.isNotEmpty(qvSortOrder)) {
|
whereQuery.append(ORDER_BY + qvSortName + SPACE + qvSortOrder);
|
}
|
} else {
|
if (StringUtils.isNotEmpty(orderBySql.toString())) {
|
whereQuery.append(SPACE).append(orderBySql);
|
}
|
}
|
}
|
|
return whereQuery.append(SPACE);
|
}
|
|
private boolean genSql(Map<String, Object> item, StringBuffer whereQuery, boolean paramEmpty, String operator, boolean like, boolean generateWhere, List keyList, Map<String, Object> countMap) {
|
|
boolean flag_in=operator.equals(IN) || operator.equals(NOTIN);
|
for (Map.Entry<String, Object> entry : item.entrySet()) {
|
if(excludeQueryVO.containsKey(entry.getKey())){
|
continue;
|
}
|
String key = "";
|
if (keyList.contains(entry.getKey())) {
|
if (!countMap.containsKey(entry.getKey())) {
|
countMap.put(entry.getKey(), 0);
|
} else {
|
countMap.put(entry.getKey(), countMap.get(entry.getKey() + 1));
|
}
|
key = entry.getKey() + com.walker.infrastructure.utils.StringUtils.STRING_UNDERLINE + countMap.get(entry.getKey());
|
} else {
|
keyList.add(entry.getKey());
|
key = entry.getKey();
|
}
|
|
if (StringUtils.isNotEmpty(String.valueOf(entry.getValue())) && !String.valueOf(entry.getValue()).equalsIgnoreCase("[]")) {
|
if(flag_in){
|
if (generateWhere && paramEmpty) {
|
whereQuery.append(WHERE + entry.getKey() + operator + "(:" + key+")");
|
generateWhere = false;
|
} else {
|
whereQuery.append(AND + entry.getKey() + operator + "(:" + key+")");
|
}
|
List<Object> value=new ArrayList<>();
|
if(entry.getValue().toString().startsWith("[") && entry.getValue().toString().endsWith("]")){
|
// JSONArray array= new JSONArray(entry.getValue().toString());
|
// if(array.length()>0&&array.get(0).toString().startsWith(";")&&array.get(0).toString().endsWith(";")) {
|
// for (int i = 0; i < array.length(); i++) {
|
// String v = array.get(i).toString();
|
// value.add(v.substring(1, v.length() - 1));
|
// }
|
// }else{
|
// value=array.toList();
|
// }
|
try {
|
ArrayNode arrayNode = JsonUtils.toJsonArray(entry.getValue().toString());
|
if(arrayNode.size() > 0
|
&& arrayNode.get(0).toString().startsWith(";") && arrayNode.get(0).toString().endsWith(";")){
|
for (int i = 0; i < arrayNode.size(); i++) {
|
String v = arrayNode.get(i).toString();
|
value.add(v.substring(1, v.length() - 1));
|
}
|
} else {
|
for (int i = 0; i < arrayNode.size(); i++) {
|
value.add(arrayNode.get(i).toString());
|
}
|
}
|
} catch (Exception e) {
|
throw new RuntimeException(e);
|
}
|
|
}else{
|
if(entry.getValue().toString().startsWith(";")&&entry.getValue().toString().endsWith(";")) {
|
List<String> templist=Arrays.asList(String.valueOf(entry.getValue()).trim().split(","));
|
for (String tv:templist) {
|
value.add(tv.substring(1, tv.length() - 1));
|
}
|
}else{
|
value=Arrays.asList(String.valueOf(entry.getValue()).trim().split(com.walker.infrastructure.utils.StringUtils.DEFAULT_SPLIT_SEPARATOR));
|
}
|
value.remove("");
|
}
|
queryMap.put(key, value);
|
}else {
|
if (generateWhere && paramEmpty) {
|
whereQuery.append(WHERE + entry.getKey() + operator + ":" + key);
|
} else {
|
whereQuery.append(AND + entry.getKey() + operator + ":" + key);
|
}
|
String value = "";
|
if (like) {
|
value = PERCENT + String.valueOf(entry.getValue()).trim() + PERCENT;
|
} else {
|
if(defalutSuffixItem.containsKey(key)){
|
value = String.valueOf(entry.getValue()).trim()+defalutSuffixItem.get(key);
|
}else{
|
value = String.valueOf(entry.getValue()).trim();
|
}
|
}
|
paramEmpty = false;
|
queryMap.put(key, value);
|
}
|
}
|
}
|
return paramEmpty;
|
}
|
|
/**
|
* 获取sql查询用的参数Map("key","value")
|
*
|
* @return
|
* @author 张亮
|
*/
|
public Map<String, Object> getQueryMap() {
|
return queryMap;
|
}
|
|
/**
|
* 增加order by语句
|
*
|
* @param orderBySqlOnce 自己定义的order by语句
|
*/
|
public void appendOrderBySql(String orderBySqlOnce) {
|
orderBySql.append(orderBySqlOnce);
|
}
|
|
/**
|
* 增加一段自己定义的sql语句
|
*
|
* @param extSqlonce 自己定义的sql语句
|
* @return
|
*/
|
public void appendExtSql(String extSqlonce) {
|
extSql.append(extSqlonce);
|
}
|
|
/**
|
* 增加一个等于查询条件
|
*
|
* @param key
|
* @param value
|
*/
|
public void addEqualItem(String key, Object value) {
|
equalItem.put(key, value);
|
}
|
|
/**
|
* 增加一个like查询条件
|
*
|
* @param key
|
* @param value
|
*/
|
public void addLikeItem(String key, Object value) {
|
likeItem.put(key, value);
|
}
|
public void addIncludeItem(String key, Object value) {
|
includeItem.put(key, value);
|
}
|
public void initIncludeItem(String key,Object value){ includeItem.put(key,";"+value+";");}
|
|
/**
|
* 增加一个小于查询条件
|
*
|
* @param key
|
* @param value
|
*/
|
public void addLessItem(String key, Object value) {
|
lessItem.put(key, value);
|
}
|
|
/**
|
* 增加一个大于查询条件
|
*
|
* @param key
|
* @param value
|
*/
|
public void addGreaterItem(String key, Object value) {
|
greaterItem.put(key, value);
|
}
|
|
/**
|
* 增加一个不等于的变量
|
*
|
* @param key
|
* @param value
|
*/
|
public void addUnequalItem(String key, Object value) {
|
unequalItem.put(key, value);
|
}
|
|
/**
|
* 增加一个绑定变量值
|
*
|
* @param key
|
* @param value
|
*/
|
public void addQueryMap(String key, Object value) {
|
queryMap.put(key, value);
|
}
|
|
/**
|
* 增加一组绑定变量值
|
*
|
* @param map
|
*/
|
public void addQueryMap(Map map) {
|
queryMap.putAll(map);
|
}
|
|
/**
|
* 获取一个equal变量
|
*
|
* @param key
|
* @return equalItem中的一个变量
|
*/
|
public Object getEqualItemValue(String key) {
|
return getObject(equalItem, key);
|
}
|
/**
|
* 获取一个include变量
|
*
|
* @param key
|
* @return includeItem中的一个变量
|
*/
|
public Object getIncludeItemValue(String key) {
|
return getObject(includeItem, key);
|
}
|
|
/**
|
* 获取一个like变量
|
*
|
* @param key
|
* @return
|
*/
|
public Object getLikeItemValue(String key) {
|
return getObject(likeItem, key);
|
}
|
|
private Object getObject(Map<String, Object> item, String key) {
|
for (Map.Entry<String, Object> entry : item.entrySet()) {
|
if (entry.getKey().equals(key)) {
|
return entry.getValue();
|
}
|
}
|
return null;
|
}
|
|
public Map<String, Object> getEqualItem() {
|
return equalItem;
|
}
|
|
public void setEqualItem(Map<String, Object> equalItem) {
|
this.equalItem = equalItem;
|
}
|
|
public Map<String, Object> getLikeItem() {
|
return likeItem;
|
}
|
|
public void setLikeItem(Map<String, Object> likeItem) {
|
this.likeItem = likeItem;
|
}
|
|
public String getToggleColumnValue() {
|
return toggleColumnValue;
|
}
|
|
public void setToggleColumnValue(String toggleColumnValue) {
|
this.toggleColumnValue = toggleColumnValue;
|
}
|
|
public String getSerializeQuery() {
|
return serializeQuery;
|
}
|
|
public void setSerializeQuery(String serializeQuery) {
|
this.serializeQuery = serializeQuery;
|
}
|
|
public Map<String, Object> getLessItem() {
|
return lessItem;
|
}
|
|
public void setLessItem(Map<String, Object> lessItem) {
|
this.lessItem = lessItem;
|
}
|
|
public Map<String, Object> getGreaterItem() {
|
return greaterItem;
|
}
|
|
public void setGreaterItem(Map<String, Object> greaterItem) {
|
this.greaterItem = greaterItem;
|
}
|
|
public Map<String, Object> getUnequalItem() {
|
return unequalItem;
|
}
|
|
public void setUnequalItem(Map<String, Object> unequalItem) {
|
this.unequalItem = unequalItem;
|
}
|
|
public Map<String, Object> getIncludeItem() {
|
return includeItem;
|
}
|
|
public void setIncludeItem(Map<String, Object> includeItem) {
|
this.includeItem = includeItem;
|
}
|
|
public Map<String, Object> getNotincludeItem() {
|
return notincludeItem;
|
}
|
|
public void setNotincludeItem(Map<String, Object> notincludeItem) {
|
this.notincludeItem = notincludeItem;
|
}
|
|
public Map<String, Object> getDefalutSuffixItem() {
|
return defalutSuffixItem;
|
}
|
|
public void setDefalutSuffixItem(Map<String, Object> defalutSuffixItem) {
|
this.defalutSuffixItem = defalutSuffixItem;
|
}
|
|
/**
|
* 增加一个绑定变量值
|
*
|
* @param key
|
* @param value
|
*/
|
public void addExcludeQueryVO(String key, Object value) {
|
excludeQueryVO.put(key, value);
|
}
|
public Map<String, Object> getExcludeQueryVO() {
|
return excludeQueryVO;
|
}
|
public void clearExcludeQueryVO(){
|
excludeQueryVO.clear();
|
keyList.clear();
|
countMap.clear();
|
}
|
|
public List<Map<String, Object>> getLabelCount() {
|
return labelCount;
|
}
|
|
public void setLabelCount(List<Map<String, Object>> labelCount) {
|
this.labelCount = labelCount;
|
}
|
|
public static long getSerialVersionUID() {
|
return serialVersionUID;
|
}
|
|
public String getQvSortName() {
|
return qvSortName;
|
}
|
|
public void setQvSortName(String qvSortName) {
|
if(!columnCheck(qvSortName)) {
|
throw new PersistenceException(qvSortName+"为非法的属性值");
|
}
|
this.qvSortName = qvSortName;
|
}
|
|
public String getQvSortOrder() {
|
return qvSortOrder;
|
}
|
|
public void setQvSortOrder(String qvSortOrder) {
|
if(StringUtils.isNotEmpty(qvSortOrder)) {
|
if(!qvSortOrder.equalsIgnoreCase("DESC")&&!qvSortOrder.equalsIgnoreCase("ASC")) {
|
throw new PersistenceException(qvSortName+"为非法的关键字");
|
}
|
}
|
this.qvSortOrder = qvSortOrder;
|
}
|
|
public Integer getSfzsgd() {
|
if(sfzsgd==null){
|
sfzsgd = 0;
|
}
|
return sfzsgd;
|
}
|
|
public void setSfzsgd(Integer sfzsgd) {
|
this.sfzsgd = sfzsgd;
|
}
|
|
private static Pattern p = Pattern.compile("^[A-Za-z0-9_\\.]+$");
|
|
/**
|
* 检查列名是否合法
|
* @param column
|
* @return
|
*/
|
private boolean columnCheck(String column) {
|
if(column == null ||column.isEmpty()) {
|
return true;
|
} else {
|
Matcher m = p.matcher(column);
|
return m.matches();
|
}
|
}
|
|
class SaveIdentityHashMap<K, V> extends IdentityHashMap<K, V> {
|
@Override
|
public V put(K k, V v) {
|
if(!columnCheck(k.toString())) {
|
throw new PersistenceException(k+"为非法的属性名");
|
}
|
return super.put(k, v);
|
}
|
}
|
}
|
