bankdao.java

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

			System.out.println("select ComputerType");
			try {
				stmt=con.prepareStatement(strselectComputerType);
				stmt.setString(1, vo.getComputerType());
				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(strselectUserID);
				stmt.setString(1, vo.getUserID());
				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(strselectUserUnit);
				stmt.setString(1,vo.getUserUnit());
				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 void update(bankVO vo)
	     {
	 		dbaccess=new DBAccess();
			dbaccess.getConn();
			con=dbaccess.connDB();
			try {
				stmt=con.prepareStatement(strupdate);
				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.setInt(19,vo.getID());
	            stmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	     }
	     public void delete(bankVO vo)
	     {
		 dbaccess=new DBAccess();
		 dbaccess.getConn();
		 con=dbaccess.connDB(); 
		 try {
			stmt=con.prepareStatement(strdelete);
			stmt.setInt(1,vo.getID());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	     }
	     public List mohuselect(String strmohu)
	     {
			 dbaccess=new DBAccess();
			 dbaccess.getConn();
			 con=dbaccess.connDB();
			 bankVO bvo;
			 List list=new ArrayList();
		     Statement sm;
			 try {
				sm=con.createStatement();
				rs=sm.executeQuery(strmohu);
			     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 count(String strcount,int i)
	     {
			 dbaccess=new DBAccess();
			 dbaccess.getConn();
			 con=dbaccess.connDB();
			 bankVO bvo;
			 List list=new ArrayList();
		     Statement sm;
		     try {
				sm=con.createStatement();
				rs=sm.executeQuery(strcount);
				while(rs.next())
				{
				 bvo=new bankVO();
				 bvo.setID(rs.getInt(2));
				 if(i==1)
				 {
				 bvo.setUserUnit(rs.getString("UserUnit"));
				 bvo.setHouseID("");
				 }
				 else if(i==2)
				 {
			     bvo.setHouseID(rs.getString("HouseID"));
				 bvo.setUserUnit("");
				 }
				 list.add(bvo);
				}
				return list;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		     return null;
	     }
	   public List applyfind()
	   {
			dbaccess = new DBAccess();
			dbaccess.getConn();
			con = dbaccess.connDB();
			bankapply bvo;
			List list=new ArrayList();
			try {		
				stmt = con.prepareStatement(strapplyfind);
				rs = stmt.executeQuery();
				while(rs.next())
				{
					bvo = new bankapply();
			        bvo.setNumberID(rs.getString("NumberID"));
			        bvo.setApplyWorker(rs.getString("UserUnit"));
			        list.add(bvo);
				}
		        return list;
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return null; 
	   }
	   public List applyselect(String NumberID)
	   {
			dbaccess = new DBAccess();
			dbaccess.getConn();
			con = dbaccess.connDB();
			List list=new ArrayList();
			bankapply vo=new bankapply();
			try {
				stmt=con.prepareStatement(strapplyselect);
				stmt.setString(1, NumberID);
				rs=stmt.executeQuery();
				while(rs.next())
				{
					vo.setNumberID(rs.getString("NumberID"));
					vo.setState(rs.getString("State"));
					vo.setApplyWorker(rs.getString("ApplyWorker"));
					vo.setApplyReason(rs.getString("ApplyReason"));
					vo.setRejectReason(rs.getString("RejectReason"));
					list.add(vo);
				}
				return list;
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return null;
	   }
	   public void applyinsert(bankapply vo)
	   {
			dbaccess = new DBAccess();
			dbaccess.getConn();
			con = dbaccess.connDB();
			try {
				stmt=con.prepareStatement(strapplyinsert);
				stmt.setString(1, vo.getNumberID());
				stmt.setString(2, "未审批");
				stmt.setString(3, vo.getApplyWorker());
				stmt.setString(4, vo.getApplyReason());
				stmt.setString(5, "");
				stmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	   }
	   public void applydelete(String NumberID)
	   {
			dbaccess = new DBAccess();
			dbaccess.getConn();
			con = dbaccess.connDB();
			try {
				stmt=con.prepareStatement(strapplydelete);
				stmt.setString(1, NumberID);
				stmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	   }
	   public List applyfindall()
	   {
			dbaccess = new DBAccess();
			dbaccess.getConn();
			con = dbaccess.connDB();
			List list=new ArrayList();
			bankapply bvo=new bankapply();
			try {
				stmt=con.prepareStatement(strapplyfindall);
				rs=stmt.executeQuery();
				while(rs.next())
				{
					bvo.setApplyReason(rs.getString("ApplyReason"));
					bvo.setApplyWorker(rs.getString("ApplyWorker"));
					bvo.setNumberID(rs.getString("NumberID"));
					bvo.setState(rs.getString("State"));
					list.add(bvo);
				}
				return list;
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return null;
	   }
	   public void applyupdate(bankapply vo)
	   {
			dbaccess = new DBAccess();
			dbaccess.getConn();
			con = dbaccess.connDB();
			try {
				stmt=con.prepareStatement(strapplyupdate);
				stmt.setString(1, vo.getRejectReason());
				stmt.setString(2, "驳回");
				stmt.setString(3, vo.getNumberID());
				stmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	   }
	   public void compstateupdate(String NumberID)
	   {
			dbaccess = new DBAccess();
			dbaccess.getConn();
			con = dbaccess.connDB();
			try {
				stmt=con.prepareStatement(strcompstateupdate);
				stmt.setString(1, "可用");
				stmt.setString(2, NumberID);
				stmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	   }
	public static void main(String args[])
	{
		bankDAO dao = new bankDAO();
		bankVO vo=new bankVO();
//		String str="select HouseID,count(ID) from comp group by HouseID";
//        List list=dao.count(str,2);
//        vo=(bankVO)list.get(0);
//        System.out.println(vo.getUserUnit());
//        System.out.print(vo.getID());
////        for(int i=0;i<list.size();i++)
////        {
////        vo=(bankVO)list.get(i);
////        System.out.println(vo.getNumberID());
////        }
//////		Bankvoe vo =new Bankvoe();
//////		vo.setWorkID("001");
//////		vo.setPassword("123");
//////		int i=dao.login(vo);
//////		System.out.print(i);
//		List list=dao.applyfind();
//		System.out.println(list.size());
		bankapply bvo=new bankapply();
		bvo.setRejectReason("不行");
		bvo.setNumberID("55188");
		bvo.setState("驳回");
		dao.applyupdate(bvo);
        
	}      
}