jahiacontainerutilsdb.java

java 写的一个新闻发布系统

//
//                                   ____.
//                       __/\ ______|    |__/\.     _______
//            __   .____|    |       \   |    +----+       \
//    _______|  /--|    |    |    -   \  _    |    :    -   \_________
//   \\______: :---|    :    :           |    :    |         \________>
//           |__\---\_____________:______:    :____|____:_____\
//                                      /_____|
//
//                 . . . i n   j a h i a   w e   t r u s t . . .
//

/***
    * JahiaContainerUtilsDB
    * @author Eric Vassalli
    *
    * Various utilities for containers
    *
    */

//
//  db_get_container_list_id( listName, pageID )
//  db_get_container_list_ids( ctndefid, pageID )
//  db_get_container_ids_in_container_list( listID )
//  db_get_field_ids_in_container( ctnid )
//  db_get_all_container_definition_ids()
//


package org.jahia.services.containers;

import java.sql.*;                      // ResultSet
import java.util.*;                     // Vector

import org.jahia.params.*;          // ParamBean
import org.jahia.data.*;            // JahiaData
import org.jahia.utils.*;           // JahiaConsole
import org.jahia.services.pages.JahiaPage;
import org.jahia.data.fields.*;     // JahiaField
import org.jahia.data.containers.*; // JahiaContainers
import org.jahia.registries.*;      // ServiceRegistry
import org.jahia.exceptions.JahiaException;


public class JahiaContainerUtilsDB
{



    /***
        * constructor
        *
        */
    public JahiaContainerUtilsDB()
    {
    } // end constructor



    /***
     * finds a container list id from its name and page id
     *
     * @param           listName            the container list name
     * @param           pageID              the page id
     * @return          a container list ID
     *
     * @exception       raises a critical JahiaException if a data access error occurs
     * @exception       raises a critical JahiaException if cannot free resources
     * @exception       raises a warning JahiaException if cannot find id
     *
     */
    public int db_get_container_list_id( String listName, int pageID )
    throws JahiaException
    {
        Connection dbConn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int listID = -1;
        int ctndefid = -1;
        try {
            // gets connexion
            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(30);
            stmt = dbConn.createStatement();

            // gets page definition id
            // gets the page
            JahiaPage remotePage = ServicesRegistry.getInstance().
                    getJahiaPageService().lookupPage (pageID);

            if (remotePage == null) {
                String errorMsg = "Container " + listName + " is being loaded from an unexisting page (" + pageID + ")";
                JahiaException je = new JahiaException( errorMsg,
                                        errorMsg, JahiaException.DATABASE_ERROR, JahiaException.WARNING );
                return -1;
            }

            int pageDefID = remotePage.getPageTemplateID();

            // gets container definition id
            String sqlQuery = "SELECT id_jahia_ctn_def FROM jahia_ctn_def ";
            sqlQuery += "WHERE (name_jahia_ctn_def='" + listName + "' and jahiaid_jahia_ctn_def="+ remotePage.getJahiaID()+ ")";
            rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery( stmt, sqlQuery );
            if (rs.next()) {
                ctndefid = rs.getInt( "id_jahia_ctn_def" );
            } else {
                String errorMsg = "Container " + listName + " is not defined";
                JahiaException je = new JahiaException( errorMsg,
                                        errorMsg, JahiaException.DATABASE_ERROR, JahiaException.WARNING );
                return -1;
            }

            // gets container list id
            sqlQuery = "SELECT id_jahia_ctn_lists FROM jahia_ctn_lists ";
            sqlQuery += "WHERE ((pageid_jahia_ctn_lists=" + pageID + ") AND ";
            sqlQuery += "(ctndefid_jahia_ctn_lists=" + ctndefid + "))";
            rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery( stmt, sqlQuery );
            if (rs.next()) {
                listID = rs.getInt( "id_jahia_ctn_lists" );
            } else {
                return -1;
            }
        } catch (SQLException se) {
            String errorMsg = "Error in db_get_container_list_id : " + se.getMessage();
            JahiaConsole.println( "JahiaContainerUtilsDB", errorMsg + " -> BAILING OUT" );
            throw new JahiaException(   "Cannot load containers 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",
                                        "db_get_container_list_id : cannot free resources",
                                        JahiaException.DATABASE_ERROR, JahiaException.WARNING );
            }
        }

