bankdao.java

程序为银行管理系统,分为四个子模块,分别为人事管理系统,计算机管理系统,机构管理系统和bbs论坛

package org.xjtu.bank.compdao;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.xjtu.bank.compdb.DBAccess;
import org.xjtu.bank.compvo.Bankvoe;
import org.xjtu.bank.compvo.bankVO;
import org.xjtu.bank.compvo.bankapply;

public class bankDAO {
	
	private Connection con;
	private DBAccess dbaccess;
	private PreparedStatement stmt;
	private ResultSet rs;
	
	private String strfindall = "select * from comp";
	private String strfindall1 = "select * from comp where UserUnit=?";
	private String strinsert ="insert into comp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	private String strselectNumberID="select * from comp where NumberID=?";
	private String strselectComputerType="select * from comp where ComputerType=?";
	private String strselectUserID="select * from comp where UserID=?";
	private String strselectUserUnit="select * from comp where UserUnit=?";
	private String strselectNumberID1="select * from comp where NumberID=? and UserUnit=?";
	private String strselectComputerType1="select * from comp where ComputerType=? and UserUnit=?";
	private String strselectUserID1="select * from comp where UserID=? and UserUnit=?";
	private String strselectUserUnit1="select * from comp where UserUnit=? and UserUnit=?";
	private String strselectID="select * from comp where ID=?";
	private String strupdate="update comp set NumberID=?,ComputerType=?,CPU=?,Memory=?,Harddisk=?,OS=?,Mend=?,AntiVirus=?,IPAddress=?,MACAddress=?,UserID=?,UserName=?,UserUnit=?,HouseID=?,DutyPeople=?,BuyTime=?,UseTime=?,WhichUse=? where ID=?";
	private String strdelete="delete from comp where ID=?";
	private String strlogin="select * from emp where WorkerID=? and password=?";
	private String strapplyfind="select * from comp where State='未批准'";
	private String strapplyselect="select * from apply where NumberID=?";
	private String strapplyinsert="insert into apply values (?,?,?,?,?)";
	private String strapplydelete="delete from apply where NumberID=?";
	private String strapplyfindall="select * from apply where State='未审批'";
	private String strapplyupdate="update apply set RejectReason=?,State=? where NumberID=?";
	private String strcompstateupdate="update comp set State=? where NumberID=?";
	
