cmsdbaccess.java

java 编写的程序

            cmsException = new CmsException(errorIn, CmsException.C_CLASSLOADER_ERROR);
            break;
        case 20030 :
            cmsException = new CmsException(errorIn, CmsException.C_SHORT_PASSWORD);
            break;
        case 20031 :
            cmsException = new CmsException(errorIn, CmsException.C_ACCESS_DENIED);
            break;
        case 20032 :
            cmsException = new CmsException(errorIn, CmsException.C_RESOURCE_DELETED);
            break;
        case 20033 :
            cmsException = new CmsException(errorIn, CmsException.C_RB_INIT_ERROR);
            break;
        case 20034 :
            cmsException = new CmsException(errorIn, CmsException.C_REGISTRY_ERROR);
            break;
        default :
            cmsException = new CmsException(errorIn + exc.getMessage(), CmsException.C_SQL_ERROR, exc);
            break;
    }
    return cmsException;
}
/**
 * retrieve the correct instance of the queries holder.
 * This method should be overloaded if other query strings should be used.
 */
protected com.opencms.file.genericSql.CmsQueries getQueries()
{
    return new com.opencms.file.oracleplsql.CmsQueries();
}

    /**
     * Gets all users of a type.
     *
     * @param type The type of the user.
     * @exception thorws CmsException if something goes wrong.
     */
    public Vector getUsers(int type)
        throws CmsException {
        //System.out.println("PL/SQL: getUsers");
        Vector users = new Vector();
        PreparedStatement statement = null;
        Connection con = null;
        ResultSet res = null;
        try {
                        con = DriverManager.getConnection(m_poolName);
            statement = con.prepareStatement(m_cq.get("C_USERS_GETUSERS"));
            statement.setInt(1,type);
            res = statement.executeQuery();
            // create new Cms user objects
            while( res.next() ) {
                // read the additional infos.
                oracle.sql.BLOB blob = ((OracleResultSet)res).getBLOB(m_cq.get("C_USERS_USER_INFO"));
                                byte[] value = new byte[(int) blob.length()];
                value = blob.getBytes(1, (int) blob.length());
                // now deserialize the object
                ByteArrayInputStream bin= new ByteArrayInputStream(value);
                ObjectInputStream oin = new ObjectInputStream(bin);
                Hashtable info=(Hashtable)oin.readObject();
                CmsUser user = new CmsUser(res.getInt(m_cq.get("C_USERS_USER_ID")),
                                           res.getString(m_cq.get("C_USERS_USER_NAME")),
                                           res.getString(m_cq.get("C_USERS_USER_PASSWORD")),
                                           res.getString(m_cq.get("C_USERS_USER_RECOVERY_PASSWORD")),
                                           res.getString(m_cq.get("C_USERS_USER_DESCRIPTION")),
                                           res.getString(m_cq.get("C_USERS_USER_FIRSTNAME")),
                                           res.getString(m_cq.get("C_USERS_USER_LASTNAME")),
                                           res.getString(m_cq.get("C_USERS_USER_EMAIL")),
                                           SqlHelper.getTimestamp(res,m_cq.get("C_USERS_USER_LASTLOGIN")).getTime(),
                                           SqlHelper.getTimestamp(res,m_cq.get("C_USERS_USER_LASTUSED")).getTime(),
                                           res.getInt(m_cq.get("C_USERS_USER_FLAGS")),
                                           info,
                                           new CmsGroup(res.getInt(m_cq.get("C_GROUPS_GROUP_ID")),
                                                        res.getInt(m_cq.get("C_GROUPS_PARENT_GROUP_ID")),
                                                        res.getString(m_cq.get("C_GROUPS_GROUP_NAME")),
                                                        res.getString(m_cq.get("C_GROUPS_GROUP_DESCRIPTION")),
                                                        res.getInt(m_cq.get("C_GROUPS_GROUP_FLAGS"))),
                                           res.getString(m_cq.get("C_USERS_USER_ADDRESS")),
                                           res.getString(m_cq.get("C_USERS_USER_SECTION")),
                                           res.getInt(m_cq.get("C_USERS_USER_TYPE")));
                users.addElement(user);
            }
        } catch (SQLException e){
            throw new CmsException("["+this.getClass().getName()+"]"+e.getMessage(),CmsException.C_SQL_ERROR, e);
        } catch (Exception e) {
            throw new CmsException("["+this.getClass().getName()+"]", e);
        } finally {
            if (res != null) {
                try {
                    res.close();
                } catch (SQLException se) {
                }
            }
            if( statement != null) {
                try {
                    statement.close();
                } catch (SQLException exc) {
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException se) {
                }
            }
        }
        return users;
    }

     /**
     * Gets all users of a type and namefilter.
     *
     * @param type The type of the user.
     * @param namestart The namefilter
     * @exception thorws CmsException if something goes wrong.
     */
    public Vector getUsers(int type, String namefilter)
        throws CmsException {
        //System.out.println("PL/SQL: getUsers");
        Vector users = new Vector();
        Statement statement = null;
        Connection con = null;
        ResultSet res = null;

        try {
            con = DriverManager.getConnection(m_poolName);
            statement = con.createStatement();

            /*statement.setInt(1,type);
            statement.setString(2,namefilter+"%");
            res = statement.executeQuery();*/

            //res = statement.executeQuery("SELECT * FROM CMS_USERS,CMS_GROUPS where USER_TYPE = "+type+" and USER_DEFAULT_GROUP_ID = GROUP_ID and USER_NAME like '"+namefilter+"%' ORDER BY USER_NAME");
            res = statement.executeQuery(m_cq.get("C_USERS_GETUSERS_FILTER1")+type+
                                         m_cq.get("C_USERS_GETUSERS_FILTER2")+namefilter+
                                         m_cq.get("C_USERS_GETUSERS_FILTER3"));
            // create new Cms user objects
            while( res.next() ) {
                // read the additional infos.
                oracle.sql.BLOB blob = ((OracleResultSet)res).getBLOB(m_cq.get("C_USERS_USER_INFO"));
                byte[] value = new byte[(int) blob.length()];
                value = blob.getBytes(1, (int) blob.length());
                // now deserialize the object
                ByteArrayInputStream bin= new ByteArrayInputStream(value);
                ObjectInputStream oin = new ObjectInputStream(bin);
                Hashtable info=(Hashtable)oin.readObject();

                CmsUser user = new CmsUser(res.getInt(m_cq.get("C_USERS_USER_ID")),
                                           res.getString(m_cq.get("C_USERS_USER_NAME")),
                                           res.getString(m_cq.get("C_USERS_USER_PASSWORD")),
                                           res.getString(m_cq.get("C_USERS_USER_RECOVERY_PASSWORD")),
                                           res.getString(m_cq.get("C_USERS_USER_DESCRIPTION")),
                                           res.getString(m_cq.get("C_USERS_USER_FIRSTNAME")),
                                           res.getString(m_cq.get("C_USERS_USER_LASTNAME")),
                                           res.getString(m_cq.get("C_USERS_USER_EMAIL")),
                                           SqlHelper.getTimestamp(res,m_cq.get("C_USERS_USER_LASTLOGIN")).getTime(),
                                           SqlHelper.getTimestamp(res,m_cq.get("C_USERS_USER_LASTUSED")).getTime(),
                                           res.getInt(m_cq.get("C_USERS_USER_FLAGS")),
                                           info,
                                           new CmsGroup(res.getInt(m_cq.get("C_GROUPS_GROUP_ID")),
                                                        res.getInt(m_cq.get("C_GROUPS_PARENT_GROUP_ID")),
                                                        res.getString(m_cq.get("C_GROUPS_GROUP_NAME")),
                                                        res.getString(m_cq.get("C_GROUPS_GROUP_DESCRIPTION")),
                                                        res.getInt(m_cq.get("C_GROUPS_GROUP_FLAGS"))),
                                           res.getString(m_cq.get("C_USERS_USER_ADDRESS")),
                                           res.getString(m_cq.get("C_USERS_USER_SECTION")),
                                           res.getInt(m_cq.get("C_USERS_USER_TYPE")));

                users.addElement(user);
            }

            //res.close();
        } catch (SQLException e){
            throw new CmsException("["+this.getClass().getName()+"]"+e.getMessage(),CmsException.C_SQL_ERROR, e);
        } catch (Exception e) {
            throw new CmsException("["+this.getClass().getName()+"]", e);
        } finally {
            if (res != null) {
                try {
                    res.close();
                } catch (SQLException se) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException exc) {
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException se) {
                }
            }
        }
        return users;
    }
