dbaccessor.java~1~

来自「一个自己做的公司网站和办公职员管理系统。」· JAVA~1~ 代码 · 共 544 行 · 第 1/2 页

        sb.append("       UserInfo.GroupID    AS GroupID  ,");
        sb.append("       GroupInfo.GroupName AS GroupName,");
        sb.append("       GroupInfo.GroupType AS GroupType,");
        sb.append("       UserInfo.Name       AS Name     ,");
        sb.append("       UserInfo.Mail       AS Mail     ,");
        sb.append("       UserInfo.Tel        AS Tel      ,");
        sb.append("       UserInfo.Address    AS Address  ,");
        sb.append("       UserInfo.UseFlag    AS UseFlag  ,");
        sb.append("       UserInfo.AddDate    AS AddDate  ,");
        sb.append("       UserInfo.RepDate    AS RepDate   ");
        sb.append("FROM UserInfo,GroupInfo ");
        sb.append("WHERE UserInfo.GroupID=GroupInfo.GroupID ");
        sb.append("ORDER BY UserInfo.GroupID,UserInfo.Account");
        
        Vector v = new Vector();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        while(rs.next()){
            long   userID    = rs.getLong("UserID");
            String account   = rs.getString("Account");
            String password  = rs.getString("Password");
            long   groupID   = rs.getLong("GroupID");
            String groupName = rs.getString("GroupName");
            int    groupType = rs.getInt("GroupType");
            String name      = rs.getString("Name");
            String mail      = rs.getString("Mail");
            String tel       = rs.getString("Tel");
            String address   = rs.getString("Address");
            int    use       = rs.getInt("UseFlag");
            Date   addDate   = rs.getTimestamp("AddDate");
            Date   repDate   = rs.getTimestamp("RepDate");
            
            boolean useFlag = false;
            if(use==1){ useFlag = true; }
            
            v.add(new UserInfo(userID,account,password,groupID,groupName,
                               GroupType.toGroupType(groupType),
                               name,mail,tel,address,useFlag,addDate,repDate));
        }
        rs.close();
        stmt.close();
        
        UserInfo[] info = new UserInfo[v.size()];
        for(int i=0;i<v.size();i++){
            info[i] = (UserInfo)v.get(i);
        }
        return info;
    }
    
    /**
     * 巜掕偟偨僌儖乕僾ID偺僌儖乕僾忣曬傪庢摼偟傑偡丅
     * 
     * @param conn    僨乕僞儀乕僗愙懕
     * @param groupID 僌儖乕僾ID
     * @return 僌儖乕僾忣曬
     */
    public static GroupInfo getGroupInfo(Connection conn,long groupID) throws SQLException {
        
        StringBuffer sb = new StringBuffer();
        
        sb.append("SELECT GroupID   ,");
        sb.append("       GroupName ,");
        sb.append("       GroupType ,");
        sb.append("       UseFlag   ,");
        sb.append("       AddDate   ,");
        sb.append("       RepDate    ");
        sb.append("FROM GroupInfo ");
        sb.append("WHERE GroupID=" + groupID);
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        GroupInfo group = null;
        if(rs.next()){
            String groupName = rs.getString("GroupName");
            int    groupType = rs.getInt("GroupType");
            int    use       = rs.getInt("UseFlag");
            Date   addDate   = rs.getTimestamp("AddDate");
            Date   repDate   = rs.getTimestamp("RepDate");
            
            boolean useFlag = false;
            if(use==1){ useFlag = true; }
            
            group = new GroupInfo(groupID,groupName,GroupType.toGroupType(groupType),
                                  useFlag,addDate,repDate);
        }
        rs.close();
        stmt.close();
        
        return group;
    }
    
    /**
     * 搊榐偝傟偰偄傞慡偰偺僌儖乕僾偺僌儖乕僾忣曬傪庢摼偟傑偡丅
     * 
     * @param conn 僨乕僞儀乕僗愙懕
     * @return 慡偰偺僌儖乕僾忣曬
     */
    public static GroupInfo[] getAllGroupInfo(Connection conn) throws SQLException {
        
        StringBuffer sb = new StringBuffer();
        
        sb.append("SELECT GroupID   ,");
        sb.append("       GroupName ,");
        sb.append("       GroupType ,");
        sb.append("       UseFlag   ,");
        sb.append("       AddDate   ,");
        sb.append("       RepDate    ");
        sb.append("FROM GroupInfo ");
        sb.append("ORDER BY GroupID");
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sb.toString());
        Vector v = new Vector();
        
        while(rs.next()){
            long   groupID   = rs.getLong("GroupID");
            String groupName = rs.getString("GroupName");
            int    groupType = rs.getInt("GroupType");
            int    use       = rs.getInt("UseFlag");
            Date   addDate   = rs.getTimestamp("AddDate");
            Date   repDate   = rs.getTimestamp("RepDate");
            
            boolean useFlag = false;
            if(use==1){ useFlag = true; }
            
            v.add(new GroupInfo(groupID,groupName,GroupType.toGroupType(groupType),
                                useFlag,addDate,repDate));
        }
        rs.close();
        stmt.close();
        
        GroupInfo[] group = new GroupInfo[v.size()];
        for(int i=0;i<v.size();i++){
            group[i] = (GroupInfo)v.get(i);
        }
        
        return group;
    }
    
    /**
     * 僌儖乕僾傪捛壛偟傑偡丅
     * 
     * @param conn      僨乕僞儀乕僗愙懕
     * @param groupID   僌儖乕僾ID
     * @param groupName 僌儖乕僾柤
     * @param groupType 僌儖乕僾庬暿
     */
    public static void insertGroup(Connection conn,String groupName,
                                   int groupType,boolean useFlag) throws SQLException, ApplicationException {
        // 摨偠ID偑側偄偐傪妋擣
        /*
        StringBuffer sb2 = new StringBuffer();
        sb2.append("SELECT COUNT(*) AS Count FROM GroupInfo ");
        sb2.append("WHERE GroupID='"+StrUtil.dbFilter(groupID)+"'");
        Statement checkStmt = conn.createStatement();
        ResultSet checkRs = checkStmt.executeQuery(sb2.toString());
        int count = 0;
        if(checkRs.next()){
            count = checkRs.getInt("Count");
        }
        checkRs.close();
        checkStmt.close();
        if(count!=0){
            throw new ApplicationException("擖椡偝傟偨僌儖乕僾ID偼婛偵搊榐偝傟偰偄傑偡丄",
                                           "javascript:history.back()");
        }
        */
        int use = 0;
        if(useFlag){ use = 1; }
        
        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO GroupInfo ");
        sb.append("(GroupName,GroupType,UseFlag,AddDate,RepDate)");
        sb.append(" VALUES (");
        sb.append(" '" + StrUtil.dbFilter(groupName) + "',");
        sb.append("  " + groupType                   + " ,");
        sb.append("  " + use                         + " ,");
        sb.append("  SYSDATE(),SYSDATE())");
        
        //System.out.println(sb.toString());
        
        // 僩儔儞僓僋僔儑儞懳墳
        Statement stmt = conn.createStatement();
        try {
            stmt.executeUpdate(sb.toString());
        } catch(SQLException ex){
            conn.rollback();
            throw ex;
        } finally {
            stmt.close();
            conn.commit();
        }
    }
    
    /**
     * 僌儖乕僾傪峏怴偟傑偡丅
     * 
     * @param conn      僨乕僞儀乕僗愙懕
     * @param groupID   僌儖乕僾ID
     * @param groupName 僌儖乕僾柤
     * @param groupType 僌儖乕僾庬暿
     */
    public static void updateGroup(Connection conn,long groupID,String groupName,
                                   int groupType,boolean useFlag) throws SQLException {
        
        int use = 0;
        if(useFlag){ use = 1; }
        
        StringBuffer sb = new StringBuffer();
        sb.append("UPDATE GroupInfo SET ");
        sb.append("GroupName ='" + StrUtil.dbFilter(groupName) + "',");
        sb.append("GroupType = " + groupType                   + " ,");
        sb.append("UseFlag   = " + use                         + " ,");
        sb.append("RepDate   = SYSDATE() ");
        sb.append("WHERE GroupID=" + groupID);
        
        // 僩儔儞僓僋僔儑儞懳墳
        Statement stmt = conn.createStatement();
        try {
            stmt.executeUpdate(sb.toString());
        } catch(SQLException ex){
            conn.rollback();
            throw ex;
        } finally {
            stmt.close();
            conn.commit();
        }
    }
    
    /**
     * 僌儖乕僾傪嶍彍偟傑偡丅
     * 
     * @param conn    僨乕僞儀乕僗愙懕
     * @param groupID 僌儖乕僾ID
     */
