staffdao.java

oa办公管理系统。一些小型的企业办公管理用的系统。一个月废寝忘食的结果。

package com.soft.staff.dao;

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

import com.soft.pagecut.PageableResultSet;
import com.soft.pagecut.pageable;
import com.soft.util.DBConn;
import com.soft.vo.DepartmentInfo;
import com.soft.vo.Deptment;
import com.soft.vo.RoleInfo;
import com.soft.vo.UserInfo;

public class StaffDAO {

	
	

	
	private DBConn tj = new DBConn();
	private Connection conn = null; 
	private Statement st = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private pageable res=null;
	int PageCount;
	int rowsCount;
	int PageRowsCount;
	
	int PageSize;
	int CurPage;
	public int getPageCount() {
		return PageCount;
	}

	

	public int getPageRowsCount() {
		return PageRowsCount;
	}

	

	public int getPageSize() {
		return PageSize;
	}

	public void setPageSize(int pageSize) {
		PageSize = pageSize;
	}

	public int getCurPage() {
		return CurPage;
	}

	public int getRowsCount() {
		return rowsCount;
	}

	public void setCurPage(int curPage) {
		CurPage = curPage;
	}
	public List getAllStaff(String departmentidkey,String namekey)
	{
		List<UserInfo> lt = new ArrayList<UserInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			
			
			String sql="select e.employeeid,e.name,e.sex,e.nation,d.departmentid,d.departmentname,c.mobilephone from employee e,department d ,contactlist c where e.departmentid=d.departmentid and e.employeeid=c.employeeid and 1=1";
			if(namekey!=null&&!namekey.equals(""))
			{
				sql += "and e.name like ?";
				System.out.println("namesql:"+sql);
			}
			if(departmentidkey!=null&&!departmentidkey.equals(""))
			{
				sql +=" and e.departmentid=?";
				System.out.println("departmentidsql:"+sql);
			}
			System.out.println("sssssqlll:"+sql);
			ps=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			if(namekey!=null&&!namekey.equals(""))
			{
				ps.setString(1,"%"+namekey+"%");
				if(departmentidkey!=null&&!departmentidkey.equals(""))
				{
					ps.setInt(2,Integer.parseInt(departmentidkey));
				}
			}
			else
			{
				if(departmentidkey!=null&&!departmentidkey.equals(""))
				{
					ps.setInt(1,Integer.parseInt(departmentidkey));
				}
			}
			//st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			//res=new PageableResultSet(st.executeQuery("select e.employeeid,e.name,e.sex,e.nation,d.departmentid,d.departmentname,c.mobilephone from employee e,department d ,contactlist c where e.departmentid=d.departmentid and e.employeeid=c.employeeid"));//构造一个Pageable
			
          res=new PageableResultSet(ps.executeQuery());
			
			
			
			
			
			res.setPageSize(5);//每页5个记录
            
			System.out.println("pagetcount:"+res.getPageCount()+"rowscount:"+res.getRowsCount()+"pagesize:"+res.getPageSize());
			
			res.gotoPage(getCurPage());//跳转到第n页
			PageCount=res.getPageCount();
			rowsCount=res.getRowsCount();
			PageRowsCount=res.getPageRowsCount();
			
			System.out.println("current page:"+res.getCurPage());
			
			for(int i=0; i<res.getPageRowsCount(); i++)
			
			{
		       String sexstr=null;
			   UserInfo uif = new UserInfo();
			   int employeeid=res.getInt("employeeid");
			   String name=res.getString("name");
			   int sex=res.getInt("sex");
			   String nation=res.getString("nation");
			   int departmentid=res.getInt("departmentid");
			   String departmentname=res.getString("departmentname");
			   String phone=res.getString("mobilephone");
			   if(sex==1)
				   sexstr="女";
			   if(sex==0)
				   sexstr="男";
			   uif.setEmployeeId(employeeid);
			   uif.setName(name);
			   uif.setSex(sex);
			   uif.setSexstr(sexstr);
			   uif.setNation(nation);
			   uif.setDepartmentId(departmentid);
			   uif.setDepartmentName(departmentname);
			   uif.setPhone(phone);
			   lt.add(uif);
			   res.next();
			}
			System.out.println(lt.size());
		    conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
				if(res!=null)
				{
					res.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lt;
	}
	public List getAllDepts()
	{
		List<DepartmentInfo> lt = new ArrayList<DepartmentInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			res=new PageableResultSet(st.executeQuery("select * from department"));//构造一个Pageable
			
			while(res.next())
			
			{
			    DepartmentInfo dept = new DepartmentInfo();
				int departmentid=res.getInt("departmentid");
				String departmentname=res.getString("departmentname");
				dept.setDepartmentid(departmentid);
				dept.setDepartmentname(departmentname);
				lt.add(dept);
			
			}
			System.out.println(lt.size());
		    conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lt;
	}
	public List getAllRole()
	{
		List<RoleInfo> lt = new ArrayList<RoleInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			res=new PageableResultSet(st.executeQuery("select * from role"));//构造一个Pageable
		
			while(res.next())
			
			{
			    RoleInfo rif = new RoleInfo();
				int roleid=res.getInt("roleid");
				String rolename=res.getString("rolename");
				rif.setRoleid(roleid);
				rif.setRolename(rolename);
				lt.add(rif);
			
			}
			System.out.println(lt.size());
		    conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lt;
	}
	public int addStaff(int employeeid,String name,int sex,String birthday,String nation,String major,String liveaddress,String phone,String mail,String QQ,String MSN,int departmentid,String position,int roleid)
	{
		int i = 0;
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			
			
			//String  departmentid=departmentid;
			String  professiontitle="";
			//String  position=position;
			int  jobtype=0;
			int  isinservice=0;
			
			String  oldname="";
			//String  sex=sex;
			//String  birthday=birthday;
			String  hometown="";
			//String  nation=nation;
			String  politicalappear="";
			int  ismarried=0;
			String  health="";
			int  weight=0;
			int  height=0;
			String  ingredient="";
			//String  major=major;
			String  workyears="";
			String  educationdegree="";
			String  foreignlanguage="";
			String  foreignlanLevel="";
			String  chineselevel="";
			String  computerlevel="";
			String  residenceplace="";
			//String  liveaddress="";
			String  archivesplace="";
			String  specialskills="";
			String  rewardandpunish="";
			String  workexperience="";
			String  familysituation="";
			String  emergencontact="";
			String  remark="";
            String  yueyulevel="";
			String sql="insert  into employee values("+employeeid+","+departmentid+",'"+professiontitle+"','"+position+"',"+jobtype+","+isinservice+",'"+name+"','"+oldname+"',"+sex+",to_date('"+birthday+"','yyyy-mm-dd'),'"+hometown+"','"+nation+"','"+politicalappear+"',"+ismarried+",'"+health+"',"+weight+","+height+",'"+ingredient+"','"+major+"','"+workyears+"','"+educationdegree+"','"+foreignlanguage+"','"+foreignlanLevel+"','"+chineselevel+"','"+yueyulevel+"','"+computerlevel+"','"+residenceplace+"','"+liveaddress+"','"+archivesplace+"','"+specialskills+"','"+rewardandpunish+"','"+workexperience+"','"+familysituation+"','"+emergencontact+"','"+remark+"')";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			i = ps.executeUpdate();
			if(i>0)
			{
					
			    String password="666666";
			   st.executeUpdate("insert into login values("+employeeid+",'"+password+"',"+roleid+")");
			     
			   st.executeUpdate("insert into contactlist values("+employeeid+",'"+phone+"','"+QQ+"','"+MSN+"','"+mail+"')");
			} 
	    	  
			
		    conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{