com.centit.support.database.utils.QueryUtils Maven / Gradle / Ivy

Go to download
package com.centit.support.database.utils;

import com.centit.support.algorithm.*;
import com.centit.support.common.LeftRightPair;
import com.centit.support.compiler.EmbedFunc;
import com.centit.support.compiler.Lexer;
import com.centit.support.compiler.VariableFormula;
import com.centit.support.compiler.VariableTranslate;
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 {

    /**
     * 表示这个参数不需要
     */
    public static final String SQL_PRETREAT_NO_PARAM = "NP";
    /**
     * 转化为模式匹配字符串，字符串中间的空格、tab都会被%替换
     */
    public static final String SQL_PRETREAT_LIKE = "LIKE";
    /**
     * 用于like语句，只在参数后面添加一个 %，MySql建议只用这个，其他的匹配方式在MySql中效率都比较低
     */
    public static final String SQL_PRETREAT_STARTWITH = "STARTWITH";
    /**
     * 用于like语句，只在参数前面添加一个 %
     */
    public static final String SQL_PRETREAT_ENDWITH = "ENDWITH";
    /**
     * 转化为日期类型, 没有时间， 处理效果和SQL_PRETREAT_TRUNC_DAY一致
     */
    public static final String SQL_PRETREAT_DATE = "DATE";
    /**
     * 转化为带时间的，日期的类型
     */
    public static final String SQL_PRETREAT_DATETIME = "DATETIME";
    /**
     * 转化为日期类型，并且计算第二天的日期，没有时间（时间为00:00:00） 用于区间查询的结束时间
     */
    public static final String SQL_PRETREAT_NEXT_DAY = "NEXTDAY";
    /**
     * 转化为日期类型，并且计算下一月的一日，没有时间（时间为00:00:00） 用于区间查询的结束时间
     */
    public static final String SQL_PRETREAT_NEXT_MONTH = "NEXTMONTH";
    /**
     * 转化为日期类型，并且计算下一年的一月一日，没有时间（时间为00:00:00） 用于区间查询的结束时间
     */
    public static final String SQL_PRETREAT_NEXT_YEAR = "NEXTYEAR";
    /**
     * 转化为日期类型，并且计算下一周周一，没有时间（时间为00:00:00） 用于区间查询的结束时间
     */
    public static final String SQL_PRETREAT_NEXT_WEEK = "NEXTWEEK";
    /**
     * 转化为日期类型，并且截断到当天，没有时间（时间为00:00:00）） 用于区间查询的开始时间
     */
    public static final String SQL_PRETREAT_TRUNC_DAY = "TRUNCDAY";
    /**
     * 转化为日期类型，并且截断到当月一日，没有时间（时间为00:00:00） 用于区间查询的开始时间
     */
    public static final String SQL_PRETREAT_TRUNC_MONTH = "TRUNCMONTH";
    /**
     * 转化为日期类型，并且截断到当年一月一日，没有时间（时间为00:00:00）用于区间查询的开始时间
     */
    public static final String SQL_PRETREAT_TRUNC_YEAR = "TRUNCYEAR";
    /**
     * 转化为日期类型，并且截断到本周周一，没有时间（时间为00:00:00）用于区间查询的开始时间
     */
    public static final String SQL_PRETREAT_TRUNC_WEEK = "TRUNCWEEK";

    /**
     * 转化为 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[];对于支持数组变量的spring jdbcTemplate
     * 或者hibernate中的hql用这个处理就可以了，先腾实现的jpa也支持数组参数
     */
    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";

    /**
     * 根据 数组变量 循环
     */
    public static final String SQL_PRETREAT_LOOP = "LOOP";

    /**
     * 根据 数组变量 循环 并且用 or 连接
     * 结果是  and （ sentence or  sentence or ....)
     * 这个预处理会自动添加  and () 所以语句开头不能添加 and
     */
    public static final String SQL_PRETREAT_LOOP_WITH_OR = "LOOPWITHOR";
    /**
     * 将参数值 拼接到 sql对应的参数位置，同时要避免sql注入；一般用与Order by中
     */
    public static final String SQL_PRETREAT_INPLACE = "INPLACE";
    /**
     * 过滤参数中的html标签
     */
    public static final String SQL_PRETREAT_ESCAPE_HTML = "ESCAPEHTML";

    private QueryUtils() {
        throw new IllegalAccessError("Utility class");
    }

    /**
     * 把字符串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 rowcounts from " +
                removeOrderBy(sqlPieces.get(2));

        return sqlPieces.get(0) + " count(*) as rowcounts 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 rowCounts 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);
    }

    /**
     * 生成PostgreSql分页查询语句
     *
     * @param sql         sql
     * @param offset      offset
     * @param maxsize     maxsize
     * @param asParameter asParameter
     * @return String
     */
    public static String buildPostgreSqlLimitQuerySQL(String sql, int offset, int maxsize, boolean asParameter) {
        if (asParameter)
            return "select * from ("+sql+") a " + (offset > 0 ? " limit ? offset ?" : " limit ?");
        else
            return "select * from ("+sql+") a " + (offset > 0 ? " limit " + maxsize + " offset " + offset :
                " limit " + maxsize);
    }

    public static String buildMySqlLimitQuerySQL(String sql, int offset, int maxsize, boolean asParameter) {
        if (asParameter)
            return "select * from ("+sql+") a " + (offset > 0 ? " limit ?, ?" : " limit ?");
        else
            return "select * from ("+sql+") a " + (offset > 0 ? " limit " + offset+ "," + maxsize:
                " limit " + 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*/){
        if (offset > 0) {

            List sqlPieces = splitSqlByFields(sql);
            if (sqlPieces == null || sqlPieces.size() < 3)
                return sql;
            String alias_list = StringBaseOpt.objectToString(splitSqlFieldNames(sqlPieces.get(1)));
            String whereSql = QueryUtils.removeOrderBy(sqlPieces.get(2));
            String oderbySql = sqlPieces.get(2).substring(whereSql.length());
            if(StringUtils.isBlank(oderbySql)){
                oderbySql = "ORDER BY CURRENT_TIMESTAMP";
            } else {
                oderbySql = oderbySql.trim();
            }
            StringBuilder sqlStr = new StringBuilder(sql.length() * 2);
            sqlStr.append("WITH query AS (SELECT inner_query.* , ROW_NUMBER() OVER ( ")
                .append(oderbySql).append(" ) as __row_nr__ FROM ( ")
                .append(sqlPieces.get(0)).append(sqlPieces.get(1))
                .append(whereSql).append(") inner_query ) SELECT ")
                .append(alias_list).append( " FROM query WHERE __row_nr__ >")
                .append(offset).append(" AND __row_nr__ <= ").append(offset + maxsize);
            return sqlStr.toString();

        } else {
            Lexer sqlLexer = new Lexer(sql, Lexer.LANG_TYPE_SQL);
            StringBuilder sqlStr = new StringBuilder(sql.length() + 20);
            String sw = sqlLexer.getAWord();
            while(StringUtils.isNotBlank(sw)){
                if(sw.equals("(")){
                    int pos = sqlLexer.getCurrPos();
                    sqlLexer.seekToRightBracket();
                    int endPos = sqlLexer.getCurrPos();
                    sqlStr.append(" ").append(sql.substring(pos-1, endPos)).append(" ");
                } else if(sw.equalsIgnoreCase("select")){
                    sqlStr.append(sw).append(" ");
                    String sw2 = sqlLexer.getAWord();
                    if(sw2.equalsIgnoreCase("distinct")){
                        sqlStr.append(sw2).append(" ");
                        sw2 = sqlLexer.getAWord();
                    }
                    if(sw2.equalsIgnoreCase("top")){
                        sqlLexer.getAWord(); // 获取数字 忽略
                        sw2 = sqlLexer.getAWord();
                    }
                    sqlStr.append("top ").append(maxsize).append(" ")
                        .append(sw2).append(" ");
                } else {
                    sqlStr.append(sw).append(" ");
                }
                sw = sqlLexer.getAWord();
            }
            return sqlStr.toString();
        }
    }

    public static String buildLimitQuerySQL(String sql, int offset, int maxsize,
                                            boolean asParameter, DBType dbType) {
        switch (dbType) {
            case Oracle:
            case DM:
            case KingBase:
            case GBase:
            case Oscar:
                return buildOracleLimitQuerySQL(sql, offset, maxsize, asParameter);
            case DB2:
                return buildDB2LimitQuerySQL(sql, offset, maxsize);
            case SqlServer:
            case Access:
                return buildSqlServerLimitQuerySQL(sql, offset, maxsize);
            case MySql:
            case H2:
                return buildMySqlLimitQuerySQL(sql, offset, maxsize, asParameter);
            case PostgreSql:
                return buildPostgreSqlLimitQuerySQL(sql, offset, maxsize, asParameter);

            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 LeftRightPair> 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 LeftRightPair<>(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) && !VariableFormula.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> extraSqlFieldNamePieceMap(String sql) {

        List sqlPieces = splitSqlByFields(sql);
        if (sqlPieces == null || sqlPieces.size() < 3)
            return new ArrayList<>(0);

        return extraFieldNamePieceMap(sqlPieces.get(1));
    }

    public static Map extraTables(String fromSql) {
        Lexer lex = new Lexer(fromSql, Lexer.LANG_TYPE_SQL);
        String aWord = lex.getAWord();
        Map tableNameMap = new HashMap<>(4);
        while (aWord != null && !"".equals(aWord) && !StringUtils.equalsAnyIgnoreCase(aWord,
            "where", "group", "order")) {

            if("(".equals(aWord)) {
                int nPos2 = lex.getCurrPos();
                lex.seekToRightBracket();
                int nPosEnd = lex.getCurrPos();
                ;
                String subQuery = fromSql.substring(nPos2, nPosEnd);
                List sqlPieces = splitSqlByFields(subQuery);
                // 子查询的别名
                aWord = lex.getAWord();
                if (sqlPieces != null && sqlPieces.size() > 2) {
                    Map subTtableNameMap = extraTables(sqlPieces.get(2));
                    if (subTtableNameMap != null) {
                        if (!StringUtils.equalsAnyIgnoreCase(aWord,
                            ",", "left", "right", "inner", "outer", "join")) {
                            for (String subTableName : subTtableNameMap.keySet()) {
                                tableNameMap.put(subTableName, aWord);
                            }
                            aWord = lex.getAWord();
                        } else {
                            tableNameMap.putAll(subTtableNameMap);
                        }
                    } else {
                        if (!StringUtils.equalsAnyIgnoreCase(aWord,
                            ",", "left", "right", "inner", "outer", "join")) {
                            aWord = lex.getAWord();
                        }
                    }
                }
            }else {
                String talbeName = aWord;
                aWord = lex.getAWord(); // 别名
                if(!StringUtils.equalsAnyIgnoreCase(aWord,
                    ",", "on", "left", "right", "inner", "outer", "join", "where", "group", "order")){
                    tableNameMap.put(talbeName, aWord);
                    aWord = lex.getAWord();
                } else {
                    tableNameMap.put(talbeName, talbeName);
                }
            }
            if("on".equals(aWord)){
                aWord = lex.getAWord();
                while(!StringUtils.equalsAnyIgnoreCase(aWord,
                    ",", "left", "right", "inner", "outer", "join")){
                    aWord = lex.getAWord();
                }
            }
            while(StringUtils.equalsAnyIgnoreCase(aWord,
                ",", "left", "right", "inner", "outer", "join")){
                aWord = lex.getAWord();
            }
        }
        return tableNameMap;
    }
    public static Pair>, Map> extraFieldAndTable(String sql) {

        List sqlPieces = splitSqlByFields(sql);
        if (sqlPieces == null || sqlPieces.size() < 3)
            return null;

        List> fieldNameMap = extraFieldNamePieceMap(sqlPieces.get(1));
        Map tableNameMap = extraTables(sqlPieces.get(2));

        return new MutablePair<>(fieldNameMap, tableNameMap);
    }

    public static List> extraFieldNamePieceMap(String sFieldSql) {
        List> fields = new ArrayList<>(20);
        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 = VariableFormula.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;
    }

    /**
     * 返回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)) {
            while (!"".equals(aWord) && !",".equals(aWord)
                && !"from".equalsIgnoreCase(aWord)) {
                if ("(".equals(aWord)) {
                    lex.seekToRightBracket();
                    filedName = null;
                } else {
                    // 如果有 * 则不能解析 字段名
                    if ("*".equals(aWord)) {
                        return null;
                    }
                    if (VariableFormula.getOptID(aWord) > 0) {
                        filedName = null;
                    } else {
                        filedName = StringRegularOpt.trimString(aWord);
                    }
                }
                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
     * @see com.centit.support.algorithm.CollectionsOpt 方法 createHashMap
     */
    @Deprecated
    public static Map createSqlParamsMap(Object... objs) {
        return CollectionsOpt.createHashMap(objs);
    }

    /**
     * 对参数进行预处理
     *
     * @param pretreatment pretreatment
     * @param paramValue   paramValue
     * @return Object
     */
    public static Object scalarPretreatParameter(String pretreatment, Object paramValue) {
        if (paramValue == null)
            return null;
        switch (pretreatment.toUpperCase()) {
            case SQL_PRETREAT_LIKE:
                return getMatchString(StringBaseOpt.objectToString(paramValue));
            case SQL_PRETREAT_STARTWITH:
                return StringBaseOpt.objectToString(paramValue) + "%";
            case SQL_PRETREAT_ENDWITH:
                return "%" + StringBaseOpt.objectToString(paramValue);
            case SQL_PRETREAT_NEXT_DAY:
                return DatetimeOpt.addDays(DatetimeOpt.truncateToDay(
                    DatetimeOpt.castObjectToDate(paramValue)), 1);
            case SQL_PRETREAT_NEXT_MONTH:
                return DatetimeOpt.addMonths(DatetimeOpt.truncateToMonth(
                    DatetimeOpt.castObjectToDate(paramValue)), 1);
            case SQL_PRETREAT_NEXT_YEAR:
                return DatetimeOpt.addYears(DatetimeOpt.truncateToYear(
                    DatetimeOpt.castObjectToDate(paramValue)), 1);
            case SQL_PRETREAT_NEXT_WEEK:
                return DatetimeOpt.addDays(DatetimeOpt.truncateToWeek(
                    DatetimeOpt.castObjectToDate(paramValue)), 7);

            case SQL_PRETREAT_TRUNC_MONTH:
                return DatetimeOpt.truncateToMonth(
                    DatetimeOpt.castObjectToDate(paramValue));
            case SQL_PRETREAT_TRUNC_YEAR:
                return DatetimeOpt.truncateToYear(
                    DatetimeOpt.castObjectToDate(paramValue));
            case SQL_PRETREAT_TRUNC_WEEK:
                return DatetimeOpt.truncateToWeek(
                    DatetimeOpt.castObjectToDate(paramValue));

            case SQL_PRETREAT_TRUNC_DAY:
            case SQL_PRETREAT_DATE:
                return DatetimeOpt.truncateToDay(
                    DatetimeOpt.castObjectToDate(paramValue));

            case SQL_PRETREAT_DATETIME:
                return DatetimeOpt.castObjectToDate(paramValue);

            case SQL_PRETREAT_DATESTR:
                return DatetimeOpt.convertDateToString(
                    DatetimeOpt.castObjectToDate(paramValue));

            case SQL_PRETREAT_DATETIMESTR:
                return DatetimeOpt.convertDatetimeToString(
                    DatetimeOpt.castObjectToDate(paramValue));
            case SQL_PRETREAT_DIGIT:
                return StringRegularOpt.trimDigits(StringBaseOpt.objectToString(paramValue));
            case SQL_PRETREAT_UPPERCASE:
                return StringUtils.upperCase(StringBaseOpt.objectToString(paramValue));
            case SQL_PRETREAT_LOWERCASE:
                return StringUtils.lowerCase(StringBaseOpt.objectToString(paramValue));
            case SQL_PRETREAT_NUMBER:
                return StringRegularOpt.trimNumber(StringBaseOpt.objectToString(paramValue));
            case SQL_PRETREAT_INTEGER:
            case SQL_PRETREAT_LONG:
                return NumberBaseOpt.castObjectToLong(paramValue);
            case SQL_PRETREAT_FLOAT:
                return NumberBaseOpt.castObjectToDouble(paramValue);
            case SQL_PRETREAT_ESCAPE_HTML:
                return StringEscapeUtils.escapeHtml4(StringBaseOpt.objectToString(paramValue));
            case SQL_PRETREAT_QUOTASTR:
                return buildStringForQuery(StringBaseOpt.objectToString(paramValue));

            case SQL_PRETREAT_STRING:
                return StringBaseOpt.objectToString(paramValue);
        /*case SQL_PRETREAT_SPLITFORIN:
            return String.valueOf(paramValue).split(",");*/
            default:
                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