jahiacontainerlistpropdb.java

java 写的一个新闻发布系统

     * @param containerListID identifier of the containerList
	 * @param jahiaID , the site id
     * @param propertyName name of the property to add in the database
     * @param propertyValue name of the property to add in the database
     *
     * @throws JahiaException generated if there were problems executing the
     * query or communicating with the database.
     */
    public void setProperty(int containerListID,
    						int jahiaID,
                            String propertyName,
                            String propertyValue)
    throws JahiaException {

        removeProperty(containerListID, propertyName);

        Connection dbConn = null;
		PreparedStatement pstmt = null;	
        ResultSet rs = null;
        try {
            // opens connection
            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(20);

			pstmt = dbConn.prepareStatement("INSERT INTO jahia_ctnlists_prop VALUES(?,?,?,?)");
   			pstmt.setInt(1, containerListID);
   			pstmt.setInt(2, jahiaID);
   			pstmt.setString(3, propertyName);
   			pstmt.setString(4, propertyValue);
 			pstmt.execute();

        } catch (SQLException se) {
            String errorMsg = "Error in " + CLASS_NAME + ".setProperty : " + se.getMessage();
            JahiaConsole.println( CLASS_NAME + ".setProperty", errorMsg );
            throw new JahiaException(   "Cannot create container list property in the database",
                                        errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL );
        } finally {
            closeDBConnection (dbConn);
            closeStatement ((Statement)pstmt);
        }
    }

    /**
     * Removes a single property for the given containerList.
     *
     * @param containerListID identifer of the containerList
     * @param propertyName name of the property to be deleted.
     *
     * @throws JahiaException generated if there were problems executing the
     * query or communicating with the database.
     */
    public void removeProperty(int containerListID,
                               String propertyName)
    throws JahiaException {
        Connection dbConn = null;
		PreparedStatement pstmt = null;	
        try {

            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(22);

            // composes the query
            String sqlQuery = "DELETE FROM jahia_ctnlists_prop ";
            sqlQuery += "WHERE ctnlistid_ctnlists_prop = ?";
            sqlQuery += " AND name_jahia_ctnlists_prop=?";

            // executes the query
			pstmt = dbConn.prepareStatement(sqlQuery);
   			pstmt.setInt(1, containerListID);
   			pstmt.setString(2, propertyName);
 			pstmt.executeUpdate();
        }
        // catches error if cannot execute update query
        catch (SQLException se)
        {
            String errorMsg = "Error in " + CLASS_NAME + ".removeProperty : " + se.getMessage();
            JahiaConsole.println( CLASS_NAME +".removeProperty", errorMsg );
            throw new JahiaException(   "Cannot delete container list property in the database",
                                        errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL );
        } finally {
            closeDBConnection (dbConn);
            closeStatement ((Statement)pstmt);
        }
    }

    //--------------------------------------------------------------------------
    /**
     * return a DOM document of all the properties of all the containers for
     * a given siteID
     *
     * @param siteID the identifier for the siteID for which to extract all the
     * containerList properties
     *
     * @return JahiaDOMObject a DOM representation of the properties of the
     * containerList
     *
     * @throws JahiaException generated if there were problems executing the
     * query or communicating with the database.
     */
    public JahiaDOMObject getPropertiesAsDOM( int siteID )
    throws JahiaException{

        Connection dbConn = null;
        Statement statement = null;

        String output = null;
        JahiaDBDOMObject dom = null;

        try {

            String sqlQuery = "SELECT DISTINCT * FROM jahia_ctnlists_prop WHERE "
            +"jahiaid_ctnlists_prop="+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_ctnlists_prop",rs);
                    return dom;
                }
            }
        } catch (SQLException se) {
            String errorMsg = "Error in " + CLASS_NAME + ".getPropertiesAsDOM : " + se.getMessage();
            JahiaConsole.println( CLASS_NAME + ".getPropertiesAsDOM", errorMsg );
            throw new JahiaException(   "Cannot load container list properties from the database",
                                        errorMsg, JahiaException.DATABASE_ERROR,
                                        JahiaException.CRITICAL );
        } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
        }

        return dom;
    }

    //--------------------------------------------------------------------------
    /**
     * return a Vector of all view fields acl ids for a given siteID
     *
     * @param siteID the identifier for the siteID for which to extract all the
     * view fields acl
     *
     * @return Vector , the vector of acl ids
     *
     * @throws JahiaException generated if there were problems executing the
     * query or communicating with the database.
     */
    public Vector getCtnListFieldACLs( int siteID )
    throws JahiaException{

        Connection dbConn = null;
        Statement statement = null;

        String output = null;
        JahiaDBDOMObject dom = null;
		
		Vector ids = new Vector();
			
        try {

            String sqlQuery = "SELECT DISTINCT name_ctnlists_prop,value_ctnlists_prop FROM jahia_ctnlists_prop WHERE "
            +"jahiaid_ctnlists_prop="+siteID;

            dbConn = getDBConnection(0);
            statement = dbConn.createStatement();
            if (statement != null) {
                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery( statement, sqlQuery );
                String name = "";
                String value = "";
                while (rs.next()) {
                    name = rs.getString("name_ctnlists_prop");
                    value = rs.getString("value_ctnlists_prop");
					if ( (value != null) && ( !value.trim().equals("") ) && ( name != null ) && ( name.startsWith("view_field_acl_") ) ){
						try {
							ids.add(new Integer(value));				
						} catch (Throwable t ){
						}
					}
                }
            }
        } catch (SQLException se) {
            String errorMsg = "Error in " + CLASS_NAME + ".getPropertiesAsDOM : " + se.getMessage();
            JahiaConsole.println( CLASS_NAME + ".getPropertiesAsDOM", errorMsg );
            throw new JahiaException(   "Cannot load container list properties from the database",
                                        errorMsg, JahiaException.DATABASE_ERROR,
                                        JahiaException.CRITICAL );
        } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
        }

        return ids;
    }

    //-------------------------------------------------------------------------
    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 (CLASS_NAME +".getDBConnection", "Null Pointer Exception, DB Pool Service instance might be null!");
        }
        catch (SQLException ex) {
            JahiaConsole.println (CLASS_NAME +".getDBConnection", "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 (CLASS_NAME + ".closeDBConnection", "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);
        }
    }

}