拼装getTotalCount的sql_JAVA_编程开发_程序员俱乐部

class="java" name="code">package testsql;

/**
 * Created by dinghw on 2015/5/4.
 *
 * @desc 优化了第一版包含group by查询效率低的问题
 * 此类只支持特定的场景使用，
 * 并且存在写法上的要求：group by 后的参数必须不能存在空格，并且最后一个参数后需要保留一个空格，
 * 例：group by t.app_id,t.channel_id  (channel_id后必须得有空格)
 * <p/>
 * v1.3 修复group by  后面存在order by时sql拼接错误
 * 修复两层select查询 sql拼接错误
 */
public class SqlUtil {
    private static final String SELECT = "SELECT ";
    private static final String FROM = " FROM ";
    private static final String WHERE = " WHERE ";
    private static final String GROUP = " GROUP BY ";
    private static final String ORDER = " ORDER BY ";
    private static final String AS = "\\) AS ";

    public static StringBuffer getCountSql(String sql) throws Exception {
        StringBuffer countSql = new StringBuffer();
        sql = sql.replaceAll(FROM, " from ").replaceAll(GROUP, " group by ").replaceAll(WHERE, " where ").replaceAll(ORDER, " order by ").replaceAll(AS, ") as ");
        int indexFrom = sql.indexOf(" from ");
        if (indexFrom > 0) {
            //判断有几层from，并获取内层的sql
            sql = getInnerFrom(sql);
            indexFrom = sql.indexOf(" from ");
            int indexGroupBy = sql.indexOf(" group by ");
            if (indexGroupBy > 0) {
                countSql.append("select count(distinct ");
                countSql.append(getDistinctParameter(sql));
                countSql.append(") num");
                countSql.append(" from ").append(sql.substring(indexFrom + 6, indexGroupBy));
            } else {
                countSql.append("select count(*) num ");
                countSql.append(sql.substring(sql.indexOf(" from ")));
            }
        } else {
            throw new Exception("error sql");
        }
        System.out.println("getTotalCount sql:" + countSql.toString());
        return countSql;
    }

    private static String getInnerFrom(String sql) {
        int indexFrom = sql.indexOf(" from ");
        int lastFrom = sql.lastIndexOf(" from ");
        if (indexFrom < lastFrom) {
            sql = sql.substring(lastFrom, sql.indexOf(") as "));
        }
        return sql;
    }

    private static String getDistinctParameter(String sql) {
        int indexGroupBy = sql.indexOf(" group by ");
        int indexIterator = sql.indexOf(" ", indexGroupBy + 10);
        return sql.substring(indexGroupBy + 10, indexIterator);
    }

}