dbaccess.java

一个图书管理系统

package com.database;

import java.sql.*;
import com.exception.DataBaseException;
/**
 * @author Administrator
 *
 *单例模式(数据库不能随便关闭，只有程序运行结束后才关闭)——构造
 *函数用private修饰,每次只能创建一个实例
 */
public class DBAccess {
	/*
	 // 此处用于jdbc-odbc桥
	  final public static String strDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
	  
	  final public static String strURL = "jdbc:odbc:test";
	 
	  final public static String strUser = "sa";
	  
	  final public static String strPwd = "sa";
	*/
	//驱动器字符串
	 public static final String strDriver="com.microsoft." +
	 		"jdbc.sqlserver.SQLServerDriver";
	 //连接地址字符串
	 public static final String strURL="jdbc:microsoft:" +
	 		"sqlserver://localhost:1433;databasename=BookDB";
	//用户名和密码
	 public static final String strUserName="sa";
	 public static final String strPwd="sa";
	
	private static DBAccess db=null;
	private  static Connection con=null;
	private Statement stmt=null;
	private ResultSet ret=null;
	
	private DBAccess() throws DataBaseException{
		try {
			Class.forName(DBAccess.strDriver);
			con=DriverManager.getConnection(DBAccess.strURL,DBAccess.strUserName,DBAccess.strPwd);
			stmt=con.createStatement();
		} catch (ClassNotFoundException e) {
			System.out.println("数据库驱动没加载");
		}catch (SQLException e) {
			throw new DataBaseException(DataBaseException.CONN_ON_EEROR);
		}
	}
	
	public static DBAccess getDBAccess() throws DataBaseException{
		if (db==null)
			db=new DBAccess();
		return db;
	}
	
	public Connection getConnection(){
		return con;
	}
	
	public ResultSet query(String sql) throws DataBaseException{
			try {
				ret=stmt.executeQuery(sql);
			} catch (SQLException e) {
				if (ret==null){
					throw new DataBaseException(DataBaseException.FIND_ON_EEROR);
				}
			}
			 return ret;
		 }
		 
	public int update(String sql) throws DataBaseException{
		int flag=0;
		int temp=0;
		try {
			 if (sql.trim().startsWith("insert")) temp=1;
			 if (sql.trim().startsWith("delete")) temp=2;
			 if (sql.trim().startsWith("update")) temp=3;
			 flag=stmt.executeUpdate(sql);
			 //如不做判断，当做删除,按用户进行操作时,输入空值时或值不存在，不会产生异常
			//当做修改,按用户进行操作时,输入空值时或值不存在，不会产生异常
			 if (flag==0 && temp==2){
					throw new DataBaseException(DataBaseException.DEL_ON_EEROR);
				}else if (flag==0 && temp==3){
					throw new DataBaseException(DataBaseException.UPDATE_ON_EEROR);
				}
		//	 System.out.println(flag+""+temp);
		 } catch (SQLException e) {
			if (flag==0 && temp==1){
				throw new DataBaseException(DataBaseException.INSERT_ON_EEROR);
			}else if (flag==0 && temp==2){
				throw new DataBaseException(DataBaseException.DEL_ON_EEROR);
			}else if (flag==0 && temp==3){
				throw new DataBaseException(DataBaseException.UPDATE_ON_EEROR);
			}
		}
		 return flag;
	 }
	 
	 public void close(){
		 try {
			if (ret!=null){
				 ret.close(); 
			 }
			 if (ret!=null){
				 stmt.close(); 
			 }
			 if (ret!=null){
				 con.close(); 
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
	 }

}