//    public static void deleteGroup(Connection conn,String groupID) throws SQLException, ApplicationException {
//        
//        // 儐乕僓偑搊榐偝傟偰偄側偄偐妋擣
//        StringBuffer sb = new StringBuffer();
//        sb.append("SELECT COUNT(*) AS Count ");
//        sb.append("FROM UserInfo WHERE GroupID='"+StrUtil.dbFilter(groupID)+"'");
//        
//        Statement stmt = conn.createStatement();
//        ResultSet rs = stmt.executeQuery(sb.toString());
//        int count = 0;
//        if(rs.next()){
//            count = rs.getInt("Count");
//        }
//        rs.close();
//        stmt.close();
//        
//        if(count!=0){
//            throw new ApplicationException("儐乕僓偑搊榐偝傟偰偄傞偨傔嶍彍偱偒傑偣傫丅",
//                                           "javascript:history.back()");
//        }
//        
//        // 僩儔儞僓僋僔儑儞懳墳
//        String sql = "DELETE FROM GroupInfo WHERE GroupID='"+StrUtil.dbFilter(groupID)+"'";
//        Statement stmt2 = conn.createStatement();
//        
//        try {
//            stmt2.executeQuery(sql);
//        } catch(SQLException ex){
//            conn.rollback();
//            throw ex;
//        } finally {
//            stmt2.close();
//            conn.commit();
//        }
//    }
}