.#deptinfodao.java.1.1

实现办公自动化系统

/**
 * @author cf
 * 部门信息操作类
 */

package com.oa.struts.depmgr.dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.oa.util.*;
import com.oa.struts.vo.*;
public class DeptInfoDAO
{
	public Connection con;
	public PreparedStatement pstmt;
	public Statement stmt;
	public DBConn dbcon;
	public ResultSet rs;
	public DeptInfoDAO()
	{
		
	}
	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);
			}
			
			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 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(sql);
			while(rs.next())
			{
				UserInfo userInfo=new UserInfo();
				userInfo.setUserId(rs.getInt("USERID"));
				userInfo.setRealName(rs.getString("REALNAME"));
				lt.add(userInfo);
			}
			
			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 lt;
	}
    public List searchUser(int deptId,String realName)
    {
    	List<UserInfo> lt=new ArrayList<UserInfo>();
		dbcon=new DBConn();
		String sql="select * from tb_userinfo where flag=0 ";
		//System.out.println("dept="+deptId);
		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);
				//System.out.println("deptId="+deptId);
				//System.out.println("test1=======");
				if(realName!=null&&!realName.equals(""))
				{
					//System.out.println("test2=======");
					pstmt.setString(2,"%"+realName+"%");
				}
			}
			else
			{   
				//System.out.println("test3=======");
				if(realName!=null&&!realName.equals(""))
				{
					pstmt.setString(1,"%"+realName+"%");
				}
			}
			//System.out.println("sql2="+sql);
			rs=pstmt.executeQuery();   // 注意pstmt.excuteQuery()中是不带sql参数的
			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(userInfo);
			}
			con.commit();
			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 lt;
    }
}