org.jivesoftware.database.DbConnectionManager Java Examples

/**
 * Deletes all offline messages in the store for a user.
 *
 * @param username the username of the user who's messages are going to be deleted.
 */
public void deleteMessages(String username) {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(DELETE_OFFLINE);
        pstmt.setString(1, username);
        pstmt.executeUpdate();
        
        removeUsernameFromSizeCache(username);
    }
    catch (Exception e) {
        Log.error("Error deleting offline messages of username: " + username, e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

/**
 * Loads properties from the database.
 */
private synchronized void loadPropertiesFromDb() {
    this.properties = new Hashtable<String, String>();
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(LOAD_PROPERTIES);
        pstmt.setLong(1, bookmarkID);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            String name = rs.getString(1);
            String value = rs.getString(2);
            properties.put(name, value);
        }
        rs.close();
    }
    catch (SQLException sqle) {
        Log.error(sqle.getMessage(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

/**
 * Updated the existing privacy list in the database.
 *
 * @param username the username of the user that updated a privacy list.
 * @param list the PrivacyList to update in the database.
 */
public void updatePrivacyList(String username, PrivacyList list) {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(UPDATE_PRIVACY_LIST);
        pstmt.setInt(1, (list.isDefault() ? 1 : 0));
        pstmt.setString(2, list.asElement().asXML());
        pstmt.setString(3, username);
        pstmt.setString(4, list.getName());
        pstmt.executeUpdate();
    }
    catch (Exception e) {
        Log.error("Error updating privacy list: " + list.getName() + " of username: " +
                username, e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
    databaseContainsPrivacyLists.set(true);
}
 

@Override
public void setName(String username, String name) throws UserNotFoundException {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(UPDATE_NAME);
        if (name == null || name.matches("\\s*")) {
            pstmt.setNull(1, Types.VARCHAR);
        } 
        else {
            pstmt.setString(1, name);
        }
        pstmt.setString(2, username);
        pstmt.executeUpdate();
    }
    catch (SQLException sqle) {
        throw new UserNotFoundException(sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

@Override
public void setCreationDate(String username, Date creationDate) throws UserNotFoundException {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(UPDATE_CREATION_DATE);
        pstmt.setString(1, StringUtils.dateToMillis(creationDate));
        pstmt.setString(2, username);
        pstmt.executeUpdate();
    }
    catch (SQLException sqle) {
        throw new UserNotFoundException(sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

private void deleteOfflinePresenceFromDB(String username) {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(DELETE_OFFLINE_PRESENCE);
        pstmt.setString(1, username);
        pstmt.execute();
    }
    catch (SQLException sqle) {
        Log.error(sqle.getMessage(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

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

/**
 * Removes any existing defined permission and configuration for the specified
 * remote server.
 *
 * @param domain the domain of the remote server.
 */
public static void deleteConfiguration(String domain) {
    // Remove configuration from cache
    configurationsCache.remove(domain);
    // Remove the permission for the entity from the database
    java.sql.Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(DELETE_CONFIGURATION);
        pstmt.setString(1, domain);
        pstmt.executeUpdate();
    }
    catch (SQLException sqle) {
        Log.error(sqle.getMessage(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

/**
 * Creates and saves the new privacy list to the database.
 *
 * @param username the username of the user that created a new privacy list.
 * @param list the PrivacyList to save.
 */
public void createPrivacyList(String username, PrivacyList list) {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(INSERT_PRIVACY_LIST);
        pstmt.setString(1, username);
        pstmt.setString(2, list.getName());
        pstmt.setInt(3, (list.isDefault() ? 1 : 0));
        pstmt.setString(4, list.asElement().asXML());
        pstmt.executeUpdate();
    }
    catch (Exception e) {
        Log.error("Error adding privacy list: " + list.getName() + " of username: " + username,
                e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
    databaseContainsPrivacyLists.set(true);
}
 

/**
 * Gets a specific subdomain by a service's ID number.
 * @param serviceID ID to retrieve subdomain for.
 * @return Subdomain of service.
 */
private String loadServiceSubdomain(Long serviceID) {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String subdomain = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(LOAD_SUBDOMAIN);
        pstmt.setLong(1, serviceID);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            subdomain = rs.getString(1);
        }
        else {
            throw new Exception("Unable to locate subdomain for service ID "+serviceID);
        }
    }
    catch (Exception e) {
        // No problem, considering this as a "not found".
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return subdomain;
}
 

/**
 * Updates an existing service's subdomain and description in the database.
 * @param serviceID ID of the service to update.
 * @param subdomain Subdomain to set service to.
 * @param description Description of MUC service.  Can be null for default description.
 */
private void updateService(Long serviceID, String subdomain, String description) {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(UPDATE_SERVICE);
        pstmt.setString(1, subdomain);
        if (description != null) {
            pstmt.setString(2, description);
        }
        else {
            pstmt.setNull(2, Types.VARCHAR);
        }
        pstmt.setLong(3, serviceID);
        pstmt.executeUpdate();
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

private void writeToDatabase(String username, String offlinePresence, Date offlinePresenceDate) {
    // delete existing offline presence (if any)
    deleteOfflinePresenceFromDB(username);

    // Insert data into the database.
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(INSERT_OFFLINE_PRESENCE);
        pstmt.setString(1, username);
        if (offlinePresence != null) {
            DbConnectionManager.setLargeTextField(pstmt, 2, offlinePresence);
        } else {
            pstmt.setNull(2, Types.VARCHAR);
        }
        pstmt.setString(3, StringUtils.dateToMillis(offlinePresenceDate));
        pstmt.execute();
    } catch (SQLException sqle) {
        Log.error("Error storing offline presence of user: " + username, sqle);
    } finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

@Override
public void insertProperty( String username, String propName, String propValue )
{
    Connection con = null;
    PreparedStatement pstmt = null;
    try
    {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement( INSERT_PROPERTY );
        pstmt.setString( 1, username );
        pstmt.setString( 2, propName );
        pstmt.setString( 3, propValue );
        pstmt.executeUpdate();
    }
    catch ( SQLException e )
    {
        Log.error( e.getMessage(), e );
    }
    finally
    {
        DbConnectionManager.closeConnection( pstmt, con );
    }
}
 

/**
 * Returns the number of XML messages stored for a particular user.
 *
 * @param username the username of the user.
 * @return the amount of stored messages.
 */
public int getCount(String username) {
    // No cache: this needs to be more accurate than the 'size' method (that does have a cache).
    // Maintaining a cache would likely add more overhead than that the cache would save.
    int count = 0;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(SELECT_COUNT_OFFLINE);
        pstmt.setString(1, username);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
    }
    catch (Exception e) {
        Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return count;
}
 

/**
 * Persist a new group property to the database for the current group
 * 
 * @param key Property name
 * @param value Property value
 */
private synchronized void insertProperty(String key, String value) {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(INSERT_PROPERTY);
        pstmt.setString(1, group.getName());
        pstmt.setString(2, key);
        pstmt.setString(3, value);
        pstmt.executeUpdate();
    }
    catch (SQLException e) {
        logger.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
    Map<String, Object> event = new HashMap<>();
    event.put("propertyKey", key);
    event.put("type", "propertyAdded");
    GroupEventDispatcher.dispatchEvent(group,
            GroupEventDispatcher.EventType.group_modified, event);
}
 

/**
 * Deletes a bookmark with the specified bookmark ID.
 *
 * @param bookmarkID the ID of the bookmark to remove from the database.
 */
public static void deleteBookmark(long bookmarkID) {
    Connection con = null;
    PreparedStatement pstmt = null;

    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(DELETE_BOOKMARK);
        pstmt.setLong(1, bookmarkID);
        pstmt.execute();
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

@Override
public void createAffiliation(Node node, NodeAffiliate affiliate)
{
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(ADD_AFFILIATION);
        pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
        pstmt.setString(2, encodeNodeID(node.getNodeID()));
        pstmt.setString(3, affiliate.getJID().toString());
        pstmt.setString(4, affiliate.getAffiliation().name());
        pstmt.executeUpdate();
    }
    catch (SQLException sqle) {
        log.error("An exception occurred while creating an affiliation ({}) to a node ({}) in the database.", affiliate, node.getUniqueIdentifier(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

@Override
public void removeAffiliation(Node node, NodeAffiliate affiliate)
{
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        // Remove the affiliate from the table of node affiliates
        pstmt = con.prepareStatement(DELETE_AFFILIATION);
        pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
        pstmt.setString(2, encodeNodeID(node.getNodeID()));
        pstmt.setString(3, affiliate.getJID().toString());
        pstmt.executeUpdate();
    }
    catch (SQLException sqle) {
        log.error("An exception occurred while removing an affiliation ({}) to a node ({}) in the database.", affiliate, node.getUniqueIdentifier(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

@Override
public void removeSubscription(NodeSubscription subscription)
{
    log.trace( "Removing node subscription: {} {} (write to database)", subscription.getNode().getUniqueIdentifier(), subscription.getID() );

    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        // Remove the affiliate from the table of node affiliates
        pstmt = con.prepareStatement(DELETE_SUBSCRIPTION);
        pstmt.setString(1, subscription.getNode().getUniqueIdentifier().getServiceIdentifier().getServiceId());
        pstmt.setString(2, encodeNodeID(subscription.getNode().getNodeID()));
        pstmt.setString(3, subscription.getID());
        pstmt.executeUpdate();
    }
    catch (SQLException sqle) {
        log.error("An exception occurred while removing a subscription ({}) in the database.", subscription, sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

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);
    }
}
 

@Override
public int getItemCount(String username) {
    int count = 0;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(COUNT_ROSTER_ITEMS);
        pstmt.setString(1, username);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
    }
    catch (SQLException e) {
        Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return count;
}
 

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 );
    }
}
 

@Override
public void setModificationDate(String username, Date modificationDate) throws UserNotFoundException {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(UPDATE_MODIFICATION_DATE);
        pstmt.setString(1, StringUtils.dateToMillis(modificationDate));
        pstmt.setString(2, username);
        pstmt.executeUpdate();
    }
    catch (SQLException sqle) {
        throw new UserNotFoundException(sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

@Override
public void purgeNode(LeafNode leafNode)
{
	Connection con = null;
	boolean rollback = false;

	try
	{
		con = DbConnectionManager.getTransactionConnection();

		purgeNode(leafNode, con);
	}
	catch (SQLException exc)
	{
		log.error(exc.getMessage(), exc);
		rollback = true;
	}
	finally
	{
		DbConnectionManager.closeTransactionConnection(con, rollback);
	}
}
 

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);
	}
}
 

@Override
public void updateProperty( String username, String propName, String propValue )
{
    Connection con = null;
    PreparedStatement pstmt = null;
    try
    {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement( UPDATE_PROPERTY );
        pstmt.setString( 1, propValue );
        pstmt.setString( 2, propName );
        pstmt.setString( 3, username );
        pstmt.executeUpdate();
    }
    catch ( SQLException e )
    {
        Log.error( e.getMessage(), e );
    }
    finally
    {
        DbConnectionManager.closeConnection( pstmt, con );
    }
}
 

@Override
public int getUserCount() {
    int count = 0;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = getConnection();
        pstmt = con.prepareStatement(userCountSQL);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return count;
}
 

@Override
public Collection<String> getGroupNames() {
    List<String> groupNames = new ArrayList<>();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = getConnection();
        pstmt = con.prepareStatement(allGroupsSQL);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            groupNames.add(rs.getString(1));
        }
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return groupNames;
}