staffinfodao.java

这是本人曾经在公司里用的,内部开发框架,基于struts+hibernate今天分享给大家

/**
 * 
 */
package cn.bway.admin.dao;

import java.sql.*;
import java.util.*;
import org.apache.log4j.*;

import cn.bway.admin.vo.StaffInfoVO;
import cn.bway.common.BwayHibernateException;

/**
 * @author Kson
 *
 */
public class StaffInfoDAO {
	
	protected final Logger log = LogManager.getLogger(getClass());
    private Connection conn = null;
    public StaffInfoDAO(Connection conn) {
        this.conn = conn;
    }
    
    private ResultSet rs = null;
    private PreparedStatement stat = null;
    RightInfoDAO rightDao = null;
    
    /**
     * ��ѯԱ����Ϣ
     * @param vo StaffInfoVO
     * @return List
     * @throws BwayHibernateException
     */
    public List queryStaffInfo(StaffInfoVO vo) throws   BwayHibernateException  {
        List resultList = new ArrayList();
        StaffInfoVO resultVO = null;
        try  {
            String strsql =
                "select * from staffinfo where staffid not in (1, 2, 3) ";
            if (vo.getStaffname() != null && !vo.getStaffname().equals(""))
            {
                strsql += " and (staffname like '%" + vo.getStaffname() +
                    "%' or staffcnname like'%" + vo.getStaffname() + "%')";
            }
            if (vo.getSex() != null && !vo.getSex().equals(""))
            {
                strsql += " and sex like '%" + vo.getSex() + "%' ";
            }
            strsql += " order by staffid desc";
            stat = conn.prepareStatement(strsql);
            rs = stat.executeQuery();
            while (rs != null && rs.next())  {
                resultVO = new StaffInfoVO();
                resultVO.setStaffid(rs.getString("staffid"));
                resultVO.setStaffname(rs.getString("staffname"));
                resultVO.setSex(rs.getString("sex"));
                resultVO.setPassward(rs.getString("passward"));
                resultVO.setStaffcnname(rs.getString("staffcnname"));
                resultVO.setPhone(rs.getString("phone"));
                resultVO.setEmail(rs.getString("email"));
                resultList.add(resultVO);
            }
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.debug("��ѯԱ����Ϣ��ݿ��쳣: "+ex.getMessage());
        } 
        finally {
            try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        return resultList;
    }
    
    /**
     * ����Ա��
     * @param vo StaffInfoVO
     * @throws BwayHibernateException
     */
    public void updateStaffInfo(StaffInfoVO vo) throws   BwayHibernateException  {
        try  {
            String strsql = "update staffinfo set staffname=?, sex=?, staffcnname=?, phone=?, email=? where staffid not in (1, 2, 3) and staffid = ?";
            stat = conn.prepareStatement(strsql);
            stat.setString(1, vo.getStaffname());
            stat.setString(2, vo.getSex());
            stat.setString(3, vo.getStaffcnname());
            stat.setString(4, vo.getPhone());
            stat.setString(5, vo.getEmail());
            stat.setInt(6, Integer.parseInt(vo.getStaffid()));
            stat.executeUpdate();
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.info("����Ա����ݿ��쳣: "+ex.getMessage());
        } 
        finally {
            try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
    }
    
    /**
     * ɾ��Ա��
     * @param vo StaffInfoVO
     * @throws PlatformException
     */
    public void deleteStaffInfo(StaffInfoVO vo) throws  BwayHibernateException {
        try {
            String strsql = "delete staffinfo where  staffid not in (1, 2, 3) ";
            if (!vo.getStaffid().equals(""))
            {
                strsql += "  and staffid = '" + vo.getStaffid() + "' ";
            }
            log.info(strsql);
            stat = conn.prepareStatement(strsql);
            stat.executeUpdate();
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.info("ɾ��Ա����ݿ��쳣: "+ex.getMessage());
        }
        finally {
            try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
    }
    
    /**
     * ����Ա��
     * @param vo StaffInfoVO
     * @throws BwayHibernateException
     */
    public void insertStaffInfo(StaffInfoVO vo) throws  BwayHibernateException  {
        try {
            String strsql = "insert into staffinfo (staffid, staffname, sex, staffcnname, phone, email) values (?,?,?,?,?,?)";
            stat = conn.prepareStatement(strsql);
            stat.setString(1, vo.getStaffid());
            stat.setString(2, vo.getStaffname());
            stat.setString(3, vo.getSex());
            stat.setString(4, vo.getStaffcnname());
            stat.setString(5, String.valueOf(vo.getPhone()));
            stat.setString(6, vo.getEmail());
            stat.executeUpdate();
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.info("����Ա����ݿ��쳣: "+ex.getMessage());
        }
        finally {
            try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
    }
    
    /**
     * �޸�Ա������
     * @param modifyPasswordMap Map
     * @throws BwayHibernateException
     */
    public void modifyPassword(Map modifyPasswordMap) throws  BwayHibernateException {
        try  {
            String password = (String) modifyPasswordMap.get("password");
            String userName = (String) modifyPasswordMap.get("staffname");
            String strsql = "update staffinfo set PASSWARD='" + password + "' where staffname='" + userName + "'";
            stat = conn.prepareStatement(strsql);
            stat.executeUpdate();
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.info("�޸�Ա����������쳣: "+ex.getMessage());
        }
        finally  {
            try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
    }
    
    
    /**
     * ��ȡ����
     * @return int
     * @throws SQLException 
     * @throws BwayHibernateException
     */
    public String querySeq(String tmpTableName) throws BwayHibernateException  {
        String returnInt = "0";
        String strsql ="";
        try  {
        	strsql = "update makerconfig set currentvalue = currentvalue+1 where  tablename = '"+tmpTableName+"' ";
            stat = conn.prepareStatement(strsql);
            stat.executeUpdate();
        	strsql = "select currentvalue from makerconfig where  tablename = '"+tmpTableName+"' ";
            stat = conn.prepareStatement(strsql);
            ResultSet rs = stat.executeQuery();
            if (rs != null && rs.next()) {
                returnInt = rs.getString("currentvalue");
            }
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.info("��ȡ������ݿ��쳣: "+ex.getMessage());            
        }
        return returnInt;
    }
    
    /**
     * ��ѯԱ��
     * @param vo StaffInfoVO
     * @return List
     * @throws BwayHibernateException
     */
    public List getStaffInfo(StaffInfoVO vo) throws  BwayHibernateException  {
        List resultList = new ArrayList();
        StaffInfoVO resultVO = null;
        try {
            String strsql = "select * from staffinfo where 1=1 ";
            if (null != vo.getStaffname() && !vo.getStaffname().equals("")) {
                strsql += " and staffname = '" + vo.getStaffname() + "' ";
            }
            else if (null != vo.getStaffid() && !vo.getStaffid().equals("")) {
                strsql += " and staffid = '" + vo.getStaffid() + "' ";
            }
            strsql += " and passward = '" + vo.getPassward() + "' ";
            
            log.info(" 员工sql:   "+strsql);
            
            stat = conn.prepareStatement(strsql);
            rs = stat.executeQuery();
            while (rs != null && rs.next()) {
                resultVO = new StaffInfoVO();
                resultVO.setStaffid(rs.getString("staffid"));
                resultVO.setStaffname(rs.getString("staffname"));
                resultVO.setSex(rs.getString("sex"));
                resultVO.setPassward(rs.getString("passward"));
                resultVO.setStaffcnname(rs.getString("staffcnname"));
                resultVO.setPhone(rs.getString("phone"));
                resultVO.setEmail(rs.getString("email"));
                resultList.add(resultVO);
            }
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.info("��ѯԱ����ݿ��쳣: "+ex.getMessage());
        }
//        finally {
//            try {
//				rs.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//        }
        return resultList;
    }
    
    /**
     * �жϵ�ǰԱ���Ƿ���и�Ȩ��
     * @param staffIf String
     * @param rightId String
     * @return boolean
     * @throws BwayHibernateException
     */
    public boolean isAuth(String staffId, String rightId) throws BwayHibernateException  {
    	boolean ret = false;
        int returnInt = 0;
        try  {
            rightDao = new RightInfoDAO(conn);
            String id = rightDao.getListId("select roleid from staffrole where staffid='" + staffId + "'");
            String listId = rightDao.getListId("select rightid from rolesright where roleid in ("+id+")");
            String strsql = "select count(*) as count from rolesright where rightid in (" + listId +") and rightid = '" + rightId + "'";
            
            log.info("查看是否有权限 strsql:  "+strsql);
            
            stat = conn.prepareStatement(strsql);
            rs = stat.executeQuery();
            while (rs != null && rs.next()) {
                returnInt = rs.getInt("count");
            }
            if (returnInt <= 0) {
            	ret = false;
            } else  {
            	ret = true;
            }
        } catch (SQLException ex)  {
        	ex.printStackTrace();
            log.info("�жϵ�ǰԱ���Ƿ���и�Ȩ�޲����쳣:" + ex.getMessage());
        } finally {
            try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        return ret;
    }
    
	
}