dbcontrol.java

这是我平时做练习的翻页源码

package com.dj.db;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DBControl {
	// 每页的记录数
	public static final int PAGEROWCOUNT = 2;

	// 数据库连接类
	private Connection conn;

	// 数据库操作对象
	private Statement stat;

	// 打印控制标志
	public static boolean printFlag = true;

	// 结果集
	ResultSet rs;

	// 数据库连接数
	int count = 0;

	// 获取数据库操作对象
	public void openConnnection() {
		DataSource dataSource;
		try {
			Context initCtx = new InitialContext();
			dataSource = (DataSource) initCtx
					.lookup("java:comp/env/jdbc/TestDB1");
			this.conn = dataSource.getConnection();
			if (this.conn != null) {
				this.stat = conn.createStatement();
				count++;
			}

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

	/**
	 * 关闭连接、句柄及结果集
	 */
	public void close() {

		try {
			if (this.rs != null) {
				this.rs.close();
			}
			if (this.stat != null) {
				this.stat.close();
			}
			if (this.conn != null) {
				this.conn.close();
				count--;
				this.print("关闭连接 ...................");
				this.print("剩余连接数 ..................." + count);
			}
		} catch (Exception e) {
			this.print("关闭句柄出错!");
		}

	}

	/**
	 * 打印方法
	 * 
	 * @param s
	 */
	public void print(String s) {
		if (printFlag)
			System.out.println(s);
	}

	/**
	 * 分页查询方法(未做sql检查)
	 * 
	 * @param sql
	 * @param pageNumber
	 *            当前页
	 * @param tableKey
	 *            自增长key
	 * @return
	 */
	public ResultSetCache queryPage(String sql, int pageNumber, String tableKey) {
		this.openConnnection();
		ResultSetCache res = null;
		StringBuffer sqls = new StringBuffer();
		sqls.append(sql.substring(0, 6) + " top " + PAGEROWCOUNT + " ");
		if (sql.indexOf("where") > 0) {
			sqls.append(sql.substring(6) + " and " + tableKey + ">=(");
		} else {
			sqls.append(sql.substring(6) + " where " + tableKey + ">=(");
		}
		sqls.append("select top 1 " + tableKey + " from (");
		sqls.append(" select top " + ((pageNumber - 1) * PAGEROWCOUNT + 1)
				+ " " + tableKey + " ");
		if (sql.indexOf("where") > 0) {
			// sqls.append(sql.substring(sql.indexOf("from"))+" and order by
			// "+tableKey+") a ");
			sqls.append(sql.substring(sql.indexOf("from")) + " order by "
					+ tableKey + ") a ");
		} else {
			sqls.append(sql.substring(sql.indexOf("from")) + " order by "
					+ tableKey + ") a ");
		}
		sqls.append("order by " + tableKey + " desc)");
		try {
			res = new ResultSetCache(this.stat.executeQuery(sqls.toString()));
			this.print("pageNumber=============" + sqls.toString());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.close();
		}
		return res;
	}

	/**
	 * 返回结果集的记录数
	 * 
	 * @param sql
	 * @return
	 */
	public int query(String sql) {
		this.openConnnection();
		int rowCount = 0;// 行数
		if (sql != null) {
			StringBuffer newsql = new StringBuffer();
			newsql.append("select count(*) as count from ");
			newsql.append(sql.substring(sql.indexOf("from") + 4));
			try {
				ResultSet res = this.stat.executeQuery(newsql.toString());
				if (res.next()) {
					rowCount = res.getInt("count");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rowCount;
	}
}