perinfodao.java

实现办公自动化系统

package com.oa.struts.perOffice.modle;

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.oa.struts.vo.*;
import com.oa.util.Chinese_Do;
import com.oa.util.DBConn;

public class PerInfoDAO 
{
	Connection con=null;
	PreparedStatement pstmt=null;
	Statement stmt=null;
	ResultSet rs=null;
	DBConn db=null;
	public PerInfoDAO() {
		super();
		// TODO Auto-generated constructor stub
	}
	public int getTotulRows(int deptId,String realName) 
	{   
		int i = 0;
		String sql="select count(*) totulrows from tb_userinfo  where flag=0 and isshare=0";
		if(deptId!=0)//当用户选择了部门而没有选择写姓名
		{
				sql+=" and DEPTID=? ";
				if(realName!=null&&!realName.equals(""))
				{
					sql+="and realName like ?";
				}
	   }
	  else
	  {
			if(realName!=null&&!realName.equals(""))
			{
				sql+="and realName like ?";
			}
	  }
	try {
			db=new DBConn();
			con=db.getConnection();
			con.setAutoCommit(false);
			pstmt=con.prepareStatement(sql);
			if(deptId!=0)//当用户选择了部门而没有选择写姓名
			{
				pstmt.setInt(1, deptId);
				if(realName!=null&&!realName.equals(""))
				{
					pstmt.setString(2,"%"+realName+"%");
				}
			}
			else
			{   
				if(realName!=null&&!realName.equals(""))
				{
					pstmt.setString(1,"%"+realName+"%");
				}
			}
			rs=pstmt.executeQuery();   // 注意pstmt.excuteQuery()中是不带sql参数的
			if(rs.next())
			{
				i = rs.getInt("totulrows");
			}
		    con.commit();
		} catch (SQLException e) {
			
			try {
				if(con!=null)
				{
					con.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(con!=null)
				{
					con.close();
				}
				if(pstmt!=null)
				{
					pstmt.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}
	// 查询个人信息
	public UserInfo searchInfo(int userId)
	{
		UserInfo userInfo=new UserInfo();
		try
		{
			String sql="select * from tb_userInfo where userid=?";
			db=new DBConn();
			con=db.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,userId);		
			rs=pstmt.executeQuery();
			String passWord=null;
			String realName=null;  //真实姓名
			int departID; //所属部门号
			int userSex;   //性别
			String userBirth=null;
			String userNational=null; //民族
			String userTel=null;   //手机
			String userDepTel=null;  //部门电话
			String userEmail=null;
			String userInterest=null;//兴趣
			String userAddress=null;
			String userCode=null;  //邮编
			String userPost=null; //职位
			int userRole; //角色
			
			String Resume=null;  //自我简介
			int flag;  //标记
			int isShare;//是否共享
			while(rs.next())
			{
				userInfo.setPassWord(rs.getString("passWord"));
				userInfo.setRealName(rs.getString("realName"));
				userInfo.setDepartID(rs.getInt("deptid"));
				userInfo.setUserSex(rs.getInt("USERSEX"));
				userInfo.setUserBirth(rs.getString("USERBIRTH"));
				userInfo.setUserNational(rs.getString("USERNATIONAL"));
				userInfo.setUserTel(rs.getString("USERTEL"));
				userInfo.setUserDepTel(rs.getString("USERDEPTEL"));
				userInfo.setUserEmail(rs.getString("USEREMAIL"));
				userInfo.setUserInterest(rs.getString("USERINTEREST"));
				userInfo.setUserAddress(rs.getString("USERADDRESS"));
				userInfo.setUserCode(rs.getString("USERCODE"));
				userInfo.setUserPost(rs.getString("USERPOST"));
				userInfo.setResume(rs.getString("RESUME"));
				userInfo.setIsShare(rs.getInt("ISSHARE"));
			}
			
			
			
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
			if(con!=null) con.close();		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	
		return userInfo;
	}
	
//	修改个人信息
	public boolean updateuserInfo(UserInfo userInfo)
	{
		boolean result=false;
		try
		{
			String sql="update tb_userInfo set realName=?,userSex=?,userBirth=?,userNational=?,userTel=?,userDepTel=?,userEmail=?," +
					"userAddress=?,userCode=?,isshare=? where userid=? and  flag=0";
			db=new DBConn();
			con=db.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1,userInfo.getRealName());
			//System.out.println("realName="+userInfo.getRealName());
			pstmt.setInt(2,userInfo.getUserSex());
			pstmt.setString(3,userInfo.getUserBirth());
			pstmt.setString(4,userInfo.getUserNational());
			pstmt.setString(5,userInfo.getUserTel());
			pstmt.setString(6,userInfo.getUserDepTel());
			pstmt.setString(7,userInfo.getUserEmail());
			pstmt.setString(8,userInfo.getUserAddress());
			pstmt.setString(9,userInfo.getUserCode());
		    pstmt.setInt(10, userInfo.getIsShare());
		    pstmt.setInt(11, userInfo.getUserId());
			int i=pstmt.executeUpdate();
			if(i>0)
			{
				result=true;
			}
				
			if(pstmt!=null) pstmt.close();
			if(con!=null) con.close();		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return result;
	}

	public List searchUser(int deptId,String realName,int startRow,int endRow)
    {
    	List<UserInfo> lt=new ArrayList<UserInfo>();
    	System.out.println("4*****************************************");
		db=new DBConn();
		String sql="select t.*,rownum rn from tb_userinfo t where flag=0 ";
		sql = "select * from ("+ sql+" and rownum<="+endRow +") t where rn>="+startRow;
		if(deptId!=0)//当用户选择了部门而没有选择写姓名
		{
			sql+=" and DEPTID=? ";
			if(realName!=null&&!realName.equals(""))
			{
				sql+="and realName like ?";
			}
		}
		else
		{
			if(realName!=null&&!realName.equals(""))
			{
				sql+="and realName like ?";
			}
		}
		System.out.println("sql="+sql);
		try
		{
			con=db.getConnection();
			con.setAutoCommit(false);
			pstmt=con.prepareStatement(sql);
			if(deptId!=0)//当用户选择了部门而没有选择写姓名
			{
				pstmt.setInt(1, deptId);
				if(realName!=null&&!realName.equals(""))
				{
					pstmt.setString(2,"%"+realName+"%");
				}
			}
			else
			{   
				if(realName!=null&&!realName.equals(""))
				{
					pstmt.setString(1,"%"+realName+"%");
				}
			}
			rs=pstmt.executeQuery();   // 注意pstmt.excuteQuery()中是不带sql参数的
			int i=0;
			while(rs.next())
			{
				Chinese_Do cd=new Chinese_Do();
				UserInfo userInfo=new UserInfo();
				userInfo.setUserId(rs.getInt("USERID"));
				userInfo.setRealName(rs.getString("REALNAME"));
				userInfo.setDepartID(rs.getInt("DEPTID"));
				userInfo.setUserSex(rs.getInt("USERSEX"));
				userInfo.setUserNational(rs.getString("USERNATIONAL"));
				lt.add(i++,userInfo);
			}
			con.commit();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return lt;
    }

	public boolean updatePwd(int userId,String passWord)
	{
		boolean flag=false;
		int i=0;
		String sql="update tb_userinfo set password=? where userid=?";
		try
		{
			db=new DBConn();
			con=db.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, passWord);
			pstmt.setInt(2, userId);
			i=pstmt.executeUpdate();
			if(i>0)
			{
				flag=true;
			}
		
		 con.commit();
	  } catch (SQLException e) {
		
		try {
			if(con!=null)
			{
				con.rollback();//事务回滚
			}
		} catch (SQLException e1) {
			
			e1.printStackTrace();
		}
		e.printStackTrace();
	}
	finally
	{
		try {
			if(con!=null)
			{
				con.close();
			}
			if(pstmt!=null)
			{
				pstmt.close();
			}
			if(rs!=null)
			{
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
		
		return flag;
	
	}
}