cmsdbaccess.java

内容管理

            }
            if (con != null) {
                try {
                    con.setAutoCommit(true);
                } catch (SQLException se) {
                }
                try {
                    con.close();
                } catch (SQLException e) {
                }
            }
        }
        return readUser(id);
    }

    /**
     * Adds a user to the database.
     *
     * @param name username
     * @param password user-password
     * @param recoveryPassword user-recoveryPassword
     * @param description user-description
     * @param firstname user-firstname
     * @param lastname user-lastname
     * @param email user-email
     * @param lastlogin user-lastlogin
     * @param lastused user-lastused
     * @param flags user-flags
     * @param additionalInfos user-additional-infos
     * @param defaultGroup user-defaultGroup
     * @param address user-defauladdress
     * @param section user-section
     * @param type user-type
     *
     * @return the created user.
     * @throws thorws CmsException if something goes wrong.
     */
    public CmsUser addImportUser(String name, String password, String recoveryPassword, String description, String firstname, String lastname, String email, long lastlogin, long lastused, int flags, Hashtable additionalInfos, CmsGroup defaultGroup, String address, String section, int type) throws CmsException {
        int id = nextId(C_TABLE_USERS);
        byte[] value = null;
        PreparedStatement statement = null;
        PreparedStatement statement2 = null;
        PreparedStatement nextStatement = null;
        Connection con = null;
        ResultSet res = null;
        try {
            // serialize the hashtable
            ByteArrayOutputStream bout = new ByteArrayOutputStream();
            ObjectOutputStream oout = new ObjectOutputStream(bout);
            oout.writeObject(additionalInfos);
            oout.close();
            value = bout.toByteArray();

            // write data to database
            // first insert the data without user_info
            con = DriverManager.getConnection(m_poolName);
            statement = con.prepareStatement(m_cq.get("C_ORACLE_USERSFORINSERT"));
            statement.setInt(1, id);
            statement.setString(2, name);
            // crypt the password with MD5
            statement.setString(3, checkNull(password));
            statement.setString(4, checkNull(recoveryPassword));
            statement.setString(5, checkNull(description));
            statement.setString(6, checkNull(firstname));
            statement.setString(7, checkNull(lastname));
            statement.setString(8, checkNull(email));
            statement.setTimestamp(9, new Timestamp(lastlogin));
            statement.setTimestamp(10, new Timestamp(lastused));
            statement.setInt(11, flags);
            //statement.setBytes(12,value);
            statement.setInt(12, defaultGroup.getId());
            statement.setString(13, checkNull(address));
            statement.setString(14, checkNull(section));
            statement.setInt(15, type);
            statement.executeUpdate();
            statement.close();
            // now update user_info of the new user
            statement2 = con.prepareStatement(m_cq.get("C_ORACLE_USERSFORUPDATE"));
            statement2.setInt(1, id);
            con.setAutoCommit(false);
            res = statement2.executeQuery();
            while (res.next()) {
                oracle.sql.BLOB blob = ((OracleResultSet) res).getBLOB("USER_INFO");
                ByteArrayInputStream instream = new ByteArrayInputStream(value);
                OutputStream outstream = blob.getBinaryOutputStream();
                byte[] chunk = new byte[blob.getChunkSize()];
                int i = -1;
                while ((i = instream.read(chunk)) != -1) {
                    outstream.write(chunk, 0, i);
                }
                instream.close();
                outstream.close();
            }
            statement2.close();
            res.close();
            // for the oracle-driver commit or rollback must be executed manually
            // because setAutoCommit = false in CmsDbPool.CmsDbPool
            nextStatement = con.prepareStatement(m_cq.get("C_COMMIT"));
            nextStatement.execute();
            nextStatement.close();
            con.setAutoCommit(true);
        } catch (SQLException e) {
            throw new CmsException("[" + this.getClass().getName() + "]" + e.getMessage(), CmsException.C_SQL_ERROR, e);
        } catch (IOException e) {
            throw new CmsException("[CmsAccessUserInfoMySql/addUserInformation(id,object)]:" + CmsException.C_SERIALIZATION, e);
        } finally {
            if (res != null) {
                try {
                    res.close();
                } catch (SQLException se) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException exc){
                }
            }
            if (statement2 != null) {
                try {
                    statement2.close();
                } catch (SQLException exc){
                }
                try {
                    nextStatement = con.prepareStatement(m_cq.get("C_ROLLBACK"));
                    nextStatement.execute();
                } catch (SQLException se) {
                }
            }
            if (nextStatement != null) {
                try {
                    nextStatement.close();
                } catch (SQLException exc){
                }
            }
            if (con != null) {
                try {
                    con.setAutoCommit(true);
                } catch (SQLException se) {
                }
                try {
                    con.close();
                } catch (SQLException e) {
                }
            }
        }
        return readUser(id);
    }

