人工智能-项目实践-数据预处理-SQL语句分析、内置变量预处理、命名参数支持以及关系数据库访问与写入

package com.centit.support.database; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.Set; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.tuple.ImmutableTriple; import com.centit.support.algorithm.DatetimeOpt; import com.centit.support.algorithm.StringBaseOpt; import com.centit.support.algorithm.StringRegularOpt; import com.centit.support.common.KeyValuePair; import com.centit.support.compiler.EmbedFunc; import com.centit.support.compiler.Formula; import com.centit.support.compiler.Lexer; import com.centit.support.compiler.MapTranslate; import com.centit.support.compiler.VariableTranslate; /** * @author [email protected] */ public class QueryUtils { public static String SQL_PRETREAT_LIKE = "LIKE"; public static String SQL_PRETREAT_DATE = "DATE"; public static String SQL_PRETREAT_DATETIME = "DATETIME"; public static String SQL_PRETREAT_DATESTR = "DATESTR"; public static String SQL_PRETREAT_DATETIMESTR = "DATETIMESTR"; public static String SQL_PRETREAT_DIGIT = "DIGIT"; public static String SQL_PRETREAT_UPPERCASE = "UPPERCASE"; public static String SQL_PRETREAT_LOWERCASE = "LOWERCASE"; public static String SQL_PRETREAT_NUMBER = "NUMBER"; public static String SQL_PRETREAT_QUOTASTR = "QUOTASTR"; public static String SQL_PRETREAT_SPLITFORIN = "SPLITFORIN"; public static String SQL_PRETREAT_CREEPFORIN = "CREEPFORIN"; public static String SQL_PRETREAT_INPLACE = "INPLACE"; /** * 把字符串string包装成'string',并将字符传中的数里的"'"替换为“''” * @param value * @return 对应的'value' */ public static String buildStringForQuery(String value) { if (value == null || "".equals(value)) return "''"; return "'" + StringUtils.replace(value.trim(), "'", "''") + "'"; } public static String buildObjectsStringForQuery(Object [] objects) { if (objects == null || objects.length<1) return "()"; StringBuilder sb = new StringBuilder("("); int dataCount=0; for(Object obj:objects){ if(obj!=null){ if(dataCount>0) sb.append(","); sb.append(buildStringForQuery(String.valueOf(obj))); dataCount++; } } sb.append(")"); return sb.toString(); } public static String buildObjectsStringForQuery(Collection<?> objects) { if (objects == null || objects.size()<1) return "()"; StringBuilder sb = new StringBuilder("("); int dataCount=0; for(Object obj:objects){ if(obj!=null){ if(dataCount>0) sb.append(","); sb.append(buildStringForQuery(String.valueOf(obj))); dataCount++; } } sb.append(")"); return sb.toString(); } public static String buildObjectStringForQuery(Object fieldValue) { if(fieldValue instanceof java.util.Date){ return QueryUtils.buildDatetimeStringForQuery((java.util.Date)fieldValue); }else if(fieldValue instanceof java.sql.Date){ return QueryUtils.buildDatetimeStringForQuery((java.sql.Date)fieldValue); }else if(fieldValue.getClass().getSuperclass().equals(Number.class)){ return fieldValue.toString(); }else if(fieldValue instanceof Object[]) { return QueryUtils.buildObjectsStringForQuery((Object[]) fieldValue); }else if(fieldValue instanceof Collection<?>) { return QueryUtils.buildObjectsStringForQuery((Collection<?>) fieldValue); }else { return QueryUtils.buildStringForQuery(fieldValue.toString()); } } public static String buildDateStringForQuery(Date value) { return "'" + DatetimeOpt.convertDateToString(value, "yyyy-MM-dd") + "'"; } public static String buildDateStringForQuery(java.sql.Date value) { return "'" + DatetimeOpt.convertDateToString(value, "yyyy-MM-dd") + "'"; } public static String buildDatetimeStringForQuery(Date value) { return "'" + DatetimeOpt.convertDateToString(value, "yyyy-MM-dd HH:mm:ss") + "'"; } public static String buildDatetimeStringForQuery(java.sql.Date value) { return "'" + DatetimeOpt.convertDateToString(value, "yyyy-MM-dd HH:mm:ss") + "'"; } /** * 在HQL检索策略以外,模糊拼接key-value键值对,把string包装成to-char('value','yyyy-MM-dd') * * @param value * @return 对应的to-char('value','yyyy-MM-dd') */ public static String buildDateStringForOracle(Date value) { return "TO_DATE('" + DatetimeOpt.convertDateToString(value, "yyyy-MM-dd") + "','yyyy-MM-dd')"; } public static String buildDateStringForOracle(java.sql.Date value) { return "TO_DATE('" + DatetimeOpt.convertDateToString(value, "yyyy-MM-dd") + "','yyyy-MM-dd')"; } /** * 在HQL检索策略以外,模糊拼接key-value键值对,把string包装成to-char('value','yyyy-MM-dd * hh24:mi:ss') * * @param value * @return 对应的to-char('value','yyyy-MM-dd hh24:mi:ss') */ public static String buildDateTimeStringForOracle(java.util.Date value) { return "TO_DATE('" + DatetimeOpt.convertDateToString(value, "yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss')"; } public static String buildDateTimeStringForOracle(java.sql.Date value) { return "TO_DATE('" + DatetimeOpt.convertDateToString(value, "yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss')"; } /** * 将string中的 空格换成 % 作为like语句的匹配串 * 比如在客户端输入 “hello world”，会转变为 "%hello%world%"，即将头尾和中间的空白转换为%用于匹配。 * @param sMatch * @return */ public static String getMatchString(String sMatch) { StringBuilder sRes = new StringBuilder("%"); char preChar = '%', curChar; int sL = sMatch.length(); for (int i = 0; i < sL; i++) { curChar = sMatch.charAt(i); if ((curChar == ' ') || (curChar == '\t') || (curChar == '%') || (curChar == '*')) { curChar = '%'; if (preChar != '%') { sRes.append(curChar); preChar = curChar; } } else if (curChar == '?' ){ //|| curChar == '\'' || curChar == '\"' || curChar == '<' || curChar == '>') { sRes.append("_"); preChar = curChar; }else{ sRes.append(curChar); preChar = curChar; } } if (preChar != '%') sRes.append('%'); return sRes.toString(); } /** * 将查序变量中 用于 like语句的变量转换为match字符串，比如“hello world”会转变为 "%hello%world%"， * @param queryParams 查询命名变量和值对 * @param likeParams 用于like 的变量名 * @return 返回在查询变量中找到的like变量 */ public static int replaceMatchParams(Map<String,Object> queryParams,Collection<String> likeParams){ if(likeParams==null||likeParams.size()==0||queryParams==null) return 0; int n=0; for(String f:likeParams){ Object value = queryParams.get(f); if(value!=null){ queryParams.put(f, getMatchString(StringBaseOpt.objectToString(value))); n++; } } return n; } /** * 将查序变量中 用于 like语句的变量转换为match字符串，比如