jahiapagedefinitionpropdb.java

java 写的一个新闻发布系统

     */
    public static void setProperty( JahiaPageDefinition pageDef, String name, String value)
    throws JahiaException {
		
		if ( pageDef == null )
			return;
		
        removeProperty(pageDef.getID(),name);

        Connection dbConn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // opens connection
            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(34);
            String sqlQuery = "INSERT INTO jahia_pages_def_prop VALUES(?,?,?,?)";
            
            pstmt = dbConn.prepareStatement(sqlQuery);
			pstmt.setInt(1,pageDef.getID());
			pstmt.setInt(2,pageDef.getJahiaID());
			pstmt.setString(3,name);
			pstmt.setString(4,value);
			
            // executes the query, then closes the connection
            pstmt.execute( sqlQuery );

            closeDBConnection (dbConn);
            closeStatement ((Statement)pstmt);

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

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

            // composes the query
            String sqlQuery = "DELETE FROM jahia_pages_def_prop WHERE id_jahia_pages_def_prop = ? AND name_pages_def_prop = ?";
			
            // executes the query
            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(35);
            pstmt = dbConn.prepareStatement(sqlQuery);
            pstmt.setInt(1,id);
            pstmt.setString(2,name);
            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 page def 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 page def for
     * a given site
     *
     * @param int siteID
     *
     * @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 static JahiaDOMObject getPageDefPropsAsDOM( int siteID )
    throws JahiaException{

        Connection dbConn = null;
        Statement statement = null;

        String output = null;
        JahiaDBDOMObject dom = null;

        try {

            String sqlQuery = "SELECT * FROM jahia_pages_def_prop WHERE jahiaid_pages_def_prop=" + siteID;

            dbConn = getDBConnection(36);
            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_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(   "Error loading pages def properties from the database",
                                        errorMsg, JahiaException.DATABASE_ERROR,
                                        JahiaException.CRITICAL );
        } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
        }

        return dom;
    }

    //--------------------------------------------------------------------------
    /**
     * gets all acl ids of a site's page def ACL ids
     *
     * @return       a Vector of all acl id
     */
    public static 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 value_pages_def_prop FROM jahia_pages_def_prop "
            + "WHERE jahiaid_pages_def_prop="+siteID+" AND name_pages_def_prop='" + JahiaPageDefinition.ACLID_PROP + "'";

            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection(37);
            stmt = dbConn.createStatement();
            rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery( stmt,sqlQuery );
			
			String value = null;
            while (rs.next())
            {
                value = rs.getString( "value_pages_def_prop" );
                if ( value != null ){
                	try {
                		theIDs.add( new Integer(value) );
                	} catch (Throwable t){
                		t.printStackTrace();
                	}
                }
            }
        }
        catch (SQLException se)
        {
            String errorMsg = "Error in db_get_all_acl_id : " + se.getMessage();
            JahiaConsole.println (CLASS_NAME+".db_get_all_acl_id", errorMsg );
            throw new JahiaException ("Cannot load acl id from the database",
                                      errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL);
        } finally {
            closeDBConnection (dbConn);
            closeStatement (stmt);
        }
        return theIDs;

    }


    //-------------------------------------------------------------------------
    private static 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 static 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 static 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);
        }
    }

}