/**
 * Returns a list of users of a group.<P/>
 *
 * @param name The name of the group.
 * @param type the type of the users to read.
 * @return Vector of users
 * @exception CmsException Throws CmsException if operation was not succesful
 */
public Vector getUsersOfGroup(CmsUser currentUser, String name, int type) throws CmsException {
    //System.out.println("PL/SQL: getUsersOfGroup");
    com.opencms.file.oracleplsql.CmsQueries cq = (com.opencms.file.oracleplsql.CmsQueries) m_cq;
    CmsGroup group;
    Vector users = new Vector();
    CallableStatement statement = null;
    Connection con = null;
    ResultSet res = null;
    try {
        con = DriverManager.getConnection(m_poolName);
        statement = con.prepareCall(cq.get("C_PLSQL_GROUPS_GETUSERSOFGROUP"));
        statement.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
        statement.setInt(2, currentUser.getId());
        statement.setString(3, name);
        statement.setInt(4, type);
        statement.execute();
        res = (ResultSet) statement.getObject(1);
        while (res.next()) {
            // read the additional infos.
            oracle.sql.BLOB blob = ((OracleResultSet)res).getBLOB(m_cq.get("C_USERS_USER_INFO"));
            byte[] value = new byte[(int) blob.length()];
            value = blob.getBytes(1, (int) blob.length());
            // now deserialize the object
            ByteArrayInputStream bin = new ByteArrayInputStream(value);
            ObjectInputStream oin = new ObjectInputStream(bin);
            Hashtable info = (Hashtable) oin.readObject();
            CmsUser user = new CmsUser(res.getInt(m_cq.get("C_USERS_USER_ID")),
                                       res.getString(m_cq.get("C_USERS_USER_NAME")),
                                       res.getString(m_cq.get("C_USERS_USER_PASSWORD")),
                                       res.getString(m_cq.get("C_USERS_USER_RECOVERY_PASSWORD")),
                                       res.getString(m_cq.get("C_USERS_USER_DESCRIPTION")),
                                       res.getString(m_cq.get("C_USERS_USER_FIRSTNAME")),
                                       res.getString(m_cq.get("C_USERS_USER_LASTNAME")),
                                       res.getString(m_cq.get("C_USERS_USER_EMAIL")),
                                       SqlHelper.getTimestamp(res, m_cq.get("C_USERS_USER_LASTLOGIN")).getTime(),
                                       SqlHelper.getTimestamp(res, m_cq.get("C_USERS_USER_LASTUSED")).getTime(),
                                       res.getInt(m_cq.get("C_USERS_USER_FLAGS")), info,
                                       new CmsGroup(res.getInt(m_cq.get("C_USERS_USER_DEFAULT_GROUP_ID")),
                                       res.getInt(m_cq.get("C_GROUPS_PARENT_GROUP_ID")),
                                       res.getString(m_cq.get("C_GROUPS_GROUP_NAME")),
                                       res.getString(m_cq.get("C_GROUPS_GROUP_DESCRIPTION")),
                                       res.getInt(m_cq.get("C_GROUPS_GROUP_FLAGS"))),
                                       res.getString(m_cq.get("C_USERS_USER_ADDRESS")),
                                       res.getString(m_cq.get("C_USERS_USER_SECTION")),
                                       res.getInt(m_cq.get("C_USERS_USER_TYPE")));
            users.addElement(user);
        }
    } catch (SQLException sqlexc) {
        CmsException cmsException = getCmsException("[" + this.getClass().getName() + "] ", sqlexc);
        throw cmsException;
    } catch (Exception e) {
        throw new CmsException("[" + this.getClass().getName() + "]", e);
    } finally {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException se) {
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException exc) {
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException se) {
            }
        }
    }
    return users;
}

 /**
     * Gets all users with a certain Lastname.
     *
     * @param Lastname      the start of the users lastname
     * @param UserType      webuser or systemuser
     * @param UserStatus    enabled, disabled
     * @param wasLoggedIn   was the user ever locked in?
     * @param nMax          max number of results
     *
     * @return the users.
     *
     * @exception CmsException if operation was not successful.
     */
    public Vector getUsersByLastname(String lastname, int userType,
                                     int userStatus, int wasLoggedIn, int nMax)
                                     throws CmsException {
        Vector users = new Vector();
        PreparedStatement statement = null;
        ResultSet res = null;
        Connection con = null;
        int i = 0;
        // "" =  return (nearly) all users
        if(lastname == null) lastname = "";

        try {
            con = DriverManager.getConnection(m_poolName);
            //con = DriverManager.ge