	public List findall()
	{
		dbaccess = new DBAccess();
		dbaccess.getConn();
		con = dbaccess.connDB();
		bankVO bvo;
		List list=new ArrayList();
		try {		
			stmt = con.prepareStatement(strfindall);
			rs = stmt.executeQuery();
			while(rs.next())
			{
				bvo = new bankVO();
		        bvo.setNumberID(rs.getString("NumberID"));
		        bvo.setComputerType(rs.getString("ComputerType"));
		        bvo.setCPU(rs.getString("CPU"));
		        bvo.setMemory(rs.getInt("Memory"));
		        bvo.setHarddisk(rs.getInt("Harddisk"));
		        bvo.setOS(rs.getString("OS"));
		        bvo.setMend(rs.getString("Mend"));
		        bvo.setAntiVirus(rs.getString("AntiVirus"));
		        bvo.setIPAddress(rs.getString("IPAddress"));
		        bvo.setMACAddress(rs.getString("MACAddress"));
		        bvo.setUserID(rs.getString("UserID"));
		        bvo.setUserName(rs.getString("UserName"));
		        bvo.setUserUnit(rs.getString("UserUnit"));
		        bvo.setHouseID(rs.getString("HouseID"));
		        bvo.setDutyPeople(rs.getString("DutyPeople"));
		        bvo.setBuyTime(rs.getString("BuyTime"));
		        bvo.setUseTime(rs.getString("UseTime"));
		        bvo.setWhichUse(rs.getString("WhichUse"));
		        bvo.setID(rs.getInt("ID"));
		        bvo.setState(rs.getString("State"));
		        list.add(bvo);
			}
	        return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public List findall1(String Unit)
	{
		dbaccess = new DBAccess();
		dbaccess.getConn();
		con = dbaccess.connDB();
		bankVO bvo;
		List list=new ArrayList();
		try {		
			stmt = con.prepareStatement(strfindall1);
			stmt.setString(1, Unit);
			rs = stmt.executeQuery();
			while(rs.next())
			{
				bvo = new bankVO();
		        bvo.setNumberID(rs.getString("NumberID"));
		        bvo.setComputerType(rs.getString("ComputerType"));
		        bvo.setCPU(rs.getString("CPU"));
		        bvo.setMemory(rs.getInt("Memory"));
		        bvo.setHarddisk(rs.getInt("Harddisk"));
		        bvo.setOS(rs.getString("OS"));
		        bvo.setMend(rs.getString("Mend"));
		        bvo.setAntiVirus(rs.getString("AntiVirus"));
		        bvo.setIPAddress(rs.getString("IPAddress"));
		        bvo.setMACAddress(rs.getString("MACAddress"));
		        bvo.setUserID(rs.getString("UserID"));
		        bvo.setUserName(rs.getString("UserName"));
		        bvo.setUserUnit(rs.getString("UserUnit"));
		        bvo.setHouseID(rs.getString("HouseID"));
		        bvo.setDutyPeople(rs.getString("DutyPeople"));
		        bvo.setBuyTime(rs.getString("BuyTime"));
		        bvo.setUseTime(rs.getString("UseTime"));
		        bvo.setWhichUse(rs.getString("WhichUse"));
		        bvo.setID(rs.getInt("ID"));
		        bvo.setState(rs.getString("State"));
		        list.add(bvo);
			}
	        return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public List login(Bankvoe vo)
	{
		dbaccess=new DBAccess();
		dbaccess.getConn();
		con=dbaccess.connDB();
		Bankvoe bvo=new Bankvoe();
		List list = new ArrayList();
		try {
			stmt=con.prepareStatement(strlogin);
			stmt.setString(1, vo.getWorkID());
			stmt.setString(2, vo.getPassword());
			rs=stmt.executeQuery();
			while(rs.next())
			{
			bvo.setDepartment(rs.getInt("Department"));
			bvo.setID(rs.getInt("ID"));
			bvo.setUnit(rs.getString("Unit"));
			bvo.setName(rs.getString("Name"));
			bvo.setWorkID(rs.getString("WorkerID"));
			bvo.setPassword(rs.getString("password"));
			list.add(bvo);
			}
            return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public void insert(bankVO vo)
	{
		dbaccess=new DBAccess();
		dbaccess.getConn();
		con=dbaccess.connDB();
		try {
			stmt=con.prepareStatement(strinsert);
			stmt.setString(1,vo.getNumberID());
			stmt.setString(2,vo.getComputerType());
            stmt.setString(3,vo.getCPU());
            stmt.setInt(4,vo.getMemory());
            stmt.setInt(5, vo.getHarddisk());
            stmt.setString(6,vo.getOS());
            stmt.setString(7,vo.getMend());
            stmt.setString(8,vo.getAntiVirus());
            stmt.setString(9,vo.getIPAddress());
            stmt.setString(10,vo.getMACAddress());
            stmt.setString(11,vo.getUserID());
            stmt.setString(12,vo.getUserName());
            stmt.setString(13,vo.getUserUnit());
            stmt.setString(14,vo.getHouseID());
            stmt.setString(15,vo.getDutyPeople());
            stmt.setString(16,vo.getBuyTime());
            stmt.setString(17,vo.getUseTime());
            stmt.setString(18,vo.getWhichUse());
            stmt.setString(19, "未批准");
            stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public List select1(bankVO vo,String str,String Unit)
	{
		dbaccess=new DBAccess();
		dbaccess.getConn();
		con=dbaccess.connDB();
		List list=new ArrayList();
		bankVO bvo;
		if(str.equalsIgnoreCase("NumberID"))
		{
		try {
			stmt=con.prepareStatement(strselectNumberID1);
			stmt.setString(1,vo.getNumberID());
			stmt.setString(2, Unit);
			rs=stmt.executeQuery();
			while(rs.next())
			{
				bvo=new bankVO();
		        bvo.setNumberID(rs.getString("NumberID"));
		        bvo.setComputerType(rs.getString("ComputerType"));
		        bvo.setCPU(rs.getString("CPU"));
		        bvo.setMemory(rs.getInt("Memory"));
		        bvo.setHarddisk(rs.getInt("Harddisk"));
		        bvo.setOS(rs.getString("OS"));
		        bvo.setMend(rs.getString("Mend"));
		        bvo.setAntiVirus(rs.getString("AntiVirus"));
		        bvo.setIPAddress(rs.getString("IPAddress"));
		        bvo.setMACAddress(rs.getString("MACAddress"));
		        bvo.setUserID(rs.getString("UserID"));
		        bvo.setUserName(rs.getString("UserName"));
		        bvo.setUserUnit(rs.getString("UserUnit"));
		        bvo.setHouseID(rs.getString("HouseID"));
		        bvo.setDutyPeople(rs.getString("DutyPeople"));
		        bvo.setBuyTime(rs.getString("BuyTime"));
		        bvo.setUseTime(rs.getString("UseTime"));
		        bvo.setWhichUse(rs.getString("WhichUse"));
		        bvo.setID(rs.getInt("ID"));
		        bvo.setState(rs.getString("State"));
		        list.add(bvo);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		}
		else if(str.equalsIgnoreCase("ComputerType"))
		{
			System.out.println("select ComputerType");
			try {
				stmt=con.prepareStatement(strselectComputerType1);
				stmt.setString(1, vo.getComputerType());
				stmt.setString(2, Unit);
				rs=stmt.executeQuery();
				while(rs.next())
				{
					bvo=new bankVO();
			        bvo.setNumberID(rs.getString("NumberID"));
			        bvo.setComputerType(rs.getString("ComputerType"));
			        bvo.setCPU(rs.getString("CPU"));
			        bvo.setMemory(rs.getInt("Memory"));
			        bvo.setHarddisk(rs.getInt("Harddisk"));
			        bvo.setOS(rs.getString("OS"));
			        bvo.setMend(rs.getString("Mend"));
			        bvo.setAntiVirus(rs.getString("AntiVirus"));
			        bvo.setIPAddress(rs.getString("IPAddress"));
			        bvo.setMACAddress(rs.getString("MACAddress"));
			        bvo.setUserID(rs.getString("UserID"));
			        bvo.setUserName(rs.getString("UserName"));
			        bvo.setUserUnit(rs.getString("UserUnit"));
			        bvo.setHouseID(rs.getString("HouseID"));
			        bvo.setDutyPeople(rs.getString("DutyPeople"));
			        bvo.setBuyTime(rs.getString("BuyTime"));
			        bvo.setUseTime(rs.getString("UseTime"));
			        bvo.setWhichUse(rs.getString("WhichUse"));
			        bvo.setID(rs.getInt("ID"));
			        bvo.setState(rs.getString("State"));
			        list.add(bvo);
				}
				return list;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		else if(str.equalsIgnoreCase("UserID"))
		{
			try {
				stmt=con.prepareStatement(strselectUserID1);
				stmt.setString(1, vo.getUserID());
				stmt.setString(2, Unit);
				rs=stmt.executeQuery();
				while(rs.next())
				{
					bvo=new bankVO();
			        bvo.setNumberID(rs.getString("NumberID"));
			        bvo.setComputerType(rs.getString("ComputerType"));
			        bvo.setCPU(rs.getString("CPU"));
			        bvo.setMemory(rs.getInt("Memory"));
			        bvo.setHarddisk(rs.getInt("Harddisk"));
			        bvo.setOS(rs.getString("OS"));
			        bvo.setMend(rs.getString("Mend"));
			        bvo.setAntiVirus(rs.getString("AntiVirus"));
			        bvo.setIPAddress(rs.getString("IPAddress"));
			        bvo.setMACAddress(rs.getString("MACAddress"));
			        bvo.setUserID(rs.getString("UserID"));
			        bvo.setUserName(rs.getString("UserName"));
			        bvo.setUserUnit(rs.getString("UserUnit"));
			        bvo.setHouseID(rs.getString("HouseID"));
			        bvo.setDutyPeople(rs.getString("DutyPeople"));
			        bvo.setBuyTime(rs.getString("BuyTime"));
			        bvo.setUseTime(rs.getString("UseTime"));
			        bvo.setWhichUse(rs.getString("WhichUse"));
			        bvo.setID(rs.getInt("ID"));
			        bvo.setState(rs.getString("State"));
			        list.add(bvo);
				}
				return list;
			} catch (SQLException e) {
				e.printStackTrace();
			}	
		}
		else if(str.equalsIgnoreCase("UserUnit"))
		{
			try {
				stmt=con.prepareStatement(strselectUserUnit1);
				stmt.setString(1,vo.getUserUnit());
				stmt.setString(2, Unit);
				rs=stmt.executeQuery();
				while(rs.next())
				{
					bvo=new bankVO();
			        bvo.setNumberID(rs.getString("NumberID"));
			        bvo.setComputerType(rs.getString("ComputerType"));
			        bvo.setCPU(rs.getString("CPU"));
			        bvo.setMemory(rs.getInt("Memory"));
			        bvo.setHarddisk(rs.getInt("Harddisk"));
			        bvo.setOS(rs.getString("OS"));
			        bvo.setMend(rs.getString("Mend"));
			        bvo.setAntiVirus(rs.getString("AntiVirus"));
			        bvo.setIPAddress(rs.getString("IPAddress"));
			        bvo.setMACAddress(rs.getString("MACAddress"));
			        bvo.setUserID(rs.getString("UserID"));
			        bvo.setUserName(rs.getString("UserName"));
			        bvo.setUserUnit(rs.getString("UserUnit"));
			        bvo.setHouseID(rs.getString("HouseID"));
			        bvo.setDutyPeople(rs.getString("DutyPeople"));
			        bvo.setBuyTime(rs.getString("BuyTime"));
			        bvo.setUseTime(rs.getString("UseTime"));
			        bvo.setWhichUse(rs.getString("WhichUse"));
			        bvo.setID(rs.getInt("ID"));
			        bvo.setState(rs.getString("State"));
			        list.add(bvo);
				}
				return list;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		else if(str.equalsIgnoreCase("ID"))
		{
			try {
				stmt=con.prepareStatement(strselectID);
				stmt.setInt(1,vo.getID());
				rs=stmt.executeQuery();
				while(rs.next())
				{
					bvo=new bankVO();
			        bvo.setNumberID(rs.getString("NumberID"));
			        bvo.setComputerType(rs.getString("ComputerType"));
			        bvo.setCPU(rs.getString("CPU"));
			        bvo.setMemory(rs.getInt("Memory"));
			        bvo.setHarddisk(rs.getInt("Harddisk"));
			        bvo.setOS(rs.getString("OS"));
			        bvo.setMend(rs.getString("Mend"));
			        bvo.setAntiVirus(rs.getString("AntiVirus"));
			        bvo.setIPAddress(rs.getString("IPAddress"));
			        bvo.setMACAddress(rs.getString("MACAddress"));
			        bvo.setUserID(rs.getString("UserID"));
			        bvo.setUserName(rs.getString("UserName"));
			        bvo.setUserUnit(rs.getString("UserUnit"));
			        bvo.setHouseID(rs.getString("HouseID"));
			        bvo.setDutyPeople(rs.getString("DutyPeople"));
			        bvo.setBuyTime(rs.getString("BuyTime"));
			        bvo.setUseTime(rs.getString("UseTime"));
			        bvo.setWhichUse(rs.getString("WhichUse"));
			        bvo.setID(rs.getInt("ID"));
			        bvo.setState(rs.getString("State"));
			        list.add(bvo);
				}
				return list;
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return null;
		}
		return null;	
	}
	public List select(bankVO vo,String str)
	{
		dbaccess=new DBAccess();
		dbaccess.getConn();
		con=dbaccess.connDB();
		List list=new ArrayList();
		bankVO bvo;
		if(str.equalsIgnoreCase("NumberID"))
		{
		try {
			stmt=con.prepareStatement(strselectNumberID);
			stmt.setString(1,vo.getNumberID());
			rs=stmt.executeQuery();
			while(rs.next())
			{
				bvo=new bankVO();
		        bvo.setNumberID(rs.getString("NumberID"));
		        bvo.setComputerType(rs.getString("ComputerType"));
		        bvo.setCPU(rs.getString("CPU"));
		        bvo.setMemory(rs.getInt("Memory"));
		        bvo.setHarddisk(rs.getInt("Harddisk"));
		        bvo.setOS(rs.getString("OS"));
		        bvo.setMend(rs.getString("Mend"));
		        bvo.setAntiVirus(rs.getString("AntiVirus"));
		        bvo.setIPAddress(rs.getString("IPAddress"));
		        bvo.setMACAddress(rs.getString("MACAddress"));
		        bvo.setUserID(rs.getString("UserID"));
		        bvo.setUserName(rs.getString("UserName"));
		        bvo.setUserUnit(rs.getString("UserUnit"));
		        bvo.setHouseID(rs.getString("HouseID"));
		        bvo.setDutyPeople(rs.getString("DutyPeople"));
		        bvo.setBuyTime(rs.getString("BuyTime"));
		        bvo.setUseTime(rs.getString("UseTime"));
		        bvo.setWhichUse(rs.getString("WhichUse"));
		        bvo.setID(rs.getInt("ID"));
		        bvo.setState(rs.getString("State"));
		        list.add(bvo);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		}
		else if(str.equalsIgnoreCase("ComputerType"))
		{