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

@Override
public void deleteVCard(String username) {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(DELETE_PROPERTIES);
        pstmt.setString(1, username);
        pstmt.executeUpdate();
    }
    catch (SQLException e) {
        Log.error("Error deleting vCard of username: " + username, e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

@Override
public Iterator<String> getUsernames(String jid) {
    List<String> answer = new ArrayList<>();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(LOAD_USERNAMES);
        pstmt.setString(1, jid);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            answer.add(rs.getString(1));
        }
    }
    catch (SQLException e) {
        Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return answer.iterator();
}
 

/**
 * Returns the reserved room nickname for the bare JID in a given room or null if none.
 *
 * @param room the room where the user would like to obtain his reserved nickname. 
 * @param bareJID The bare jid of the user of which you'd like to obtain his reserved nickname.
 * @return the reserved room nickname for the bare JID or null if none.
 */
public static String getReservedNickname(MUCRoom room, String bareJID) {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String answer = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(GET_RESERVED_NAME);
        pstmt.setLong(1, room.getID());
        pstmt.setString(2, bareJID);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            answer = rs.getString(1);
        }
    }
    catch (SQLException sqle) {
        Log.error(sqle.getMessage(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return answer;
}
 

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

/**
 * Adds a new permission for the specified remote server.
 *
 * @param configuration the new configuration for a remote server
 */
private static void addConfiguration(RemoteServerConfiguration configuration) {
    // Remove configuration from cache
    configurationsCache.put(configuration.getDomain(), configuration);
    // Remove the permission for the entity from the database
    java.sql.Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(ADD_CONFIGURATION);
        pstmt.setString(1, configuration.getDomain());
        pstmt.setInt(2, configuration.getRemotePort());
        pstmt.setString(3, configuration.getPermission().toString());
        pstmt.executeUpdate();
    }
    catch (SQLException sqle) {
        Log.error(sqle.getMessage(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

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

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

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

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 initializePlugin(PluginManager manager, File pluginDirectory) {
	try {
		openfireDBConn = DbConnectionManager.getConnection();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	
	server = XMPPServer.getInstance();
	
	server.getIQRouter().addHandler(new LocationHandler(MODULE_NAME_LOCATION));
}
 

@Override
public Element updateVCard(String username, Element vCardElement) throws NotFoundException {
    if (loadVCard(username) == null) {
        // The user does not have a vCard
        throw new NotFoundException("Username " + username + " does not have a vCard");
    }

    if ( JiveGlobals.getBooleanProperty( PhotoResizer.PROPERTY_RESIZE_ON_CREATE, PhotoResizer.PROPERTY_RESIZE_ON_CREATE_DEFAULT ) )
    {
        PhotoResizer.resizeAvatar( vCardElement );
    }

    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(UPDATE_PROPERTIES);
        pstmt.setString(1, vCardElement.asXML());
        pstmt.setString(2, username);
        pstmt.executeUpdate();
    }
    catch (SQLException e) {
        Log.error("Error updating vCard of username: " + username, e);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
    return vCardElement;
}
 

/**
 * The default provider retrieves events from a ofSecurityAuditLog table in the database.
 * @see org.jivesoftware.openfire.security.SecurityAuditProvider#getEvent(Integer)
 */
@Override
public SecurityAuditEvent getEvent(Integer msgID) throws EventNotFoundException {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    SecurityAuditEvent event = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(GET_EVENT);
        pstmt.setLong(1, msgID);
        rs = pstmt.executeQuery();
        if (!rs.next()) {
            throw new EventNotFoundException();
        }
        event = new SecurityAuditEvent();
        event.setMsgID(rs.getLong(1));
        event.setUsername(rs.getString(2));
        event.setEventStamp(new Date(rs.getLong(3)));
        event.setSummary(rs.getString(4));
        event.setNode(rs.getString(5));
        event.setDetails(rs.getString(6));
    }
    catch (Exception e) {
        throw new EventNotFoundException();
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }

    return event;
}
 

@Override
public Element loadVCard(String username) {
    synchronized (userBaseMutex.intern(username)) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Element vCardElement = null;
        SAXReader xmlReader = null;
        try {
            // Get a sax reader from the pool
            xmlReader = xmlReaders.take();
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(LOAD_PROPERTIES);
            pstmt.setString(1, username);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                vCardElement =
                        xmlReader.read(new StringReader(rs.getString(1))).getRootElement();
            }
        }
        catch (Exception e) {
            Log.error("Error loading vCard of username: " + username, e);
        }
        finally {
            // Return the sax reader to the pool
            if (xmlReader != null) {
                xmlReaders.add(xmlReader);
            }
            DbConnectionManager.closeConnection(rs, pstmt, con);
        }

        if ( JiveGlobals.getBooleanProperty( PhotoResizer.PROPERTY_RESIZE_ON_LOAD, PhotoResizer.PROPERTY_RESIZE_ON_LOAD_DEFAULT ) )
        {
            PhotoResizer.resizeAvatar( vCardElement );
        }

        return vCardElement;
    }
}
 

/**
 * Loads a bookmark from the database.
 *
 * @throws NotFoundException if the bookmark could not be loaded.
 */
private void loadFromDb() throws NotFoundException {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(LOAD_BOOKMARK);
        pstmt.setLong(1, bookmarkID);
        rs = pstmt.executeQuery();
        if (!rs.next()) {
            throw new NotFoundException("Bookmark not found: " + bookmarkID);
        }
        this.type = Type.valueOf(rs.getString(1));
        this.name = rs.getString(2);
        this.value = rs.getString(3);
        this.global = rs.getInt(4) == 1;
        rs.close();
        pstmt.close();
    }
    catch (SQLException sqle) {
        Log.error(sqle.getMessage(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
}
 

@Override
public PublishedItem getLastPublishedItem(LeafNode node) {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    PublishedItem item = null;

    try {
        con = DbConnectionManager.getConnection();
        // Get published items of the specified node
        pstmt = con.prepareStatement(LOAD_LAST_ITEM);
        pstmt.setFetchSize(1);
        pstmt.setMaxRows(1);
        pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
        pstmt.setString(2, encodeNodeID(node.getNodeID()));
        rs = pstmt.executeQuery();
        // Rebuild loaded published items
        if (rs.next()) {
            String itemID = rs.getString(1);
            JID publisher = new JID(rs.getString(2));
            Date creationDate = new Date(Long.parseLong(rs.getString(3).trim()));
            // Create the item
            item = new PublishedItem(node, publisher, itemID, creationDate);
            // Add the extra fields to the published item
            if (rs.getString(4) != null) {
            	item.setPayloadXML(rs.getString(4));
            }
        }
    }
    catch (Exception sqle) {
        log.error(sqle.getMessage(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return item;
}
 

@Override
public void updateSubscription(Node node, NodeSubscription subscription)
{
    log.trace( "Updating node subscription: {} {} (write to database)", node.getUniqueIdentifier(), subscription.getID() );
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
        con = DbConnectionManager.getConnection();
        if (NodeSubscription.State.none == subscription.getState()) {
            // Remove the subscription of the user from the table
            pstmt = con.prepareStatement(DELETE_SUBSCRIPTION);
            pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
            pstmt.setString(2, encodeNodeID(node.getNodeID()));
            pstmt.setString(2, subscription.getID());
            pstmt.executeUpdate();
        }
        else {
            // Update the subscription of the user in the backend store
            pstmt = con.prepareStatement(UPDATE_SUBSCRIPTION);
            pstmt.setString(1, subscription.getOwner().toString());
            pstmt.setString(2, subscription.getState().name());
            pstmt.setInt(3, (subscription.shouldDeliverNotifications() ? 1 : 0));
            pstmt.setInt(4, (subscription.isUsingDigest() ? 1 : 0));
            pstmt.setInt(5, subscription.getDigestFrequency());
            Date expireDate = subscription.getExpire();
            if (expireDate == null) {
                pstmt.setString(6, null);
            }
            else {
                pstmt.setString(6, StringUtils.dateToMillis(expireDate));
            }
            pstmt.setInt(7, (subscription.isIncludingBody() ? 1 : 0));
            pstmt.setString(8, encodeWithComma(subscription.getPresenceStates()));
            pstmt.setString(9, subscription.getType().name());
            pstmt.setInt(10, subscription.getDepth());
            pstmt.setString(11, subscription.getKeyword());
            pstmt.setString(12, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
            pstmt.setString(13, encodeNodeID(node.getNodeID()));
            pstmt.setString(14, subscription.getID());
            pstmt.executeUpdate();
        }
    }
    catch (SQLException sqle) {
        log.error("An exception occurred while updating a subscription ({}) to a node ({}) in the database.", subscription, node.getUniqueIdentifier(), sqle);
    }
    finally {
        DbConnectionManager.closeConnection(pstmt, con);
    }
}
 

@Override
public String getPassword(String username) throws UserNotFoundException {
    if (!supportsPasswordRetrieval()) {
        // Reject the operation since the provider is read-only
        throw new UnsupportedOperationException();
    }
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    if (username.contains("@")) {
        // Check that the specified domain matches the server's domain
        int index = username.indexOf("@");
        String domain = username.substring(index + 1);
        if (domain.equals(XMPPServer.getInstance().getServerInfo().getXMPPDomain())) {
            username = username.substring(0, index);
        } else {
            // Unknown domain.
            throw new UserNotFoundException();
        }
    }
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(LOAD_PASSWORD);
        pstmt.setString(1, username);
        rs = pstmt.executeQuery();
        if (!rs.next()) {
            throw new UserNotFoundException(username);
        }
        String plainText = rs.getString(1);
        String encrypted = rs.getString(2);
        if (encrypted != null) {
            try {
                return AuthFactory.decryptPassword(encrypted);
            }
            catch (UnsupportedOperationException uoe) {
                // Ignore and return plain password instead.
            }
        }
        if (plainText == null) {
            throw new UnsupportedOperationException();
        }
        return plainText;
    }
    catch (SQLException sqle) {
        throw new UserNotFoundException(sqle);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
}
 

/**
 * The default provider retrieves events from a ofSecurityAuditLog table in the database.
 * @see org.jivesoftware.openfire.security.SecurityAuditProvider#getEvents(String, Integer, Integer, java.util.Date, java.util.Date)
 */
@Override
public List<SecurityAuditEvent> getEvents(String username, Integer skipEvents, Integer numEvents, Date startTime, Date endTime) {
    List<SecurityAuditEvent> events = new ArrayList<>();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = GET_EVENTS;
    boolean addedOne = false;
    if (username != null) {
        sql += " WHERE username = ?";
        addedOne = true;
    }
    if (startTime != null) {
        if (!addedOne) {
            sql += " WHERE";
        }
        else {
            sql += " AND";
        }
        sql += " entryStamp >= ?";
        addedOne = true;
    }
    if (endTime != null) {
        if (!addedOne) {
            sql += " WHERE";
        }
        else {
            sql += " AND";
        }
        sql += " entryStamp <= ?";
    }
    sql += " ORDER BY entryStamp DESC";
    try {
        con = DbConnectionManager.getConnection();
        pstmt = DbConnectionManager.createScrollablePreparedStatement(con, sql);
        
        int i = 1;
        if (username != null) {
            pstmt.setString(i, username);
            i++;
        }
        if (startTime != null) {
            pstmt.setLong(i, startTime.getTime());
            i++;
        }
        if (endTime != null) {
            pstmt.setLong(i, endTime.getTime());
        }
        
        rs = pstmt.executeQuery();
        if (skipEvents != null) {
            DbConnectionManager.scrollResultSet(rs, skipEvents);
        }
        if (numEvents != null) {
            DbConnectionManager.setFetchSize(rs, numEvents);
        }
        
        int count = 0;
        while (rs.next() && (numEvents == null || count < numEvents)) {
            SecurityAuditEvent event = new SecurityAuditEvent();
            event.setMsgID(rs.getLong(1));
            event.setUsername(rs.getString(2));
            event.setEventStamp(new Date(rs.getLong(3)));
            event.setSummary(rs.getString(4));
            event.setNode(rs.getString(5));
            event.setDetails(rs.getString(6));
            events.add(event);
            count++;
        }
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return events;
}
 

private UserInfo getUserInfo(String username, boolean recurse) throws UnsupportedOperationException, UserNotFoundException {
    if (!isScramSupported()) {
        // Reject the operation since the provider  does not support SCRAM
        throw new UnsupportedOperationException();
    }
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(TEST_PASSWORD);
        pstmt.setString(1, username);
        rs = pstmt.executeQuery();
        if (!rs.next()) {
            throw new UserNotFoundException(username);
        }
        UserInfo userInfo = new UserInfo();
        userInfo.plainText = rs.getString(1);
        userInfo.encrypted = rs.getString(2);
        userInfo.iterations = rs.getInt(3);
        userInfo.salt = rs.getString(4);
        userInfo.storedKey = rs.getString(5);
        userInfo.serverKey = rs.getString(6);
        if (userInfo.encrypted != null) {
            try {
                userInfo.plainText = AuthFactory.decryptPassword(userInfo.encrypted);
            }
            catch (UnsupportedOperationException uoe) {
                // Ignore and return plain password instead.
            }
        }
        if (!recurse) {
            if (userInfo.plainText != null) {
                boolean scramOnly = JiveGlobals.getBooleanProperty("user.scramHashedPasswordOnly");
                if (scramOnly || userInfo.salt == null) {
                    // If we have a password here, but we're meant to be scramOnly, we should reset it.
                    setPassword(username, userInfo.plainText);
                    // RECURSE
                    return getUserInfo(username, true);
                }
            }
        }
        // Good to go.
        return userInfo;
    }
    catch (SQLException sqle) {
        Log.error("User SQL failure:", sqle);
        throw new UserNotFoundException(sqle);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
}
 

private Connection getConnection() throws SQLException {
    if (useConnectionProvider)
        return DbConnectionManager.getConnection();
    return DriverManager.getConnection(connectionString);
}