accountdbutils.java

java 写的一个新闻发布系统

            query.append (FIELD_ACTIVATED_ACCOUNTS);
            query.append ("=1");
        } else {
            query.append (FIELD_ACTIVATED_ACCOUNTS);
            query.append ("=0");
        }

        query.append (" WHERE ");
        query.append (FIELD_ACCOUNT_USER_KEY);
        query.append ("='");
        query.append (account.getUserKey());
        query.append ("' AND ");
        query.append (FIELD_ACCOUNT_SITE_KEY);
        query.append ("='");
        query.append (account.getSiteKey());
        query.append ("'");

        Statement statement = null;
        try {
            statement = connection.createStatement();
            if (statement != null) {
                statement.execute (query.toString());
                result = true;
            }
        }
        catch (SQLException ex) {
            StringBuffer buffer =
                    new StringBuffer ("Cannot update the account for user [");
            buffer.append (account.getUserKey());
            buffer.append ("] on site [");
            buffer.append (account.getSiteKey());
            buffer.append ("]");


            throw new JahiaDatabaseException (buffer.toString(),
                 query.toString(), ex, JahiaDatabaseException.ERROR);
        }
        finally {
            query = null;
            closeStatement (statement);
            closeDBConnection (connection);
        }

        return result;
    }


    //-------------------------------------------------------------------------
    // Foux     17 Apr. 2001
    /**
     * Return all (enabled and disabled) accounts according to the site ID.
     *
     * @param   siteID
     *      The unique site identification number.
     * @param   activation
     *      For the given site key, specified -1 to get all the accounts, 0 to
     *      get all the disabled accounts and 1 to get all the enabled accounts.
     *
     * @return
     *      Return an AccountList of accounts. This AccountList is always
     *      non-null.
     *
     * @exception   JahiaDatabaseException
     *      This exception is thrown on any database failure.
     */
    public AccountList getAccountsList (String siteKey, int activation)
        throws  JahiaDatabaseException
    {
        AccountList result = new AccountList ();

                // get the DB connection
        Connection connection = getDBConnection();
        if (connection == null) {
            throw new JahiaDatabaseException (
                    "Accounts list loading process could not get a connection.",
                    JahiaDatabaseException.CRITICAL);
        }

        Statement statement = null;
        StringBuffer query = new StringBuffer ("");

        try {
            query.append ("SELECT ");
            query.append (FIELD_ACCOUNT_USER_KEY);
            query.append (",");
            query.append (FIELD_CREATION_DATE_ACCOUNTS);
            query.append (",");
            query.append (FIELD_EXPIRATION_DATE_ACCOUNTS);
            query.append (",");
            query.append (FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
            query.append (",");
            query.append (FIELD_LAST_LOGIN_DATE_ACCOUNTS);

            if (activation < 0) {
                query.append (",");
                query.append (FIELD_ACTIVATED_ACCOUNTS);
            }

            query.append (" FROM ");
            query.append (JAHIA_ACCOUNTS);
            query.append (" WHERE ");
            query.append (FIELD_ACCOUNT_SITE_KEY);
            query.append ("='");
            query.append (siteKey);
            query.append ("'");

            if (activation >= 0) {
                query.append (" AND ");
                query.append (FIELD_ACTIVATED_ACCOUNTS);
                query.append ("=");
                query.append (activation>0?1:0);
            }

            statement = connection.createStatement();
            if (statement != null) {
                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery (statement, query.toString());
                if (rs != null) {
                    while (rs.next()) {

                            String userKey = rs.getString (FIELD_ACCOUNT_USER_KEY);
                            if (userKey == null) {
                                throw new JahiaDatabaseException ("Unallowed null value for column ["+
                                    FIELD_ACCOUNT_USER_KEY+"] in table ["+JAHIA_ACCOUNTS+"].",
                                    JahiaDatabaseException.ERROR);
                            }

                            Date creationDate = extractDate (rs,
                                    FIELD_CREATION_DATE_ACCOUNTS);
                            Date expirationDate = extractDate (rs,
                                    FIELD_EXPIRATION_DATE_ACCOUNTS);
                            Date pwdExpirationDate = extractDate (rs,
                                    FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
                            Date lastLoginDate = extractDate (rs,
                                    FIELD_LAST_LOGIN_DATE_ACCOUNTS);

                            boolean activated = false;
                            if (activation < 0) {
                                activated = (rs.getInt (FIELD_ACTIVATED_ACCOUNTS) == 1);
                            } else {
                                activated = activation == 1;
                            }

                            Account account = new Account (userKey, siteKey,
                                creationDate, expirationDate, pwdExpirationDate,
                                lastLoginDate, activated);

                        result.add (account);
                    } // while
                    rs = null;
                }
            }
        } catch (SQLException ex) {
            throw new JahiaDatabaseException ("Cannot load the accounts for site ["+
                siteKey + "]", query.toString(), ex,
                JahiaDatabaseException.ERROR);
        }
        finally {
            query = null;
            closeDBConnection (connection);
            closeStatement (statement);
        }

        return result;
    }


    //-------------------------------------------------------------------------
    // Foux     17 Apr. 2001
    /**
     * Return all (enabled and disabled) accounts for the specified user.
     *
     * @param   userKey
     *      The unique user identification key.
     *
     * @return
     *      Return an AccountList of accounts. This AccountList is always
     *      non-null.
     *
     * @exception   JahiaDatabaseException
     *      This exception is thrown on any database failure.
     */
    public AccountList getUserAccounts (String userKey)
        throws  JahiaDatabaseException
    {
                // get the DB connection
        Connection connection = getDBConnection();
        if (connection == null) {
            throw new JahiaDatabaseException (
                    "Accounts list loading process could not get a connection.",
                    JahiaDatabaseException.CRITICAL);
        }

        AccountList result = new AccountList ();
        Statement statement = null;
        StringBuffer query = new StringBuffer ("");

        try {
            query.append ("SELECT ");
            query.append (FIELD_ACCOUNT_SITE_KEY);
            query.append (",");
            query.append (FIELD_CREATION_DATE_ACCOUNTS);
            query.append (",");
            query.append (FIELD_EXPIRATION_DATE_ACCOUNTS);
            query.append (",");
            query.append (FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
            query.append (",");
            query.append (FIELD_LAST_LOGIN_DATE_ACCOUNTS);
            query.append (",");
            query.append (FIELD_ACTIVATED_ACCOUNTS);

            query.append (" FROM ");
            query.append (JAHIA_ACCOUNTS);

            query.append (" WHERE ");
            query.append (FIELD_ACCOUNT_USER_KEY);
            query.append ("='");
            query.append (userKey);
            query.append ("'");

            statement = connection.createStatement();
            if (statement != null) {
                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery (statement, query.toString());
                if (rs != null) {
                    while (rs.next()) {
                            String siteKey = rs.getString (FIELD_ACCOUNT_SITE_KEY);

                            Date creationDate = extractDate (rs,
                                    FIELD_CREATION_DATE_ACCOUNTS);
                            Date expirationDate = extractDate (rs,
                                    FIELD_EXPIRATION_DATE_ACCOUNTS);
                            Date pwdExpirationDate = extractDate (rs,
                                    FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
                            Date lastLoginDate = extractDate (rs,
                                    FIELD_LAST_LOGIN_DATE_ACCOUNTS);

                            boolean activated = (rs.getInt (FIELD_ACTIVATED_ACCOUNTS) == 1);

                            Account account = new Account (userKey, siteKey,
                                creationDate, expirationDate, pwdExpirationDate,
                                lastLoginDate, activated);

                        result.add (account);
                    } // while
                    rs = null;
                }
            }
        } catch (SQLException ex) {
            throw new JahiaDatabaseException ("Cannot load the accounts for user ["+
                userKey + "]", query.toString(), ex,
                JahiaDatabaseException.ERROR);
        }
        finally {
            query = null;
            closeDBConnection (connection);
            closeStatement (statement);
        }

        return result;
    }


    //-------------------------------------------------------------------------
    // Foux     17 Apr. 2001
    /** This method try to get a database connection from the DB Pool Connection
     * service. If not connection could be taken, or if any database error
     * occured, null will be returned. Otherwise the reference on a valid
     * connection is returned.
     *
     * @return
     *      Return a connection to the database. return <code>null</code> if no
     *      connection could be obtained from the database connection pool.
     */
    private Connection getDBConnection ()
        throws  JahiaDatabaseException
    {
        Connection connection = null;

        try {
            connection = ServicesRegistry.getInstance().
                                          getDBPoolService().
                                          getConnection (10000);
        }
        catch (SQLException ex) {
            throw new JahiaDatabaseException (
                "SQL Exception: cannot get a connection.", ex,
                JahiaDatabaseException.CRITICAL);
        }

        return connection;
    }


    //-------------------------------------------------------------------------
    // Foux     17 Apr. 2001
    /**
     * Release the specified database connection
     */
    private void closeDBConnection (Connection connection)
    {
        if (connection != null) {
            try {
                ServicesRegistry.getInstance().getDBPoolService().
                        freeConnection (connection);
                connection = null;
            }
            catch (SQLException sqlEx) {
                // just create an exception without raising it, just to notify
                // it in the logs.
                JahiaDatabaseException ex = new JahiaDatabaseException (
                        "Cannot free resources", sqlEx,
                        JahiaDatabaseException.WARNING);
            }
            catch (NullPointerException ex) {
                JahiaConsole.println ("JahiaPagesDB",
                    "Null Pointer Exception, DB Pool Service instance might be null!");
            }
        }
    }

    //-------------------------------------------------------------------------
    // Foux     17 Apr. 2001
    /**
     * Close the specified SQL statement.
     */
    private void closeStatement (Statement statement)
    {
        // Close the opened statement
        try {
            if (statement!=null) {
                statement.close();
                statement = null;
            }
        }
        catch (SQLException sqlEx) {
            // just create an exception without raising it, just to notify it
            // in the logs.
            JahiaDatabaseException ex = new JahiaDatabaseException (
                    "Cannot close a statement", sqlEx,
                    JahiaDatabaseException.WARNING);
        }
    }

    //-------------------------------------------------------------------------
    // Fulco    18 Apr. 2001
    /**
     * Extract the creation date from the ResultSet.
     *
     * @param   rs
     *      Result set from where the creation date has to be extracted.
     * @param   columnName
     *      Column name from where the date as to be extracted.
     *
     * @return
     *      Return the creation date or <code>null</code> if the date in the
     *      database is empty.
     *
     * @exception   SQLException
     *      Throw this exception on any database failure.
     */
    private Date extractDate (ResultSet rs, String columnName)
        throws  SQLException
    {
        Date date = null;

        String dateTime = rs.getString (columnName);
        if (!NULL_STRING.equals(dateTime)) {
            date = new Date();
            date.setTime (Long.parseLong(dateTime));
        }
        dateTime = null;
        return date;
    }

    //-------------------------------------------------------------------------
    // Fulco    18 Apr. 2001
    /**
     * Convert the specified date into a string format.
     *
     * @param   date
     *      Date to be converted.
     * @return
     *      Return a string representation of the passed date.
     */
    private String processDate (Date date)
    {
        String result = NULL_STRING;

        if (date != null) {
            result = Long.toString(date.getTime());
        }

        return result;
    }
}