摘要:java與資料庫的分頁
資料庫分頁：
--SQL SERVER ：缺點 - 需指定一個欄位當order BY ；優點 - 有index時，速度更快
select * from (
SELECT ROW_NUMBER() OVER(ORDER BY RESOURCE_ID) AS ROWNO,
* from ( select * from YDD_RESOURCE_DESCRIPTION ) e
) R1 WHERE R1.ROWNO >= 11 and R1.ROWNO <=20;
--SQL SERVER 優點：不需指定欄位 ；缺點 - 不如上面有index 。
select * from [dbo].[YDD_RESOURCE_DESCRIPTION]
order by RESOURCE_ID
offset 0 rows
fetch next 10 rows only;
==========================================================
java分頁：
1：memory(依sql全部撈進list，再分頁，只對小量資料適合)
public List<Map<String, Object>> doQueryPageData(JdbcTemplate jdbcTemplate,
String sqlStr, PageRequest pageRequest, String[] columns) throws SQLException {
int curPage = pageRequest.getPageNumber();
int pageSize = pageRequest.getPageSize();
int startRow = curPage * pageSize - (pageSize - 1);
int endRow = curPage * pageSize ;
List<Map<String, Object>> dataLs = null;
dataLs = jdbcTemplate.queryForList(sqlStr, paramMp);
return dataLs.subList(startRow-1, endRow);
}
2：對sql加入db分頁
StringBuffer sb = new StringBuffer();
sb.append(" SELECT * FROM (");
sb.append(" SELECT ROW_NUMBER() OVER(ORDER BY "+ id +") AS ROWNO, * from ( ");
sb.append( sqlStr ); //********* 換sqlStr
sb.append(" ) e ) R1 ");
sb.append(" WHERE R1.ROWNO >= " + String.valueOf(satrtRow));
sb.append(" AND R1.ROWNO <= " + String.valueOf(endRow));
3：使用resultset，設定statement的poolable(true)
public List<Map<String, Object>> doQueryPageData(JdbcTemplate jdbcTemplate,
String sqlStr, PageRequest pageRequest, String[] columns) throws SQLException {
int curPage = pageRequest.getPageNumber();
int pageSize = pageRequest.getPageSize();
int startRow = curPage * pageSize - (pageSize - 1);
int endRow = curPage * pageSize ;
if (endRow > totalPages)
{endRow = totalPages;}
List<Map<String, Object>> data = new ArrayList<>();
Connection conn = jdbcTemplate.getDataSource().getConnection();
Statement st = null;
ResultSet rs = null;
try {
st = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
st.setMaxRows(endRow);
st.setPoolable(true);
rs = st.executeQuery(" select * from tableA ");
rs.absolute(startRow);
if (rs.getRow() != 0) {
for(int i=startRow-1;i<endRow;i++ ) {
Map<String, Object> testMap = new HashMap<String, Object>();
for (int col = 0; col < columns.length ; col++) {
testMap.put(columns[col], (rs.getString(columns[col]) == null? "" : rs.getString(columns[col])));
}
data.add(testMap);
rs.next();
}
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
try { if (rs != null) rs.close(); } catch (Exception e) {};
try { if (st != null) st.close(); } catch (Exception e) {};
try { if (conn != null) conn.close(); } catch (Exception e) {};
}
return data;
}
4：使用EntityManager回傳Query設定回傳筆數
import javax.persistence.EntityManager;
import javax.persistence.Query;
public AdmSqlPaging findByPed(String ped,PageRequest pageRequest) throws SQLException {
Query query = entityManager.createNativeQuery(" select CONVERT(VARCHAR(19), 欄位A) ped,CONVERT(VARCHAR(19),欄位B) styr from 資料表A");
query.setFirstResult(pageRequest.getPageNumber());
query.setMaxResults(pageRequest.getPageSize());
List<Object> dataLs = query.getResultList();
for (Object[] row : (List<Object[]>)query.getResultList()) {
System.out.print("row[0]=" + row[0]);
System.out.println("row[1]=" + row[1]);
}
}