Examples with ResultSet java.sql.ResultSet used on opensource projects

Example 96 with ResultSet

use of java.sql.ResultSet in project Openfire by igniterealtime.

the class PubSubPersistenceManager method loadNode.

/**
	 * Loads all nodes from the database and adds them to the PubSub service.
     *
	 * @param service
	 *            the pubsub service that is hosting the nodes.
	 */
public static void loadNode(PubSubService service, String nodeId) {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Map<String, 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, service.getServiceID());
        pstmt.setString(2, nodeId);
        rs = pstmt.executeQuery();
        Map<String, String> parentMapping = new HashMap<>();
        // Rebuild loaded non-leaf nodes
        if (rs.next()) {
            loadNode(service, nodes, parentMapping, rs);
        }
        DbConnectionManager.fastcloseStmt(rs, pstmt);
        String parentId = parentMapping.get(nodeId);
        if (parentId != null) {
            CollectionNode parent = (CollectionNode) service.getNode(parentId);
            if (parent == null) {
                log.error("Could not find parent node " + parentId + " for node " + nodeId);
            } else {
                nodes.get(nodeId).changeParent(parent);
            }
        }
        // Get JIDs associated with all nodes
        pstmt = con.prepareStatement(LOAD_NODE_JIDS);
        pstmt.setString(1, service.getServiceID());
        pstmt.setString(2, nodeId);
        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, service.getServiceID());
        pstmt.setString(2, nodeId);
        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, service.getServiceID());
        pstmt.setString(2, nodeId);
        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, service.getServiceID());
        pstmt.setString(2, nodeId);
        rs = pstmt.executeQuery();
        // Add to each node the corresponding subscriptions
        while (rs.next()) {
            loadSubscriptions(service, nodes, rs);
        }
        DbConnectionManager.fastcloseStmt(rs, pstmt);
    } catch (SQLException sqle) {
        log.error(sqle.getMessage(), 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);
    }
}

Example 97 with ResultSet

use of java.sql.ResultSet in project Openfire by igniterealtime.

the class PubSubPersistenceManager method purgeItems.

/**
     * Purges all items from the database that exceed the defined item count on
     * all nodes.
     */
private static void purgeItems() {
    boolean abortTransaction = false;
    Connection con = null;
    PreparedStatement pstmt = null;
    PreparedStatement nodeConfig = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getTransactionConnection();
        nodeConfig = con.prepareStatement(PERSISTENT_NODES);
        rs = nodeConfig.executeQuery();
        PreparedStatement 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(rs, nodeConfig);
        DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction);
    }
}

Example 98 with ResultSet

use of java.sql.ResultSet in project Openfire by igniterealtime.

the class ChatTranscriptManager method getChatSession.

/**
     * Retrieves a <code>ChatSession</code> based on it's session ID.
     *
     * @param sessionID the session ID.
     * @return the ChatSession or null if no ChatSession is found.
     */
public static ChatSession getChatSession(String sessionID) {
    final ChatSession session = new ChatSession();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(GET_SESSION);
        pstmt.setString(1, sessionID);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            String userID = rs.getString(1);
            long workgroupID = rs.getLong(2);
            String transcript = DbConnectionManager.getLargeTextField(rs, 3);
            String startTime = rs.getString(4);
            String endTime = rs.getString(5);
            long queueWaitTime = rs.getLong(6);
            int state = rs.getInt(7);
            session.setSessionID(sessionID);
            session.setWorkgroupID(workgroupID);
            session.setUserID(userID);
            session.setTranscript(formatTranscript(transcript));
            if (startTime.trim().length() > 0) {
                session.setStartTime(Long.parseLong(startTime));
            }
            if (endTime.trim().length() > 0) {
                session.setEndTime(Long.parseLong(endTime));
            }
            session.setQueueWaitTime(queueWaitTime);
            session.setState(state);
            if (startTime.trim().length() > 0 && endTime.trim().length() > 0) {
                populateSessionWithMetadata(session);
                populateSessionWithAgents(session);
            }
        }
    } catch (Exception ex) {
        Log.error(ex.getMessage(), ex);
    } finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return session;
}

Example 99 with ResultSet

use of java.sql.ResultSet in project Openfire by igniterealtime.

the class ChatTranscriptManager method getChatSessionsForWorkgroup.

/**
     * Returns a collection of ChatSessions for a particular workgroup.
     *
     * @param workgroup the workgroup.
     * @param start retrieve all ChatSessions at or after this specified date.
     * @param end retrieve all ChatSessions before or on this specified date.
     * @return a collection of ChatSessions.
     */
