java web jdbc oracle 分页_springboot集成jdbcTemplate，分页查询(oracle)数据

package xxx.utils;

import oracle.sql.TIMESTAMP;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.dao.DataAccessException;

import org.springframework.data.domain.*;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.ResultSetExtractor;

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.*;

/**

* @author 张茂原

* @date 2021-01-02

* @description jdbcTemplate操作工具类

*

*/

public class DataBaseHelper {

private static final Logger log = LoggerFactory.getLogger("DataBaseHelper");

/**

* @description 查询Map格式的数据集

*/

public static List> findList(String sql, Object[] params, String[] resultParams, JdbcTemplate jdbcTemplate) {

return jdbcTemplate.query(sql, new RowMapper>() {

@Override

public Map mapRow(ResultSet rs, int i) throws SQLException {

Map map = new HashMap();

for(String resultParam: resultParams){

if(rs.getObject(resultParam) instanceof TIMESTAMP){

map.put(resultParam, rs.getTimestamp(resultParam));

}else{

map.put(resultParam, rs.getObject(resultParam));

}

}

return map;

}

},params);

}

/**

* @description 分页查询数据,返回Page分页需要的数据内容

*/

public static Page> fetchPageForOracle(

String sql,

final Object[] params, final int pageNo, final int pageSize, final String[] resultParams, JdbcTemplate jdbcTemplate) {

final String sqlCount = "select count(*) " + (sql.replaceAll(" ", "").toLowerCase().contains("orderby") ?

sql.substring(sql.toLowerCase().indexOf("from"), sql.toLowerCase().lastIndexOf("order")) :

sql.substring(sql.toLowerCase().indexOf("from")));

final Long rowCount = jdbcTemplate.queryForObject(sqlCount, Long.class, params);

sql = proOracleSql(sql, pageNo, pageSize);

Object queryData = jdbcTemplate.query(sql, new ResultSetExtractor() {

@Override

public List> extractData(ResultSet rs) throws SQLException, DataAccessException {

List> dataList = new ArrayList>();

while(rs.next()){

Map map = new HashMap<>();

for(String resultParam : resultParams){

if(rs.getObject(resultParam) instanceof TIMESTAMP){

map.put(resultParam, rs.getTimestamp(resultParam));

}else{

map.put(resultParam, rs.getObject(resultParam));

}

}

dataList.add(map);

}

return dataList;

}

}, params);

return new PageImpl>((List>)queryData, PageRequest.of(pageNo - 1, pageSize), rowCount.longValue());

}

/**

* @description 将不带分页参数的oracle数据库查询sql修改为优化性能的分页sql 带*号的sql记得在*前加表别名

*/

public static String proOracleSql(String sql, int pageNo, int pageSize){

String sqlRep = sql.replaceAll(" ","").toLowerCase();

if(sqlRep.contains("orderby")){

sql = "SELECT * " +

"FROM (SELECT ROWNUM AS rowno,r.* " +

"FROM (" + sql + ") r " +

"where ROWNUM <= " + pageNo*pageSize +

") table_alias " +

"WHERE table_alias.rowno > " + (pageNo-1)*pageSize;

}else{

sql = "SELECT * " +

"FROM (SELECT ROWNUM AS rowno, " + sql.substring(sql.toLowerCase().indexOf("select") + 7) +

(sqlRep.contains("where") ? " AND" : " where") + " ROWNUM <= " + pageNo*pageSize + ") table_alias " +

"WHERE table_alias.rowno > " + (pageNo-1)*pageSize;

}

return sql;

}

}