com.centit.support.database.utils.QueryUtils Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of centit-database Show documentation
Show all versions of centit-database Show documentation
数据库操作通用方法和函数，从以前的util包中分离出来，并且整合了部分sys-module中的函数
package com.centit.support.database.utils;
import com.centit.support.algorithm.*;
import com.centit.support.common.KeyValuePair;
import com.centit.support.compiler.*;
import org.apache.commons.lang3.StringEscapeUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.ImmutableTriple;
import org.apache.commons.lang3.tuple.MutablePair;
import org.apache.commons.lang3.tuple.Pair;
import java.util.*;
/**
* @author [email protected]
*/
@SuppressWarnings("unused")
public abstract class QueryUtils {
private QueryUtils() {
throw new IllegalAccessError("Utility class");
}
/**
* 表示这个参数不需要
*/
public static final String SQL_PRETREAT_NO_PARAM = "NP";
/**
* 转化为模式匹配字符串
*/
public static final String SQL_PRETREAT_LIKE = "LIKE";
/**
* 用于like语句，只在参数后面添加一个 %
*/
public static final String SQL_PRETREAT_STARTWITH = "STARTWITH";
/**
* 用于like语句，只在参数前面添加一个 %
*/
public static final String SQL_PRETREAT_ENDWITH = "ENDWITH";
/**
* 转化为日期类型,
*/
public static final String SQL_PRETREAT_DATE = "DATE";
/**
* 转化为日期类型，并且计算第二天的日期，没有时间（时间为00:00:00） 用于区间查询的结束时间
*/
public static final String SQL_PRETREAT_NEXTDAY = "NEXTDAY";
/**
* 转化为带时间的，日期的类型
*/
public static final String SQL_PRETREAT_DATETIME = "DATETIME";
/**
* 转化为 2016-6-16这样的日期字符串
*/
public static final String SQL_PRETREAT_DATESTR = "DATESTR";
/**
* 转化为 2016-6-16 10:25:34这样的日期和时间字符串
*/
public static final String SQL_PRETREAT_DATETIMESTR = "DATETIMESTR";
/**
* 过滤掉所有非数字字符
*/
public static final String SQL_PRETREAT_DIGIT = "DIGIT";
/**
* 大写
*/
public static final String SQL_PRETREAT_UPPERCASE = "UPPERCASE";
/**
* 小写
*/
public static final String SQL_PRETREAT_LOWERCASE = "LOWERCASE";
/**
* 转化为符合数字的字符串，
*/
public static final String SQL_PRETREAT_NUMBER = "NUMBER";
/**
* 给子符串添加''使其可以拼接到sql语句中，并避免sql注入
*/
public static final String SQL_PRETREAT_QUOTASTR = "QUOTASTR";
/**
* 应该转化 Integer类型，单对于数据库来说他和long没有区别所以也返回的Long类型
*/
public static final String SQL_PRETREAT_INTEGER = "INTEGER";
/**
* 转化 Long 类型
*/
public static final String SQL_PRETREAT_LONG = "LONG";
/**
* 转化为 Double 类型
*/
public static final String SQL_PRETREAT_FLOAT = "FLOAT";
/**
* 将对象转换为 String， 如果是数组用 ','连接。
*/
public static final String SQL_PRETREAT_STRING = "STRING";
/**
* 将字符串 用,分割返回 String[]
*/
public static final String SQL_PRETREAT_SPLITFORIN = "SPLITFORIN";
/**
* 修改语句中的 命名参数，使其能够接受 多个参数以便用于in语句，比如： in(:a)
* 传入a为一个数组，会根据a的实际长度变为 in(:a0,:a1,a2,......)
*/
public static final String SQL_PRETREAT_CREEPFORIN = "CREEPFORIN";
/**
* 将参数值 拼接到 sql对应的参数位置，同时要避免sql注入
*/
public static final String SQL_PRETREAT_INPLACE = "INPLACE";
/**
* 过滤参数中的html标签
*/
public static final String SQL_PRETREAT_ESCAPE_HTML = "ESCAPEHTML";
/**
* 把字符串string包装成'string',并将字符传中的数里的"'"替换为“''”
* @param value 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 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 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 sMatch
* @return String
*/
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 queryParams,Collection 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字符串，比如“hello world”会转变为 "%hello%world%"，
* @param queryParams 查询命名变量和值对
* @param likeParams 用于like 的变量名
* @return 返回在查询变量中找到的like变量
*/
public static int replaceMatchParams(Map queryParams,String... likeParams){
if(likeParams==null||likeParams.length==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;
}
/**
* 去掉 order by 语句
* @param sql sql
* @return sql
*/
public static boolean hasOrderBy(String sql){
Lexer lex = new Lexer(sql,Lexer.LANG_TYPE_SQL);
String aWord = lex.getAWord();
while (aWord != null && !"".equals(aWord) && !"order".equalsIgnoreCase(aWord)) {
aWord = lex.getAWord();
}
return "order".equalsIgnoreCase(aWord);
}
/**
* 去掉 order by 语句
* @param sql sql
* @return sql
*/
public static String removeOrderBy(String sql){
Lexer lex = new Lexer(sql,Lexer.LANG_TYPE_SQL);
String aWord = lex.getAWord();
int nPos = lex.getCurrPos();
while (aWord != null && !"".equals(aWord) && !"order".equalsIgnoreCase(aWord)) {
if (aWord.equals("(")) {
lex.seekToRightBracket();
}
nPos = lex.getCurrPos();
aWord = lex.getAWord();
if (aWord == null || "".equals(aWord))
return sql;
}
return sql.substring(0, nPos);
}
/**
* 去掉 order by 语句
* @param sql sql
* @return sql
*/
public static String getGroupByField(String sql){
Lexer lex = new Lexer(sql,Lexer.LANG_TYPE_SQL);
String aWord = lex.getAWord();
while (aWord != null && !"".equals(aWord) && !"group".equalsIgnoreCase(aWord)) {
if (aWord.equals("(")) {
lex.seekToRightBracket();
//aWord = lex.getAWord();
}
aWord = lex.getAWord();
if (aWord == null || "".equals(aWord))
return null;
}
if("group".equalsIgnoreCase(aWord)){
while (aWord != null && !"".equals(aWord) && !"by".equalsIgnoreCase(aWord)){
aWord = lex.getAWord();
}
}
if(!"by".equalsIgnoreCase(aWord))
return null;
int nPos = lex.getCurrPos();
int nEnd = nPos;
while (aWord != null && !"".equals(aWord) && !"order".equalsIgnoreCase(aWord)) {
nEnd = lex.getCurrPos();
aWord = lex.getAWord();
}
if(nEnd>nPos)
return sql.substring(nPos,nEnd);
return null;
}
/**
* 将sql语句 filed部分为界 分三段；
* 第一段为 select 之前的内容，如果是sql server 将包括 top [n] 的内容
* 第二段为 from 和select 之间的内容，就是field内容
* 第三段为 where 之后的内容包括 order by
*
* @param sql sql
* @return sql
*/
public static List splitSqlByFields(String sql){
Lexer lex = new Lexer(sql,Lexer.LANG_TYPE_SQL);
List sqlPiece = new ArrayList<>(5);
int sl = sql.length();
String aWord = lex.getAWord();
while (aWord != null && !"".equals(aWord) && !"select".equalsIgnoreCase(aWord)) {
if (aWord.equals("(")) {
lex.seekToRightBracket();
}
aWord = lex.getAWord();
if (aWord == null || "".equals(aWord))
break;
}
int nSelectPos = lex.getCurrPos();
int nFieldBegin = nSelectPos;
if(nSelectPos>=sl){
lex.setFormula(sql);
nSelectPos=0;
nFieldBegin=0;
aWord = lex.getAWord();
}else{
//特别处理sql server 的 top 语句
aWord = lex.getAWord();
if ("top".equalsIgnoreCase(aWord)) {
aWord = lex.getAWord();
if (StringRegularOpt.isNumber(aWord))
nFieldBegin = lex.getCurrPos();
}
}
while (aWord != null && !"".equals(aWord) && !"from".equalsIgnoreCase(aWord)){
if (aWord.equals("(")) {
lex.seekToRightBracket();
}
aWord = lex.getAWord();
if (aWord == null || "".equals(aWord))
return sqlPiece;
}
int nFieldEnd = lex.getCurrPos();
sqlPiece.add(sql.substring(0, nSelectPos));
sqlPiece.add(sql.substring(nFieldBegin, nFieldEnd));
sqlPiece.add(sql.substring(nFieldEnd));
if (nFieldBegin > nSelectPos) // 只有 sqlserver 有 top 字句的语句 才有这部分
sqlPiece.add(sql.substring(nSelectPos, nFieldBegin));
return sqlPiece;
}
/**
* 将查询语句转换为相同条件的查询符合条件的记录数的语句, 需要考虑with语句
* 即将 select 的字段部分替换为 count(*) 并去掉 order by排序部分
* 对查询语句中有distinct的sql语句不使用
* @param sql sql
* @return sql
*/
public static String buildGetCountSQLByReplaceFields(String sql) {
List sqlPieces = splitSqlByFields(sql);
if (sqlPieces == null || sqlPieces.size() < 3)
return "";
if(StringUtils.isBlank(sqlPieces.get(0))) {
sqlPieces.set(0, "select");
}
String groupByField = QueryUtils.getGroupByField(sqlPieces.get(2));
if(groupByField==null)
return sqlPieces.get(0) + " count(*) as rowcount from " +
removeOrderBy(sqlPieces.get(2));
return sqlPieces.get(0) + " count(*) as rowcount from (select "+
groupByField + " from " + removeOrderBy(sqlPieces.get(2)) + ") a";
}
/**
* 通过子查询来实现获取计数语句
* @param sql sql 或者 hql 语句
* @return sql
*/
public static String buildGetCountSQLBySubSelect(String sql) {
List sqlPieces = splitSqlByFields(sql);
if (sqlPieces == null || sqlPieces.size() < 3)
return "";
if(StringUtils.isBlank(sqlPieces.get(0))) {
sqlPieces.set(0, "select");
}
//这个仅仅为了兼容hibernate
if("from".equalsIgnoreCase(sqlPieces.get(1).trim())) {
sqlPieces.set(1, " * from");
}
return sqlPieces.get(0) + " count(*) as rowCount from (select "+
sqlPieces.get(1) + sqlPieces.get(2) + ") a";
}
/**
* sql 语句可以用 子查询和替换查询字段的方式获得总数，
* 但是 有distinct的语句只能用子查询的方式。distinct的语句也可以用 group by的方式来转换，
*
* @param sql sql
* @return sql
*/
public static String buildGetCountSQL(String sql) {
return buildGetCountSQLBySubSelect(sql);
}
/**
* hql语句不能用子查询的方式，只能用buildGetCountSQLByReplaceFields
* @param hql sql
* @return sql
*/
public static String buildGetCountHQL(String hql) {
return buildGetCountSQLByReplaceFields(hql);
}
/**
* 生成MySql分页查询语句
* @param sql sql
* @param offset offset
* @param maxsize maxsize
* @param asParameter asParameter
* @return String
*/
public static String buildMySqlLimitQuerySQL(String sql,int offset,int maxsize,boolean asParameter) {
if(asParameter)
return sql + (offset>0 ? " limit ?, ?" : " limit ?");
else
return sql + (offset>0 ? " limit "+String.valueOf(offset)+","+String.valueOf(maxsize) :
" limit "+String.valueOf(maxsize));
}
/** org.hibernate.dialect
* 生成Oracle分页查询语句, 不考虑for update语句
* @param sql sql
* @param offset offset
* @param maxsize maxsize
* @param asParameter asParameter
* @return String
*/
public static String buildOracleLimitQuerySQL(String sql,int offset,int maxsize,boolean asParameter) {
final StringBuilder pagingSelect = new StringBuilder( sql.length()+100 );
if(asParameter){
if (offset>0) {
pagingSelect.append( "select * from ( select row_.*, rownum rownum_ from ( " );
}
else {
pagingSelect.append( "select * from ( " );
}
pagingSelect.append( sql );
if (offset>0) {
pagingSelect.append( " ) row_ ) where rownum_ <= ? and rownum_ > ?" );
}
else {
pagingSelect.append( " ) where rownum <= ?" );
}
}else{
if (offset>0) {
pagingSelect.append( "select * from ( select row_.*, rownum rownum_ from ( " );
}
else {
pagingSelect.append( "select * from ( " );
}
pagingSelect.append( sql );
if (offset>0) {
pagingSelect.append( " ) row_ ) where rownum_ <= ")
.append(offset + maxsize)
.append(" and rownum_ > ")
.append(offset);
}
else {
pagingSelect.append( " ) where rownum <= " ).append(maxsize);
}
}
return pagingSelect.toString();
}
/**
* 生成DB2分页查询语句
* @param sql sql
* @param offset offset
* @param maxsize maxsize
* @return String
*/
public static String buildDB2LimitQuerySQL(String sql,int offset,int maxsize/*,boolean asParameter*/)
/*throws SQLException*/{
/*if(asParameter)*/
//throw new SQLException("DB2 unsupported parameter in fetch statement.");
if ( offset == 0 ) {
return maxsize>1?sql + " fetch first " + maxsize + " rows only":
" fetch first 1 row only";
}
//nest the main query in an outer select
return "select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( "
+ sql + " fetch first " + String.valueOf(offset+maxsize) + " rows only ) as inner2_ ) as inner1_ where rownumber_ > "
+ offset + " order by rownumber_";
}
/**
* 生成SqlServer分页查询语句
* @param sql sql
* @param offset offset
* @param maxsize maxsize
* @return String
*/
public static String buildSqlServerLimitQuerySQL(String sql,int offset,int maxsize/*,boolean asParameter*/)
/*throws SQLException*/{
/*if(asParameter)
throw new SQLException("SQL Server unsupported parameter in fetch statement.");
*/
if ( offset > 0 ) {
// SQL SERVER 2012 才支持
/*return sql + "offset "+String.valueOf(offset)
+ " rows fetch next "+String.valueOf(maxsize)+" rows only";*/
/*
* WITH query AS (
* SELECT inner_query.*
* , ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_nr__
* FROM ( original_query ) inner_query
* )
* SELECT alias_list FROM query WHERE __row_nr__ >= offset AND __row_nr__ < offset + maxsize
*
*/
String alias_list = StringBaseOpt.objectToString( getSqlFiledNames(sql));
return "WITH query AS ("
+"SELECT inner_query.* "
+", ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_nr__ "
+" FROM ( " + sql + ") inner_query"
+" ) "
+" SELECT "+ alias_list +" FROM query WHERE __row_nr__ >=" + String.valueOf(offset)
+ " AND __row_nr__ < " + String.valueOf(offset + maxsize);
}else{
int selectIndex = sql.toLowerCase(Locale.ROOT).indexOf( "select" );
int selectDistinctIndex = sql.toLowerCase(Locale.ROOT).indexOf( "select distinct" );
selectIndex = selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);
return new StringBuilder( sql.length() + 8 )
.append( sql )
.insert( selectIndex, " top " + maxsize )
.toString();
}
}
public static String buildLimitQuerySQL(String sql,int offset,int maxsize,
boolean asParameter, DBType dbType){
switch (dbType){
case Oracle:
return buildOracleLimitQuerySQL(sql,offset, maxsize,asParameter);
case DB2:
return buildDB2LimitQuerySQL(sql,offset, maxsize);
case SqlServer:
return buildSqlServerLimitQuerySQL(sql,offset, maxsize);
case MySql:
return buildMySqlLimitQuerySQL(sql,offset, maxsize,asParameter);
case H2:
return buildMySqlLimitQuerySQL(sql,offset, maxsize,asParameter);
case Access:
default:
throw new PersistenceException(PersistenceException.ORM_METADATA_EXCEPTION,
"不支持的数据库类型："+dbType.toString());
}
}
/**
* 返回sql语句中所有的 命令变量（:变量名）,最后一个String 为转换为？变量的sql语句
*
* @param sql sql
* @return 返回sql语句中所有的 命令变量（:变量名）,最后一个String 为转换为？变量的sql语句
* Key 为转化成？的sql语句，value为对应的命名变量名，如果一个变量出现多次在list中也会出现多次
*/
public static KeyValuePair> transNamedParamSqlToParamSql(String sql){
StringBuilder sqlb = new StringBuilder();
List params = new ArrayList<>();
Lexer lex = new Lexer(sql,Lexer.LANG_TYPE_SQL);
int prePos = 0;
String aWord = lex.getAWord();
while (aWord != null && !"".equals(aWord)) {
if (":".equals(aWord)) {
int curPos = lex.getCurrPos();
if(curPos-1>prePos)
sqlb.append(sql.substring(prePos, curPos-1));
aWord = lex.getAWord();
if (aWord == null || "".equals(aWord))
break;
params.add(aWord);
sqlb.append("?");
prePos = lex.getCurrPos();
}
aWord = lex.getAWord();
}
sqlb.append(sql.substring(prePos));
//params.add(sqlb.toString());
return new KeyValuePair<>(sqlb.toString(),params);
}
/**
* 获取sql语句中所有的 命名参数
* @param sql sql
* @return 按照参数出现顺序排列的 list
*/
public static List getSqlNamedParameters(String sql){
List params = new ArrayList();
Lexer lex = new Lexer(sql,Lexer.LANG_TYPE_SQL);
String aWord = lex.getAWord();
while (aWord != null && !"".equals(aWord)) {
if (":".equals(aWord)) {
aWord = lex.getAWord();
if (aWord == null || "".equals(aWord))
break;
params.add(aWord);
}
aWord = lex.getAWord();
}
return params;
}
/**
* 一、 p1.1:(like)ps return p1.1
* 二、:(like)ps return ps
* 三、:ps return ps
* 四、 ps return ps
* @param pramePiece pramePiece
* @return String
*/
public static String fetchTemplateParamName(String pramePiece){
String paramName=null;
int n = pramePiece.indexOf(':');
if(n<0){//四
return pramePiece.trim();
}
if(n>1){
paramName = pramePiece.substring(0, n).trim();
if(StringUtils.isNotBlank(paramName))//一
return paramName;
}
//二、三
String paramAlias = pramePiece.substring(n+1).trim();
n = paramAlias.lastIndexOf(')');
if(n<0)
return paramAlias;
return paramAlias.substring(n+1).trim();
}
/**
* 参数 模板 p1.1:(like,,)ps
* 条件模板： [(条件)(参数)| 语句]
* [参数| 语句]
* @param paramString paramString
* @return Set String
*/
public static Set fetchTemplateParamNames(String paramString){
Set params = new HashSet<>();
List pramePieces = Lexer.splitByWord(paramString, ",");
for(String pramePiece : pramePieces){
params.add(fetchTemplateParamName(pramePiece));
}
return params;
}
/**
* 参数 模板 p1.1:(like)ps
* 条件模板： [(条件)(参数)| 语句]
* [参数| 语句]
* @param queryPiece queryPiece
* @return Set String
*/
public static Set fetchParamsFromTemplateConditions(String queryPiece){
Lexer varMorp = new Lexer(queryPiece,Lexer.LANG_TYPE_SQL);
String aWord = varMorp.getARawWord();
if(aWord==null || aWord.length() == 0)
return null;
Set paramList = new HashSet();
if("(".equals(aWord)){
//获取条件语句，如果条件语句没有，则返回 null
int curPos = varMorp.getCurrPos();
if(!varMorp.seekToRightBracket())
return null;
int prePos = varMorp.getCurrPos();
String condition = queryPiece.substring(curPos,prePos-1);
Lexer labelSelected = new Lexer(condition,Lexer.LANG_TYPE_SQL);
aWord = labelSelected.getARawWord();
while(StringUtils.isNotBlank(aWord)){
if( aWord.equals("$")){
aWord = labelSelected.getAWord();
if(aWord.equals("{")){
aWord = labelSelected.getStringUntil("}");
paramList.add(aWord);
}
}else if(Lexer.isLabel(aWord) && !Formula.isKeyWord(aWord)
&& EmbedFunc.getFuncNo(aWord) == -1){
paramList.add(aWord);
}
aWord = labelSelected.getARawWord();
}
aWord = varMorp.getARawWord();
if("(".equals(aWord)){
curPos = varMorp.getCurrPos();
if(!varMorp.seekToRightBracket())
return null;
prePos = varMorp.getCurrPos();
aWord = varMorp.getARawWord();
String paramsString = null;
if(prePos-1>curPos)
paramsString = queryPiece.substring(curPos,prePos-1);
if(paramsString!=null){//找出所有的 变量，如果变量表中没有则设置为 null
paramList.addAll(fetchTemplateParamNames(paramsString));
}
}
}else{ // 简易写法 ([:]params)* | queryPiece
if(!varMorp.seekTo("|",false))
return null;
int curPos = varMorp.getCurrPos();
String paramsString = queryPiece.substring(0,curPos-1);
if(StringUtils.isBlank(paramsString))
return null;
paramList.addAll(fetchTemplateParamNames(paramsString));
}
return paramList;
}
/*
* 返回SqlTemplate(sql语句模板)中所有的 命令变量（:变量名）
* 包括 [(${p1.1} > 2 && p2 > 2)| table1 t1,]
* [p1.1,:p2,p3:px| and (t2.b > :p2 or t3.c > :px)]
* 中的原始参数 p1.1,p2,p3
* @param sql sql
* @return 返回sql语句中所有的 命令变量（:变量名）
*/
public static Set getSqlTemplateParameters(String sql){
Set params = new HashSet();
Lexer lex = new Lexer(sql,Lexer.LANG_TYPE_SQL);
String aWord = lex.getAWord();
while (aWord != null && !"".equals(aWord)) {
if (":".equals(aWord)) {
aWord = lex.getAWord();
if (aWord == null || "".equals(aWord))
return params;
params.add(aWord);
}else if(aWord.equals("[")){
int beginPos = lex.getCurrPos();
lex.seekToRightSquareBracket();
int endPos = lex.getCurrPos();
//分析表别名， 格式为 TableNameOrClass:alias,TableNameOrClass:alias,.....
String queryPiece = sql.substring(beginPos,endPos-1).trim();
Set subParams=fetchParamsFromTemplateConditions(queryPiece);
if(subParams!=null && subParams.size()>0)
params.addAll(subParams);
}
aWord = lex.getAWord();
}
return params;
}
/**
* 返回sql语句中所有的 字段 语句表达式
* 获得查询语句中的所有 字段描述 ,比如 select a, (b+c) as d, f fn from ta 语句 返回 [ a, (b+c) as d , f fn ]
* @param sql sql
* @return 返回feild字句，这个用户 sql语句编辑界面，在dde，stat项目中使用，一般用不到。
*/
public static List> getSqlFieldNamePieceMap(String sql){
List> fields = new ArrayList<>(20);
List sqlPieces = splitSqlByFields(sql);
if (sqlPieces == null || sqlPieces.size() < 3)
return fields;
String sFieldSql = sqlPieces.get(1);
Lexer lex = new Lexer(sFieldSql,Lexer.LANG_TYPE_SQL);
int nFiledNo=0;
int nPos = 0;
String aWord = lex.getAWord();
while (aWord != null && !"".equals(aWord) && !"from".equalsIgnoreCase(aWord)) {
int nPos2 = lex.getCurrPos();
int nPosEnd=-1;
String filedName = null;
boolean prewordIsOpt = false;
while ( (!"".equals(aWord) &&
!",".equals(aWord) &&
!"from".equalsIgnoreCase(aWord))) {
if ("(".equals(aWord)){
lex.seekToRightBracket();
prewordIsOpt = false;
} else {
if ("as".equalsIgnoreCase(aWord)) {
nPosEnd = nPos2;
aWord = lex.getAWord();
filedName = aWord;
} else {
if (Lexer.isLabel(aWord)) {
if (!prewordIsOpt) {
nPosEnd = nPos2;
filedName = aWord;
}
prewordIsOpt = false;
} else {
prewordIsOpt = Formula.getOptID(aWord) > 0;
if (prewordIsOpt) {
filedName = null;
}
}
}
}
nPos2 = lex.getCurrPos();
aWord = lex.getAWord();
}
nFiledNo ++;
if(filedName==null) {
filedName = "column" + String.valueOf(nFiledNo);
nPosEnd = -1;
}else {
/*if(filedName.endsWith("*"))
return null;*/
int n = filedName.lastIndexOf('.');
if (n > 0) {
filedName = filedName.substring(n + 1);
}
}
fields.add(new MutablePair<>(
filedName, sFieldSql.substring(nPos, (nPosEnd>nPos ? nPosEnd: nPos2)).trim()));
nPos = nPos2;
if (",".equals(aWord)) {
nPos = lex.getCurrPos();
aWord = lex.getAWord();
//filedName = aWord;
}
}
return fields;
}
/* public static String trimFieldPiece(String fieldPiece){
}*/
/**
* 返回sql语句中所有的 字段 语句表达式
* 获得查询语句中的所有 字段描述 ,比如 select a, (b+c) as d, f fn from ta 语句 返回 [ a, (b+c) as d , f fn ]
* @param sql sql
* @return 返回feild字句，这个用户 sql语句编辑界面，在dde，stat项目中使用，一般用不到。
*/
public static List getSqlFieldPieces(String sql){
List fields = new ArrayList<>(5);
List sqlPieces = splitSqlByFields(sql);
if (sqlPieces == null || sqlPieces.size() < 3)
return fields;
String sFieldSql = sqlPieces.get(1);
Lexer lex = new Lexer(sFieldSql,Lexer.LANG_TYPE_SQL);
int nPos = 0;
String aWord = lex.getAWord();
while (aWord != null && !"".equals(aWord) && !"from".equalsIgnoreCase(aWord)) {
int nPos2 = lex.getCurrPos();
while (!"".equals(aWord) && !",".equals(aWord) && !"from".equalsIgnoreCase(aWord)) {
if ("(".equals(aWord)){
lex.seekToRightBracket();
}
nPos2 = lex.getCurrPos();
aWord = lex.getAWord();
}
fields.add(sFieldSql.substring(nPos, nPos2).trim());
nPos = nPos2;
if (",".equals(aWord)) {
nPos = lex.getCurrPos();
aWord = lex.getAWord();
}
}
return fields;
}
/**
* 返回sql语句中所有的 字段 名称
* 获得 查询语句中的所有 字段名称,比如 a, (b+c) as d, f fn from 语句 返回 [a,d,fn]
* @param sFieldSql sFieldSql
* @return 字段名子列表
*/
public static List splitSqlFieldNames(String sFieldSql){
List fields = new ArrayList<>(20);
Lexer lex = new Lexer(sFieldSql,Lexer.LANG_TYPE_SQL);
String aWord = lex.getAWord();
String filedName = aWord;
int nFiledNo=0;
while (aWord != null && !"".equals(aWord) && !"from".equalsIgnoreCase(aWord)) {
boolean prewordIsOpt = false;
while (!"".equals(aWord) && !",".equals(aWord)
&& !"from".equalsIgnoreCase(aWord)) {
if ("(".equals(aWord)){
lex.seekToRightBracket();
prewordIsOpt = false;
} else {
// 如果有 * 则不能解析 字段名
if("*".equals(aWord)){
return null;
}
if (Lexer.isLabel(aWord)) {
if (!prewordIsOpt) {
filedName = aWord;
}
prewordIsOpt = false;
} else {
prewordIsOpt = Formula.getOptID(aWord) > 0;
if (prewordIsOpt) {
filedName = null;
}
}
}
aWord = lex.getAWord();
}
nFiledNo ++;
if(filedName==null) {
filedName = "";
}else {
/*if(filedName.endsWith("*"))
return null;*/
int n = filedName.lastIndexOf('.');
if (n > 0) {
filedName = filedName.substring(n + 1);
// 如果有 * 则不能解析 字段名
if("*".equals(filedName)){
return null;
}
}
}
fields.add(filedName);
if (",".equals(aWord)) {
filedName = aWord;
aWord = lex.getAWord();
}
}
return fields;
}
/**
* 返回sql语句中所有的 字段 名称
* 获得 查询语句中的所有 字段名称,比如 select a, (b+c) as d, f fn from ta 语句 返回 [a,d,fn]
* @param sql sql
* @return 字段名子列表 ， 如果 查询语句中有 * 将返回 null
*/
public static List getSqlFiledNames(String sql){
List sqlPieces = splitSqlByFields(sql);
if (sqlPieces == null || sqlPieces.size() < 3)
return null;
return splitSqlFieldNames(sqlPieces.get(1));
}
/**
* 返回SqlTemplate(sql语句模板)中所有的所有的 字段 名称
* 获得 查询语句中的所有 字段名称,比如 select a, (b+c) as d, f fn from ta 语句 返回 [a,d,fn]
* @param sql sql
* @return 字段名子列表
*/
public static List getSqlTemplateFiledNames(String sql){
List sqlPieces = splitSqlByFields(sql);
if (sqlPieces == null || sqlPieces.size() < 3)
return null;
String sFieldSql = sqlPieces.get(1);
Lexer varMorp = new Lexer(sFieldSql,Lexer.LANG_TYPE_SQL);
StringBuilder sbSql = new StringBuilder();
int prePos = 0;
String aWord = varMorp.getAWord();
while (aWord != null && !"".equals(aWord) && !"from".equalsIgnoreCase(aWord)) {
if(aWord.equals("[")){
int curPos = varMorp.getCurrPos();
if(curPos-1>prePos)
sbSql.append( sFieldSql.substring(prePos, curPos-1));
aWord = varMorp.getAWord();
while(aWord != null && !"|".equals(aWord)){
if("(".equals(aWord)){
varMorp.seekToRightBracket();
}
aWord = varMorp.getAWord();
}
if("|".equals(aWord)){
curPos = varMorp.getCurrPos();
varMorp.seekToRightSquareBracket();
prePos = varMorp.getCurrPos();
sbSql.append( sFieldSql.substring(curPos, prePos-1));
}
aWord = varMorp.getAWord();
}
aWord = varMorp.getAWord();
}
sbSql.append(sFieldSql.substring(prePos));
return splitSqlFieldNames(sbSql.toString());
}
/**
* 过滤 order by 语句中无效信息，在可能带入乱码和注入的情况下使用
* @param sqlOrderBy sqlOrderBy
* @return String
*/
public static String trimSqlOrderByField(String sqlOrderBy){
if (sqlOrderBy == null)
return null;
StringBuilder sb= new StringBuilder();
Lexer lex = new Lexer(sqlOrderBy,Lexer.LANG_TYPE_SQL);
boolean haveOrder = false;
boolean bLastDouHao = false;
String aWord = lex.getAWord();
while (aWord != null && !"".equals(aWord)) {
if(Lexer.isLabel(aWord) || StringUtils.isNumeric(aWord) ){
if( haveOrder){
if(bLastDouHao)
sb.append(",");
else
return null;
}
sb.append(aWord);
haveOrder = true;
bLastDouHao = false;
aWord = lex.getAWord();
if("asc".equalsIgnoreCase(aWord) || "desc".equalsIgnoreCase(aWord)){
sb.append(" ").append(aWord);
aWord = lex.getAWord();
}
if("nulls".equalsIgnoreCase(aWord)){
aWord = lex.getAWord();
if("first".equalsIgnoreCase(aWord) || "last".equalsIgnoreCase(aWord)){
sb.append(" nulls ").append(aWord);
aWord = lex.getAWord();
}else
return null;
}
}else if(",".equals(aWord)){
if(bLastDouHao)
return null;
bLastDouHao = true;//sb.append(",");
aWord = lex.getAWord();
}else
return null;
}
return sb.toString();
}
/**
* 创建sql语句参数键值对
* @param objs 奇数变量为参数名，类型为string，偶数变量为参数值，类型为任意对象（object）
* @return Map String Object
*/
public static Map createSqlParamsMap(Object... objs){
if(objs==null || objs.length<2)
return null;
Map paramsMap = new HashMap<>();
for(int i=0;i tableAlias);
String translateColumn(String columnDesc);
KeyValuePair translateParam(String paramName);
}
public static class SimpleFilterTranslater implements IFilterTranslater{
private Map paramsMap;
private Map tableAlias;
private ObjectTranslate mapTranslate;
public SimpleFilterTranslater(Map paramsMap)
{
this.tableAlias = null;
this.paramsMap = paramsMap;
this.mapTranslate = new ObjectTranslate(paramsMap);
}
@Override
public void setTableAlias(Map tableAlias) {
this.tableAlias = tableAlias;
}
@Override
public String translateColumn(String columnDesc){
if(tableAlias==null||columnDesc==null||tableAlias.size()==0)
return null;
int n = columnDesc.indexOf('.');
if(n<0){
return tableAlias.get(columnDesc);
}
String poClassName = columnDesc.substring(0,n);
String alias = tableAlias.get(poClassName);
if(alias==null)
return null;
return StringUtils.isBlank(alias)? columnDesc.substring(n+1):alias+'.'+ columnDesc.substring(n+1);
}
@Override
public KeyValuePair translateParam(String paramName){
if(paramsMap==null)
return null;
Object obj = paramsMap.get(paramName);
if(obj==null)
return null;
if(obj instanceof String){
if(StringUtils.isBlank((String)obj))
return null;
}
return new KeyValuePair<>(paramName, obj);
}
@Override
public Object getVarValue(String varName) {
return mapTranslate.getVarValue(varName);
}
@Override
public Object getLabelValue(String labelName) {
return mapTranslate.getLabelValue(labelName);
}
}
/**
* 对参数进行预处理
* @param pretreatment pretreatment
* @param paramValue paramValue
* @return Object
*/
public static Object scalarPretreatParameter(String pretreatment,Object paramValue){
if(paramValue==null)
return null;
if(SQL_PRETREAT_LIKE.equalsIgnoreCase(pretreatment))
return getMatchString(StringBaseOpt.objectToString(paramValue));
if(SQL_PRETREAT_STARTWITH.equalsIgnoreCase(pretreatment))
return StringBaseOpt.objectToString(paramValue)+"%";
if(SQL_PRETREAT_ENDWITH.equalsIgnoreCase(pretreatment))
return "%"+StringBaseOpt.objectToString(paramValue);
if(SQL_PRETREAT_NEXTDAY.equalsIgnoreCase(pretreatment))
return DatetimeOpt.addDays(DatetimeOpt.truncateToDay(
DatetimeOpt.castObjectToDate(paramValue)),1);
if( SQL_PRETREAT_DATE.equalsIgnoreCase(pretreatment))
return DatetimeOpt.truncateToDay(
DatetimeOpt.castObjectToDate(paramValue));
if(SQL_PRETREAT_DATETIME.equalsIgnoreCase(pretreatment))
return DatetimeOpt.castObjectToDate(paramValue);
if(SQL_PRETREAT_DATESTR.equalsIgnoreCase(pretreatment))
return DatetimeOpt.convertDateToString(
DatetimeOpt.castObjectToDate(paramValue));
if(SQL_PRETREAT_DATETIMESTR.equalsIgnoreCase(pretreatment))
return DatetimeOpt.convertDatetimeToString(
DatetimeOpt.castObjectToDate(paramValue));
if(SQL_PRETREAT_DIGIT.equalsIgnoreCase(pretreatment))
return StringRegularOpt.trimDigits(StringBaseOpt.objectToString(paramValue));
if(SQL_PRETREAT_UPPERCASE.equalsIgnoreCase(pretreatment))
return StringUtils.upperCase(StringBaseOpt.objectToString(paramValue));
if(SQL_PRETREAT_LOWERCASE.equalsIgnoreCase(pretreatment))
return StringUtils.lowerCase(StringBaseOpt.objectToString(paramValue));
if(SQL_PRETREAT_NUMBER.equalsIgnoreCase(pretreatment))
return StringRegularOpt.trimNumber(StringBaseOpt.objectToString(paramValue));
if(SQL_PRETREAT_INTEGER.equalsIgnoreCase(pretreatment)
|| SQL_PRETREAT_LONG.equalsIgnoreCase(pretreatment))
return NumberBaseOpt.castObjectToLong(paramValue);
if(SQL_PRETREAT_FLOAT.equalsIgnoreCase(pretreatment))
return NumberBaseOpt.castObjectToDouble(paramValue);
if(SQL_PRETREAT_ESCAPE_HTML.equalsIgnoreCase(pretreatment))
return StringEscapeUtils.escapeHtml4(StringBaseOpt.objectToString(paramValue));
if(SQL_PRETREAT_QUOTASTR.equalsIgnoreCase(pretreatment))
return buildStringForQuery(StringBaseOpt.objectToString(paramValue));
/*if(SQL_PRETREAT_STRING.equalsIgnoreCase(pretreatment))
return StringBaseOpt.objectToString(paramValue);
if(SQL_PRETREAT_SPLITFORIN.equalsIgnoreCase(pretreatment))
return String.valueOf(paramValue).split(",");*/
return paramValue;
}
public static Object onePretreatParameter(String pretreatment,Object paramValue){
if(paramValue==null)
return null;
if(SQL_PRETREAT_STRING.equalsIgnoreCase(pretreatment))
return StringBaseOpt.objectToString(paramValue);
if(SQL_PRETREAT_SPLITFORIN.equalsIgnoreCase(pretreatment)){
String sValue = StringBaseOpt.objectToString(paramValue);
if(sValue==null)
return null;
return sValue.split(",");
}
if(paramValue instanceof Collection){
Collection valueList = (Collection )paramValue;
List