Util工具类 跨Oracle、MySQL通用分页查询

/** *  * 跨Oracle、MySQL通用分页查询 */public class PagingUtil {    public static final String MYSQL = "MYSQL";    public static final String ORACLE = "ORACLE";    private static Logger logger = Logger.getLogger(PagingUtil.class);    private static String databaseType = null;    /**     * 获取数据库类型     * @return     */    public static String getDatabaseType() {        if (databaseType != null) {            return databaseType;        }        Connection connection = null;        try {            JdbcTemplate jdbcTemplate = (JdbcTemplate) SpringUtil.getBean("jdbcTemplate");            connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());            DatabaseMetaData databaseMetaData = connection.getMetaData();            String driverName = databaseMetaData.getDriverName();            if (connection != null) {                connection.close();                connection = null;            }            if (StringUtils.isEmpty(driverName)) {                return null;            } else {                driverName = driverName.toUpperCase();                if (driverName.indexOf(MYSQL) != -1) {                    databaseType = MYSQL;                } else if (driverName.indexOf(ORACLE) != -1) {                    databaseType = ORACLE;                } else {                    return null;                }            }            return databaseType;        } catch (Exception e) {            logger.error(e.getMessage(), e);        } finally {            if (connection != null) {                try {                    connection.close();                } catch (SQLException e) {                    logger.error(e.getMessage(), e);                }            }        }        return null;    }    /**     * 获取通用于MySQL和Oracle数据库的分页语句     * @param sql           需要分页的sql语句，参数采用{0}，{1}这样的方式，与params数组的数据一一对应     * @param pageable     * @return     */    public static String getPageSql(String sql, Pageable pageable) {        return getPageSql(sql, null, pageable, false);    }    /**     * 获取通用于MySQL和Oracle数据库的分页语句     * @param sql           需要分页的sql语句，参数采用{0}，{1}这样的方式，与params数组的数据一一对应     * @param params        参数数组     * @param pageable     * @return     */    public static String getPageSql(String sql, Object[] params, Pageable pageable) {        return getPageSql(sql, params, pageable, false);    }    /**     * 获取通用于MySQL和Oracle数据库的分页语句     * @param sql           需要分页的sql语句，参数采用{0}，{1}这样的方式，与params数组的数据一一对应     * @param params        参数数组     * @param pageable     * @param isOrder       是否生成排序 true:生成排序，false不生成     * @return     */    public static String getPageSql(String sql, Object[] params, Pageable pageable, boolean isOrder) {        if (StringUtils.isEmpty(sql)) {            return null;        }        /**         * order by         */        if (isOrder) {            sql = addOrderToSql(sql, pageable);        }        /**         * 根据数据库类型不同调用不同的方法         */        String databaseType = getDatabaseType();        if (MYSQL.equals(databaseType)) {            return getMysqlSql(sql, params, pageable);        } else if (ORACLE.equals(databaseType)) {            return getOracleSql(sql, params, pageable);        }        return null;    }    /**     * 获取通用于MySQL和Oracle数据库的分页语句     * @param sql           sql语句     * @return              统计数量取字段：CNT     */    public static String getCountSql(String sql) {        return getCountSql(sql, null);    }    /**     * 获取通用于MySQL和Oracle数据库的分页语句     * @param sql           sql语句，参数采用{0}，{1}这样的方式，与params数组的数据一一对应     * @param params        参数数组     * @return              统计数量取字段：CNT     */    public static String getCountSql(String sql, Object[] params) {        if (StringUtils.isEmpty(sql)) {            return null;        }        String upperSql = sql.toUpperCase();        if (upperSql.indexOf(" FROM ") == -1) {            return null;        }        /**         * 格式化参数         */        if (params != null && params.length > 0) {            MessageFormat messageFormat = new MessageFormat(sql);            sql = messageFormat.format(params);        }        int index = upperSql.indexOf(" FROM ");        StringBuilder sb = new StringBuilder();        sb.append("SELECT COUNT(1) CNT ")                .append(sql.substring(index));        return sb.toString();    }    /**     * oracle 分页  rownum     * @param sql           需要分页的sql语句，参数采用{0}，{1}这样的方式，与params数组的数据一一对应     * @param params        参数数组     * @param pageable     * @return     */    private static String getOracleSql(String sql, Object[] params, Pageable pageable) {        /**         * 格式化参数         */        if (params != null && params.length > 0) {            MessageFormat messageFormat = new MessageFormat(sql);            sql = messageFormat.format(params);        }        if (pageable == null)            return sql;        StringBuilder sb = new StringBuilder();        sb.append("select * from (")                .append("select rownum rn, a.* from(")                .append(sql)                .append(") a where rownum <= ")                .append((pageable.getPageNumber() + 1) * pageable.getPageSize())                .append(") where rn > ")                .append(pageable.getPageNumber() * pageable.getPageSize());        return sb.toString();    }    /**     * mysql分页  limit     * @param sql           需要分页的sql语句，参数采用{0}，{1}这样的方式，与params数组的数据一一对应     * @param params        参数数组     * @param pageable     * @return     */    private static String getMysqlSql(String sql, Object[] params, Pageable pageable) {        /**         * 格式化参数         */        if (params != null && params.length > 0) {            MessageFormat messageFormat = new MessageFormat(sql);            sql = messageFormat.format(params);        }        StringBuilder sb = new StringBuilder();        sb.append(sql);        if (pageable != null) {            sb.append(" LIMIT ");            sb.append(pageable.getPageNumber() * pageable.getPageSize());            sb.append(" , ");            sb.append(pageable.getPageSize());        }        return sb.toString();    }    /**     * 传入sql语句，根据Pageable内参数加上order by语句     * @param sql       不带order by的语句     * @param pageable     * @return     */    public static String addOrderToSql(String sql, Pageable pageable) {        if (pageable != null && !StringUtils.isEmpty(sql)) {            Sort sort = pageable.getSort();            Iterator<Sort.Order> iterator = sort.iterator();            Sort.Order order;            String direction;            String property;            /**             * 包含排序语句             */            if (iterator.hasNext()) {                StringBuilder sb = new StringBuilder(sql);                sb.append(" order by ");                while (iterator.hasNext()) {                    order = iterator.next();                    direction = order.getDirection().toString();                    property = order.getProperty();                    sb.append(property).append(" ").append(direction).append(",");                }                return sb.substring(0, sb.length() - 1);            }            return sql;        } else {            return sql;        }    }    /**     * 计算总页数     *     * @param totals 总记录数     * @param rows   每页显示数     * @return     */    public static int getPages(Integer totals, Integer rows) {        if (totals <= 0 || rows <= 0) {            return 0;        }        int pages = totals / rows;        if (totals % rows > 0) {            pages++;        }        return pages;    }}

// 使用demopublic List<Bond> findBondByNameSecond(String name) {    Pageable pageable = new PageRequest(0, 10);    StringBuilder sb = new StringBuilder();    sb.append("select t.tradingcode as tradingcode,t.secuabbr as secuabbr,t.exchangecode as exchangecode");    sb.append("  from BND_BASICINFO t");    sb.append("  left join BND_BONDTYPESWIND a");    sb.append("    on t.secucode = a.secucode");    sb.append("   where a.categorycodeii != '1000002990'");    sb.append("   and a.categorycodei in ('a101020500',");    sb.append("                           'a101020a00')");    sb.append("                           and t.secuabbr like '%");    sb.append(name);    sb.append("%' ");    sb.append("   order by secuabbr ");    String sql = PagingUtil.getPageSql(sb.toString(), pageable);    List<Bond> list = jdbcTemplate.query(sql, new RowMapper<Bond>() {        @Override        public Bond mapRow(ResultSet resultSet, int i) throws SQLException {            Bond bond = new Bond();            bond.setTradingcode(resultSet.getString("tradingcode"));            bond.setSecuabbr(resultSet.getString("secuabbr"));            bond.setExchangecode(resultSet.getString("exchangecode"));            return bond;        }    });    return list;}