roleinfodao.java

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

			}
        }
        return returnInt;
    }

    /**
     * ��ݽ�ɫID��ѯ�ý�ɫ���е�Ա���б� added by liuwei 2006/08/20
     * @param qVO RoleAndStaffVO
     * @return List
     * @throws BwayHibernateException
     */
    public List queryStaffFromRoleId(RoleAndStaffVO
                                     qVO) throws  BwayHibernateException  {
        ArrayList arrayList = new ArrayList();
        RoleAndStaffVO vo = null;
        try {
            String strsql = "";
            if (qVO.getRoleId() != null && qVO.getRoleId().length() > 0) {
                strsql = "select * from staffinfo where staffid in " +
                		"(Select Sr.staffid From staffrole Sr Where Sr.roleid = '" + qVO.getRoleId() + "')";
            }
            stat = conn.prepareStatement(strsql);
            rs = stat.executeQuery();
            while (rs.next()) {
                vo = new RoleAndStaffVO();
                vo.setStaffId(rs.getString("staffid"));
                vo.setStaffName(rs.getString("staffcnname"));
                arrayList.add(vo);
            }
        } catch (SQLException ex) {
        	ex.printStackTrace();
        	log.info("��ݽ�ɫID��ѯ�ý�ɫ���е�Ա���б��쳣:" + ex.getMessage());
        }
        finally {
        	try {
				rs.close();
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        return arrayList;
    }
    
    /**
     * ��ݽ�ɫID��ѯ�ý�ɫ�����е�Ա���б� added by liuwei 2006/08/20
     * @param qVO RoleAndStaffVO
     * @return List
     * @throws BwayHibernateException
     */
    public List queryStaffExceptRoleId(RoleAndStaffVO
                       qVO) throws  BwayHibernateException    {
        ArrayList arrayList = new ArrayList();
        RoleAndStaffVO vo = null;
        try {
            String strsql = "";
            if (qVO.getRoleId() != null && qVO.getRoleId().length() > 0) {
                strsql = "select * from staffinfo where staffid not in (" +
                "Select Sr.staffid From staffrole Sr Where Sr.roleid = '" + qVO.getRoleId() + "')";
            }
            stat = conn.prepareStatement(strsql);
            rs = stat.executeQuery();
            while (rs.next()) {
                vo = new RoleAndStaffVO();
                vo.setStaffId(rs.getString("staffid"));
                vo.setStaffName(rs.getString("staffcnname"));
                arrayList.add(vo);
            }
        } catch (SQLException ex) {
        	ex.printStackTrace();
        	log.info("��ݽ�ɫID��ѯ�ý�ɫ�����Ա���б��쳣: "+ ex.getMessage());
        }
        finally {
        	try {
				rs.close();
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        return arrayList;
    }

    /**
     * ɾ��ԭ�н�ɫ��Ա���Ĺ�ϵ added by liuwei 2006/08/20
     * @param qvo RoleAndStaffVO
     * @return int
     * @throws BwayHibernateException
     */
    public int deleteRoleAndStaffRelation(RoleAndStaffVO
                         qvo) throws BwayHibernateException {
        int ret = 0;
        try {
            String strsql = "";
            if (qvo.getRoleId() != null && qvo.getRoleId().length() > 0){
                strsql = "delete from staffrole where roleid = '" +  qvo.getRoleId() + "'";
            }
            
            stat = conn.prepareStatement(strsql);
            ret = stat.executeUpdate();
        } catch (SQLException ex){
        	ex.printStackTrace();
        	log.info("ɾ��ԭ�н�ɫ��Ա���Ĺ�ϵ: " + ex.getMessage());
        }
        finally {
        	try {
        		if(null !=rs)
				rs.close();
        		if(null !=stat)
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        return ret;
    }
    
    /**
     * ��ѯԭ�н�ɫ��Ա���Ĺ�ϵ added by liuwei 2006/08/20
     * @param qvo RoleAndStaffVO
     * @return int
     * @throws BwayHibernateException
     */
    public boolean selectRoleAndStaffRelation(RoleAndStaffVO
                         qvo) throws BwayHibernateException {
    	boolean ret = false;
        try {
            String strsql = "";
            if (qvo.getRoleId() != null && qvo.getRoleId().length() > 0){
                strsql = "select * from staffrole Where roleid = '" +  qvo.getRoleId() + "'";
            }
            
            stat = conn.prepareStatement(strsql);
            rs = stat.executeQuery();
            if(rs.next()){
            	ret = true;
            }
        } catch (SQLException ex){
        	ex.printStackTrace();
        	log.info("��ѯԭ�н�ɫ��Ա���Ĺ�ϵ: " + ex.getMessage());
        }
        finally {
        	try {
				rs.close();
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        return ret;
    }    

    /**
     * �����ɫ��Ա���Ĺ�ϵ added by liuwei 2006/08/20
     * @param relationVo RoleAndStaffVO
     * @return int
     * @throws BwayHibernateException
     */
    public int saveRoleAndStaffRelation(RoleAndStaffVO relationVo) throws
        BwayHibernateException   {
        int ret = 0;
        try {
            String sql = "insert into staffrole values("+ "'" + relationVo.getStaffId() + "','" + relationVo.getRoleId() + "')";
            
            stat = conn.prepareStatement(sql);
            stat.executeUpdate();
            ret = 1;
        } catch (SQLException ex) {
        	log.info("�����ɫ��Ա���Ĺ�ϵ:" + ex.getMessage());
        	ex.printStackTrace();
        }
        finally {
        	try {
				rs.close();
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        return ret;
    }


    /**
     * �õ�Ȩ����ϸ��Ϣ
     * @param qVO RoleInfoVO
     * @return RoleInfoVO
     * @throws BwayHibernateException
     */
    public RoleInfoVO queryRoleInfoVO(RoleInfoVO
                            qVO) throws BwayHibernateException {
        RoleInfoVO vo = null;
        try {
            String strsql = "";
            if (qVO.getRoleId() != null && qVO.getRoleId().length() > 0) {
                strsql = "select * from roles where roleid = '" + qVO.getRoleId() + "'";
            }
            stat = conn.prepareStatement(strsql);
            rs = stat.executeQuery();
            while (rs.next()) {
                vo = new RoleInfoVO();
                vo.setRoleName(rs.getString("rolename"));
                //toDo ����������
            }
        } catch (SQLException ex) {
            log.info("��ݽ�ɫID��ѯ�õõ�Ȩ����ϸ��Ϣ�쳣: " + ex.getMessage());
            ex.printStackTrace();
        }
        finally {
        	try {
				rs.close();
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        return vo;
    }
    
    /**
     * ��ȡ����
     * @return int
     * @throws PlatformException
     */
    public int querySeq() throws
        PlatformException
    {
        int returnInt = 0;
        try
        {
            String strsql = "select seq_roleid.nextval as seq from dual";
            stat = conn.prepareStatement(strsql);
            ResultSet rs = stat.executeQuery();
            while (rs != null && rs.next())
            {
                returnInt = rs.getInt("seq");
            }
        } catch (SQLException ex)  {
            throw new PlatformException("��ȡ���в����쳣��", ex);
        } finally   {
        	try {
				rs.close();
	        	stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        return returnInt;
    }    
    
}