jahiasitegroupmanagerdbservice.java

java 写的一个新闻发布系统

            if (statement != null) {

				StringBuffer query = new StringBuffer("select * from jahia_sites_grps where siteid_sites_grps=");
				query.append(siteID);
                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery( statement,query.toString() );
                if (rs != null) {
	            	while (rs.next()) {
                   		sgBean = getSiteGroupBeanFromResultSet(rs);
						if ( sgBean != null ){
							groups.put(sgBean.groupname,sgBean.groupID);
						}	
                    }
                }
            }

	    } catch (SQLException se) {
	        String errorMsg = "Error in getGroups : " + se.getMessage();
            JahiaConsole.println( "JahiaSiteGroupManagerDBService", errorMsg + " -> BAILING OUT" );
	        throw new JahiaException(   "Cannot load sites from the database",
	                                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL );
	    } catch (JahiaException je) {
	        String errorMsg = "Error in getGroups(siteID) : " + je.getMessage();
            JahiaConsole.println( "JahiaSiteGroupManagerDBService", errorMsg + " -> BAILING OUT" );
	        throw new JahiaException(   "Cannot get site's groups from database",
	                                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL );
	    } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
		}
		
		return groups;
    }


    //-------------------------------------------------------------------------
	/**
	 * Build a SiteGroupBean from a resultset
	 *
	 * @return SiteGroupBean the site group bean 
	 */
	protected SiteGroupBean getSiteGroupBeanFromResultSet(ResultSet rs) throws JahiaException {
	
		SiteGroupBean sgBean 		= null;
		
		if ( rs != null ){
			
			String groupname		= null;			
			int siteID				= -1;
			String groupID			= null;

			try {			
       		
				groupname			= rs.getString("grpname_sites_grps");
           		siteID 				= rs.getInt("siteid_sites_grps");
				groupID				= rs.getString("grpid_sites_grps");
           		
           	}catch ( SQLException se ){
		        String errorMsg = "Error in getSiteGroupBeanFromResultSet(rs) : " + se.getMessage();
    	        JahiaConsole.println( "JahiaSiteGroupManagerDBService", errorMsg + " -> BAILING OUT" );
	    	    throw new JahiaException(   "Cannot read data from resultset",
	                                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL );
           	}


   			sgBean = new SiteGroupBean(
	       								groupname,
           								siteID,
           								groupID
           								);	

		}	


		return sgBean;
	}


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

		String output = null;
		JahiaDBDOMObject dom = null;
		
	    try {
            String sqlQuery = "SELECT * FROM jahia_sites_grps where siteid_sites_grps="+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_sites_grps",rs);
					return dom;
				}
			}
	    } catch (SQLException se) {
	        String errorMsg = "Error in getGroupMembershipsAsDOM(int siteID) : " + se.getMessage();
            JahiaConsole.println( "JahiaSiteGroupManagerDBService", errorMsg );
	        throw new JahiaException(   "Cannot load data from the database",
	                                    errorMsg, JahiaException.DATABASE_ERROR,
	                                    JahiaException.CRITICAL );
	    } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
        }

		return dom;
	}


    //--------------------------------------------------------------------------
	/**
	 * return a DOM document of external groups ( from other sites ) 
	 * that have membership access on this site
	 *
	 * @param int the site id
	 *
	 * @return JahiaDOMObject a DOM representation of this object
	 *
	 * @author NK
	 */
	public JahiaDOMObject getAuthExternalGroupsAsDOM( int siteID )
	throws JahiaException{
	
		Connection dbConn = null;
        Statement statement = null;

		String output = null;
		JahiaDBDOMObject dom = null;
		
	    try {
            String sqlQuery = "SELECT * FROM jahia_grps"
            +" WHERE siteid_jahia_grps<>0 AND siteid_jahia_grps<>"+siteID
            +" AND name_jahia_grps IN "
            +"(SELECT grpname_sites_grps from jahia_sites_grps where siteid_sites_grps="
            +siteID+") AND key_jahia_grps IN "
            +"(SELECT grpid_sites_grps from jahia_sites_grps where siteid_sites_grps="
            +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_grps",rs);
					return dom;
				}
			}
	    } catch (SQLException se) {
	        String errorMsg = "Error in getAuthExternalGroupsAsDOM(int siteID) : " + se.getMessage();
            JahiaConsole.println( "JahiaSiteUsermanagerDBService", errorMsg );
	        throw new JahiaException(   "Cannot load data from the database",
	                                    errorMsg, JahiaException.DATABASE_ERROR,
	                                    JahiaException.CRITICAL );
	    } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
        }

		return dom;
	}



    //-------------------------------------------------------------------------
    private void executeQueryNoResultSet(String queryStr) throws JahiaException
    {

		Connection dbConn = null;
        Statement statement = null;
		
		try {
			dbConn = getDBConnection();
            statement = dbConn.createStatement();
            if (statement != null) {
                ServicesRegistry.getInstance().getDBPoolService().executeUpdate(statement,queryStr);
			}
	    } catch (SQLException se) {
	        String errorMsg = "Error in executeQueryNoResultSet(String queryStr) : " + se.getMessage();
            JahiaConsole.println( "JahiaSiteGroupManagerDBService", errorMsg + " -> BAILING OUT" );
	        throw new JahiaException(   "Cannot execute query" + queryStr,
	                                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL );
	    } finally {
            closeDBConnection (dbConn);
            closeStatement (statement);
        }

    }


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

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

        return dbConn;
    }



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

        try {
            dbConn = ServicesRegistry.getInstance().getDBPoolService().getConnection (debugInfo);
        }
        catch (NullPointerException ex) {
            JahiaConsole.println ("JahiaSiteGroupManagerDBService", "Null Pointer Exception, DB Pool Service instance might be null!");
        }
        catch (SQLException ex) {
            JahiaConsole.println ("JahiaSiteGroupManagerDBService", "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 ("JahiaSiteGroupManagerDBService", "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);
        }
    }



}