        return listID;
    } // db_get_container_list_id



    /***
        * loads all the container list ids by their page id and definition id
        *
        * @param        pageID          the page ID
        * @param        defID           the container definition id
        * @return       a Vector of container list IDs
        *
        * @exception    raises a JahiaException if a data access error occurs
        * @exception    raises a JahiaException if cannot free resources
        *
        */
    public Vector db_get_container_list_ids( int pageID, int defID )
    throws JahiaException
    {
        Connection dbConn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Vector theList = new Vector();
        try {
            // gets connexion
            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(31);
            stmt = dbConn.createStatement();

            String sqlQuery = "SELECT DISTINCT listid_jahia_ctn_entries FROM jahia_ctn_entries ";
            sqlQuery += "WHERE pageid_jahia_ctn_entries=" + pageID + " ";
            sqlQuery += "AND ctndefid_jahia_ctn_entries=" + defID + " ";
            sqlQuery += " ORDER BY listid_jahia_ctn_entries ASC";
            rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery( stmt, sqlQuery );
            while (rs.next()) {
                theList.add( new Integer( rs.getInt( "listid_jahia_ctn_entries" ) ) );
            }
        } catch (SQLException se) {
            String errorMsg = "Error in db_get_container_list_ids : " + se.getMessage();
            JahiaConsole.println( "JahiaContainerUtilsDB", errorMsg + " -> BAILING OUT" );
            throw new JahiaException(   "Cannot load containers 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",
                                        "db_get_container_list_ids : cannot free resources",
                                        JahiaException.DATABASE_ERROR, JahiaException.WARNING );
            }
        }

        return theList;
    } // db_get_container_list_ids

    /**
     * loads all the container ids of a container list. This list is sorted
     * by rank.
     *
     * @param           listID              the container list ID
     * @return          a Vector of container IDs
     *
     * @exception       raises a JahiaException if a data access error occurs
     * @exception       raises a JahiaException if cannot free resources
     *
     */
    public Vector db_get_container_ids_in_container_list( int listID )
    throws JahiaException
    {
        Connection dbConn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Vector theList = new Vector();
        try {
            String sqlQuery = "SELECT * FROM jahia_ctn_entries ";
            sqlQuery += "WHERE listid_jahia_ctn_entries=" + listID + " ";
            sqlQuery += "ORDER BY rank_jahia_ctn_entries, id_jahia_ctn_entries ASC";

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

            while (rs.next()) {
                theList.add( new Integer( rs.getInt( "id_jahia_ctn_entries" ) ) );
            }
        } catch (SQLException se) {
            String errorMsg = "Error in db_get_container_ids_in_container_list : " + se.getMessage();
            JahiaConsole.println( "JahiaContainerUtilsDB", errorMsg + " -> BAILING OUT" );
            throw new JahiaException(   "Cannot load containers 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",
                                        "db_get_container_ids_in_container_list : cannot free resources",
                                        JahiaException.DATABASE_ERROR, JahiaException.WARNING );
            }
        }

        return theList;
    } // db_get_container_ids_in_container_list

    
    public Hashtable db_load_all_fields_from_container_from_page(int pageID) throws JahiaException
    {
        Hashtable result = new Hashtable();
        Connection dbConn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            String sqlQuery = "SELECT * FROM jahia_fields_data ";
            sqlQuery += "WHERE pageid_jahia_fields_data=" + pageID + " ";
            sqlQuery += "ORDER BY ctnid_jahia_fields_data, rank_jahia_fields_data, id_jahia_fields_data ASC";

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

            while (rs.next()) {
                int ctnid = rs.getInt ("ctnid_jahia_fields_data");
                Vector theList = (Vector)result.get(new Integer(ctnid));
                if (theList == null)
                {
                    theList = new Vector();
                    result.put (new Integer(ctnid), theList);
                }
                theList.add( new Integer( rs.getInt( "id_jahia_fields_data" ) ) );
            }
        } catch (SQLException se) {
            String errorMsg = "Error in db_get_field_ids_in_container : " + se.getMessage();
            JahiaConsole.println( "JahiaContainerUtilsDB", errorMsg + " -> BAILING OUT" );
            throw new JahiaException(   "Cannot load containers 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",
                                        "db_get_field_ids_in_container : cannot free resources",
                                        JahiaException.DATABASE_ERROR, JahiaException.WARNING );
            }
        }

        return result;
    }

    /**
     * loads all the container ids of a container list. This list is sorted
     * on a given field name.
     *
     * @param        listID              the container list ID
     * @param        String fieldName    the fieldname on which to filter
     * @param        boolean asc         Asc. or desc. ordering ( true = asc )
     * @return       a Vector of container IDs
     *
     * @exception       raises a JahiaException if a data access error occurs
     * @exception       raises a JahiaException if cannot free resources
     * @author NK
     */
    public Vector db_get_container_ids_in_container_list( int listID, String fieldName, boolean asc )
    throws JahiaException
    {
        Connection dbConn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Vector theList = new Vector();
        try {
			StringBuffer buff = new StringBuffer("SELECT DISTINCT id_jahia_ctn_entries, value_jahia_fields_data FROM jahia_ctn_entries a, jahia_fields_data b, jahia_fields_def c WHERE listid_jahia_ctn_entries=");
			buff.append(listID);
			buff.append(" AND ( a.id_jahia_ctn_entries = b.ctnid_jahia_fields_data AND b.fielddefid_jahia_fields_data = c.id_jahia_fields_def AND c.name_jahia_fields_def='");
			buff.append(JahiaTools.quote(fieldName));
			buff.append("') ORDER BY value_jahia_fields_data ");
			if ( !asc ){
				buff.append(" DESC ");
			}

            //JahiaConsole.println( "JahiaContainerUtilsDB.db_get_container_ids_in_container_list", " query : " + buff.toString() );
			
            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(32);
            stmt = dbConn.createStatement();
            rs = stmt.executeQuery( buff.toString() );
			
            while (rs.next()) {
            	int id = rs.getInt( "id_jahia_ctn_entries" );
            	String val = rs.getString( "value_jahia_fields_data" );
            	//JahiaConsole.println( "JahiaContainerUtilsDB.db_get_container_ids_in_container_list", " added id : " + id );
            	//JahiaConsole.println( "JahiaContainerUtilsDB.db_get_container_ids_in_container_list", " added val : " + val );
                theList.add( new Integer( id ) );
            }
        } catch (SQLException se) {
            String errorMsg = "Error in db_get_container_ids_in_container_list : " + se.getMessage();
            JahiaConsole.println( "JahiaContainerUtilsDB", errorMsg + " -> BAILING OUT" );
            throw new JahiaException(   "Cannot load containers 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",
                                        "db_get_container_ids_in_container_list : cannot free resources",
                                        JahiaException.DATABASE_ERROR, JahiaException.WARNING );
            }
        }