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

public class AddressListDAO {
	
	private DBConn tj = new DBConn();
	private Connection conn = null; 
	private Statement st = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	public AddressListDAO()
	{
		super();
		// TODO Auto-generated constructor stub
	}
	
	public List searchAddressBook(int deptid,String userName)
	{
		
		System.out.print("AddressListDAO");
		GetNames get=new GetNames();
		String deptName=get.getDeptName(deptid);
		List<AddressBook> lt = new ArrayList<AddressBook>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
		    String sql = "select * from tb_addressBook where 1=1  ";
		    if(userName!=null&&!userName.equals(""))
			{
				sql += "and userName like ?";
			}
			if(deptName!=null&&!deptName.equals(""))
			{
				sql +=" and tb_deptid=?";
			}
		
			
			System.out.println("sql======="+sql);
			ps = conn.prepareStatement(sql);
			if(userName!=null&&!userName.equals(""))
			{
				ps.setString(1,"%"+userName+"%");
				if(deptName!=null&&!deptName.equals(""))
				{
					ps.setInt(2,deptid);
				}
			}
			else
			{
				if(deptName!=null&&!deptName.equals(""))
				{
					ps.setInt(1,deptid);
				}
			}
			rs = ps.executeQuery();
			int i=0;
			while(rs.next())
			{
				AddressBook ab = new AddressBook();
			
				ab.setID(rs.getInt("ID"));
				ab.setDeptID(rs.getInt("tb_deptid"));
				ab.setUserID(rs.getInt("userid"));
				ab.setUserName(rs.getString("userName"));
				ab.setOfficeTel(rs.getInt("officeTel"));
				ab.setPhone(rs.getString("phone"));
				ab.setQQ(rs.getString("QQ"));
				ab.setEmail(rs.getString("email"));
				ab.setAddress(rs.getString("address"));
				lt.add(i++,ab);
			}
		    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;
			
  }	
	
}