userdao.java

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

package com.soft.usermgr.dao;

import java.io.UnsupportedEncodingException;
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.List;

import com.soft.util.DBConn;
import com.soft.vo.LoginInfo;
import com.soft.vo.UserInfo;

public class UserDAO {
	private DBConn tj = new DBConn();
	private Connection conn = null; 
	private Statement st = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	public int delUser(int userNo)
	{
		int i = 0;
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			
			ps = conn.prepareStatement("delete from userinfo where userno=?");
			ps.setInt(1,userNo);
			i = ps.executeUpdate();
		    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 i;
	}
	public int updateUser(String userNo,String userName,String deptNo)
	{
		int i = 0;
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement("update  userinfo set deptNo=?,username=? where userNo=?");
			ps.setInt(1,Integer.parseInt(deptNo));
			ps.setString(2,userName);
			
			ps.setInt(3,Integer.parseInt(userNo));
			i = ps.executeUpdate();
		    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 i;
	}
	
	public int updateUserInfo(int employeeid,int departmentid,String professiontitle,String position,int jobtype,int isinservice,String name,String oldname,int sex,String birthday,String hometown,String nation,String politicalappear,int ismarried,String health,int weight,int height,String ingredient,String major,int workyears,String educationdegree,String foreignlanguage,String foreignlanLevel,String chineselevel,String yueyulevel,String computerlevel,String residenceplace,String liveaddress,String archivesplace,String specialskills,String rewardandpunish,String workexperience,String familysituation,String emergencontact,String remark) throws UnsupportedEncodingException
	{
		int i = 0;
	try{
		name=new String(name.getBytes("ISO-8859-1"),"utf-8");
	professiontitle=new String(professiontitle.getBytes("ISO-8859-1"),"utf-8");
	position=new String(position.getBytes("ISO-8859-1"),"utf-8");
	oldname=new String(oldname.getBytes("ISO-8859-1"),"utf-8");
	hometown=new String(hometown.getBytes("ISO-8859-1"),"utf-8");
	nation=new String(nation.getBytes("ISO-8859-1"),"utf-8");
	politicalappear=new String(politicalappear.getBytes("ISO-8859-1"),"utf-8");
	health=new String(health.getBytes("ISO-8859-1"),"utf-8");
	ingredient=new String(ingredient.getBytes("ISO-8859-1"),"utf-8");
	major=new String(major.getBytes("ISO-8859-1"),"utf-8");
	educationdegree=new String(educationdegree.getBytes("ISO-8859-1"),"utf-8");
	foreignlanguage=new String(foreignlanguage.getBytes("ISO-8859-1"),"utf-8");
	foreignlanLevel=new String(foreignlanLevel.getBytes("ISO-8859-1"),"utf-8");
	chineselevel=new String(chineselevel.getBytes("ISO-8859-1"),"utf-8");
	computerlevel=new String(computerlevel.getBytes("ISO-8859-1"),"utf-8");
	residenceplace=new String(residenceplace.getBytes("ISO-8859-1"),"utf-8");
	liveaddress=new String(liveaddress.getBytes("ISO-8859-1"),"utf-8");
	archivesplace=new String(archivesplace.getBytes("ISO-8859-1"),"utf-8");
	specialskills=new String(specialskills.getBytes("ISO-8859-1"),"utf-8");
	rewardandpunish=new String(rewardandpunish.getBytes("ISO-8859-1"),"utf-8");
	workexperience=new String(workexperience.getBytes("ISO-8859-1"),"utf-8");
    familysituation=new String(familysituation.getBytes("ISO-8859-1"),"utf-8");
    emergencontact=new String(emergencontact.getBytes("ISO-8859-1"),"utf-8");
    remark=new String(remark.getBytes("ISO-8859-1"),"utf-8");
	}
	catch(Exception es){i=-1;}
		try {
			
			conn = tj.getConnection();
			String sql="update employee set employeeid="+employeeid+",departmentid="+departmentid+",professiontitle='"+professiontitle+"',position='"+position+"',jobtype="+jobtype+",isinservice="+isinservice+",name='"+name+"',oldname='"+oldname+"',sex="+sex+",birthday=to_date('"+birthday+"','yyyy-mm-dd'),hometown='"+hometown+"',nation='"+nation+"',politicalappear='"+politicalappear+"',ismarried="+ismarried+",health='"+health+"',weight="+weight+",height="+height+",ingredient='"+ingredient+"',major='"+major+"',workyears="+workyears+",educationdegree='"+educationdegree+"',foreignlanguage='"+foreignlanguage+"',foreignlanLevel='"+foreignlanLevel+"',chineselevel='"+chineselevel+"',yueyulevel='"+yueyulevel+"',computerlevel='"+computerlevel+"',residenceplace='"+residenceplace+"',liveaddress='"+liveaddress+"',archivesplace='"+archivesplace+"',specialskills='"+specialskills+"', rewardandpunish='"+rewardandpunish+"',workexperience='"+workexperience+"',familysituation='"+familysituation+"',emergencontact='"+emergencontact+"',remark='"+remark+"' where employeeid="+employeeid;
			System.out.println("sql="+sql);
	        st=conn.createStatement();
	        i=st.executeUpdate(sql);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(st!=null)
				{
					st.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}
	public int updatePass(String password,int employeeid)
	{
		int i=0;
		try
		{
			conn=tj.getConnection();
			String sql="update login set password='"+password+"' where employeeid="+employeeid+"";
			st=conn.createStatement();
			i=st.executeUpdate(sql);
			
			
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
			if(conn!=null)
			{
				conn.close();
			}
			if(st!=null)
			{
				st.close();
			}
			}
		catch (SQLException e1) {
			e1.printStackTrace();
		}
		}
		return i;
	}
	
	public boolean checkPass(int pasword)
	{
		
		try
		{
			conn=tj.getConnection();
			String sql="select * from login where password='"+pasword+"'";
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				return true;
			}
			
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
			if(conn!=null)
			{
				conn.close();
			}
			if(st!=null)
			{
				st.close();
			}
			}
		catch (SQLException e1) {
			e1.printStackTrace();
		}
		}
		return false;
	}
	public List searchUser(int employeeId)
	{
		List<UserInfo> lt = new ArrayList<UserInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select * from employee where employeeid=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,employeeId);
			rs = ps.executeQuery();
			while(rs.next())
			{
				UserInfo ui = new UserInfo();
				ui.setEmployeeId(rs.getInt("employeeid"));
				ui.setDepartmentId(rs.getInt("departmentid"));
				ui.setProfessionalTitle(rs.getString("professiontitle"));
				ui.setPosition(rs.getString("position"));
				ui.setJobType(rs.getString("jobtype"));
				ui.setInService(rs.getInt("isinservice"));
				ui.setName(rs.getString("name"));
				ui.setOldName(rs.getString("oldname"));
				ui.setSex(rs.getInt("sex"));
				ui.setBirthday(rs.getDate("birthday"));
				ui.setHometown(rs.getString("hometown"));
				ui.setNation(rs.getString("nation"));
				ui.setPoliticalAppear(rs.getString("politicalappear"));
				ui.setMarried(rs.getInt("ismarried"));
				ui.setHealth(rs.getString("health"));
				ui.setWeight(rs.getString("weight"));
				ui.setHeight(rs.getString("height"));
				ui.setIngredient(rs.getString("ingredient"));
				ui.setMajor(rs.getString("major"));
				ui.setWorkYears(rs.getInt("workyears"));
				ui.setEducationDegree(rs.getString("educationdegree"));
				ui.setForeignLanguage(rs.getString("foreignlanguage"));
				ui.setForeignLanLevel(rs.getString("foreignlanLevel"));
				ui.setChineseLevel(rs.getString("chineselevel"));
				ui.setComputerLevel(rs.getString("computerlevel"));
				ui.setResidencePlace(rs.getString("residenceplace"));
				ui.setLiveAddress(rs.getString("liveaddress"));
				ui.setArchivesPlace(rs.getString("archivesplace"));
				ui.setSpecialSkills(rs.getString("specialskills"));
				ui.setRewardAndPunish(rs.getString("rewardandpunish"));
				ui.setWorkExperience(rs.getString("workexperience"));
				ui.setFamilySituation(rs.getString("familysituation"));
				ui.setEmergenContact(rs.getString("emergencontact"));
				ui.setRemark(rs.getString("remark"));
				lt.add(ui);
			}
		    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 searchUsersById(int departmentid)
	{
		List<UserInfo> lt = new ArrayList<UserInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select * from employee where departmentid=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,departmentid);
			rs = ps.executeQuery();
			while(rs.next())
			{
				UserInfo ui = new UserInfo();
				ui.setEmployeeId(rs.getInt("employeeid"));
				ui.setName(rs.getString("name"));
				lt.add(ui);
			}
		    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 searchAllUsers()
	{
		List<UserInfo> lt = new ArrayList<UserInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select * from employee ";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				UserInfo ui = new UserInfo();
				ui.setEmployeeId(rs.getInt("employeeid"));
				ui.setName(rs.getString("name"));
				lt.add(ui);
			}
		    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 searchUsers(String userName,String userNo)
	{
		List<UserInfo> lt = new ArrayList<UserInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select u.*,d.deptname from userinfo u,deptment d where u.deptno=d.deptno and 1=1 ";
			if(userName!=null&&!userName.equals(""))
			{
				sql += "and u.username like ?";
			}
			if(userNo!=null&&!userNo.equals(""))
			{
				sql +=" and u.userno=?";
			}
			ps = conn.prepareStatement(sql);
			if(userName!=null&&!userName.equals(""))
			{
				ps.setString(1,"%"+userName+"%");
				if(userNo!=null&&!userNo.equals(""))
				{
					ps.setInt(2,Integer.parseInt(userNo));
				}
			}
			else
			{
				if(userNo!=null&&!userNo.equals(""))
				{
					ps.setInt(1,Integer.parseInt(userNo));
				}
			}
			rs = ps.executeQuery();
			while(rs.next())
			{
				UserInfo ui = new UserInfo();
				int userno=rs.getInt("userno");
				String username=rs.getString("username");
				int deptno=rs.getInt("deptno");
				String deptname=rs.getString("deptname");
			//	ui.setUserNo(userno);
			//	ui.setUserName(username);
			//	ui.setDeptNo(deptno);
			//	ui.setDeptName(deptname);
				lt.add(ui);
			}
		    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;
	}
}