mySql,oracle分页工具类

/**
*

• 跨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 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 list = jdbcTemplate.query(sql, new RowMapper() {
@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;
}

作者：Java软件小白
来源：优快云
原文：https://blog.youkuaiyun.com/qq_34117825/article/details/78393376
版权声明：本文为博主原创文章，转载请附上博文链接！