public static Collection<ChatSession> getChatSessionsForWorkgroup(Workgroup workgroup, Date start, Date end) {
    final List<ChatSession> resultList = new ArrayList<ChatSession>();
    long wgID = workgroup.getID();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(GET_WORKGROUP_SESSIONS);
        pstmt.setLong(1, wgID);
        pstmt.setString(2, StringUtils.dateToMillis(start));
        pstmt.setString(3, StringUtils.dateToMillis(end));
        rs = pstmt.executeQuery();
        while (rs.next()) {
            String sessionID = rs.getString(1);
            String userID = rs.getString(2);
            String startTime = rs.getString(3);
            String endTime = rs.getString(4);
            long queueWaitTime = rs.getLong(5);
            int state = rs.getInt(6);
            ChatSession session = new ChatSession();
            session.setSessionID(sessionID);
            session.setUserID(userID);
            session.setWorkgroupID(wgID);
            if (startTime.trim().length() > 0) {
                session.setStartTime(Long.parseLong(startTime));
            }
            if (endTime.trim().length() > 0) {
                session.setEndTime(Long.parseLong(endTime));
            }
            session.setQueueWaitTime(queueWaitTime);
            session.setState(state);
            populateSessionWithMetadata(session);
            populateSessionWithAgents(session);
            resultList.add(session);
        }
    } catch (Exception ex) {
        Log.error(ex.getMessage(), ex);
    } finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    // Sort by date
    Collections.sort(resultList, dateComparator);
    return resultList;
}

Example 100 with ResultSet

use of java.sql.ResultSet in project Openfire by igniterealtime.

the class ChatTranscriptManager method getTextTranscriptFromSessionID.

/**
     * Return the plain text version of a chat transcript.
     *
     * @param sessionID the sessionID of the <code>ChatSession</code>
     * @return the plain text version of a chat transcript.
     */
public static String getTextTranscriptFromSessionID(String sessionID) {
    String transcript = null;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(GET_SESSION_TRANSCRIPT);
        pstmt.setString(1, sessionID);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            transcript = DbConnectionManager.getLargeTextField(rs, 1);
        }
    } catch (Exception ex) {
        Log.error(ex.getMessage(), ex);
    } finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    if (transcript == null || "".equals(transcript)) {
        return "";
    }
    // Define time zone used in the transcript.
    SimpleDateFormat UTC_FORMAT = new SimpleDateFormat(XMPPDateTimeFormat.XMPP_DELAY_DATETIME_FORMAT);
    UTC_FORMAT.setTimeZone(TimeZone.getTimeZone("UTC"));
    final SimpleDateFormat formatter = new SimpleDateFormat("h:mm a");
    Document element = null;
    try {
        element = DocumentHelper.parseText(transcript);
    } catch (DocumentException e) {
        Log.error(e.getMessage(), e);
    }
    StringBuilder buf = new StringBuilder();
    // Add the Messages and Presences contained in the retrieved transcript element
    for (Iterator<Element> it = element.getRootElement().elementIterator(); it.hasNext(); ) {
        Element packet = it.next();
        String name = packet.getName();
        String message = "";
        String from = "";
        if ("presence".equals(name)) {
            String type = packet.attributeValue("type");
            from = new JID(packet.attributeValue("from")).getResource();
            if (type == null) {
                message = from + " has joined the room";
            } else {
                message = from + " has left the room";
            }
        } else if ("message".equals(name)) {
            from = new JID(packet.attributeValue("from")).getResource();
            message = packet.elementText("body");
            message = StringUtils.escapeHTMLTags(message);
        }
        List<Element> el = packet.elements("x");
        for (Element ele : el) {
            if ("jabber:x:delay".equals(ele.getNamespaceURI())) {
                String stamp = ele.attributeValue("stamp");
                try {
                    String formattedDate;
                    synchronized (UTC_FORMAT) {
                        Date d = UTC_FORMAT.parse(stamp);
                        formattedDate = formatter.format(d);
                    }
                    if ("presence".equals(name)) {
                        buf.append("[").append(formattedDate).append("] ").append(message).append("\n");
                    } else {
                        buf.append("[").append(formattedDate).append("] ").append(from).append(": ").append(message).append("\n");
                    }
                } catch (ParseException e) {
                    Log.error(e.getMessage(), e);
                }
            }
        }
    }
    return buf.toString();
}