databaseutil.java

一个带界面的在线测试系统

/**
 * DataAccess.java
 * create by ZZ, 2007.12.16
 */
package olts.database;
import java.sql.*;

/**
 * 该类用于封装JDBC对数据库的访问
 * @author ZZ
 * @version 1.0
 */
public class DatabaseUtil {
	//private DatabaseAccess da = new DatabaseAccess();
	protected Connection connection;
	protected Statement statement;
	protected PreparedStatement pstatement;
	
	protected String driver;
	protected String url;
	protected String user;
	protected String password;
	
	/**
	 * DatabaseUtil的构造函数
	 * @param DbDriver 该数据库连接应用的驱动程序
	 * @param DbUrl 该数据库连接应用的url
	 * @param u 要连接的数据库的帐户名
	 * @param pw 要连接的数据库的密码
	 */
	public DatabaseUtil(String DbDriver, String DbUrl,
			String u, String pw)throws Exception{
		
		this.driver = DbDriver;
		this.url = DbUrl;
		this.user = u;
		this.password = pw;
		
		this.init();
	}
	
	private void init()throws Exception{
		
		Class.forName(this.driver);
		
		this.connection = DriverManager.getConnection(this.url, this.user, this.password);
		
		this.statement = this.connection.createStatement();
		
	}
	
	/**
	 * 执行一条SQL查询语句，并返回结果
	 * @param sql 查询的SQL语句
	 * @return 查询结果
	 */
	public ResultSet query(String sql)throws SQLException{
            /*
            if (this.statement != null){
                this.statement.close();
            }
            this.statement = this.connection.createStatement();*/
            this.connection = DriverManager.getConnection(this.url, this.user, this.password);
            this.statement = this.connection.createStatement();
	    return this.statement.executeQuery(sql);
             
            //return this.da.query(sql);
	}
	
	/**
	 * 执行一条执行update操作的语句，并返回结果
	 * @param sql update的语句
	 */
	public void update(String sql)throws SQLException{
            /*
            if (this.statement != null){
                this.statement.close();
            }
            this.statement = this.connection.createStatement();*/
            this.connection = DriverManager.getConnection(this.url, this.user, this.password);
            this.statement = this.connection.createStatement();
            this.statement.executeUpdate(sql);
             
             //da.update(sql);
	}
	
	/**
	 * 该方法用于在数据库中执行删除的元组的语句
	 */
	public void delete(String sql)throws SQLException{
            /*
            if (this.statement != null){
                this.statement.close();
            }
            this.statement = this.connection.createStatement();**/
            this.connection = DriverManager.getConnection(this.url, this.user, this.password);
            this.statement = this.connection.createStatement();
            this.statement.executeUpdate(sql);
             
            //da.update(sql);
	}
	
	/**
	 * 结束该数据库连接，并释放相关资源
	 */
	public void close()throws SQLException{
		
		if (this.statement != null){
			this.statement.close();
		}
		if (this.pstatement != null){
			this.pstatement.close();
		}
		if (this.connection != null){
			this.connection.close();
		}
		
	}
	/**
	 * 生成预编译SQL语句
	 * @throws SQLException
	 */
	public void prepare(String sql)throws SQLException{
            //int i = 0;
            this.connection = DriverManager.getConnection(this.url, this.user, this.password);
	    this.pstatement = this.connection.prepareStatement(sql);
                //this.pstatement.clearParameters();
	}
	
	/**
	 *  设置预编译语句的参数
	 * @param index 参数的索引，或者说字段的位置
	 * @param value 参数的值
	 * @throws SQLException
	 */
	public void setPreparePara(int index, int value)throws SQLException{
		this.pstatement.setInt(index, value);
	}
	
	/**
	 *  设置预编译语句的参数
	 * @param index 参数的索引，或者说字段的位置
	 * @param value 参数的值
	 * @throws SQLException
	 */
	public void setPreparePara(int index, String value)throws SQLException{
		this.pstatement.setString(index, value);
	}
	

	/**
	 * 执行预编译命令。在执行这个方法之前，
	 * 请先运行prepare方法以生成一条预编译命令，
	 * 并设置预编译语句的参数
	 * @return 返回查询的结果
	 * @throws SQLException
	 */
	public ResultSet preparedQuery()throws SQLException{
		return this.pstatement.executeQuery();
	}
	
	/**
	 * 执行预编译命令。在执行这个方法之前，
	 * 请先运行prepare方法以生成一条预编译命令
	 * 并设置预编译语句的参数
	 * @return 返回影响的行数
	 * @throws SQLException
	 */
	public int preparedUpdate()throws SQLException{
                //int i = 4;
		return this.pstatement.executeUpdate();
	}


	public static void main(String[] args) {
		// 测试该类
		
		String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
		String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};";
		url += "DBQ=./db.mdb";
		DatabaseUtil du = null;
		
		try{
			
			//测试连接
			du = new DatabaseUtil(driver,url,"","");
			System.out.println("连接成功！");
			du.query("select * from Item");
                        du.query("select * from Item");
                        
                        ResultSet rs = du.query("select * from Item");
			while (rs.next()){
				int oid = rs.getInt("oid");
				int id = rs.getInt("id");
				int d = rs.getInt("difficulty");
				String c = rs.getString("content");
				String a = rs.getString("answer");
				int score = rs.getInt("score");
				int timelimit = rs.getInt("timelimit");
				String type = rs.getString("type");
				
				StringBuffer sb = new StringBuffer();
				sb.append(oid);sb.append(" ");
				sb.append(id);sb.append(" ");
				sb.append(d);sb.append(" ");
				sb.append(c);sb.append(" ");
				sb.append(a);sb.append(" ");
				sb.append(score);sb.append(" ");
				sb.append(timelimit);sb.append(" ");
				sb.append(type);sb.append(" ");
				System.out.println(sb);sb.append(" ");
			}
			
			rs.close();
			//测试插入命令
                        
			du.prepare("insert into Item values(?,?,?,?,?,?,?,?)");
			du.setPreparePara(1, 469);
			du.setPreparePara(2, 2499);
			du.setPreparePara(3, 1);
			du.setPreparePara(4, "How are you？");
			du.setPreparePara(5, "Fine,thank you!");
			du.setPreparePara(6, 6);
			du.setPreparePara(7, 10);
			du.setPreparePara(8, "BlankFillItem");
			du.preparedUpdate();
			
			//测试删除
			du.prepare("delete from Item where oid = ?");
			du.setPreparePara(1, 3);
			du.preparedUpdate();
			
			//测试查询
			rs = du.query("select * from Item");
			while (rs.next()){
				int oid = rs.getInt("oid");
				int id = rs.getInt("id");
				int d = rs.getInt("difficulty");
				String c = rs.getString("content");
				String a = rs.getString("answer");
				int score = rs.getInt("score");
				int timelimit = rs.getInt("timelimit");
				String type = rs.getString("type");
				
				StringBuffer sb = new StringBuffer();
				sb.append(oid);sb.append(" ");
				sb.append(id);sb.append(" ");
				sb.append(d);sb.append(" ");
				sb.append(c);sb.append(" ");
				sb.append(a);sb.append(" ");
				sb.append(score);sb.append(" ");
				sb.append(timelimit);sb.append(" ");
				sb.append(type);sb.append(" ");
				System.out.println(sb);sb.append(" ");
			}
			
			rs.close();
                        
                        
			
		}catch(Exception e){
			
			System.out.println(e);
			
		}finally {
			if (du != null ){
				try{
					du.close();
				}catch(Exception e){
					System.out.println(e);
				}
			}
		}

	}

}