table.java

我自己实现了一个数据访问层

package dark.db.table;

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

import dark.db.record.Query;
import dark.db.record.Record;
import dark.db.record.Update;

/**
 * <p>Title:            数据表基本操作抽象类</p>
 * <p>Description:      </p>
 * <p>Copyright:        Copyright (c) 2003</p>
 * <p>Company:          DIS</p>
 * <p>Create Time:      2004-3-21 20:38:05</p>
 * @author             <a href="mailto:[e-mail]">darkhe</a>
 * @version            1.0
 */
public class Table
{
	protected Connection conn;
	protected String table_name;

	/**
	 * 通过指定的表名和数据库连接得到一个数据表的当前操作实例
	 * @param table_name
	 * @param conn
	 */
	public Table(String table_name, Connection conn)
	{
		this.table_name = table_name;
		this.conn = conn;
	}

	/**
	 * 向数据表中添加一条记录
	 * @param record
	 * @throws SQLException
	 */
	public void insert(Record record) throws SQLException
	{
		Update u = new Update(table_name, conn);
		u.setRecord(record);
		u.doInsert();
		u = null;
	}

	/**
	 * 根据指定的查询条件删除记录信息
	 * @param condition
	 * @throws SQLException
	 */
	public void delete(String condition) throws SQLException
	{
		Update u = new Update(table_name, conn);
		u.setCondition(condition);
		u.doDelete();
		u = null;
	}

	/**
	 * 删除数据表中的所有记录信息
	 * @throws SQLException
	 */
	public void deleteAll() throws SQLException
	{
		Update u = new Update(table_name, conn);
		u.doDelete();
		u = null;
	}

	/**
	 * 根据指定的查询条件返回查询结果集
	 * @param condition
	 * @return
	 * @throws SQLException
	 */
	public ResultSet select(String condition) throws SQLException
	{
		Query q = new Query(table_name, conn);
		q.setCondition(condition);
		return q.executeQuery();
	}

	/**
	 * 返回当前表中所有数据的结果集
	 * @return
	 * @throws SQLException
	 */
	public ResultSet selectAll() throws SQLException
	{
		Query q = new Query(table_name, conn);
		return q.executeQuery();
	}

	/**
	 * 根据指定的查询条件排序条件以升序方式返回查询结果集
	 * @param condition
	 * @param orderBy
	 * @return
	 * @throws SQLException
	 */
	public ResultSet selectOrerByASC(String condition, String orderBy)
		throws SQLException
	{
		Query q = new Query(table_name, conn);
		q.setCondition(condition);
		q.setOrderByASC(orderBy);
		return q.executeQuery();
	}

	/**
	 * 根据指定的查询条件排序条件以降序方式返回查询结果集
	 * @param condition
	 * @param orderBy
	 * @return
	 * @throws SQLException
	 */
	public ResultSet selectOrerByDESC(String condition, String orderBy)
		throws SQLException
	{
		Query q = new Query(table_name, conn);
		q.setCondition(condition);
		q.setOrderByDESC(orderBy);
		return q.executeQuery();
	}

	/**
	 * 返回当前表中所有数据的结果集，排序方式为升序
	 * @param orderBy
	 * @return
	 * @throws SQLException
	 */
	public ResultSet selectAllOrderByASC(String orderBy) throws SQLException
	{
		Query q = new Query(table_name, conn);
		q.setOrderByASC(orderBy);
		return q.executeQuery();
	}

	/**
	 * 返回当前表中所有数据的结果集，排序方式为降序
	 * @param orderBy
	 * @return
	 * @throws SQLException
	 */
	public ResultSet selectAllOrderByDESC(String orderBy) throws SQLException
	{
		Query q = new Query(table_name, conn);
		q.setOrderByDESC(orderBy);
		return q.executeQuery();
	}

	/**
	 * 根据指定的条件更新记录信息
	 * @param record
	 * @param condition
	 * @throws SQLException
	 */
	public void update(Record record, String condition) throws SQLException
	{
		Update u = new Update(table_name, conn);
		u.setRecord(record);
		u.setCondition(condition);
		u.doUpdate();
		u = null;
	}

	/**
	 * 更新所有的记录信息
	 * @param record
	 * @param condition
	 * @throws SQLException
	 */
	public void updateAll(Record record) throws SQLException
	{
		Update u = new Update(table_name, conn);
		u.setRecord(record);
		u.doUpdate();
		u = null;
	}

	/**
	 * 执行分页查询，并返回结果集
	 * 算法：
	 * SELECT * FROM
	 * (
	 *   SELECT TOP($PageSize) * FROM
	 *    (
	 *       SELECT TOP ($PageSize * $PageIndex) *
	 *       FROM $TableName
	 *       ORDER BY$PubTime DESC
	 *     )
	 *    ORDER BY $PubTime DESC
	 *   )
	 *   ORDER BY $PubTime DESC
	 * @param pageSize 每页记录数
	 * @param pageIndex 页数
	 * @param where 查询条件
	 * @param orderBy 排序条件*  
	 * @return
	 */
	public ResultSet paginationSelect(
		int pageSize,
		int pageIndex,
		String where,
		String orderBy)
		throws SQLException
	{
		String sql = getPaginationSQL(pageSize, pageIndex, where, orderBy);

		//String sql= "SELECT * FROM ( SELECT TOP(PageSize) * FROM ( SELECT TOP (PageSize * PageIndex) * FROM Articles ORDER BY PubTime DESC )  ORDER BY PubTime ASC) ORDER BY PubTime DESC ";

		Query q = new Query(table_name, conn);
		return q.executeQuery(sql);
	}

	/**
	 * 构造分页查询的SQL语句串
	 * @param pageSize 每页记录数
	 * @param pageIndex 页数
	 * @param where 查询条件
	 * @param orderBy 排序条件
	 * @return
	 */
	public String getPaginationSQL(
		int pageSize,
		int pageIndex,
		String where,
		String orderBy)
	{
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT * FROM ( SELECT TOP(");
		sb.append(pageSize * pageIndex);
		sb.append(") * FROM ");
		sb.append(table_name);
		sb.append(" ");

		if (where != null)
		{
			sb.append("WHERE ");
			sb.append(where);
		}

		sb.append(" ");

		if (orderBy != null)
		{
			sb.append("ORDER BY ");
			sb.append(orderBy);
		}

		sb.append(") ");

		if (orderBy != null)
		{
			sb.append("ORDER BY ");
			sb.append(orderBy);
		}

		sb.append(") ");

		if (orderBy != null)
		{
			sb.append("ORDER BY ");
			sb.append(orderBy);
		}
		return sb.toString();
	}

}