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

Example 81 with ResultSet

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

the class RoutingManager method getRoutingRules.

/**
     * Returns all RoutingRules belong to the given workgroup.
     *
     * @param workgroup the workgroup.
     * @return a Collection of all RoutingRules sorted by position.
     */
public Collection<RoutingRule> getRoutingRules(Workgroup workgroup) {
    final List<RoutingRule> rules = new ArrayList<RoutingRule>();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(LOAD_RULES);
        pstmt.setLong(1, workgroup.getID());
        rs = pstmt.executeQuery();
        while (rs.next()) {
            long queueID = rs.getLong("queueID");
            int position = rs.getInt("rulePosition");
            String query = rs.getString("query");
            RoutingRule rule = new RoutingRule(queueID, position, query);
            rules.add(rule);
        }
    } catch (Exception ex) {
        Log.error(ex.getMessage(), ex);
    } finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    // Sort by position.
    Collections.sort(rules, positionComparator);
    return rules;
}

Example 82 with ResultSet

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

the class ChatSearchManager method getChatsInformation.

/**
     * Returns information about the chats that took place since a given date. The result is
     * sorted from oldest chats to newest chats.
     *
     * @param since the date to use as the lower limit.
     * @return information about the chats that took place since a given date.
     */
private List<ChatInformation> getChatsInformation(Date since) {
    List<ChatInformation> chats = new ArrayList<ChatInformation>();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet result = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(CHATS_SINCE_DATE);
        pstmt.setLong(1, workgroup.getID());
        pstmt.setString(2, StringUtils.dateToMillis(since));
        result = pstmt.executeQuery();
        while (result.next()) {
            String sessionID = result.getString(1);
            String transcript = result.getString(2);
            String startTime = result.getString(3);
            ChatNotes chatNotes = new ChatNotes();
            String notes = chatNotes.getNotes(sessionID);
            // Create a ChatInformation with the retrieved information
            ChatInformation chatInfo = new ChatInformation(sessionID, transcript, startTime, notes);
            if (chatInfo.getTranscript() != null) {
                chats.add(chatInfo);
            }
        }
        result.close();
        // For each ChatInformation add the agents involved in the chat
        for (ChatInformation chatInfo : chats) {
            pstmt.close();
            pstmt = con.prepareStatement(AGENTS_IN_SESSION);
            pstmt.setString(1, chatInfo.getSessionID());
            result = pstmt.executeQuery();
            while (result.next()) {
                chatInfo.getAgentJIDs().add(result.getString(1));
            }
            result.close();
        }
    } catch (Exception ex) {
        Log.error(ex.getMessage(), ex);
        // Reset the answer if an error happened
        chats = new ArrayList<ChatInformation>();
    } finally {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (Exception e) {
            Log.error(e.getMessage(), e);
        }
        try {
            if (result != null) {
                result.close();
            }
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        try {
            if (con != null) {
                con.close();
            }
        } catch (Exception e) {
            Log.error(e.getMessage(), e);
        }
        try {
            if (result != null) {
                result.close();
            }
        } catch (Exception e) {
            Log.error(e.getMessage(), e);
        }
    }
    // Return the chats order by startTime
    return chats;
}

Example 83 with ResultSet

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

the class ChatSearchManager method loadLastUpdated.

private void loadLastUpdated() {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet result = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(LOAD_DATES);
        pstmt.setLong(1, workgroup.getID());
        result = pstmt.executeQuery();
        while (result.next()) {
            lastUpdated = new Date(Long.parseLong(result.getString(1)));
            lastOptimization = new Date(Long.parseLong(result.getString(2)));
            lastExecution = lastUpdated;
        }
    } catch (Exception ex) {
        Log.error(ex.getMessage(), ex);
    } finally {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (Exception e) {
            Log.error(e.getMessage(), e);
        }
        try {
            if (result != null) {
                result.close();
            }
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        try {
            if (con != null) {
                con.close();
            }
        } catch (Exception e) {
            Log.error(e.getMessage(), e);
        }
    }
}

Example 84 with ResultSet

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

the class WorkgroupPresence method loadRosterItems.

private void loadRosterItems() {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(LOAD_ROSTER);
        pstmt.setLong(1, workgroup.getID());
        rs = pstmt.executeQuery();
        List<String> jids = new ArrayList<String>();
        while (rs.next()) {
            jids.add(rs.getString(1));
        }
        presenceSubscribers.addAll(jids);
    } catch (SQLException e) {
        Log.error("Error loading workgroup roster items ", e);
    } finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
}

Example 85 with ResultSet

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

the class ChatHistoryUtils method getTotalWaitTimeForWorkgroup.

/**
     * Returns the number of canceled requests.
     *
     * @param workgroupName the workgroup to search
     * @param startDate the time to begin the search from.
     * @param endDate the time to end the search.
     * @return the total number of requests
     */
public static long getTotalWaitTimeForWorkgroup(String workgroupName, Date startDate, Date endDate) {
    Workgroup workgroup = null;
    try {
        workgroup = WorkgroupManager.getInstance().getWorkgroup(new JID(workgroupName));
    } catch (Exception ex) {
        Log.error(ex.getMessage(), ex);
    }
    if (workgroup == null) {
        return 0;
    }
    int waitTime = 0;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(WORKGROUP_WAIT_TIME);
        pstmt.setLong(1, workgroup.getID());
        // Set the state the ignored requests.
        pstmt.setInt(2, 1);
        pstmt.setString(2, StringUtils.dateToMillis(startDate));
        pstmt.setString(3, StringUtils.dateToMillis(endDate));
        rs = pstmt.executeQuery();
        rs.next();
        waitTime = rs.getInt(1);
    } catch (Exception ex) {
        Log.error(ex.getMessage(), ex);
    } finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return waitTime;
}