pagedstatementmysqlimpl.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 PagedStatementMysqlImpl 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;

	 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;
		 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;
	  }
	  /**
	   * Construct the SQL statement that can be used to get records by page.
	   * @param sql original SQL satatement
	   * @param startIndex start index of ROWNUM
	   * @param size indicates how many records in one page.
	   * @return the SQL statement that can be used to get records by page.
	   */
	  protected String initQuerySql(String sql, long startIndex, int size) {
	    
		StringBuffer buf = new StringBuffer( sql.length()+20 )
			.append(sql);
		if (startIndex>0) {
			buf.append(" limit ")
				.append(startIndex)
				.append(", ")
				.append(size);
		}
		else {
			buf.append(" limit ")
				.append(size);
		}
		return buf.toString();
	  }
	  /**
	   * 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 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.
		   }
		   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;

		 }
		 /**
		  * Close PreparedStatement & Connection
		  * @throws SQLException if any database exception coccurs.
		  */
		 public void close() throws SQLException{

		 }
		  /**
		  * Query one page data.
		  * @return RowSet object that contains records of page.The RowSet can be used
		  * without Database Connection actived.
		  * @throws SQLException if any databas exception occurs.
		  */
		 public RowSet getPagedRowSet() throws SQLException{
		   throw new java.lang.UnsupportedOperationException();
		 }
		 public PageInfo getPageInfo(){
		   return pageInfo;
		 }
}