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;
    }

}

// 使用demo
public 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;
}