jahiapagetemplatedb.java

java 写的一个新闻发布系统

	    try {
	        // composes the query
            String sqlQuery = "SELECT id_jahia_pages_def FROM "+
                "jahia_pages_def WHERE sourcepath_jahia_pages_def = '" + JahiaTools.quote(path) + "' and jahiaid_jahia_pages_def=" + siteID ;

	        // executes the query
	        dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(69);
            statement = dbConn.createStatement();
            if (statement != null) {
                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery( statement,sqlQuery );
                if (rs != null) {
                    if (rs.next()) {
                        templateID = rs.getInt ("id_jahia_pages_def");
                    }
                }
            }

	    }
	    // catches error if cannot execute update query
	    catch (SQLException se)
	    {
	        String errorMsg = "Error in db_check_template_sourcepath : " + se.getMessage();
            JahiaConsole.println( "JahiaPageTemplateDefinitionsDB", errorMsg + " -> BAILING OUT");
	        throw new JahiaException(   "db_check_template_sourcepath",
	                                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.ERROR );
	    } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
      }

      return templateID;
   }

    //-------------------------------------------------------------------------
    private JahiaPageDefinition readTemplateFromResultSet (ResultSet rs)
        throws SQLException
    {
        int     ID              = rs.getInt     ( "id_jahia_pages_def" );
        int     siteID          = rs.getInt     ( "jahiaid_jahia_pages_def" );
        String  name            = rs.getString  ( "name_jahia_pages_def" );
        String  sourcePath      = rs.getString  ( "sourcepath_jahia_pages_def" );
        int     visible         = rs.getInt     ( "visible_jahia_pages_def" );
        int     isBrowsable     = rs.getInt     ( "browsable_jahia_pages_def" );
        String  warningMsg      = rs.getString  ( "warning_msg_jahia_pages_def" );
        String  img             = rs.getString  ( "img_jahia_pages_def" );

        return new JahiaPageDefinition (ID, siteID, name, sourcePath, (visible==1), img);
    }



    //--------------------------------------------------------------------------
	/**
	 * return a DOM document of all page def of a site
	 *
	 * @param int the site id
	 *
	 * @return JahiaDOMObject a DOM representation of this object
	 *
	 * @author NK
	 */
	public JahiaDOMObject getPageDefsAsDOM( int siteID ) throws JahiaException{
	
		Connection dbConn = null;
        Statement statement = null;

		String output = null;
		JahiaDBDOMObject dom = null;
		
	    try {
            String sqlQuery = "SELECT * FROM jahia_pages_def where jahiaid_jahia_pages_def=" + 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_def",rs);
					return dom;
				}
			}
	    } catch (SQLException se) {
	        String errorMsg = "Error in getPagesDefAsDOM(int siteID) : " + se.getMessage();
            JahiaConsole.println( "JahiaPageDBUtilsDB", errorMsg + " -> B	AILING OUT" );
	        throw new JahiaException(   "Cannot load pages def from the database",
	                                    errorMsg, JahiaException.DATABASE_ERROR,
	                                    JahiaException.CRITICAL );
	    } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
        }

		return dom;
	}




    //-------------------------------------------------------------------------
    public synchronized int getNextID ()
    {
        int counter = -1;

        Connection dbConn = getDBConnection (74);
        if (dbConn != null) {
            Statement statement = null;

            try {
                String query = "SELECT MAX(id_jahia_pages_def) as MaxID FROM jahia_pages_def";
                statement = dbConn.createStatement();
                if (statement != null) {
                    ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery (statement,query);
                    if (rs != null) {
                        if (rs.next()) {
                            // get the highest used page ID
                            counter = rs.getInt ("MaxID");

                            // increment by one to get the next counter
                            counter++;
                        }
                    }
                }
            }
            catch (SQLException ex) {
            // ..............
            }
            finally{
                closeDBConnection (dbConn);
                closeStatement (statement);
            }
        }
        return counter;
    }


    //-------------------------------------------------------------------------
    // FH   2 May 2001
    /** Return the amount of pages templates in the database.
     *
     * @return
     *      The amount of page templates.
     */
    public synchronized int getNbPageTemplates ()
        throws  JahiaDatabaseException
    {

		return getNbPageTemplates(-1);
    }


    //-------------------------------------------------------------------------
    // NK   16 May 2001
    /** Return the amount of pages templates in the database.
     *
     * @param int siteID
     * @return
     *      The amount of page templates.
     */
    public synchronized int getNbPageTemplates (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_def) as nbItems FROM jahia_pages_def";
				
				if ( siteID != -1 ){
					query += " WHERE jahiaid_jahia_pages_def=" + 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;
    }


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

        try {
            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection (debugInfo);
        }
        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);
        }
    }

    //-------------------------------------------------------------------------
    /**
     * gets all the page template ids
     *
     * @return       a Vector of page template ids
     *
     */
    public Vector getAllPageTemplateIDs ()
        throws JahiaException
    {
        Connection dbConn = null;
        Statement statement = null;
        ResultSet rs = null;
        Vector theList = new Vector();
        try {
            String sqlQuery = "SELECT id_jahia_pages_def FROM jahia_pages_def ORDER BY name_jahia_pages_def";

            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(75);
            statement = dbConn.createStatement();
            rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery( statement,sqlQuery );
            while (rs.next()) {
                theList.add( new Integer( rs.getInt("id_jahia_pages_def") ) );
            }

        } catch (SQLException se) {
            String errorMsg = "Error in db_get_all_page_definition_ids : " + se.getMessage();
            JahiaConsole.println( "JahiaPageUtilsDB", errorMsg + " -> BAILING OUT" );
            throw new JahiaException(   "Cannot load page data from the database",
                                        errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL );
        } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
        }
        return theList;
    }

}