empeditdao.java

实现银行系统中某个部门的计算机设备管理、公共信息管理、权限用户管理、系统设置等,为系统的安全正常运行提供保障

package com.isoftstone.banktearm1.dao;
import java.util.*;
import com.isoftstone.banktearm1.db.*;
import com.isoftstone.banktearm1.vo.BankTeam1DepVo;
import com.isoftstone.banktearm1.vo.BankTeam1EmpVo;
import com.isoftstone.banktearm1.vo.BankTeam1UnitVo;
import com.isoftstone.banktearm1.vo.SelectVo;

import java.sql.*;
public class EmpEditDao extends DB{
	//创建一个新的VO,动态显示一个用户管理表
	public ArrayList getEmpAll () throws SQLException, ClassNotFoundException{
		ArrayList mylist=new ArrayList();
		Connection cn=this.getConnection();
		Statement stmt=cn.createStatement();
		ResultSet rs=null;
		String sql="select JobName,Birthday,UnitName,Name,DepartmentName,Sex,WorkerId  from emp,dep,unit"+
					" where emp.Department = cstr(dep.ID) and mid(emp.WorkerID,1,2) = cstr(unit.UnitID) order by WorkerID asc";
		rs=stmt.executeQuery(sql);
		while(rs.next()){
			SelectVo selectVo=new SelectVo();
			String str=rs.getDate("Birthday").toString();
			selectVo.setBirthday(str);
			selectVo.setDepartmentName(rs.getString("DepartmentName"));
			selectVo.setJobName(rs.getString("JobName"));
			selectVo.setName(rs.getString("Name"));
			selectVo.setSex(rs.getString("Sex"));
			selectVo.setUnitName(rs.getString("UnitName"));
			selectVo.setWorkerId(rs.getInt("WorkerId"));			    
		    mylist.add(selectVo);		    		    
		    }
		if(rs!=null){rs.close();}
		if(stmt!=null){stmt.close();}
		if(cn!=null){cn.close();}
		return mylist;	
		}
	//点编辑按钮,得到所在行的全部信息.
	public BankTeam1EmpVo getEmp(String workerId) throws Exception{
		int workerId2=(Integer.parseInt(workerId));
		Connection cn=this.getConnection();
		Statement stmt=cn.createStatement();
		ResultSet rs=null;
		String sql = "select * from emp where workerId="+workerId2;
		rs=stmt.executeQuery(sql);
		BankTeam1EmpVo myvo=new BankTeam1EmpVo();
		while(rs.next()){			
			myvo.setWorkerId(rs.getInt("WorkerID"));
			myvo.setName(rs.getString("name"));
			myvo.setPassword(rs.getString("password"));
			myvo.setSex(rs.getString("sex"));
			myvo.setBirthday(rs.getDate("Birthday"));
			myvo.setJobName(rs.getString("jobname"));						
		}
		if(rs!=null){rs.close();}
		if(stmt!=null){stmt.close();}
		if(cn!=null){cn.close();}
		return myvo;
		}
//更新emp表	
	public void updateRecort(BankTeam1EmpVo vo) throws Exception{
		int i=0;
		Connection cn=this.getConnection();
		Statement stmt=cn.createStatement();
		String sql="Update emp set Department='"+vo.getDepartment()+"',JobName='"+vo.getJobName()+
					"',password='"+vo.getPassword()+"' where WorkerID="+vo.getWorkerId();
		i=stmt.executeUpdate(sql);
		if(stmt!=null){stmt.close();}
		if(cn!=null){cn.close();};
		}
//删除	
	public void deleteRecort(int workerId) throws Exception{
		
		 String sql="";
		 Connection cn=this.getConnection();
		 Statement stmt=cn.createStatement();
		 int i=0;
		  sql="delete from emp where WorkerID="+workerId;
		  i=stmt.executeUpdate(sql);
		  if(stmt!=null){stmt.close();}
		  if(cn!=null){cn.close();};
			 }
//做一个删除的循环	
   public void delete(String str[]) throws NumberFormatException, Exception{
	   for(int i=0 ;i<str.length;i++){
		   this.deleteRecort(Integer.parseInt(str[i]));
	   }
   }
	
	
//显示出所有的部门(在编辑页面中)
	public ArrayList getDepartment()throws Exception{
		ArrayList mylist=new ArrayList();
		Connection cn=this.getConnection();
		Statement stmt=cn.createStatement();
		ResultSet rs=null;
		
		String sql="select distinct UnitName+':'+DepartmentName AS str, mid(cstr(dep.DepartmentId),4,4) as deptId from dep, unit"+
		           " where mid(dep.DepartmentId,1,2)=cstr(unit.unitid)";
		rs=stmt.executeQuery(sql);
        while(rs.next()){
        	String s[]={rs.getString("str"),rs.getString("deptId")};
		mylist.add(s);
		}		
		if(rs!=null){rs.close();}
		if(stmt!=null){stmt.close();}
		if(cn!=null){cn.close();}
		return mylist;
		}
	

	}