database.java

来自「这是一个简单的java网站工程」· Java 代码 · 共 229 行

package com.weihaixia.stumanage.db;

import java.sql.*;
import java.util.ArrayList;
import com.sun.corba.se.spi.orbutil.fsm.State;

/**
 * 数据访问类
 * @author Administrator
 *
 */
public class DataBase {
	//数据库连接
	private Connection con;
	//数据库连接URL
	private String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=StudentManager";
	//用户ID 
	private String uid = "sa";
	//密码
	private String pwd = "";
	//驱动
	private String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
	
	/** 
	 * 无参数构造函数
	 */
	public DataBase() {
		try {
			open();
		} catch(Exception ex) {
			System.out.println("连接数据库失败！");
		}
	}
	
	/** 
	 * 连接数据库  
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @throws 
	 */
	public void open() throws ClassNotFoundException, SQLException{
		if(getCon() == null) {
			Class.forName(driver);
			con = DriverManager.getConnection(url, uid, pwd);
		}
	}
	
	/** 
	 * 添加数据 
	 * @param sql插入语句
	 * @throws SQLException 
	 */
	public void add_DB(String sql) throws SQLException {
		Statement st = null;
		if(getCon() != null) {
			try {
				st = con.createStatement();
				st.execute(sql);
				
			} catch (SQLException e) {
				throw new SQLException ("插入数据失败",e.toString());
			} finally {
				st.close();
			}
			
		} else {
			throw new SQLException("未连接数据库");
		}
	}
	
	/** 
	 * 删除数据
	 * @param sql删除语句
	 * @throws SQLException 
	 */
	public void del_DB(String sql) throws SQLException {
		Statement st = null;
		if(getCon() != null) {
			try {
				st = con.createStatement();
				st.execute(sql);
			} catch (SQLException e) {
				throw new SQLException("删除数据失败",e.toString());
			} finally {
				st.close();
			}
		} else {
			throw new SQLException("未连接数据库");
		}
	}
	
	/** 
	 * 更新数据  
	 * @param sql更新语句
	 * @throws SQLException 
	 */
	public void update_DB(String sql) throws SQLException {
		Statement st = null;
		if(getCon() != null) {
			try {
				st = con.createStatement();
				st.executeUpdate(sql);
			} catch(SQLException e) {
				throw new SQLException("更新数据库失败",e.toString());
			} finally {
				st.close();
			}
		} else {
			throw new SQLException("未连接数据库");
		}
	}
	
	/**
	 * 获得结果集 
	 * @param sql查询语句
	 * @throws SQLException 
	 */
	public ResultSet select_DB(String sql) throws SQLException {
		Statement st = null;
		ResultSet rs = null;
		if(getCon() != null) {
			try {
				st = con.createStatement();
				rs = st.executeQuery(sql);
				
			} catch(SQLException e) {
				throw new SQLException("查询数据库失败",e.toString());
			} finally {
				st.close();
			}	
		
		} else {
			throw new SQLException("未连接数据库");
		}
		return rs;
	}
	/** 
	 * 生成ID
	 * @param s
	 * @param sql 
	 * @throws SQLException 
	 */
	public String getNewID(String s,String sql) throws SQLException {
		String id = "";
		Statement st = null;
		ResultSet rs = null;
		if(getCon() != null) {
			try {
				st = con.createStatement();
				rs = st.executeQuery(sql);
				if(rs.next()) {
					String TID = rs.getString(1);
					int n = Integer.parseInt(TID.substring(1)) + 1;
					id = s + n;
				}
				return id;
			} catch(SQLException e) {
				throw new SQLException("查询数据库失败",e.toString());
			} finally {
				rs.close();
				st.close();
			}
		} else {
			throw new SQLException("未连接数据库");
		}
	}
	/** 
	 * 获得结果集合
	 * @param sql
	 * @throws SQLException 
	 */
	public ArrayList getList(String sql) throws SQLException {
		Statement st = null;
		ResultSet rs = null;
		ArrayList list = new ArrayList();
		ArrayList obj_ArrayList;
		if(getCon() != null) {
			try {
				st = con.createStatement();
				rs = st.executeQuery(sql);
				ArrayList head = new ArrayList();
				for(int i=1;i<rs.getMetaData().getColumnCount();i++) {
					head.add(rs.getMetaData().getColumnName(i));
				}
				list.add(head);
				while(rs.next()) {
					obj_ArrayList = new ArrayList();
					for(int i=1;i<rs.getMetaData().getColumnCount();i++ ) {
						obj_ArrayList.add(rs.getString(i));
					}
					list.add(obj_ArrayList);
				}
				return list;
			} catch(SQLException e) {
				throw new SQLException("查询数据库失败",e.toString());
			} finally {
				rs.close();
				st.close();
					
			}
		} else {
			throw new SQLException("没有找到相关资料");
		}
		
	}
	
	/** 
	 * 关闭连接
	 */
	public void close() {
		try {
			getCon().close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/** 
	 * 获得连接 
	 */
	public Connection getCon() {
		return con;
	}




}