package pagination;

import java.sql.*;
import java.util.*;
import datasource.*;

/**
 * @author lixiaoqing
 *
 */
public abstract class Pagination {
	private String sql;
	private int rowsPerPage; // 每頁顯示的行數
	private int rowsCount;   // 總行數
	private int pagesCount;  // 總頁數

	public void setRowsPerPage(int rowsPerPage) {
		this.rowsPerPage = rowsPerPage;
	}

	// 在設置SQL語句時計算總行數和總頁數，
	// 這樣總行數只要查詢一次，可以提高效率！
	public void setSQL(String sql) throws SQLException {
		this.sql = sql;
		this.rowsCount = 0;
		this.pagesCount = 0;

		// 獲取總行數并計算總頁數
		this.rowsCount = countRows();
		this.pagesCount = countPages();
	}

	public String getSQL() {
		return sql;
	}

	public int getRowsPerPage() {
		return rowsPerPage;
	}

	public int getRowsCount() {
		return rowsCount;
	}

	public int getPagesCount() {
		return pagesCount;
	}

	public Collection getPage(int page) throws SQLException {
		Collection result = new ArrayList();

		Connection conn = DBConnection.getConnection();
		Statement stmt = conn.createStatement();

		// 根據頁號計算起始行
		int rows = this.getRowsPerPage();

		// 將SQL語句轉換為特定數據庫的定位行集SQL語句
		String pageSql = SqlPageSQL.getPageSQL(this.sql, page, rows);
		ResultSet rs = stmt.executeQuery(pageSql);

		// 將結果集包裝為對象集合
		result = packResultSet(rs);

		rs.close();
		stmt.close();
		conn.close();

		return result;
	}

	private int countRows() throws SQLException {
		String countSql = this.sql;
		countSql = countSql.toLowerCase();
		int fromPos = countSql.indexOf(" from ");
		countSql = countSql.substring(fromPos);
		countSql = "select count(*) " + countSql;

		Connection conn = DBConnection.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(countSql);

		rs.next();
		int count = rs.getInt(1);

		rs.close();
		stmt.close();
		conn.close();

		return count;
	}

	// 計算總頁數
	private int countPages() {
		if ((rowsCount % rowsPerPage) == 0) {
			return rowsCount / rowsPerPage;
		} else {
			return (rowsCount / rowsPerPage + 1);
		}
	}

    // 在子類中將結果集包裝為對象集合
	protected abstract Collection packResultSet(ResultSet rs)
		throws SQLException;

}