persondao.java

oa rar it is the jsp

package com.accp.oa.dao.imple;

import java.util.ArrayList;

import com.accp.oa.bean.Person;
import com.accp.oa.common.Constants;
import com.accp.oa.common.DbUtil;
import com.accp.oa.dao.inface.BaseDAO;

import java.sql.*;

/*
 * 此类是用于
 * 处对tb_person表
 * 的操作
 */
public class PersonDAO implements BaseDAO {
	int totalpage;

	int totalrow;

	int curpage = 1;

	int rowperpage = 5;

	/*
	 * 添加方法
	 * 
	 * @see com.accp.oa.dao.inface.BaseDAO#add(java.lang.Object)
	 */
	public boolean add(Object obj) {
		Person addbean = (Person) obj;
		boolean success = false;
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = DbUtil.connectToDB();
			pstmt = con.prepareStatement(Constants.ADDPERSONSQL);
			pstmt.setString(1, addbean.getPerId());
			pstmt.setString(2, addbean.getName());
			pstmt.setString(3, addbean.getBranch());
			pstmt.setString(4, addbean.getDuty());
			pstmt.setString(5, addbean.getSex());
			pstmt.setString(6, addbean.getBornDate());
			pstmt.setString(7, addbean.getNation());
			pstmt.setString(8, addbean.getHealth());
			pstmt.setString(9, addbean.getBornSite());
			pstmt.setString(10, addbean.getDiploma());
			pstmt.setString(11, addbean.getFinishSchool());
			pstmt.setString(12, addbean.getAddress());
			pstmt.setString(13, addbean.getTel());
			pstmt.setString(14, addbean.getIdCard());
			pstmt.setString(15, addbean.getPhoto());

			if (pstmt.executeUpdate() > 0) {
				success = true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (con != null)
					con.close();
			} catch (Exception es) {
			}
		}
		return success;
	}

	public boolean delete(Object obj) {
		return false;
	}

	public boolean delete(int id) {

		return false;
	}

	/*
	 * 根据人员标识号删除
	 * 
	 * @see com.accp.oa.dao.inface.BaseDAO#delete(java.lang.String)
	 */
	public boolean delete(String id) {
		boolean success = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = TransDAO.getConnection();
			String sql = Constants.DELETEPERSON;
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			if (pstmt.executeUpdate() > 0) {
				success = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (Exception es) {
				es.printStackTrace();
			}
		}
		return success;
	}

	// 设置总行数
	public int setTotalrow1(Person addbean1) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql1 = getSQL((Person) addbean1);
		Connection con = null;
		int count = 0;
		// 获得最大行数
		try {
			con = DbUtil.connectToDB();
			pstmt = con.prepareStatement(sql1);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				count++;
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (con != null)
					con.close();
			} catch (Exception es) {
			}
		}
		totalrow = count;
		return count;
	}

	// 获得SQL语句
	private String getSQL(Person addbean) {
		String sql1 = Constants.SEARCHPERSON;
		if (addbean.getName() != null && !"".equals(addbean.getName())) {
			sql1 += " and Name = '" + addbean.getName() + "'";
		}
		if (addbean.getPerId() != null && !"".equals(addbean.getPerId())) {
			sql1 += " and PerID = '" + addbean.getPerId() + "'";
		}
		if (addbean.getBranch() != null && !"".equals(addbean.getBranch())) {
			sql1 += " and Branch = '" + addbean.getBranch() + "'";
		}
		if (addbean.getDuty() != null && !"".equals(addbean.getDuty())) {
			sql1 += " and Duty = '" + addbean.getDuty() + "'";
		}
		if (addbean.getDiploma() != null && !"".equals(addbean.getDiploma())) {
			sql1 += " and Diploma = '" + addbean.getDiploma() + "'";
		}
		if (addbean.getSex() != null && !"".equals(addbean.getSex())) {
			sql1 += " and Sex = '" + addbean.getSex() + "'";
		}

		return sql1;
	}

	// 根据总行数求出总页数
	private int setTotalPage1() {
		if (totalrow % rowperpage == 0) {
			totalpage = totalrow / rowperpage;
		} else {
			totalpage = totalrow / rowperpage + 1;
		}
		return totalpage;
	}

	public Object search(Object addbean1, int curpage) {
		this.setTotalrow(this.setTotalrow1((Person) addbean1)); // 查询前先查出并设置总行数
		this.setTotalpage(this.setTotalPage1()); // 查询前先查出并设置总页数
		ArrayList list = new ArrayList();
		String sql1 = getSQL((Person) addbean1); // 获得SQL语句
		this.setCurpage(curpage); // 设置当前页数

		Connection con = null;
		try {
			con = DbUtil.connectToDB();
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 分页显示
		StringBuffer buffer = new StringBuffer();
		buffer.append("select top ");
		buffer.append(this.rowperpage);
		buffer.append(" * from(select top ");
		buffer.append(this.getTotalrow() - (curpage - 1) * this.rowperpage);
		buffer.append(" * from (");
		buffer.append(sql1);
		buffer.append(") a order by a.Id desc) b order by b.Id asc");
		String sql = buffer.toString();
		try {
			con = DbUtil.connectToDB();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();// 这里查询出某页的数据
			while (rs.next()) {
				Person personbean = new Person();
				personbean.setPerId(rs.getString(2));
				personbean.setName(rs.getString(3));
				personbean.setBranch(rs.getString(4));
				personbean.setDuty(rs.getString(5));
				personbean.setSex(rs.getString(6));
				personbean.setBornDate(rs.getString(7));
				personbean.setNation(rs.getString(8));
				personbean.setHealth(rs.getString(9));
				personbean.setBornSite(rs.getString(10));
				personbean.setDiploma(rs.getString(11));
				personbean.setFinishSchool(rs.getString(12));
				personbean.setAddress(rs.getString(13));
				personbean.setTel(rs.getString(14));
				personbean.setIdCard(rs.getString(15));
				personbean.setPhoto(rs.getString(16));
				personbean.setPassword(rs.getString(17));
				list.add(personbean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (con != null)
					con.close();
			} catch (Exception es) {
			}
		}
		return list;
	}
	//根据人员编号查询出ID
	public Object search(Object perid) {
		int id = 0;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = DbUtil.connectToDB();
			stmt = con.prepareStatement(Constants.SELECTIDBYPERID);
			stmt.setString(1, perid.toString());
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt("Id");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (Exception ex) {

			}
		}
		return new Integer(id);
	}

	/*
	 * 此方法用于检验是否有相应的用户
	 * 
	 * @see com.t27.DAO.BaseDAO#search(java.lang.String, java.lang.String) @
	 *      param username 用户名 @ param password 密码 @return 有此用户返回true,没返回false
	 */
	public int search(String username, String password) {
		int perId = 0;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = DbUtil.connectToDB();
			stmt = con.prepareStatement(Constants.VALEATE_USER);
			stmt.setString(1, username);
			stmt.setString(2, password);
			rs = stmt.executeQuery();
			if (rs.next()) {
				perId = rs.getInt("Id");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			return 0;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (Exception ex) {

			}
		}
		return perId;
	}

	public Object search(int id) {
		return null;
	}

	/*
	 * （查出没有角色的用户
	 * 
	 * @see com.accp.oa.dao.inface.BaseDAO#search(java.lang.String)
	 */
	public ArrayList search(String age0) {

		ArrayList usernames = new ArrayList();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = DbUtil.connectToDB();
			stmt = con.prepareStatement(Constants.SEACH_USER_NOT_ROES);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Person person = new Person();
				person.setId(rs.getInt("Id"));
				person.setName(rs.getString("Name"));
				person.setPassword("Password");
				usernames.add(person);
			}
		} catch (Exception ex) {
			return null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (Exception ex) {

			}
		}
		return usernames;
	}

	public ArrayList search(int ag0, int age1) {
		return null;
	}

	/*
	 * 修改密码
	 */
	public boolean updatePassword(int id, String newPassword) {
		boolean success = false;
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = DbUtil.connectToDB();
			String sql = Constants.UPDATEPASSWORD;
			pst = con.prepareStatement(sql);
			pst.setString(1, newPassword);
			pst.setInt(2, id);
			if (pst.executeUpdate() > 0) {
				success = true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (Exception es) {
			}
		}
		return success;
	}

	/*
	 * 修改人员信息
	 * 
	 * @see com.accp.oa.dao.inface.BaseDAO#update(java.lang.Object)
	 */
	public boolean update(Object personbean) {
		Person pb = (Person) personbean;
		boolean success = false;
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = DbUtil.connectToDB();
			String sql = Constants.UPDATEPERSON;
			pst = con.prepareStatement(sql);
			pst.setString(1, pb.getName());
			pst.setString(2, pb.getBranch());
			pst.setString(3, pb.getDuty());
			pst.setString(4, pb.getSex());
			pst.setString(5, pb.getBornDate());
			pst.setString(6, pb.getNation());
			pst.setString(7, pb.getHealth());
			pst.setString(8, pb.getBornSite());
			pst.setString(9, pb.getDiploma());
			pst.setString(10, pb.getFinishSchool());
			pst.setString(11, pb.getAddress());
			pst.setString(12, pb.getTel());
			pst.setString(13, pb.getIdCard());
			pst.setString(14, pb.getPhoto());
			// pst.setString(15, pb.getPassword());
			pst.setString(15, pb.getPerId());
			if (pst.executeUpdate() > 0) {
				success = true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (Exception es) {
			}
		}
		return success;
	}

	// 此方法用于查询出所有的用户
	public ArrayList search() {

		ArrayList usernames = new ArrayList();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = DbUtil.connectToDB();
			stmt = con.prepareStatement(Constants.SEARCH_ALL_USER);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Person person = new Person();
				person.setId(rs.getInt("Id"));
				person.setName(rs.getString("Name"));
				person.setPassword("Password");
				usernames.add(person);
			}
		} catch (Exception ex) {
			return null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (Exception ex) {

			}
		}
		return usernames;
	}

	public int getCurpage() {
		return curpage;
	}

	public void setCurpage(int curpage) {
		this.curpage = curpage;
	}

	public int getRowperpage() {
		return rowperpage;
	}

	public void setRowperpage(int rowperpage) {
		this.rowperpage = rowperpage;
	}

	public int getTotalpage() {
		return totalpage;
	}

	public void setTotalpage(int totalpage) {
		this.totalpage = totalpage;
	}

	public int getTotalrow() {
		return totalrow;
	}

	public void setTotalrow(int totalrow) {
		this.totalrow = totalrow;
	}

}