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

public void createPublishedItem(PublishedItem item)
{
    log.trace( "Creating published item: {} (write to database)", item.getUniqueIdentifier() );

    Connection con;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(ADD_ITEM);
        pstmt.setString(1, item.getNode().getUniqueIdentifier().getServiceIdentifier().getServiceId());
        pstmt.setString(2, encodeNodeID(item.getNodeID()));
        pstmt.setString(3, item.getID());
        pstmt.setString(4, item.getPublisher().toString());
        pstmt.setString(5, StringUtils.dateToMillis( item.getCreationDate()));
        pstmt.setString(6, item.getPayloadXML());
        pstmt.execute();
    } catch (SQLException ex) {
        log.error("Published item could not be created in database: {}\n{}", item.getUniqueIdentifier(), item.getPayloadXML(), ex);
    } finally {
        DbConnectionManager.closeStatement(pstmt);
    }
}
 

public void updatePublishedItem(PublishedItem item)
{
    log.trace( "Updating published item: {} (write to database)", item.getUniqueIdentifier() );

    Connection con;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(UPDATE_ITEM);
        pstmt.setString(1, item.getPublisher().toString());
        pstmt.setString(2, StringUtils.dateToMillis( item.getCreationDate()));
        pstmt.setString(3, item.getPayloadXML());
        pstmt.setString(4, item.getNode().getUniqueIdentifier().getServiceIdentifier().getServiceId());
        pstmt.setString(5, encodeNodeID(item.getNodeID()));
        pstmt.setString(6, item.getID());
        pstmt.execute();
    } catch (SQLException ex) {
        log.error("Published item could not be updated in database: {}\n{}", item.getUniqueIdentifier(), item.getPayloadXML(), ex);
    } finally {
        DbConnectionManager.closeStatement(pstmt);
    }
}
 

@Override
public void removePublishedItem(PublishedItem item) {
    Connection con;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(DELETE_ITEM);
        pstmt.setString(1, item.getNode().getUniqueIdentifier().getServiceIdentifier().getServiceId());
        pstmt.setString(2, encodeNodeID(item.getNode().getNodeID()));
        pstmt.setString(3, item.getID());
        pstmt.execute();
    } catch (SQLException ex) {
        log.error("Failed to delete published item from DB: {}", item.getUniqueIdentifier(), ex);
    } finally {
        DbConnectionManager.closeStatement(pstmt);
    }
}
 

private void purgeNode(LeafNode leafNode, Connection con) throws SQLException
{
       // Remove published items of the node being deleted
       PreparedStatement pstmt = null;

	try
	{
           pstmt = con.prepareStatement(DELETE_ITEMS);
           pstmt.setString(1, leafNode.getUniqueIdentifier().getServiceIdentifier().getServiceId());
           pstmt.setString(2, encodeNodeID(leafNode.getNodeID()));
           pstmt.executeUpdate();
	}
	finally
	{
		DbConnectionManager.closeStatement(pstmt);
	}
}
 

/**
 * Insert the groups into the given roster item.
 *
 * @param rosterID the roster ID of the item the groups belong to
 * @param iter an iterator over the group names to insert
 * @param con the database connection to use for the operation.
 * @throws SQLException if an SQL exception occurs.
 */
private void insertGroups(long rosterID, Iterator<String> iter, Connection con) throws SQLException
{
    PreparedStatement pstmt = null;
    try {
        pstmt = con.prepareStatement(String.format(CREATE_ROSTER_ITEM_GROUPS, DbConnectionManager.getDatabaseType().escapeIdentifier("rank")));
        pstmt.setLong(1, rosterID);
        for (int i = 0; iter.hasNext(); i++) {
            pstmt.setInt(2, i);
            String groupName = iter.next();
            pstmt.setString(3, groupName);
            try {
                pstmt.executeUpdate();
            }
            catch (SQLException e) {
                Log.error(e.getMessage(), e);
            }
        }
    }
    finally {
        DbConnectionManager.closeStatement(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);
    }
}
 

public void savePublishedItems(Connection con, List<PublishedItem> addList, boolean batch) throws SQLException
{
    if (addList == null || addList.isEmpty() )
    {
        return;
    }

    PreparedStatement pstmt = null;
    try {
        pstmt = con.prepareStatement(ADD_ITEM);
        boolean hasBatchItems = false;
        for ( final PublishedItem item : addList)
        {
            pstmt.setString(1, item.getNode().getUniqueIdentifier().getServiceIdentifier().getServiceId());
            pstmt.setString(2, encodeNodeID(item.getNodeID()));
            pstmt.setString(3, item.getID());
            pstmt.setString(4, item.getPublisher().toString());
            pstmt.setString(5, StringUtils.dateToMillis(item.getCreationDate()));
            pstmt.setString(6, item.getPayloadXML());
            if ( batch ) {
                hasBatchItems = true;
                pstmt.addBatch();
            } else {
                pstmt.execute();
            }
        }
        if (hasBatchItems) {
            pstmt.executeBatch();
        }
    } finally {
        DbConnectionManager.closeStatement(pstmt);
    }
}
 

protected void removePublishedItems(Connection con, List<PublishedItem> delList, boolean batch) throws SQLException
{
    if (delList == null || delList.isEmpty() )
    {
        return;
    }

    PreparedStatement pstmt = null;
    try {
        pstmt = con.prepareStatement(DELETE_ITEM);
        boolean hasBatchItems = false;
        for ( final PublishedItem item : delList )
        {
            pstmt.setString(1, item.getNode().getUniqueIdentifier().getServiceIdentifier().getServiceId());
            pstmt.setString(2, encodeNodeID(item.getNode().getNodeID()));
            pstmt.setString(3, item.getID());
            if ( batch ) {
                hasBatchItems = true;
                pstmt.addBatch();
            } else {
                pstmt.execute();
            }
        }
        if (hasBatchItems) {
            pstmt.executeBatch();
        }
    } finally {
        DbConnectionManager.closeStatement(pstmt);
    }
}
 

@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 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 createNode(Node node)
{
    log.trace( "Creating node: {} (write to database)", node.getUniqueIdentifier() );

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

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

/**
   * Purges all items from the database that exceed the defined item count on
   * all nodes.
   */
  private void purgeItems()
  {
boolean abortTransaction = false;
      Connection con = null;
PreparedStatement nodeConfig = null;
PreparedStatement purgeNode = null;
      ResultSet rs = null;

      try
      {
          con = DbConnectionManager.getTransactionConnection();
	nodeConfig = con.prepareStatement(PERSISTENT_NODES);
          rs = nodeConfig.executeQuery();
          purgeNode = con.prepareStatement(getPurgeStatement(DbConnectionManager.getDatabaseType()));

          boolean hasBatchItems = false;
          while (rs.next())
          {
              hasBatchItems = true;
          	String svcId = rs.getString(1);
          	String nodeId = rs.getString(2);
          	int maxItems = rs.getInt(3);

		setPurgeParams(DbConnectionManager.getDatabaseType(), purgeNode, svcId, nodeId, maxItems);

		purgeNode.addBatch();
          }
	if (hasBatchItems) purgeNode.executeBatch();
}
catch (Exception sqle)
{
    log.error(sqle.getMessage(), sqle);
	abortTransaction = true;
}
finally
{
          DbConnectionManager.closeResultSet(rs);
          DbConnectionManager.closeStatement(nodeConfig);
          DbConnectionManager.closeStatement(purgeNode);
          DbConnectionManager.closeTransactionConnection(con, abortTransaction);
}
  }