userdbaccessor.java~2~

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

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;

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

    /**
     * 巜掕偟偨傾僇僂儞僩丄僷僗儚乕僪偱UserInfo傪庢摼偟傑偡丅
     * 儐乕僓偑懚嵼偟側偄応崌偼null傪曉偟傑偡丅
     *
     * @param conn     僨乕僞儀乕僗愙懕
     * @param account  傾僇僂儞僩
     * @param password 僷僗儚乕僪
     */
    public static UserInfo getUserInfo(Connection conn, String account,
                                       String password) throws SQLException {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT UserInfo.UserID     AS UserID    ,");
        sb.append("       UserInfo.Account    AS Account   ,");
        sb.append("       UserInfo.Password   AS Password  ,");
        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.Account ='" + StrUtil.dbFilter(account)  + "' AND ");
        if(password!=null){
            sb.append("      UserInfo.Password='" + StrUtil.dbFilter(password) + "' AND ");
        }
        sb.append("      UserInfo.GroupID = GroupInfo.GroupID");

        Statement stmt = conn.createStatement();
        ResultSet rs   = stmt.executeQuery(sb.toString());
        UserInfo info = null;
        if(rs.next()){
            //String account  = rs.getString("Account");
            if(password==null){
                password = rs.getString("Password");
            }
            long   userID    = rs.getLong("UserID");
            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; }

            info = new UserInfo(userID,account,password,groupID,groupName,
                                GroupType.toGroupType(groupType),
                                name,mail,tel,address,useFlag,addDate,repDate);
        }
        rs.close();
        stmt.close();

        return info;
    }

    /**
     * 巜掕偟偨傾僇僂儞僩偺儐乕僓忣曬傪庢摼偟傑偡丅
     *
     * @param conn    僨乕僞儀乕僗愙懕
     * @param account 傾僇僂儞僩
     */
    public static UserInfo getUserInfo(Connection conn,String account)
                                                       throws SQLException {
        return getUserInfo(conn,account,null);
    }

    /**
     * 巜掕偟偨儐乕僓ID偺儐乕僓忣曬傪庢摼偟傑偡丅
     *
     * @param conn   僨乕僞儀乕僗愙懕
     * @param userID 儐乕僓ID
     */
    public static UserInfo getUserInfo(Connection conn,long userID)
                                                       throws SQLException {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT UserInfo.UserID     AS UserID    ,");
        sb.append("       UserInfo.Account    AS Account   ,");
        sb.append("       UserInfo.Password   AS Password  ,");
        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.UserID  = " + userID  + " AND ");
        sb.append("      UserInfo.GroupID = GroupInfo.GroupID");

        Statement stmt = conn.createStatement();
        ResultSet rs   = stmt.executeQuery(sb.toString());
        UserInfo info = null;
        if(rs.next()){
            String account  = rs.getString("Account");
            String password = rs.getString("Password");
            //long   userID    = rs.getLong("UserID");
            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; }

            info = new UserInfo(userID,account,password,groupID,groupName,
                                GroupType.toGroupType(groupType),
                                name,mail,tel,address,useFlag,addDate,repDate);
        }
        rs.close();
        stmt.close();

        return info;

    }

    /**
     * 儐乕僓乕忣曬傪峏怴偟傑偡丅
     *
     * @param conn     僨乕僞儀乕僗愙懕
     * @param userID   儐乕僓ID
     * @param account  傾僇僂儞僩
     * @param password 僷僗儚乕僪
     * @param name     巵柤
     * @param groupID  僌儖乕僾ID
     * @param mail     儊乕儖傾僪儗僗
     * @param tel      揹榖斣崋
     * @param address  廧強
     * @param useFlag  巊梡嫋壜僼儔僌
     */
    public static void updateUser(Connection conn,
                                  long    userID,
                                  String  account,
                                  String  password,
                                  String  name,
                                  long    groupID,
                                  String  mail,
                                  String  tel,
                                  String  address,
                                  boolean useFlag) throws SQLException {
        int use = 0;
        if(useFlag == true){ use = 1; }

        StringBuffer sb = new StringBuffer();
        sb.append("UPDATE UserInfo SET ");
        sb.append("Account ='" + StrUtil.dbFilter(account)  + "',");
        sb.append("Password='" + StrUtil.dbFilter(password) + "',");
        sb.append("GroupID ='" + groupID                    + "',");
        sb.append("Mail    ='" + StrUtil.dbFilter(mail)     + "',");
        sb.append("Tel     ='" + StrUtil.dbFilter(tel)      + "',");
        sb.append("Address ='" + StrUtil.dbFilter(address)  + "',");
        sb.append("UseFlag = " + use                        + " ,");
        //sb.append("RepDate = SYSDATE() "); // Modified by Wang for Postgresql
        sb.append("RepDate = now() ");
        sb.append("WHERE UserID=" + userID);

        // 僩儔儞僓僋僔儑儞懳墳
        Statement stmt = conn.createStatement();
        try {
            stmt.executeUpdate(sb.toString());
        } catch(SQLException ex){
            conn.rollback();
            throw ex;
        } finally {
            stmt.close();
            conn.commit();
        }
    }

    /**
     * 儐乕僓乕忣曬傪捛壛偟傑偡丅
     *
     * @param conn     僨乕僞儀乕僗愙懕
     * @param account  傾僇僂儞僩
     * @param password 僷僗儚乕僪
     * @param name     巵柤
     * @param groupID  僌儖乕僾ID
     * @param mail     儊乕儖傾僪儗僗
     * @param tel      揹榖斣崋
     * @param address  廧強
     * @param useFlag  巊梡嫋壜僼儔僌
     */
    public static void insertUser(Connection conn,
                                  String  account,
                                  String  password,
                                  String  name,
                                  String  groupID,
                                  String  mail,
                                  String  tel,
                                  String  address,
                                  boolean useFlag) throws SQLException {
        int use = 0;
        if(useFlag == true){ use = 1; }

        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO UserInfo ");
        sb.append("(Account,Password,GroupID,Name,Mail,Tel,Address,UseFlag,AddDate,RepDate)");
        sb.append(" VALUES (");
        sb.append("'" + StrUtil.dbFilter(account)  + "',");
        sb.append("'" + StrUtil.dbFilter(password) + "',");
        sb.append("'" + StrUtil.dbFilter(groupID)  + "',");
        sb.append("'" + StrUtil.dbFilter(name)     + "',");
        sb.append("'" + StrUtil.dbFilter(mail)     + "',");
        sb.append("'" + StrUtil.dbFilter(tel)      + "',");
        sb.append("'" + StrUtil.dbFilter(address)  + "',");
        //sb.append(" " + use      + ",SYSDATE(),SYSDATE())"); // Modified by Wang for Postgresql
        sb.append(" " + use      + ",now(),now())");

        // 僩儔儞僓僋僔儑儞懳墳
        Statement stmt = conn.createStatement();
        try {
            stmt.executeUpdate(sb.toString());
        } catch(SQLException ex){
            conn.rollback();
            throw ex;
        } finally {
            stmt.close();
            conn.commit();
        }
    }

    /**
     * 慡偰偺儐乕僓忣曬傪攝楍偱庢摼偟傑偡丅
     *
     * @param conn 僨乕僞儀乕僗愙懕
     * @return 慡偰偺儐乕僓忣曬
     */
    public static UserInfo[] getAllUserInfo(Connection conn) throws SQLException {

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT UserInfo.UserID     AS UserID   ,");
        sb.append("       UserInfo.Account    AS Account  ,");
        sb.append("       UserInfo.Password   AS Password ,");
        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;
    }

}