rightinfodao.java

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

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

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

import cn.bway.admin.vo.LabelVO;
import cn.bway.admin.vo.RightsVO;
import cn.bway.admin.vo.RoleAndRightVO;
import cn.bway.admin.vo.StaffInfoVO;
import cn.bway.common.BwayHibernateException;

/**
 * @author Kson
 *
 */
public class RightInfoDAO {
	protected final Logger log = LogManager.getLogger(getClass());
	
    private Connection conn = null;
    public RightInfoDAO(Connection conn) {
        this.conn = conn;
    }
    private ResultSet rs = null;
    private PreparedStatement stat = null;

    /**
     * ��ѯָ���û����е�Ȩ��
     * @param vo StaffInfoVO
     * @return List
     * @throws BwayHibernateException
     */
    public Map queryRightInfoByStaff(StaffInfoVO vo) throws  BwayHibernateException
    {
        Map resultMap = new HashMap();
        List resultList = new ArrayList();
        RightsVO resultVO = null;
        String rightId = "";
        try {
            String strsql = "select * from rights where rightid in ( ";
            strsql += " select r1.rightid from rolesright r1, staffrole s1 where r1.roleid=s1.roleid and s1.staffid in ( ";
            strsql += " select staffid from staffinfo where staffname='"+vo.getStaffname()+"')) ";
            stat = conn.prepareStatement(strsql);
            
            log.info(" 员工权限sql:   "+strsql);
            
//            stat.setString(1, vo.getStaffname());
            rs = stat.executeQuery();
            while (rs != null && rs.next()) {
                resultVO = new RightsVO();
                resultVO.setRightid(rs.getString(1));
                resultVO.setRightname(rs.getString(2));
                resultVO.setRighturl(rs.getString(3));
                resultVO.setRightdesc(rs.getString(4));
                resultVO.setOrderid(new Integer(rs.getInt(5)));
                resultVO.setParentId(rs.getString(6));
                resultList.add(vo);
                rightId += rs.getString(1) + ", ";
            }
            resultMap.put("list", resultList);
            resultMap.put("rightIds", rightId);
            
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.info("��ѯָ���û����е�Ȩ�޲����쳣: "+ex.getMessage());
        }
//        finally {
//            try {
//				rs.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//        }
        return resultMap;
    }

    /**
     * ���ָ��Ȩ�޵IJ˵��б�
     * @param rightIds String
     * @return List
     * @throws BwayHibernateException
     */
    public List queryRightListByStaff(String rightIds) throws
        BwayHibernateException
    {
        List resultList = new ArrayList();
        LabelVO resultVO = null;
        try {
            String strsqlforright = "";
            String getList1 = getListId("select rr.label_id from rights rr where rr.rightid in (" + rightIds +")");
            String getList2 = getListId("select ll.parent_label_id from label ll where ll.label_id in ( "+ getList1 +")");            
            strsqlforright += "select distinct l.* from label l, rights r where l.label_id=r.label_id and r.rightid in (" +
            rightIds + ") ";
            strsqlforright += " union ";
            strsqlforright += " select * from label lll where lll.label_id in (" + getList2 +")";
            
            log.info(" 权限2 strsqlforright :   "+strsqlforright);
            
            stat = conn.prepareStatement(strsqlforright);
            rs = stat.executeQuery();
            while (rs.next()) {
                resultVO = new LabelVO();
                resultVO.setLabelId(rs.getString(1));
                resultVO.setLabelName(rs.getString(2));
                resultVO.setParentId(rs.getString(3));
                resultVO.setLevel(rs.getInt(4));
                resultVO.setOrderId(rs.getInt(5));
                resultVO.setImageUrl(rs.getString(6));
                resultVO.setMapUrl(rs.getString(7));
                resultList.add(resultVO);
            }
        } catch (SQLException ex) {
            log.info("��ѯָ���û����е�Ȩ�޲����쳣: "+ex.getMessage());
        }
//        finally {
//            try {
//				rs.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//        }
        return resultList;
    }


    /**
     * ��ݽ�ɫID��ѯ�ý�ɫ���е�Ȩ���б� added by liuwei 2006/08/20
     * @param qVO RoleAndRightVO
     * @return List
     * @throws BwayHibernateException
     */
    public List queryRightFromRoleId(RoleAndRightVO qVO) throws BwayHibernateException  {
        ArrayList arrayList = new ArrayList();
        RoleAndRightVO vo = null;
        try {
            String strsql = "";
            if (qVO.getRoleId() != null && qVO.getRoleId().length() > 0) {
//                strsql = "select * from rights where rightid in " +
//                		"(Select rr.rightid From rolesright rr Where rr.roleid = '" + qVO.getRoleId() + "')";
                
                strsql = "select l.label as rightname,r.rightid from rights r,label l where r.rightid in " +
        		"(Select rr.rightid From rolesright rr Where rr.roleid = '" + qVO.getRoleId() + "') and " +
        				" l.label_id=r.label_id order by orderbyid asc ";
            }            
            
            stat = conn.prepareStatement(strsql);
            rs = stat.executeQuery();
            while (rs.next()) {
                vo = new RoleAndRightVO();
                vo.setRightId(rs.getString("rightid"));
                vo.setRightName(rs.getString("rightname"));
                arrayList.add(vo);
            }
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.info("��ݽ�ɫID��ѯ�ý�ɫ���е�Ȩ���б��쳣: "+ex.getMessage());
        }
//        finally {
//            try {
//				rs.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//        }
        return arrayList;
    }

