jahiapageutilsdb.java

java 写的一个新闻发布系统

        try {
            String sqlQuery = "SELECT id_jahia_pages_data FROM jahia_pages_data "+
                              "WHERE parentid_jahia_pages_data="+pageID;

            statement = dbConn.createStatement();
            if (statement != null) {
                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery (statement,sqlQuery);
                if (rs != null) {
                    while (rs.next()) {
                        result.add (new Integer (rs.getInt ("id_jahia_pages_data")));
                    }
                }
            }
        } catch (SQLException se) {
            String errorMsg = "Error in getPageChildIDs : " + se.getMessage() + " -> BAILING OUT";
            JahiaConsole.println( "JahiaPagesDB", errorMsg );
            throw new JahiaException(   "Cannot load the page values from the database",
                                        errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL );
        }
        finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
        }

        return result;
    }


    //-------------------------------------------------------------------------
    // FH   2 May 2001
    /** Return the amount of pages in the database.
     *
     * @return
     *      The amount of page.
     */
    public synchronized int getNbPages ()
        throws  JahiaDatabaseException
    {
        return getNbPages(-1);
    }
    public synchronized int getRealNbPages ()
        throws  JahiaDatabaseException
    {
        return getRealNbPages(-1);
    }


    //-------------------------------------------------------------------------
    // NK   17 May 2001
    /** Return the amount of pages in the database.
     *
     * @return
     *      The amount of page.
     */
    public synchronized int getNbPages (int siteID)
        throws  JahiaDatabaseException
    {
        int counter = 0;

        Connection dbConn = getDBConnection (713705);
        if (dbConn != null)
        {
            String query = "";
            Statement statement = null;

            try {
                query = "SELECT COUNT(id_jahia_pages_data) as nbItems FROM jahia_pages_data";

                if ( siteID != -1 ){
                    query +=" where jahiaid_jahia_pages_data=" + siteID;
                }

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

                    if (rs != null) {
                        if (rs.next()) {
                            counter = rs.getInt ("nbItems");
                        }
                    }

                    // Result set not needed anymore.
                    rs = null;
                }
            }
            catch (SQLException ex) {
                throw new JahiaDatabaseException ("Database error.", ex,
                    JahiaDatabaseException.ERROR);
            }
            finally {
                query = null;
                closeDBConnection (dbConn);
                closeStatement (statement);
            }
        }
        return counter;
    }
    public synchronized int getRealNbPages (int siteID)
        throws  JahiaDatabaseException
    {
        int counter = 0;

        Connection dbConn = getDBConnection (713705);
        if (dbConn != null)
        {
            String query = "";
            Statement statement = null;

            try {
                query = "SELECT COUNT(id_jahia_pages_data) as nbItems FROM jahia_pages_data where pagetype_jahia_pages_data='0'";

                if ( siteID != -1 ){
                    query +=" and jahiaid_jahia_pages_data=" + siteID;
                }

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

                    if (rs != null) {
                        if (rs.next()) {
                            counter = rs.getInt ("nbItems");
                        }
                    }

                    // Result set not needed anymore.
                    rs = null;
                }
            }
            catch (SQLException ex) {
                throw new JahiaDatabaseException ("Database error.", ex,
                    JahiaDatabaseException.ERROR);
            }
            finally {
                query = null;
                closeDBConnection (dbConn);
                closeStatement (statement);
            }
        }
        return counter;
    }

    //--------------------------------------------------------------------------
    /**
     * return a DOM document of all pages of a site
     *
     * @param int the site id
     *
     * @return JahiaDOMObject a DOM representation of this object
     *
     * @author NK
     */
    public JahiaDOMObject getPagesAsDOM( int siteID ) throws JahiaException{

        Connection dbConn = null;
        Statement statement = null;

        String output = null;
        JahiaDBDOMObject dom = null;

        try {
            String sqlQuery = "SELECT * FROM jahia_pages_data where jahiaid_jahia_pages_data=" + siteID;

            dbConn = getDBConnection(0);
            statement = dbConn.createStatement();
            if (statement != null) {
                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery( statement,sqlQuery );
                if (rs != null) {
                    dom = new JahiaDBDOMObject();
                    dom.addTable("jahia_pages_data",rs);
                    return dom;
                }
            }
        } catch (SQLException se) {
            String errorMsg = "Error in getPagesAsDOM(int siteID) : " + se.getMessage();
            JahiaConsole.println( "JahiaPageDBUtilsDB", errorMsg + " -> B	AILING OUT" );
            throw new JahiaException(   "Cannot load pages from the database",
                                        errorMsg, JahiaException.DATABASE_ERROR,
                                        JahiaException.CRITICAL );
        } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
        }

        return dom;
    }


    /***
        * gets all acl ids used by pages of a site
        *
        * @return       a Vector of all acl id of a site
        *
        * @exception   throws a warning JahiaException if cannot free resources
        *
        */
    public Vector db_get_all_acl_id(int siteID)
    throws JahiaException
    {
        Connection dbConn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Vector theIDs = new Vector();
        try
        {
            String sqlQuery = "SELECT DISTINCT rights_jahia_pages_data FROM jahia_pages_data "
            +"WHERE jahiaid_jahia_pages_data="+siteID;

            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(60);
            stmt = dbConn.createStatement();
            rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery( stmt,sqlQuery );

            while (rs.next())
            {
                theIDs.add( new Integer(rs.getInt( "rights_jahia_pages_data" )) );
            }
        }
        catch (SQLException se)
        {
            String errorMsg = "Error in db_get_all_acl_id : " + se.getMessage();
            JahiaConsole.println ("JahiaPageUtilsDB", errorMsg + " -> BAILING OUT");
            throw new JahiaException ("Cannot load acl id from the database",
                                      errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL);
        }
        finally
        {
            try
            {
                ServicesRegistry.getInstance ().getDBPoolService ().freeConnection (dbConn);
                if ( stmt != null ) stmt.close();
            }
            catch ( SQLException ex )
            {
                JahiaException je = new JahiaException(   "Cannot free resources",
                                        "JahiaPageUtilsDB.db_get_all_acl_id: cannot free resources",
                                        JahiaException.DATABASE_ERROR, JahiaException.WARNING );
            }
        }
        return theIDs;

    }


    //-------------------------------------------------------------------------
    private Connection getDBConnection (int debugInfo)
    {
        Connection dbConn = null;

        try {
            if ( debugInfo != 0 ){
                dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection (debugInfo);
            } else {
                dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection ();
            }
        }
        catch (NullPointerException ex) {
            JahiaConsole.println ("JahiaPagesDB", "Null Pointer Exception, DB Pool Service instance might be null!");
        }
        catch (SQLException ex) {
            JahiaConsole.println ("JahiaPagesDB", "SQL Exception: cannot get a connection.");
        }

        return dbConn;
    }


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

    //-------------------------------------------------------------------------
    private void closeStatement (Statement statement)
    {
        // Close the opened statement
        try {
            if (statement!=null) {
                statement.close();
            }
        }
        catch (SQLException sqlEx) {
            // just create an exception without raising it, just to notify it
            // in the logs.
            JahiaException je = new JahiaException ("Cannot close a statement",
                    "Cannot close a statement", JahiaException.DATABASE_ERROR,
                    JahiaException.WARNING);
        }
    }


} // end JahiaPageUtilssDB