pagedstatementsqlserverimpl.java

本项目是基于展示新闻的一款产品。在本系统中用户可以自定义栏目及新闻分类 发布新闻信息

package cn.handson.model.service.dao.page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.RowSet;
import sun.jdbc.rowset.CachedRowSet;

import cn.handson.model.connection.ConnectionFactory;
import cn.handson.model.service.dao.page.Pagable;
import cn.handson.model.service.dao.page.PageInfo;


public class PagedStatementSqlServerImpl extends PagedStatement{
	 public static final int MAX_PAGE_SIZE = 10000;

	 protected String querySQL = "";
	 protected String countSQL = "";
	 protected int pageCount = 0;
	 protected int pageNo = 0;
	 protected int pageSize = 0;
	 protected long rowCount = 0L;
	 protected RowSet rowSet = null;
	 protected int staIndex;

	 private PageInfo pageInfo;
	

	public void initStatement(String sql, int pageNo, int pageSize) {
		 this.pageNo = pageNo;
		 this.pageSize = pageSize;
		 this.countSQL = "SELECT COUNT(*) FROM "+ getTableName(sql);
		 int startIndex = (pageNo - 1) * pageSize + 1;
		 if( startIndex < 1 ) startIndex = 1;
		 staIndex = startIndex;
		 this.querySQL = initQuerySql( sql, startIndex, this.pageSize );
	  }
	 	 
	  private String getTableName(String sql){
		 String[] sql_strs = sql.split(" ");
		 for(int i=0;i<sql_strs.length;i++){
			 if(sql_strs[i].toUpperCase().equals("FROM")){
				 return sql_strs[i+1];
			 }
		 }
		 return null;
	  }
	  protected String initQuerySql(String sql, long startIndex, int size) {
	    size = Integer.parseInt(String.valueOf(startIndex))-1 + size;
	    return new StringBuffer( sql.length()+8 )
	        .append(sql)
	        .insert( getAfterSelectInsertPoint(sql), " top " + size )
	        .toString();
	  }
	  static int getAfterSelectInsertPoint(String sql) {
	    int selectIndex = sql.toLowerCase().indexOf( "select" );
	    final int selectDistinctIndex = sql.toLowerCase().indexOf( "select distinct" );
	    return selectIndex + ( selectDistinctIndex == selectIndex ? 15 : 6 );
	  }
	  /**
	   * Query one page data.
	   * @return ResutSet object that contains records of page.
	   * @throws SQLException if any database exception occurs.
	   */
	  public RowSet executeQuery() throws SQLException {
//		ConnectionFactory cf = 
//				(ConnectionFactory)BeanFactory.getBean("conn_provider");
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    RowSet rowSet= null;

	    try{
	      conn = connProvider.getConnection();
	      // Get record count.
	      pstmt = conn.prepareStatement(countSQL);
	      rs = pstmt.executeQuery(); //Executes the sql that use the "count(0)" function
	      if (rs.next()) {
	        rowCount = rs.getLong(1); //gets the count of the rows.
	      }
	      else {
	        rowCount = 0L;
	      }
	      rs.close();
	      pstmt.close();
	      // Get record of page
	      pstmt = conn.prepareStatement(querySQL);
	      //sets the fectch size,that will offer the higher efficiency.
	      pstmt.setFetchSize(this.pageSize);
	      rs = pstmt.executeQuery();
	      rowSet = populate(rs); //populate the ResultSet to RowSet.
	      if(staIndex > 1){
	    	  rowSet.absolute(staIndex-1);
	      }
	    }
	    finally{
	      try{
	        if(rs!=null) {
	          rs.close();
	          rs = null;
	        }
	        if(pstmt!=null){
	          pstmt.close();
	          pstmt = null;
	        }
	        if(conn!=null) {
	          conn.close();
	          conn = null;
	        }

	      }
	      catch( SQLException e ){
	        e.printStackTrace();
	      }
	    }

	    this.pageInfo = new PageInfoImpl(pageSize,rowCount,pageNo);
	    return rowSet;
	
	  }

	  /**
	   * Populates the ResultSet to RowSet
	   * @param rs the ResultSet Object
	   * @return RowSet object.
	   * @throws SQLException if any database access exception occurs.
	   * @see javax.sql.RowSet,java.sql.ResultSet
	   */
	  protected RowSet populate( ResultSet rs ) throws SQLException {
	    CachedRowSet crs = new CachedRowSet();
	    crs.populate(rs);
	    return crs;
	  }
	  
	  public RowSet getPagedRowSet() throws SQLException{
		   throw new java.lang.UnsupportedOperationException();
	  }
	  public PageInfo getPageInfo(){
		   return pageInfo;
		 }
}