分页查询：根据一条sql，同时获得数据总数和数据集合 MySql5PageHepler

分页查询：根据一条sql，同时获得数据总数和数据集合 import java.util.regex.Matcher; import java.util.regex.Pattern;/** * * MYSQL分布帮助类 * <功能详细描述> * * @author  005818 * @version  [版本号, 2012-11-29] * @see  [相关类/方法] * @since  [产品/模块版本] */public class MySql5PageHepler{    public static void main(String[] args) {        String aa = "SELECT T6211.F02 AS F01, T6230.F01 AS F02, T6230.F02 AS F03, T6230.F03 AS F04, T6230.F04 AS F05, T6230.F05 AS F06, T6230.F06 AS F07, T6230.F07 AS F08, T6230.F08 AS F09, T6230.F09 AS F10, T6230.F20 AS F11, T6230.F21 AS F12, T6230.F22 AS F13, T6230.F23 AS F14, T5124.F02 AS F15,T6230.F11 AS F16,T6230.F13 AS F17,T6230.F14 AS F18, (SELECT T6161.F18 FROM S61.T6161 WHERE T6161.F01 = T6236.F03) AS F19, T6231.F21 AS F20, T6231.F22 AS F21, T6230.F10 AS F22,T6230.F19 AS F23,T6230.F12 AS F24,T6110.F06 AS F25,T6230.F21 AS F26, case WHEN T6230.F20='HKZ' or  T6230.F20='YJQ' or  T6230.F20='YDF' THEN ( T6230.F05- T6230.F07) ELSE T6230.F05 END AS F27, CASE WHEN T6231.F21='S' THEN (T6231.F22/DAYOFMONTH(last_day(T6230.F24))) ELSE T6230.F09 END AS F28, case WHEN T6230.F20='HKZ' or  T6230.F20='YJQ' or  T6230.F20='YDF' THEN 0 ELSE T6230.F07 END AS F29 ,case WHEN T6230.F20='HKZ' or  T6230.F20='YJQ' or  T6230.F20='YDF' THEN 100 ELSE case WHEN T6230.F20='YFB' THEN -1 ELSE((T6230.F05-T6230.F07)/T6230.F05)*100 END END AS F30, ( SELECT T6161.F04 FROM S61.T6161 WHERE T6161.F01 = T6236.F03) AS F31, T6230.F28 AS F32, T6231.F29 AS F33, T6231.F30 AS F34, T6231.F27 AS F35, T6231.F28 AS F36 FROM S62.T6211 INNER JOIN S62.T6230 ON T6211.F01 = T6230.F04 INNER JOIN S62.T6231 ON T6231.F01 = T6230.F01 LEFT JOIN S51.T5124 ON T6230.F23 = T5124.F01  INNER JOIN S61.T6110 ON T6110.F01 = T6230.F02 LEFT JOIN S62.T6236 ON T6236.F02 = T6230.F01 AND T6236.F04 = 'S' WHERE T6230.F20 IN (?,?,?,?,?,?)  AND T6231.F29 = ?  ORDER BY T6231.F30 DESC";        System.out.println(getCountString(aa));    }    /**     * 得到查询总数的sql     */    public static String getCountString(String querySelect)    {        querySelect = getLineSql(querySelect);        int orderIndex = getLastOrderInsertPoint(querySelect);        int formIndex = getAfterFormInsertPoint(querySelect);        String select = querySelect.substring(0, formIndex);        //如果SELECT 中包含 DISTINCT 只能在外层包含COUNT        if (select.toLowerCase().indexOf("select distinct") != -1                || querySelect.toLowerCase().indexOf("group by") != -1)        {            return new StringBuffer(querySelect.length()).append("select count(1) count from (")                    .append(querySelect.substring(0, orderIndex))                    .append(" ) t")                    .toString();        }        else        {            return new StringBuffer(querySelect.length()).append("select count(1) count ")                    .append(querySelect.substring(formIndex, orderIndex))                    .toString();        }    }    /**     * 得到最后一个Order By的插入点位置     * @return 返回最后一个Order By插入点的位置     */    private static int getLastOrderInsertPoint(String querySelect)    {        int orderIndex = querySelect.toLowerCase().lastIndexOf("order by");        if (orderIndex == -1 || !isBracketCanPartnership(querySelect.substring(orderIndex, querySelect.length())))        {            throw new RuntimeException("My SQL 分页必须要有Order by 语句!");        }        return orderIndex;    }    /**     * 得到分页的SQL     * @param offset   偏移量     * @param limit       位置     * @return 分页SQL     */    public static String getLimitString(String querySelect, int offset, int limit)    {        querySelect = getLineSql(querySelect);        String sql = querySelect + " limit " + offset + " ," + limit;        return sql;    }    /**     * 将SQL语句变成一条语句，并且每个单词的间隔都是1个空格     *     * @param sql SQL语句     * @return 如果sql是NULL返回空，否则返回转化后的SQL     */    private static String getLineSql(String sql)    {        return sql.replaceAll("[\r\n]", " ").replaceAll("\\s{2,}", " ");    }    /**     * 得到SQL第一个正确的FROM的的插入点     */    private static int getAfterFormInsertPoint(String querySelect)    {        String regex = "\\s+FROM\\s+";        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);        Matcher matcher = pattern.matcher(querySelect);        while (matcher.find())        {            int fromStartIndex = matcher.start(0);            String text = querySelect.substring(0, fromStartIndex);            if (isBracketCanPartnership(text))            {                return fromStartIndex;            }        }        return 0;    }    /**     * 判断括号"()"是否匹配,并不会判断排列顺序是否正确     *     * @param text     *            要判断的文本     * @return 如果匹配返回TRUE,否则返回FALSE     */    private static boolean isBracketCanPartnership(String text)    {        if (text == null || (getIndexOfCount(text, '(') != getIndexOfCount(text, ')')))        {            return false;        }        return true;    }    /**     * 得到一个字符在另一个字符串中出现的次数     * @param text 文本     * @param ch    字符     */    private static int getIndexOfCount(String text, char ch)    {        int count = 0;        for (int i = 0; i < text.length(); i++)        {            count = (text.charAt(i) == ch) ? count + 1 : count;        }        return count;    }}