Java Code Examples for org.jivesoftware.database.DbConnectionManager#fastcloseStmt()

/**
 * Removes the affiliation of the user from the DB if ANY room that is persistent.
 *
 * @param affiliationJID The bareJID of the user to remove his affiliation from ALL persistent rooms.
 */
public static void removeAffiliationFromDB(JID affiliationJID)
{
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        // Remove the user from the members table
        pstmt = con.prepareStatement(DELETE_USER_MEMBER);
        pstmt.setString(1, affiliationJID.toBareJID());
        pstmt.executeUpdate();
        DbConnectionManager.fastcloseStmt(pstmt);

        // Remove the user from the generic affiliations table
        pstmt = con.prepareStatement(DELETE_USER_MUCAFFILIATION);
        pstmt.setString(1, affiliationJID.toBareJID());
        pstmt.executeUpdate();
    }
    catch (SQLException sqle) {
        Log.error(sqle.getMessage(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

private static boolean hasRootNode( Connection con, String serviceID ) throws SQLException
{
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try
    {
        pstmt = con.prepareStatement("SELECT serviceID FROM ofPubsubNode WHERE serviceID = ? AND nodeID = ? AND parent IS NULL" );
        pstmt.setString(1, serviceID);
        pstmt.setString(2, serviceID);
        rs = pstmt.executeQuery();
        return rs.next();
    }
    finally
    {
        DbConnectionManager.fastcloseStmt( rs, pstmt );
    }
}
 

private static void writeItem( Connection con, PubsubRecordData record ) throws SQLException
{
    PreparedStatement pstmt = null;
    try
    {
        pstmt = con.prepareStatement("INSERT INTO ofPubsubItem (serviceID,nodeID,id,jid,creationDate,payload) VALUES (?,?,?,?,?,?)");
        pstmt.setString(1, record.serviceID);
        pstmt.setString(2, record.nodeID);
        pstmt.setString(3, record.itemID);
        pstmt.setString(4, record.creator);
        pstmt.setString(5, record.creationDate);
        pstmt.setString(6, record.payload);
        pstmt.executeUpdate();
    }
    finally
    {
        DbConnectionManager.fastcloseStmt( pstmt );
    }
}
 

private static void writeAffiliation( Connection con, PubsubRecordData record ) throws SQLException
{
    PreparedStatement pstmt = null;
    try
    {
        pstmt = con.prepareStatement("INSERT INTO ofPubsubAffiliation (serviceID,nodeID,jid,affiliation) VALUES (?,?,?,?)" );
        pstmt.setString(1, record.serviceID);
        pstmt.setString(2, record.nodeID);
        pstmt.setString(3, record.creator);
        pstmt.setString( 4, NodeAffiliate.Affiliation.owner.name() );
        pstmt.executeUpdate();
    }
    finally
    {
        DbConnectionManager.fastcloseStmt( pstmt );
    }
}
 

/**
 * Removes the room configuration and its affiliates from the database.
 * 
 * @param room the room to remove from the database.
 */
public static void deleteFromDB(MUCRoom room) {
    if (!room.isPersistent() || !room.wasSavedToDB()) {
        return;
    }
    Connection con = null;
    PreparedStatement pstmt = null;
    boolean abortTransaction = false;
    try {
        con = DbConnectionManager.getTransactionConnection();
        pstmt = con.prepareStatement(DELETE_AFFILIATIONS);
        pstmt.setLong(1, room.getID());
        pstmt.executeUpdate();
        DbConnectionManager.fastcloseStmt(pstmt);

        pstmt = con.prepareStatement(DELETE_MEMBERS);
        pstmt.setLong(1, room.getID());
        pstmt.executeUpdate();
        DbConnectionManager.fastcloseStmt(pstmt);

        pstmt = con.prepareStatement(DELETE_ROOM);
        pstmt.setLong(1, room.getID());
        pstmt.executeUpdate();

        // Update the room (in memory) to indicate the it's no longer in the database.
        room.setSavedToDB(false);
    }
    catch (SQLException sqle) {
        Log.error(sqle.getMessage(), sqle);
        abortTransaction = true;
    }
    finally {
        DbConnectionManager.closeStatement(pstmt);
        DbConnectionManager.closeTransactionConnection(con, abortTransaction);
    }
}
 

@Override
public void setName(String oldName, String newName) throws GroupAlreadyExistsException
{
    Connection con = null;
    PreparedStatement pstmt = null;
    boolean abortTransaction = false;
    try {
        con = DbConnectionManager.getTransactionConnection();
        pstmt = con.prepareStatement(SET_GROUP_NAME_1);
        pstmt.setString(1, newName);
        pstmt.setString(2, oldName);
        pstmt.executeUpdate();
        DbConnectionManager.fastcloseStmt(pstmt);
        
        pstmt = con.prepareStatement(SET_GROUP_NAME_2);
        pstmt.setString(1, newName);
        pstmt.setString(2, oldName);
        pstmt.executeUpdate();
        DbConnectionManager.fastcloseStmt(pstmt);
        
        pstmt = con.prepareStatement(SET_GROUP_NAME_3);
        pstmt.setString(1, newName);
        pstmt.setString(2, oldName);
        pstmt.executeUpdate();
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
        abortTransaction = true;
    }
    finally {
        DbConnectionManager.closeStatement(pstmt);
        DbConnectionManager.closeTransactionConnection(con, abortTransaction);
    }
}
 

@Override
public void deleteGroup(String groupName) {
    Connection con = null;
    PreparedStatement pstmt = null;
    boolean abortTransaction = false;
    try {
        con = DbConnectionManager.getTransactionConnection();
        // Remove all users in the group.
        pstmt = con.prepareStatement(DELETE_GROUP_USERS);
        pstmt.setString(1, groupName);
        pstmt.executeUpdate();
        DbConnectionManager.fastcloseStmt(pstmt);
        
        // Remove all properties of the group.
        pstmt = con.prepareStatement(DELETE_PROPERTIES);
        pstmt.setString(1, groupName);
        pstmt.executeUpdate();
        DbConnectionManager.fastcloseStmt(pstmt);
        
        // Remove the group entry.
        pstmt = con.prepareStatement(DELETE_GROUP);
        pstmt.setString(1, groupName);
        pstmt.executeUpdate();
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
        abortTransaction = true;
    }
    finally {
        DbConnectionManager.closeStatement(pstmt);
        DbConnectionManager.closeTransactionConnection(con, abortTransaction);
    }
}
 

@Override
public void updateItem(String username, RosterItem item) {
    Connection con = null;
    PreparedStatement pstmt = null;
    long rosterID = item.getID();
    try {
        rosterItemCache.remove( username );

        con = DbConnectionManager.getConnection();
        // Update existing roster item
        pstmt = con.prepareStatement(UPDATE_ROSTER_ITEM);
        pstmt.setInt(1, item.getSubStatus().getValue());
        pstmt.setInt(2, item.getAskStatus().getValue());
        pstmt.setInt(3, item.getRecvStatus().getValue());
        pstmt.setString(4, item.getNickname());
        pstmt.setLong(5, rosterID);
        pstmt.executeUpdate();
        // Close now the statement (do not wait to be GC'ed)
        DbConnectionManager.fastcloseStmt(pstmt);

        // Delete old group list
        pstmt = con.prepareStatement(DELETE_ROSTER_ITEM_GROUPS);
        pstmt.setLong(1, rosterID);
        pstmt.executeUpdate();

        insertGroups(rosterID, item.getGroups().iterator(), con);
    }
    catch (SQLException e) {
        Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

@Override
public void deleteItem(String username, long rosterItemID) {
    // Only try to remove the user if they exist in the roster already:
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        rosterItemCache.remove( username );

        con = DbConnectionManager.getConnection();
        // Remove roster groups
        pstmt = con.prepareStatement(DELETE_ROSTER_ITEM_GROUPS);

        pstmt.setLong(1, rosterItemID);
        pstmt.executeUpdate();
        // Close now the statement (do not wait to be GC'ed)
        DbConnectionManager.fastcloseStmt(pstmt);

        // Remove roster
        pstmt = con.prepareStatement(DELETE_ROSTER_ITEM);

        pstmt.setLong(1, rosterItemID);
        pstmt.executeUpdate();
    }
    catch (SQLException e) {
        Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

@Override
public void deleteUser(String username) {
    Connection con = null;
    PreparedStatement pstmt = null;
    boolean abortTransaction = false;
    try {
        // Delete all of the users's extended properties
        con = DbConnectionManager.getTransactionConnection();
        pstmt = con.prepareStatement(DELETE_USER_PROPS);
        pstmt.setString(1, username);
        pstmt.execute();
        DbConnectionManager.fastcloseStmt(pstmt);

        pstmt = con.prepareStatement(DELETE_USER_FLAGS);
        pstmt.setString(1, username);
        pstmt.execute();
        DbConnectionManager.fastcloseStmt(pstmt);

        // Delete the actual user entry
        pstmt = con.prepareStatement(DELETE_USER);
        pstmt.setString(1, username);
        pstmt.execute();
    }
    catch (Exception e) {
        Log.error(e.getMessage(), e);
        abortTransaction = true;
    }
    finally {
        DbConnectionManager.closeStatement(pstmt);
        DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction);
    }
}
 

@Override
public void updateNode(Node node)
{
    log.trace( "Updating node: {} (write to database)", node.getUniqueIdentifier() );

    Connection con = null;
    PreparedStatement pstmt = null;
    boolean abortTransaction = false;
    try {
        con = DbConnectionManager.getTransactionConnection();
        pstmt = con.prepareStatement(UPDATE_NODE);
        pstmt.setString(1, StringUtils.dateToMillis(node.getModificationDate()));
        pstmt.setString(2, node.getParent() != null ? encodeNodeID(node.getParent().getNodeID()) : null);
        pstmt.setInt(3, (node.isPayloadDelivered() ? 1 : 0));
        if (!node.isCollectionNode()) {
            pstmt.setInt(4, ((LeafNode) node).getMaxPayloadSize());
            pstmt.setInt(5, (((LeafNode) node).isPersistPublishedItems() ? 1 : 0));
            pstmt.setInt(6, ((LeafNode) node).getMaxPublishedItems());
        }
        else {
            pstmt.setInt(4, 0);
            pstmt.setInt(5, 0);
            pstmt.setInt(6, 0);
        }
        pstmt.setInt(7, (node.isNotifiedOfConfigChanges() ? 1 : 0));
        pstmt.setInt(8, (node.isNotifiedOfDelete() ? 1 : 0));
        pstmt.setInt(9, (node.isNotifiedOfRetract() ? 1 : 0));
        pstmt.setInt(10, (node.isPresenceBasedDelivery() ? 1 : 0));
        pstmt.setInt(11, (node.isSendItemSubscribe() ? 1 : 0));
        pstmt.setString(12, node.getPublisherModel().getName());
        pstmt.setInt(13, (node.isSubscriptionEnabled() ? 1 : 0));
        pstmt.setInt(14, (node.isSubscriptionConfigurationRequired() ? 1 : 0));
        pstmt.setString(15, node.getAccessModel().getName());
        pstmt.setString(16, node.getPayloadType());
        pstmt.setString(17, node.getBodyXSLT());
        pstmt.setString(18, node.getDataformXSLT());
        pstmt.setString(19, node.getDescription());
        pstmt.setString(20, node.getLanguage());
        pstmt.setString(21, node.getName());
        if (node.getReplyPolicy() != null) {
            pstmt.setString(22, node.getReplyPolicy().name());
        }
        else {
            pstmt.setString(22, null);
        }
        if (node.isCollectionNode()) {
            pstmt.setString(23, ((CollectionNode) node).getAssociationPolicy().name());
            pstmt.setInt(24, ((CollectionNode) node).getMaxLeafNodes());
        }
        else {
            pstmt.setString(23, null);
            pstmt.setInt(24, 0);
        }
        pstmt.setString(25, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
        pstmt.setString(26, encodeNodeID(node.getNodeID()));
        pstmt.executeUpdate();
        DbConnectionManager.fastcloseStmt(pstmt);

        // Remove existing JIDs associated with the the node
        pstmt = con.prepareStatement(DELETE_NODE_JIDS);
        pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
        pstmt.setString(2, encodeNodeID(node.getNodeID()));
        pstmt.executeUpdate();
        DbConnectionManager.fastcloseStmt(pstmt);

        // Remove roster groups associated with the the node being deleted
        pstmt = con.prepareStatement(DELETE_NODE_GROUPS);
        pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
        pstmt.setString(2, encodeNodeID(node.getNodeID()));
        pstmt.executeUpdate();

        // Save associated JIDs and roster groups
        saveAssociatedElements(con, node);
    }
    catch (SQLException sqle) {
        log.error("An exception occurred while updating a node ({}) in the database.", node.getUniqueIdentifier(), sqle);
        abortTransaction = true;
    }
    finally {
        DbConnectionManager.closeStatement(pstmt);
        DbConnectionManager.closeTransactionConnection(con, abortTransaction);
    }
}
 

@Override
 public void removeNode(Node node)
 {
     log.trace( "Removing node: {} (write to database)", node.getUniqueIdentifier() );

     if ( node instanceof LeafNode ) {
         purgeNode( (LeafNode) node );
     }

     Connection con = null;
     PreparedStatement pstmt = null;
     boolean abortTransaction = false;
     try {
         con = DbConnectionManager.getTransactionConnection();
         // Remove the affiliate from the table of node affiliates
         pstmt = con.prepareStatement(DELETE_NODE);
         pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
         pstmt.setString(2, encodeNodeID(node.getNodeID()));
         pstmt.executeUpdate();
         DbConnectionManager.fastcloseStmt(pstmt);

         // Remove JIDs associated with the the node being deleted
         pstmt = con.prepareStatement(DELETE_NODE_JIDS);
         pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
         pstmt.setString(2, encodeNodeID(node.getNodeID()));
         pstmt.executeUpdate();
         DbConnectionManager.fastcloseStmt(pstmt);

         // Remove roster groups associated with the the node being deleted
         pstmt = con.prepareStatement(DELETE_NODE_GROUPS);
         pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
         pstmt.setString(2, encodeNodeID(node.getNodeID()));
         pstmt.executeUpdate();
         DbConnectionManager.fastcloseStmt(pstmt);

         // Remove published items of the node being deleted
if (node instanceof LeafNode)
{
	purgeNode((LeafNode) node, con);
}

         // Remove all affiliates from the table of node affiliates
         pstmt = con.prepareStatement(DELETE_AFFILIATIONS);
         pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
         pstmt.setString(2, encodeNodeID(node.getNodeID()));
         pstmt.executeUpdate();
         DbConnectionManager.fastcloseStmt(pstmt);

         // Remove users that were subscribed to the node
         pstmt = con.prepareStatement(DELETE_SUBSCRIPTIONS);
         pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
         pstmt.setString(2, encodeNodeID(node.getNodeID()));
         pstmt.executeUpdate();
     }
     catch (SQLException sqle) {
         log.error("An exception occurred while removing a node ({}) in the database.", node.getUniqueIdentifier(), sqle);
         abortTransaction = true;
     }
     finally {
         DbConnectionManager.closeStatement(pstmt);
         DbConnectionManager.closeTransactionConnection(con, abortTransaction);
     }
 }
 

@Override
 public void loadNodes(PubSubService service) {
     log.debug( "Loading nodes for service: {}", service.getServiceID() );

     Connection con = null;
     PreparedStatement pstmt = null;
     ResultSet rs = null;
     Map<Node.UniqueIdentifier, Node> nodes = new HashMap<>();
     try {
         con = DbConnectionManager.getConnection();
         // Get all non-leaf nodes (to ensure parent nodes are loaded before their children)
pstmt = con.prepareStatement(LOAD_NODES);
         pstmt.setString(1, service.getServiceID());
         rs = pstmt.executeQuery();
         
         Map<Node.UniqueIdentifier, Node.UniqueIdentifier> parentMappings = new HashMap<>();
         
         // Rebuild loaded non-leaf nodes
         while(rs.next()) {
             loadNode(service.getUniqueIdentifier(), nodes, parentMappings, rs);
         }
         DbConnectionManager.fastcloseStmt(rs, pstmt);

         if (nodes.size() == 0) {
         	log.info("No nodes found in pubsub for service {}", service.getServiceID() );
         	return;
         }
         
         for (Map.Entry<Node.UniqueIdentifier, Node.UniqueIdentifier> entry : parentMappings.entrySet()) {
         	Node child = nodes.get(entry.getKey());
         	CollectionNode parent = (CollectionNode) nodes.get(entry.getValue());
         	
         	if (parent == null) {
         		log.error("Could not find parent node " + entry.getValue() + " for node " + entry.getKey());
         	}
         	else {
                 child.changeParent(parent);
         	}
         }
         // Get JIDs associated with all nodes
         pstmt = con.prepareStatement(LOAD_NODES_JIDS);
         pstmt.setString(1, service.getServiceID());
         rs = pstmt.executeQuery();
         // Add to each node the associated JIDs
         while(rs.next()) {
             loadAssociatedJIDs(nodes, rs);
         }
         DbConnectionManager.fastcloseStmt(rs, pstmt);

         // Get roster groups associateds with all nodes
         pstmt = con.prepareStatement(LOAD_NODES_GROUPS);
         pstmt.setString(1, service.getServiceID());
         rs = pstmt.executeQuery();
         // Add to each node the associated Groups
         while(rs.next()) {
             loadAssociatedGroups(nodes, rs);
         }
         DbConnectionManager.fastcloseStmt(rs, pstmt);

         // Get affiliations of all nodes
         pstmt = con.prepareStatement(LOAD_AFFILIATIONS);
         pstmt.setString(1, service.getServiceID());
         rs = pstmt.executeQuery();
         // Add to each node the correspondiding affiliates
         while(rs.next()) {
             loadAffiliations(nodes, rs);
         }
         DbConnectionManager.fastcloseStmt(rs, pstmt);

         // Get subscriptions to all nodes
         pstmt = con.prepareStatement(LOAD_SUBSCRIPTIONS);
         pstmt.setString(1, service.getServiceID());
         rs = pstmt.executeQuery();
         // Add to each node the correspondiding subscriptions
         while(rs.next()) {
             loadSubscriptions(nodes, rs);
         }
         DbConnectionManager.fastcloseStmt(rs, pstmt);
     }
     catch (SQLException sqle) {
         log.error("An exception occurred while loading nodes for a service ({}) from the database.", service.getUniqueIdentifier(), sqle);
     }
     finally {
         DbConnectionManager.closeConnection(rs, pstmt, con);
     }

     for (Node node : nodes.values()) {
         // Set now that the node is persistent in the database. Note: We need to
         // set this now since otherwise the node's affiliations will be saved to the database
         // "again" while adding them to the node!
         node.setSavedToDB(true);
         // Add the node to the service
         service.addNode(node);
     }
 }
 

@Override
public void loadNode(PubSubService service, Node.UniqueIdentifier nodeIdentifier)
{
       log.debug( "Loading node: {}", nodeIdentifier );

       Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	Map<Node.UniqueIdentifier, Node> nodes = new HashMap<>();
	try
	{
		con = DbConnectionManager.getConnection();
		// Get all non-leaf nodes (to ensure parent nodes are loaded before
		// their children)
		pstmt = con.prepareStatement(LOAD_NODE);
		pstmt.setString(1, nodeIdentifier.getServiceIdentifier().getServiceId());
		pstmt.setString(2, nodeIdentifier.getNodeId());
		rs = pstmt.executeQuery();
		Map<Node.UniqueIdentifier, Node.UniqueIdentifier> parentMapping = new HashMap<>();
		
		// Rebuild loaded non-leaf nodes
		if (rs.next())
		{
			loadNode(nodeIdentifier.getServiceIdentifier(), nodes, parentMapping, rs);
		}
		DbConnectionManager.fastcloseStmt(rs, pstmt);
           Node.UniqueIdentifier parentId = parentMapping.get(nodeIdentifier);
		
		if (parentId != null) {
               nodes.get(nodeIdentifier).changeParent((CollectionNode)nodes.get(parentId));
		}
			
		// Get JIDs associated with all nodes
		pstmt = con.prepareStatement(LOAD_NODE_JIDS);
		pstmt.setString(1, nodeIdentifier.getServiceIdentifier().getServiceId());
		pstmt.setString(2, nodeIdentifier.getNodeId());
		rs = pstmt.executeQuery();
		// Add to each node the associated JIDs
		while (rs.next())
		{
			loadAssociatedJIDs(nodes, rs);
		}
		DbConnectionManager.fastcloseStmt(rs, pstmt);

		// Get roster groups associated with all nodes
		pstmt = con.prepareStatement(LOAD_NODE_GROUPS);
		pstmt.setString(1, nodeIdentifier.getServiceIdentifier().getServiceId());
		pstmt.setString(2, nodeIdentifier.getNodeId());
		rs = pstmt.executeQuery();
		// Add to each node the associated Groups
		while (rs.next())
		{
			loadAssociatedGroups(nodes, rs);
		}
		DbConnectionManager.fastcloseStmt(rs, pstmt);

		// Get affiliations of all nodes
		pstmt = con.prepareStatement(LOAD_NODE_AFFILIATIONS);
		pstmt.setString(1, nodeIdentifier.getServiceIdentifier().getServiceId());
		pstmt.setString(2, nodeIdentifier.getNodeId());
		rs = pstmt.executeQuery();
		// Add to each node the corresponding affiliates
		while (rs.next())
		{
			loadAffiliations(nodes, rs);
		}
		DbConnectionManager.fastcloseStmt(rs, pstmt);

		// Get subscriptions to all nodes
		pstmt = con.prepareStatement(LOAD_NODE_SUBSCRIPTIONS);
		pstmt.setString(1, nodeIdentifier.getServiceIdentifier().getServiceId());
		pstmt.setString(2, nodeIdentifier.getNodeId());
		rs = pstmt.executeQuery();
		// Add to each node the corresponding subscriptions
		while (rs.next())
		{
			loadSubscriptions(nodes, rs);
		}
		DbConnectionManager.fastcloseStmt(rs, pstmt);
	}
	catch (SQLException sqle)
	{
           log.error("An exception occurred while loading a node ({}) from the database.", nodeIdentifier, sqle);
	}
	finally
	{
		DbConnectionManager.closeConnection(rs, pstmt, con);
	}

	for (Node node : nodes.values())
	{
		// Set now that the node is persistent in the database. Note: We
		// need to
		// set this now since otherwise the node's affiliations will be
		// saved to the database
		// "again" while adding them to the node!
		node.setSavedToDB(true);
		// Add the node to the service
		service.addNode(node);
	}
}
 

@Override
public Iterator<RosterItem> getItems(String username) {
    final LinkedList<RosterItem> cachedValue = rosterItemCache.get( username );
    if ( cachedValue != null ) {
        return cachedValue.iterator();
    }
    LinkedList<RosterItem> itemList = new LinkedList<>();
    Map<Long, RosterItem> itemsByID = new HashMap<>();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        // Load all the contacts in the roster
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(LOAD_ROSTER);
        pstmt.setString(1, username);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            // Create a new RosterItem (ie. user contact) from the stored information
            RosterItem item = new RosterItem(rs.getLong(2),
                    new JID(rs.getString(1)),
                    RosterItem.SubType.getTypeFromInt(rs.getInt(3)),
                    RosterItem.AskType.getTypeFromInt(rs.getInt(4)),
                    RosterItem.RecvType.getTypeFromInt(rs.getInt(5)),
                    rs.getString(6),
                    null);
            // Add the loaded RosterItem (ie. user contact) to the result
            itemList.add(item);
            itemsByID.put(item.getID(), item);
        }
        // Close the statement and result set
        DbConnectionManager.fastcloseStmt(rs, pstmt);
        // Set null to pstmt to be sure that it's not closed twice. It seems that
        // Sybase driver is raising an error when trying to close an already closed statement.
        // it2000 comment: TODO interesting, that's the only place with the sybase fix
        // it2000 comment: one should move this in closeStatement()
        pstmt = null;

        // Load the groups for the loaded contact
        if (!itemList.isEmpty()) {
            pstmt = con.prepareStatement(String.format(LOAD_ROSTER_ITEM_GROUPS, DbConnectionManager.getDatabaseType().escapeIdentifier("rank")));
            pstmt.setString(1, username);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                itemsByID.get(rs.getLong(1)).getGroups().add(rs.getString(2));
            }
        }

        rosterItemCache.put( username, itemList );
    }
    catch (SQLException e) {
        Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return itemList.iterator();
}
 

private static void writeNode( Connection con, PubsubRecordData record ) throws SQLException
{
    PreparedStatement pstmt = null;
    try
    {
        pstmt = con.prepareStatement("INSERT INTO ofPubsubNode (serviceID, nodeID, leaf, creationDate, modificationDate, " +
                                         "parent, deliverPayloads, maxPayloadSize, persistItems, maxItems, " +
                                         "notifyConfigChanges, notifyDelete, notifyRetract, presenceBased, " +
                                         "sendItemSubscribe, publisherModel, subscriptionEnabled, configSubscription, " +
                                         "accessModel, payloadType, bodyXSLT, dataformXSLT, creator, description, " +
                                         "language, name, replyPolicy, associationPolicy, maxLeafNodes) " +
                                         "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" );
        pstmt.setString(1, record.serviceID);
        pstmt.setString(2, record.nodeID);
        pstmt.setInt(3, record.leaf);
        pstmt.setString(4, record.creationDate);
        pstmt.setString(5, record.modificationDate);
        pstmt.setString(6, record.parent);
        pstmt.setInt(7, record.deliverPayloads);
        pstmt.setInt(8, record.maxPayloadSize);
        pstmt.setInt(9, record.persistPublishedItems);
        pstmt.setInt(10, record.maxPublishedItems);
        pstmt.setInt(11, record.notifiedOfConfigChanges);
        pstmt.setInt(12, record.notifiedOfDelete);
        pstmt.setInt(13, record.notifiedOfRetract);
        pstmt.setInt(14, record.presenceBasedDelivery);
        pstmt.setInt(15, record.sendItemsubscribe);
        pstmt.setString(16, record.publisherModel);
        pstmt.setInt(17, record.subscriptionEnabled);
        pstmt.setInt(18, record.subscriptionConfigurationRequired);
        pstmt.setString(19, record.accessModel);
        pstmt.setString(20, record.payloadType);
        pstmt.setString(21, record.bodyXSLT);
        pstmt.setString(22, record.dataformXSLT);
        pstmt.setString(23, record.creator);
        pstmt.setString(24, record.description);
        pstmt.setString(25, record.language);
        pstmt.setString(26, record.name);
        pstmt.setString(27, record.replyPolicy);
        pstmt.setString(28, record.associationPolicy);
        pstmt.setInt(29, record.maxLeafNodes);
        pstmt.executeUpdate();
    }
    finally
    {
        DbConnectionManager.fastcloseStmt( pstmt );
    }
}