jahiagroupmanagerdbservice.java

java 写的一个新闻发布系统

                {
                    while (enum.hasMoreElements())
                    {
                        name  = (String)enum.nextElement();
                        value = properties.getProperty(name);

                        query = "INSERT INTO jahia_grp_prop (id_jahia_grp, name_jahia_grp_prop, value_jahia_grp_prop)"+
                                " VALUES ("+grpIDStr+",'" + name + "','" + value + "')";
                        ServicesRegistry.getInstance().getDBPoolService().executeUpdate (statement,query);
                    }
                }
            }
        }
        catch (SQLException sqlEx)
        {
            toConsole ("SQL Exception occured!");
            result = false;
        }
        finally
        {
            CloseDBConnection (dbConn);
            CloseStatement (statement);
        }
        return result;
    }

    //--------------------------------------------------------------------------
    // NK
    private JahiaDBGroup lookupGroupInDB (String groupKey)
    {
        JahiaDBGroup  group = null;
        Properties  properties = new Properties();

        // Get a database connection
        Connection  dbConn = getDBConnection (2004);
        if (dbConn == null) {
            return null;
        }


        // execute the SELECT query
        Statement statement = null;
        String query = "";
        try
        {
            statement = dbConn.createStatement();
            if (statement != null)
            {
                query = "SELECT id_jahia_grps, name_jahia_grps,siteid_jahia_grps FROM jahia_grps WHERE key_jahia_grps='"+groupKey+"'";
                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery (statement,query);
                if (rs != null)
                {
                    if (rs.next())
                    {
                        int 	id		= rs.getInt("id_jahia_grps");
                        String 	name	= rs.getString("name_jahia_grps");
                        int 	siteID	= rs.getInt("siteid_jahia_grps");
                        Hashtable members = getGroupMembers (groupKey);

                        // Get all the group attributes
                        query = "SELECT name_jahia_grp_prop, value_jahia_grp_prop FROM jahia_grp_prop WHERE id_jahia_grp="+
                                Integer.toString(id);

                        rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery (statement,query);

                        if (rs != null) {

                            String propName = null;
                            String propVal = null;

                            while (rs.next()) {

                                propName = rs.getString("name_jahia_grp_prop");
                                propVal = rs.getString ("value_jahia_grp_prop");
                                if ( propVal == null ){
                                    propVal = "";
                                }

                                if ( propName != null ){
                                    properties.put (propName, propVal);
                                }
                            }
                        }

                        try {
                            group = new JahiaDBGroup (id, name, groupKey, siteID, members, properties);
                            if (group != null) {
                                toDebug ("Group ["+groupKey+"/"+name+"] loaded from database");
                            }
                        }
                        catch (JahiaException ex) {
                            toConsole ("Could not create group ["+name+"] in lookupGroupInDB()");
                        }
                    }
                }
            }

        }
        catch (SQLException ex)
        {
            toConsole ("makeSelectQuery() : SQL Exception occured for query ["+query+"]");
        }
        finally
        {
            CloseDBConnection (dbConn);
            CloseStatement (statement);
        }
        return group;
    }


    //--------------------------------------------------------------------------
    private JahiaDBGroup lookupGroupInDB (int siteID, String name)
    {
        JahiaDBGroup  group = null;
        Properties  properties = new Properties();

        // Get a database connection
        Connection  dbConn = getDBConnection (2004);
        if (dbConn == null) {
            return null;
        }

        // execute the SELECT query
        Statement statement = null;
        String query = "";
        try
        {
            statement = dbConn.createStatement();
            if (statement != null)
            {
                query = "SELECT id_jahia_grps, key_jahia_grps FROM jahia_grps WHERE name_jahia_grps='"+name+"' and siteid_jahia_grps=" + siteID;
                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery (statement,query);
                if (rs != null)
                {
                    if (rs.next())
                    {
                        int groupID		= rs.getInt("id_jahia_grps");
                        String groupKey = rs.getString ("key_jahia_grps");
                        Hashtable members = getGroupMembers (groupKey);

                        // Get all the group attributes
                        query = "SELECT name_jahia_grp_prop, value_jahia_grp_prop FROM jahia_grp_prop WHERE id_jahia_grp="+
                                Integer.toString(groupID);

                        rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery (statement,query);

                        if (rs != null) {

                            String propName = null;
                            String propVal = null;

                            while (rs.next()) {

                                propName = rs.getString("name_jahia_grp_prop");
                                propVal = rs.getString ("value_jahia_grp_prop");
                                if ( propVal == null ){
                                    propVal = "";
                                }

                                if ( propName != null ){
                                    properties.put (propName, propVal);
                                }
                            }
                        }


                        try {
                            group = new JahiaDBGroup (groupID, name, groupKey, siteID, members, properties);
                            if (group != null) {
                                toDebug ("Group ["+Integer.toString (groupID)+"/"+name+"] loaded from database");
                            }
                        }
                        catch (JahiaException ex) {
                            toConsole ("Could not create group ["+name+"] in lookupGroupInDB()");
                        }
                    }
                }
            }

            //System.out.println ("Lookup Group...");
            //System.out.println (group.toString());

        }
        catch (SQLException ex)
        {
            toConsole ("makeSelectQuery() : SQL Exception occured for query ["+query+"]");
        }
        finally
        {
            CloseDBConnection (dbConn);
            CloseStatement (statement);
        }

        return group;
    }


    //--------------------------------------------------------------------------
    private Hashtable getGroupMembers (String groupKey)
    {
        Hashtable members = null;

        // Get a database connection
        Connection  dbConn = getDBConnection (2005);
        if (dbConn == null) {
            return null;
        }

        // execute the SELECT query
        Statement statement = null;
        StringBuffer query = new StringBuffer ();
        try
        {
            members = new Hashtable ();
            Vector memberKeys = new Vector();
            String idMember;

            //-------------------------
            // GET THE USER MEMBER KEYS
            statement = dbConn.createStatement();
            if (statement != null)
            {
                // load all the group's user members

                query.append ("SELECT id_jahia_member FROM jahia_grp_access WHERE id_jahia_grps='");
                query.append (groupKey);
                query.append ("' AND membertype_grp_access=");
                query.append (mUSERTYPE);

                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery (statement,query.toString());
                if (rs != null)
                {
                    while (rs.next())
                    {
                        String key = rs.getString ("id_jahia_member");
                        memberKeys.add (key);
                    }
                }
                rs = null;
                CloseStatement (statement);

                // add the users in the member list
                for (int i=0; i<memberKeys.size(); i++) {
                    JahiaUser user = ServicesRegistry.getInstance().
                                                      getJahiaUserManagerService().
                                                      lookupUser ((String)memberKeys.get(i));
                    if (user != null) {
                        members.put (memberKeys.get(i), user);
                    }
                }

                // remove all the user keys.
                memberKeys.clear();
            }
            query.delete (0, query.length());


            //--------------------------
            // GET THE GROUP MEMBER KEYS
            statement = dbConn.createStatement();
            if (statement != null)
            {
                // load all the group's group members
                query.append ("SELECT id_jahia_member FROM jahia_grp_access WHERE id_jahia_grps='");
                query.append (groupKey);
                query.append ("' AND membertype_grp_access=");
                query.append (mGROUPTYPE);

                ResultSet rs = ServicesRegistry.getInstance().getDBPoolService().executeQuery (statement,query.toString());
                if (rs != null)
                {
                    while (rs.next())
                    {
                        String key = rs.getString("id_jahia_member");
                        memberKeys.add (key);
                    }
                }
                rs = null;
                CloseStatement (statement);

                // add the users in the member list
                for (int i=0; i<memberKeys.size(); i++) {
                    JahiaGroup group = ServicesRegistry.getInstance().
                                                      getJahiaGroupManagerService().
                                                      lookupGroup ((String)memberKeys.get(i));
                    if (group != null) {
                        members.put (memberKeys.get(i), group);
                    }
                }
            }
            memberKeys = null;
            idMember = null;
        }
        catch (SQLException ex)
        {
            toConsole ("makeSelectQuery() : SQL Exception occured for query ["+query+"]");
            // FIXME -Fulco- : Don't know yet what to do with this exception.
            //                 It should be logged somewhere !
        }
        finally
        {
            CloseDBConnection (dbConn);
            CloseStatement (statement);
        }

        return members;
     }

    //--------------------------------------------------------------------------
    /**
     *
     */
    private boolean deleteGroupFromDB (JahiaGroup group)
    {
        // Get a database connection
        Connection  dbConn = getDBConnection (2006);
        if (dbConn == null) {
            return false;
        }

        boolean     result = true;
        Statement   statement = null;

        try
        {
            statement = dbConn.createStatement();

            if (statement != null) {
                String tmpStr = "WHERE key_jahia_grps='"+((JahiaDBGroup)group).getName() + "'";
                ServicesRegistry.getInstance().getDBPoolService().executeUpdate (statement,"DELETE FROM jahia_grps "+tmpStr);

                tmpStr = "WHERE id_jahia_grps='"+((JahiaDBGroup)group).getName()+"'";
                ServicesRegistry.getInstance().getDBPoolService().executeUpdate (statement,"DELETE FROM jahia_grp_access "+tmpStr);
                ServicesRegistry.getInstance().getDBPoolService().executeUpdate (statement,"DELETE FROM jahia_grp_prop WHERE id_jahia_grp="+group.hashCode());

            } else {
                result = false;
            }
        }
        catch (SQLException sqlEx_1st)
        {
            result = false;
        }
        finally
        {
            CloseDBConnection (dbConn);
            CloseStatement (statement);
        }

        return result;
    }


    //--------------------------------------------------------------------------
    /**
     * return a DOM document of all groups of a site
     *
     * @param int the site id
     *
     * @return JahiaDOMObject a DOM representation of this object
     *
     * @author NK
     */
    public JahiaDOMObject getGroupsAsDOM( 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="+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 getGroupsAsDOM(int siteID) : " + se.getMessage();
            JahiaConsole.println( "JahiaGrou鐼anagerDBService", errorMsg );
            throw new JahiaException(   "Cannot load groups from the database",
                                        errorMsg, JahiaException.DATABASE_ERROR,
                                        JahiaException.CRITICAL );
        } finally {
            CloseDBConnection (dbConn);