    /**
     * ��ݽ�ɫID��ѯ�ý�ɫ�����е�Ȩ���б� added by liuwei 2006/08/20
     * @param qVO RoleAndRightVO
     * @return List
     * @throws BwayHibernateException
     */
    public List queryRightExceptRoleId(RoleAndRightVO
               					qVO) throws    BwayHibernateException   {
    	
        ArrayList arrayList = new ArrayList();
        RoleAndRightVO vo = null;
        try {
            String strsql = "";
            if (qVO.getRoleId() != null && qVO.getRoleId().length() > 0) {
//                strsql = "select * from rights where rightid not in " +
//                		"(Select rr.rightid From rolesright rr Where rr.roleid = '" + qVO.getRoleId() + "')";
                
                strsql = "select l.label as rightname,r.rightid from rights r,label l where rightid not in " +
        		"(Select rr.rightid From rolesright rr Where rr.roleid = '" + qVO.getRoleId() + "') and " +
        				" l.label_id=r.label_id order by orderbyid ASC";
            }
            
            stat = conn.prepareStatement(strsql);
            rs = stat.executeQuery();
            while (rs.next()) {
                vo = new RoleAndRightVO();
                vo.setRightId(rs.getString("rightid"));
                vo.setRightName(rs.getString("rightname"));
                arrayList.add(vo);
            }
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.error("��ݽ�ɫID��ѯ�ý�ɫ�����Ȩ���б��쳣: " + ex.getMessage());
        }
//        finally {
//            try {
//				rs.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//        }
        return arrayList;
    }

    /**
     * ɾ��ԭ�н�ɫ��Ȩ�޵Ĺ�ϵ added by liuwei 2006/08/20
     * @param qvo RoleAndRightVO
     * @return int
     * @throws BwayHibernateException
     */
    public int deleteRoleAndRightRelation(RoleAndRightVO
    								qvo) throws BwayHibernateException  {
        int ret = 0;
        try {
            String strsql = "";
            if (qvo.getRoleId() != null && qvo.getRoleId().length() > 0) {
                strsql = "delete from rolesright  where roleid = '" + qvo.getRoleId() + "'";
            }
            
            log.info("删除员工权限sql:   "+strsql);
            
            stat = conn.prepareStatement(strsql);
            ret = stat.executeUpdate();
        } catch (SQLException ex) {
            log.error("ɾ��ԭ�н�ɫ��Ȩ�޵Ĺ�ϵ: "+ex.getMessage());
        }
//        finally {
//            try {
//            	if(null !=rs)
//				rs.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//        }
        return ret;
    }

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

    
    /**
     * �����ɫ��Ȩ�޵Ĺ�ϵ added by liuwei 2006/08/20
     * @param relationVo RoleAndRightVO
     * @return int
     * @throws BwayHibernateException
     */
    public int saveRoleAndRightRelation(RoleAndRightVO relationVo) throws BwayHibernateException
    {
        int ret = 0;
        try {
            String sql = "insert into rolesright values(" + "'" + relationVo.getRoleId() + "','" + relationVo.getRightId() + "')";
            
            log.info("保存员工权限sql:  "+sql);
            
            stat = conn.prepareStatement(sql);
            stat.executeUpdate();
            ret = 1;
        } catch (SQLException ex) {
        	ex.printStackTrace();
            log.error("�����ɫ��Ȩ�޵Ĺ�ϵ: "+ex.getMessage());
        }
//        finally {
//            try {
//            	if(null !=stat)
//				stat.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//        }
        return ret;
    }	
	
    //get staffInfo id
    public int getStaffInfoId(String strsql) throws BwayHibernateException, SQLException {
    	int ret = 0;
    	try{
    		stat = conn.prepareStatement(strsql);
    		rs = stat.executeQuery();
    		if(rs.next()){
    			ret = Integer.parseInt(rs.getString("staffid"));
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    		log.info("get the staffinfoid: "+e.getMessage());
    	}
//        finally {
//            try {
//				rs.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//        }
    	return ret;
    }
    
    // get string id
    public String getListId(String strSQL) throws BwayHibernateException {
    	String list = "";
    	try{
    		stat = conn.prepareStatement(strSQL);
    		rs = stat.executeQuery();
    		String a = "";
    		while(rs.next()){
    			a = rs.getString(1);
    			String b ="";
    			if(rs.isLast()){
    				b = a;
    			}else{
    				b = a + " , ";
    			}
    			list += b ;
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    		log.info("get the listId: "+e.getMessage());
    	}
//        finally {
//            try {
//				rs.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//        }
    	System.out.println("getListId list: "+list);
    	return list;
    }
    
    
    
}