pagedatabasemanager.java

java 编写的sqlserver快速分页通用类

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.naming.NamingException;

/**
 * <ol>
 * 分页数据库查询类，查询时把数据库所有记录 由1／2处分开，加快查询的速度
 * 
 * @author 袁 军 <br>
 *         Time: 2006-10-30 17:00:26 <br>
 *         Company: 湖北工业大学计算机学院 <br>
 *         Email: [e-mail]
 * @version 1.0
 *          </ol>
 */
public class PageDataBaseManager extends DataBaseManager {
	private final String SQL_TEMP_TABLE1 = "PAGER_TEMP1";

	private final String SQL_TEMP_TABLE2 = "PAGER_TEMP2";

	private final String SQL_TEMP_TABLE3 = "PAGER_TEMP3";

	//construct
	public PageDataBaseManager() {
	}

	public PageDataBaseManager(Connection conn) {
		super(conn);
	}

	//getConnect
	public Connection getConnect() {
		return super.getConnect();
	}

	/**
	 * pager by pager version the conditions is the final conditions
	 * 
	 * @param sql
	 * @param conditionsArray
	 * @param pager
	 */
	public void prepare(String sql, String[] conditionsArray, Pager pager)
			throws SQLException {
		String sqlt = "";
		//set total rows
		pager.setTotalRows(getCount(sql));
		pager.refresh(pager.getCurrentPage());
		//get the location of database of page's end record
		long pageendrecord = pager.getCurrentPage() * pager.getPageSize();
		//depend on the value get the deferent sql
		if (pageendrecord > 1 && pageendrecord < pager.getTotalRows() / 2) {
			sqlt = getSQL_Small(sql, conditionsArray, pager);
		}
		if (pageendrecord >= pager.getTotalRows() / 2
				&& pager.getCurrentPage() != 0) {
			sqlt = getSQL_Large(sql, conditionsArray, pager);
		}
		if (pager.getCurrentPage() == 1) {
			sqlt = getSQL_First(sql, conditionsArray, pager);
		}

		prepare(sqlt);
	}

	/**
	 * pager by pager not primarykey version this version may be make the query
	 * more slowly
	 * 
	 * @param sql
	 * @param pager
	 */
	public void prepare(String sql) throws SQLException {
		super.prepare(sql);
	}

	/**
	 * get large sql
	 * 
	 * @param sql
	 * @param pager
	 * @return return page by page sql
	 */
	private String getSQL_Large(String sql, String[] conditionsArray,
			Pager pager) {
		StringBuffer sqlb = new StringBuffer();
		//the primarykeylist
		String pkeylist = combinekey(conditionsArray);
		String pkeylistanddesc = combinekeyanddesc(conditionsArray);
		//process sql
		sqlb.append("SELECT * FROM (SELECT TOP ");
		sqlb.append(pager.getPageSize());
		sqlb.append(" *  FROM (");
		sqlb.append("SELECT TOP ");
		sqlb.append(pager.getTotalRows() - pager.getCurrentPage()
				* pager.getPageSize() + pager.getPageSize());
		sqlb.append(" * FROM (");
		sqlb.append(sql);
		sqlb.append(" ) ");
		sqlb.append(SQL_TEMP_TABLE1);
		sqlb.append(" ORDER BY ");
		sqlb.append(pkeylistanddesc);
		sqlb.append(" ) ");
		sqlb.append(SQL_TEMP_TABLE2);
		sqlb.append(" ORDER BY ");
		sqlb.append(pkeylist);
		sqlb.append(") " + SQL_TEMP_TABLE2);
		sqlb.append(" ORDER BY " + pkeylist);
		return sqlb.toString();
	}

	/**
	 * get small sql
	 * 
	 * @param sql
	 * @param pager
	 * @return return page by page sql
	 */
	private String getSQL_Small(String sql, String[] conditionsArray,
			Pager pager) {
		StringBuffer sqlb = new StringBuffer();
		//the primarykeylist
		String pkeylist = combinekey(conditionsArray);
		String pkeylistanddesc = combinekeyanddesc(conditionsArray);
		//process sql
		sqlb.append("SELECT * FROM (SELECT TOP ");
		sqlb.append(pager.getPageSize());
		sqlb.append(" *  FROM (");
		sqlb.append("SELECT TOP ");
		sqlb.append(pager.getCurrentPage() * pager.getPageSize());
		sqlb.append(" * FROM (");
		sqlb.append(sql);
		sqlb.append(" ) ");
		sqlb.append(SQL_TEMP_TABLE1);
		sqlb.append(" ORDER BY ");
		sqlb.append(pkeylist);
		sqlb.append(") ");
		sqlb.append(SQL_TEMP_TABLE2);
		sqlb.append(" ORDER BY ");
		sqlb.append(pkeylistanddesc);
		sqlb.append(" ) ");
		sqlb.append(SQL_TEMP_TABLE3);
		sqlb.append(" ORDER BY ");
		sqlb.append(pkeylist);
		return sqlb.toString();
	}

