groupdbaccessor.java

来自「一个自己做的公司网站和办公职员管理系统。」· Java 代码 · 共 251 行

package ws.woa.core;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.Date;

import ws.woa.util.StrUtil;

/**
 * 僐傾婡擻丄Group儌僕儏乕儖偺巊梡偡傞DB傾僋僙僒儊僜僢僪傪幚憰偟傑偡丅
 * 杮僋儔僗偱偼埲壓偺婡擻傪採嫙偟傑偡丅
 *
 * <UL>
 *   <LI>儐乕僓偺搊榐丄峏怴
 *   <LI>僌儖乕僾偺搊榐丄峏怴
 *   <LI>摿掕傕偟偔偼慡偰偺儐乕僓忣曬偺庢摼
 *   <LI>摿掕傕偟偔偼慡偰偺僌儖乕僾忣曬偺庢摼
 * </UL>
 *
 * @author Wang
 */
public class GroupDBAccessor {

    /**
     * 巜掕偟偨僌儖乕僾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())"); // Modified by wangxu for postgresql
        sb.append("  now(),now())");

        //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() "); // Modified by wangxu for postgresql
        sb.append("RepDate   = now() ");
        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();
//        }
//    }
}