// methods working with session-storage

    /**
     * This method creates a new session in the database. It is used
     * for sessionfailover.
     *
     * @param sessionId the id of the session.
     * @return data the sessionData.
     */
    public void createSession(String sessionId, Hashtable data) throws CmsException {
        byte[] value = null;
        PreparedStatement statement = null;
        PreparedStatement statement2 = null;
        PreparedStatement nextStatement = null;
        Connection con = null;
        ResultSet res = null;
        try {
            // serialize the hashtable
            ByteArrayOutputStream bout = new ByteArrayOutputStream();
            ObjectOutputStream oout = new ObjectOutputStream(bout);
            oout.writeObject(data);
            oout.close();
            value = bout.toByteArray();

            // write data to database
            con = DriverManager.getConnection(m_poolName);
            statement = con.prepareStatement(m_cq.get("C_ORACLE_SESSION_FORINSERT"));
            statement.setString(1, sessionId);
            statement.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));
            statement.executeUpdate();
            statement.close();
            statement2 = con.prepareStatement(m_cq.get("C_ORACLE_SESSION_FORUPDATE"));
            statement2.setString(1, sessionId);
            con.setAutoCommit(false);
            res = statement2.executeQuery();
            while (res.next()) {
                oracle.sql.BLOB blob = ((OracleResultSet) res).getBLOB("SESSION_DATA");
                ByteArrayInputStream instream = new ByteArrayInputStream(value);
                OutputStream outstream = blob.getBinaryOutputStream();
                byte[] chunk = new byte[blob.getChunkSize()];
                int i = -1;
                while ((i = instream.read(chunk)) != -1) {
                    outstream.write(chunk, 0, i);
                }
                instream.close();
                outstream.close();
            }
            statement2.close();
            res.close();
            // for the oracle-driver commit or rollback must be executed manually
            // because setAutoCommit = false
            nextStatement = con.prepareStatement(m_cq.get("C_COMMIT"));
            nextStatement.execute();
            nextStatement.close();
            con.setAutoCommit(true);
        } catch (SQLException e) {
            throw new CmsException("[" + this.getClass().getName() + "]" + e.getMessage(), CmsException.C_SQL_ERROR, e);
        } catch (IOException e) {
            throw new CmsException("[" + this.getClass().getName() + "]:" + CmsException.C_SERIALIZATION, e);
        } finally {
            if (res != null) {
                try {
                    res.close();
                } catch (SQLException se) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException exc) {
                }
            }
            if (statement2 != null) {
                try {
                    statement2.close();
                } catch (SQLException exc) {
                }
                try {
                    nextStatement = con.prepareStatement(m_cq.get("C_ROLLBACK"));
                    nextStatement.execute();
                } catch (SQLException se) {
                }
            }
            if (nextStatement != null) {
                try {
                    nextStatement.close();
                } catch (SQLException exc) {
                }
            }
            if (con != null) {
                try {
                    con.setAutoCommit(true);
                } catch (SQLException se) {
                }
                try {
                    con.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    /**
     * Returns all projects from the history.
     *
     *
     * @return a Vector of projects.
     */
     public Vector getAllBackupProjects() throws CmsException {
         Vector projects = new Vector();
         ResultSet res = null;
         PreparedStatement statement = null;
         Connection con = null;

         try {
             // create the statement
             con = DriverManager.getConnection(m_poolNameBackup);
             statement = con.prepareStatement(m_cq.get("C_ORACLE_PROJECTS_READLAST_BACKUP"));
             statement.setInt(1, 300);
             res = statement.executeQuery();
             while(res.next()) {
                 Vector resources = readBackupProjectResources(res.getInt("VERSION_ID"));
                 projects.addElement( new CmsBackupProject(res.getInt("VERSION_ID"),
                                                    res.getInt("PROJECT_ID"),
                                                    res.getString("PROJECT_NAME"),
                                                    SqlHelper.getTimestamp(res,"PROJECT_PUBLISHDATE"),
                                                    res.getInt("PROJECT_PUBLISHED_BY"),
                                                    res.getString("PROJECT_PUBLISHED_BY_NAME"),
                                                    res.getString("PROJECT_DESCRIPTION"),
                                                    res.getInt("TASK_ID"),
                                                    res.getInt("USER_ID"),
                                                    res.getString("USER_NAME"),
                                                    res.getInt("GROUP_ID"),
                                                    res.getString("GROUP_NAME"),
                                                    res.getInt("MANAGERGROUP_ID"),
                                                    res.getString("MANAGERGROUP_NAME"),
                                                    SqlHelper.getTimestamp(res,"PROJECT_CREATEDATE"),
                                                    res.getInt("PROJECT_TYPE"),
                                                    resources));
             }
         } catch( SQLException exc ) {
             throw new CmsException("[" + this.getClass().getName() + ".getAllBackupProjects()] " + exc.getMessage(),
                 CmsException.C_SQL_ERROR, exc);
         } finally {
            // close all db-resources
            if(res != null) {
                 try {
                     res.close();
                 } catch(SQLException exc) {
                     // nothing to do here
                 }
            }
            if(statement != null) {
                 try {
                     statement.close();
                 } catch(SQLException exc) {