	/**
	 * get first sql
	 * 
	 * @param sql
	 * @param pager
	 * @return return page by page sql
	 */
	private String getSQL_First(String sql, String[] conditionsArray,
			Pager pager) {
		StringBuffer sqlb = new StringBuffer();
		sqlb.append("SELECT TOP ");
		sqlb.append(pager.getPageSize());
		sqlb.append(" * FROM (");
		sqlb.append(sql);
		sqlb.append(") ");
		sqlb.append(SQL_TEMP_TABLE1);
		sqlb.append(" ORDER BY ");
		sqlb.append(combinekey(conditionsArray));
		return sqlb.toString();
	}

	/**
	 * @param sql
	 * @throws SQLException
	 */
	private long getCount(String sql) throws SQLException {
		//refresh pager count field
		long total = 0;
		StringBuffer sqlb = new StringBuffer();
		sqlb.append("SELECT COUNT(*) TOTAL FROM (");
		sqlb.append(sql + " ) ");
		sqlb.append(this.SQL_TEMP_TABLE1);
		prepare(sqlb.toString());
		ResultSet rs = null;
		try {
			rs = executeQuery();
			if (rs.next()) {
				total = ((Integer) rs.getObject("TOTAL")).longValue();
			}
		} catch (DataNotFoundException e) {
			//donothing
		} catch (SQLException e) {
			throw e;
		}
		return total;
	}

	//pager by pager version
	public ResultSet executeQuery() throws DataNotFoundException, SQLException {
		return super.executeQuery();
	}

	public ResultSet excuteQuery(List list) throws DataNotFoundException,
			SQLException {
		return super.executeQuery(list);
	}

	public void disconnect() {
		super.disconnect();
	}

	/**
	 * combinekey with the format
	 * 
	 * @param pkey
	 * @return
	 */
	private String combinekey(String[] pkey) {
		String primaykeyformat = "";
		for (int i = 0; i < pkey.length; i++) {
			primaykeyformat += pkey[i];
			if (i < pkey.length - 1)
				primaykeyformat += ",";
		}
		return primaykeyformat;
	}

	/**
	 * combinekey with the format reverse
	 * 
	 * @param pkey
	 * @return
	 */
	private String combinekeyanddesc(String[] pkey) {
		String primaykeyformat = "";
		String keytemp = "";
		for (int i = 0; i < pkey.length; i++) {

			//contain
			if (pkey[i].indexOf("desc") != -1) {
				keytemp = new String(pkey[i].replaceAll("desc", "ASC"));
			} else if (pkey[i].indexOf("DESC") != -1) {
				keytemp = new String(pkey[i].replaceAll("DESC", "ASC"));
			} else if (pkey[i].indexOf("ASC") != -1) {
				keytemp = new String(pkey[i].replaceAll("ASC", "DESC"));
			} else if (pkey[i].indexOf("asc") != -1) {
				keytemp = new String(pkey[i].replaceAll("asc", "DESC"));
			} else {
				keytemp = new String(pkey[i] + " DESC");
			}
			primaykeyformat += keytemp;
			if (i < pkey.length - 1)
				primaykeyformat += ",";
		}
		return primaykeyformat;
	}

	//测试用
	public static void main(String[] args) throws SQLException {
		PageDataBaseManager p = new PageDataBaseManager();
		Pager pager = new Pager();
		pager.setCurrentPage(2);
		try {
			p.connect("");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ResultSet rs = null;
		p.prepare("SELECT * FROM CLIENT ", new String[] { "USERID", "username",
				"userpass" }, pager);
		try {
			rs = p.executeQuery();
			while (rs.next()) {
				//System.out.println(StringUtil.zNull("userid="+rs.getString("USERID")).trim()+"
				// "+StringUtil.zNull("username="+rs.getString("USERNAME")).trim()+"
				// "+"userpass="+StringUtil.zNull(rs.getString("USERPASS")).trim());
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (DataNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}