.#usermgrdao.java.1.4

实现办公自动化系统

/**
 * @author cf
 * 用户信息操作类
 */
package com.oa.struts.usermgr.dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.oa.util.*;
import com.oa.struts.vo.*;
public class UserMgrDAO
{
	public Connection con;
	public PreparedStatement pstmt;
	public Statement stmt;
	public DBConn dbcon;
	public ResultSet rs;
	
	public UserMgrDAO() {
		super();
		// TODO Auto-generated constructor stub
	}
	public int getTotulRows() 
	{   
		int i = 0;
		try {
			dbcon=new DBConn();
			con=dbcon.getConnection();
			con.setAutoCommit(false);
			stmt =con.createStatement();
			String sql = "select count(*) totulrows from tb_userinfo  where flag=0";
            rs = stmt.executeQuery(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 List getDeptList()
	{
		List<DeptInfo> lt=new ArrayList<DeptInfo>();
		dbcon=new DBConn();
		try
		{
			con=dbcon.getConnection();
			con.setAutoCommit(false);
			String sql="select * from tb_department";
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next())
			{
				DeptInfo deptInfo=new DeptInfo();
				deptInfo.setDeptID(rs.getInt("DEPTID"));
				deptInfo.setDeptName(rs.getString("DEPTNAME"));
				deptInfo.setEXPLAIN(rs.getString("EXPLAIN"));
				lt.add(deptInfo);
			}
			
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return lt;
	}
	/**
	 * @param deptId
	 * @return
	 * 根据部门号搜索用户，以列表形式返回
	 */
	public List getDep_UserList(int deptId)
	{
		List<UserInfo> lt=new ArrayList<UserInfo>();
		dbcon=new DBConn();
		try
		{
			con=dbcon.getConnection();
			con.setAutoCommit(false);
			String sql="select * from tb_userinfo where deptid=?";
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, deptId);
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				UserInfo userInfo=new UserInfo();
				userInfo.setUserId(rs.getInt("USERID"));
				userInfo.setRealName(rs.getString("REALNAME"));
				lt.add(userInfo);
			}
			
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return lt;
	}
    public List searchUser(int deptId,String realName,int startRow,int endRow)
    {
    	List<UserInfo> lt=new ArrayList<UserInfo>();
		dbcon=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=dbcon.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 deleteUser(int userId)
    {
    	boolean flag=false;
    	dbcon=new DBConn();
    	con=dbcon.getConnection();
    	try
    	{
    		System.out.println("break1");
    		//con.setAutoCommit(false);
    		System.out.println("break2");
    		String sql="update tb_userinfo  set flag=1 where userid="+userId;
    		//pstmt=con.prepareStatement(sql);
    		System.out.println("break3");
    		//pstmt.setInt(1, userId);
    		System.out.println("sql="+sql);
    		//int i=pstmt.executeUpdate();
    		stmt=con.createStatement();
    		int i=stmt.executeUpdate(sql);
    		System.out.println("break4");
    		if(i>0)
    		{
    			flag=true;
    		}
    		
    	}
    	catch(Exception e)
    	{
    		e.printStackTrace();
    	}
    	
    	return flag;
    }
}