dbmanager.java

大型数据库课程设计源码

/**
 * 
 */
package com.vbank.db;

import java.sql.*;
import java.util.ListIterator;
import java.util.Vector;

import com.vbank.beans.Employee;
import com.vbank.beans.Priv;

/**
 * @author 李志伟
 *
 */
public class DBManager {
	private static final String dbName="lzw";
	private static final String dbDriver="oracle.jdbc.driver.OracleDriver";
	private static final String dbUrl="jdbc:oracle:thin:@localhost:1521:"+dbName;
	private static final String user="system";
	private static final String password="system";
	private static Connection con=null;
	private static Statement stmt;
	private static PreparedStatement ps;
	private static ResultSet rs;
	private static ResultSetMetaData rsmd;
	/**
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		if(con==null)
		try {
			Class.forName(dbDriver);
			con=DriverManager.getConnection(dbUrl,user,password);
			stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
		} catch (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			System.out.println( "can not find the class");
			 
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			System.out.println( "can not connect to database");
			e.printStackTrace();
		}
		return con;
		
	}
	/**
	 * 
	 * @param selectString
	 * @return
	 */
	public static ResultSet executeSelect(String selectString) {
		try {
			rs=stmt.executeQuery(selectString);
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			System.out.println("select error");
		} 
		return rs;
		
	}
	public static int executeQuery(String selectString) {
		try {
			rs=stmt.executeQuery(selectString);
			if(rs.next())
				return 0;
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			//e.printStackTrace();
			System.out.println("select error");
		} 
		return 1;
		
	}
	public static int execInsert(String insertString) {
		int n = 0;
		try {System.out.println("insert");
			  n=stmt.executeUpdate(insertString);

		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO 自动生成 catch 块
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return n;
		
	}
	public static int execUpdate(String update) {
		int n=0;
		try { 
			  n=stmt.executeUpdate(update);
			 
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO 自动生成 catch 块
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return n;
		
	}
	public static int[] execUpdate(Vector v) {
		int[] n=new int[v.size()];
		try {int i=0;
			for(;i<v.size();)
			  n[i]=stmt.executeUpdate((String)v.get(i));
			i++;
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO 自动生成 catch 块
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return n;
		
	}
	public static int execDel(String sql) {
		return execUpdate(sql);
	}
	public static void releaseCon() {
		if(stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
			stmt=null;
		}
		if(con!=null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				//e.printStackTrace();
				System.out.println("can not close connection.");
			}
			con=null;
		}
	}
	public static void execBatch(Vector v) {
		int i=0;
		
			try {
				for(;i<v.size();i++)
					stmt.addBatch((String)v.get(i));
				stmt.executeBatch();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		
	}
	public static Vector getMeta(String tableName) {
		String sql="select * from "+" "+tableName;
		Vector<String> v=new Vector<String>();int i=1;
		
		try {
			rs=stmt.executeQuery(sql);
			
			rsmd=rs.getMetaData();
			int n=rsmd.getColumnCount();
			while(i<=n) {System.out.println(rsmd.getColumnName(i)+" "+i);
				v.add(rsmd.getColumnName(i++).toString());
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return v;
	}
	public static void updatePriv(Vector<Priv> v) {
		Priv p=null;
		ListIterator it=v.listIterator();
		String sql="update priv set "
			+"openAccount=?,lostAccount=?,storeMoney=?,"
			+"cash=?,openAccountCard=?,openNetBank=?,"
			+"disableAccount=?,searchEmpInfo=?,addEmp=?,"
			+"modifyEmpInfo=?,emmPwdManage=?,iceCreditCard=?"
			+" where  operateNum in (select operateNum from operator o,employee e"
			+" where e.empName=? and e.empId=o.empID)";
		try {
			ps=con.prepareStatement(sql);
			while(it.hasNext()) {
				p=(Priv)it.next();
				ps.setString(13, p.getOperateNum());System.out.println( p.getOpenAccount().equals("true"));
				ps.setString(1, p.getOpenAccount().equals("true")?"1":"0");
				ps.setString(2, p.getLostAccount().equals("true")?"1":"0");
				ps.setString(3, p.getStoreMoney().equals("true")?"1":"0");
				ps.setString(4, p.getCash().equals("true")?"1":"0");
				ps.setString(5, p.getOpenAccountCard().equals("true")?"1":"0");
				ps.setString(6, p.getOpenNetBank().equals("true")?"1":"0");
				ps.setString(7, p.getDisableAccount().equals("true")?"1":"0");
				ps.setString(8, p.getSearchEmpInfo().equals("true")?"1":"0");
				ps.setString(9, p.getAddEmp().equals("true")?"1":"0");
				ps.setString(10, p.getModifyEmpInfo().equals("true")?"1":"0");
				ps.setString(11, p.getEmmPwdManage().equals("true")?"1":"0");
				ps.setString(12, p.getIceCreditCard().equals("true")?"1":"0");
				System.out.println(sql);
				ps.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			//e.printStackTrace();
			System.out.println(e.getMessage());
			
		}
	}
	public static void saveNewEmp(Vector<Employee> v) {
		Employee e=null;
		ListIterator it=v.listIterator();
		try {
			ps=con.prepareStatement("insert into employee values(?,?,?,?,?,?,?,?,?)");
			while(it.hasNext()) {
				e=(Employee)it.next();
				ps.setString(1, e.getId());
				ps.setString(2, e.getName());
				ps.setString(3, e.getSex());
				ps.setInt(4, e.getAge());
				ps.setString(5, e.getIDCard());
				ps.setString(6, e.getEmployeedDate());
				ps.setString(7, e.getAddress());
				ps.setString(8, e.getPhone());
				ps.setFloat(9, e.getSalary());
				ps.executeUpdate();
			}
		} catch (SQLException e1) {
			// TODO 自动生成 catch 块
			e1.printStackTrace();
		}
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {int i;Vector v=new Vector();
		// TODO 自动生成方法存根
		DBManager.getConnection();
		ResultSet rs=DBManager.executeSelect("select * from priv_view");
		try {
			while(rs.next()) { v=new Vector();
			i=2; v.add(rs.getString(1));
			for(;i<=13;i++) {
				if(rs.getString(i).equals("1"))
					v.add(new Boolean(true));
				else
					v.add(new Boolean(false)); 
				System.out.print(rs.getString(i));
				
			}
			System.out.println();
			System.out.println(v);
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
	}

}