熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Java編程 >> Java開源技術 >> 正文

用Spring的JdbcTemplate實現分頁功能

2013-11-23 20:39:10  來源: Java開源技術 

  最近使用了spring中的JdbcTemplate實現數據庫的查詢和插入操作發現spring的JdbcTemplate 不象HibernateTemplate那麼好已經實現了分頁功能所以要自己實現使用getJdbcTemplate()queryForList(string sql)得到的結果集是所有的
  
  如果你的查詢有條記錄或者更多速度肯定慢了當然你可以通過resultset中的游標控制查詢的起始和結束我這裡用的是Oracle數據庫使用偽列ROWNUM來實現分頁我的分頁代碼如下
  
  package comdeityrankingutil;import javautilList;
  import orgsprireJdbcTemplate;
  import orgspriresupportJdbcDaoSupport;
  /** * 分頁函數 * * @author allenpan */public class Pagination extends JdbcDaoSupport{
  public static final int NUMBERS_PER_PAGE = ;
  //一頁顯示的記錄數
  private int numPerPage;
  //記錄總數
  private int totalRows;
  //總頁數
  private int totalPages;
  //當前頁碼
  private int currentPage;
  //起始行數
  private int startIndex;
  //結束行數
  private int lastIndex;
  //結果集存放List
  private List resultList;
  //JdbcTemplate jTemplate
  private JdbcTemplate jTemplate;
  /**
  * 每頁顯示條記錄的構造函數使用該函數必須先給Pagination設置currentPagejTemplate初值
  * @param sql oracle語句
  */
  public Pagination(String sql){
  if(jTemplate == null){
  throw new IllegalArgumentException(comdeityrankingutilPaginationjTemplate is nullplease initial it first );
  }else if(sqlequals()){
  throw new IllegalArgumentException(comdeityrankingutilPaginationsql is emptyplease initial it first );
  }
  new Pagination(sqlcurrentPageNUMBERS_PER_PAGEjTemplate);
  }
  /**分頁構造函數
  * @param sql 根據傳入的sql語句得到一些基本分頁信息
  * @param currentPage 當前頁
  * @param numPerPage 每頁記錄數
  * @param jTemplate JdbcTemplate實例
  */
  public Pagination(String sqlint currentPageint numPerPageJdbcTemplate jTemplate){
  if(jTemplate == null){
  throw new IllegalArgumentException(comdeityrankingutilPaginationjTemplate is nullplease initial it first );
  }else if(sql == null || sqlequals()){
  throw new IllegalArgumentException(comdeityrankingutilPaginationsql is emptyplease initial it first );
  }
  //設置每頁顯示記錄數
  setNumPerPage(numPerPage);
  //設置要顯示的頁數
  setCurrentPage(currentPage);
  //計算總記錄數
  StringBuffer totalSQL = new StringBuffer( SELECT count(*) FROM ( );
  totalSQLappend(sql);
  totalSQLappend( ) totalTable );
  //給JdbcTemplate賦值
  setJdbcTemplate(jTemplate);
  //總記錄數
  setTotalRows(getJdbcTemplate()queryForInt(totalSQLtoString()));
  //計算總頁數
  setTotalPages();
  //計算起始行數
  setStartIndex();
  //計算結束行數
  setLastIndex();
  Systemoutprintln(lastIndex=+lastIndex);//////////////////
  //構造oracle數據庫的分頁語句
  StringBuffer paginationSQL = new StringBuffer( SELECT * FROM ( );
  paginationSQLappend( SELECT temp* ROWNUM num FROM ( );
  paginationSQLappend(sql);
  paginationSQLappend( ) temp where ROWNUM <= + lastIndex);
  paginationSQLappend( ) WHERE num > + startIndex);
  //裝入結果集
  setResultList(getJdbcTemplate()queryForList(paginationSQLtoString()));
  }
  /**
  * @param args
  */
  public static void main(String[] args) {
  // TODO Autogenerated method stub    }
  public int getCurrentPage() {
  return currentPage;
  }
  public void setCurrentPage(int currentPage) {
  thiscurrentPage = currentPage;
  }
  public int getNumPerPage() {
  return numPerPage;
  }
  public void setNumPerPage(int numPerPage) {
  thisnumPerPage = numPerPage;
  }
  public List getResultList() {
  return resultList;    }
  public void setResultList(List resultList) {
  thisresultList = resultList;
  }
  public int getTotalPages() {
  return totalPages;
  }
  //計算總頁數
  public void setTotalPages() {
  if(totalRows % numPerPage == ){
  thistotalPages = totalRows / numPerPage;
  }else{
  thistotalPages = (totalRows / numPerPage) + ;
  }
  }
  public int getTotalRows() {
  return totalRows;
  }
  public void setTotalRows(int totalRows) {
  thistotalRows = totalRows;
  }
  public int getStartIndex() {
  return startIndex;
  }
  public void setStartIndex() {
  thisstartIndex = (currentPage ) * numPerPage;
  }
  public int getLastIndex() {
  return lastIndex;
  }
  public JdbcTemplate getJTemplate() {
  return jTemplate;
  }
  public void setJTemplate(JdbcTemplate template) {
  jTemplate = template;
  }
  //計算結束時候的索引
  public void setLastIndex() {
  Systemoutprintln(totalRows=+totalRows);///////////
  Systemoutprintln(numPerPage=+numPerPage);///////////
  if( totalRows < numPerPage){
  thislastIndex = totalRows;
  }else if((totalRows % numPerPage == ) || (totalRows % numPerPage != && currentPage < totalPages)){
  thislastIndex = currentPage * numPerPage;
  }else if(totalRows % numPerPage != && currentPage == totalPages){//最後一頁
  thislastIndex = totalRows ;
  }
  }}在我的業務邏輯代碼中
  /**
  * find season ranking list from DC
  * @param areaId 選手區域id
  * @param rankDate 賽季
  * @param category 類別
  * @param characterName 角色名
  * @return List
  */
  public List findSeasonRankingList(Long areaId int rankYearint rankMonth
  Long categoryIdString characterName) {
  //SQL語句
  StringBuffer sql = new StringBuffer( SELECT CUSERID useridDPOSNAME posnameCGAMEID gameidCAMOUNT amountCRANK rank FROM );
  //表            sqlappend( (SELECT BUSERID USERID);
  sqlappend( BPOSID POSID);
  sqlappend( ADISTRICT_CODE DISTRICTCODE);
  sqlappend( AGAMEID GAMEID);
  sqlappend( AMOUNT AMOUNT);
  sqlappend( RANK RANK );
  sqlappend( FROM TB_FS_RANK A );
  sqlappend( LEFT JOIN TB_CHARACTER_INFO B );
  sqlappend( ON ADISTRICT_CODE = BDISTRICT_CODE );
  sqlappend( AND AGAMEID = BGAMEID );
  //附加條件
  if(areaId != null && areaIdintValue() != ){
  sqlappend( and ADISTRICT_CODE = + areaIdintValue());
  }
  if( rankYear > && rankMonth > ){
  //hqlappend( and sasiddt >= to_date( + rankYear + + rankMonth + :: + YYYYMMDD HH:MI:SS);
  //hqlappend( and sasiddt <= to_date( + rankYear + + rankMonth + + TimeToolfindMaxDateInMonth(rankYearrankMonth) + :: + YYYYMMDD HH:MI:SS);
  sqlappend( and ADT = fn_time_convert(to_date( + rankYear + + rankMonth + + YYYYMM)) );
  }
  if(categoryId != null && categoryIdintValue() != ){
  sqlappend( and ACID = + categoryIdintValue());
  }
  if(characterName != null && !characterNametrim()equals()){
  sqlappend( and AGAMEID = + characterNametrim()+ );
  }
  sqlappend( ORDER BY RANK ASC) C );
  sqlappend( LEFT JOIN TB_FS_POSITION D );
  sqlappend( ON CPOSID = DPOSID );
  sqlappend( ORDER BY CRANK );
  Systemoutprintln(hql=+sqltoString());////////////////
  //使用自己的分頁程序控制結果集
  Pagination pageInfo = new Pagination(sqltoString()getJdbcTemplate());
  return pageInfogetResultList();
  //return getJdbcTemplate()queryForList(sqltoString());
  }
From:http://tw.wingwit.com/Article/program/Java/ky/201311/28840.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.