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

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

@Override
public Collection<String> getGroupNames(int startIndex, int numResults) {
    List<String> groupNames = new ArrayList<>();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = DbConnectionManager.createScrollablePreparedStatement(con, ALL_GROUPS);
        rs = pstmt.executeQuery();
        DbConnectionManager.scrollResultSet(rs, startIndex);
        int count = 0;
        while (rs.next() && count < numResults) {
            groupNames.add(rs.getString(1));
            count++;
        }
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return groupNames;
}
 

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

@Override
public Collection<String> search(String query, int startIndex, int numResults) {
    if (query == null || "".equals(query)) {
        return Collections.emptyList();
    }
    // SQL LIKE queries don't map directly into a keyword/wildcard search like we want.
    // Therefore, we do a best approximiation by replacing '*' with '%' and then
    // surrounding the whole query with two '%'. This will return more data than desired,
    // but is better than returning less data than desired.
    query = "%" + query.replace('*', '%') + "%";
    if (query.endsWith("%%")) {
        query = query.substring(0, query.length()-1);
    }

    List<String> groupNames = new ArrayList<>();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        if ((startIndex==0) && (numResults==Integer.MAX_VALUE))
        {
           pstmt = con.prepareStatement(SEARCH_GROUP_NAME);
           pstmt.setString(1, query);
           rs = pstmt.executeQuery();
           while (rs.next()) {
               groupNames.add(rs.getString(1));
           }
        } else {
           pstmt = DbConnectionManager.createScrollablePreparedStatement(con, SEARCH_GROUP_NAME);
           DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults);
           pstmt.setString(1, query);
           rs = pstmt.executeQuery();
           // Scroll to the start index.
           DbConnectionManager.scrollResultSet(rs, startIndex);
           int count = 0;
           while (rs.next() && count < numResults) {
               groupNames.add(rs.getString(1));
               count++;
           }	
        }
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return groupNames;
}
 

private Collection<String> getUsernames(int startIndex, int numResults) {
    List<String> usernames = new ArrayList<>(500);
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        if ((startIndex==0) && (numResults==Integer.MAX_VALUE))
        {
            pstmt = con.prepareStatement(ALL_USERS);
            // Set the fetch size. This will prevent some JDBC drivers from trying
            // to load the entire result set into memory.
            DbConnectionManager.setFetchSize(pstmt, 500);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                usernames.add(rs.getString(1));
            }
        }
        else {
            pstmt = DbConnectionManager.createScrollablePreparedStatement(con, ALL_USERS);
            DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults);
            rs = pstmt.executeQuery();
            DbConnectionManager.scrollResultSet(rs, startIndex);
            int count = 0;
            while (rs.next() && count < numResults) {
                usernames.add(rs.getString(1));
                count++;
            }
        }
        if (Log.isDebugEnabled()) {
               Log.debug("Results: " + usernames.size());
               LogResults(usernames);
        }
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return usernames;
}
 

@Override
public Collection<User> findUsers(Set<String> fields, String query, int startIndex,
        int numResults) throws UnsupportedOperationException
{
    if (fields.isEmpty()) {
        return Collections.emptyList();
    }
    if (!getSearchFields().containsAll(fields)) {
        throw new IllegalArgumentException("Search fields " + fields + " are not valid.");
    }
    if (query == null || "".equals(query)) {
        return Collections.emptyList();
    }
    // SQL LIKE queries don't map directly into a keyword/wildcard search like we want.
    // Therefore, we do a best approximiation by replacing '*' with '%' and then
    // surrounding the whole query with two '%'. This will return more data than desired,
    // but is better than returning less data than desired.
    query = "%" + query.replace('*', '%') + "%";
    if (query.endsWith("%%")) {
        query = query.substring(0, query.length()-1);
    }

    List<String> usernames = new ArrayList<>(50);
    Connection con = null;
    PreparedStatement pstmt = null;
    int queries=0;
    ResultSet rs = null;
    try {
        StringBuilder sql = new StringBuilder(90);
        sql.append("SELECT username FROM ofUser WHERE");
        boolean first = true;
        if (fields.contains("Username")) {
            sql.append(" username LIKE ?");
            queries++;
            first = false;
        }
        if (fields.contains("Name")) {
            if (!first) {
                sql.append(" AND");
            }
            sql.append(" name LIKE ?");
            queries++;
            first = false;
        }
        if (fields.contains("Email")) {
            if (!first) {
                sql.append(" AND");
            }
            sql.append(" email LIKE ?");
            queries++;
        }
        con = DbConnectionManager.getConnection();
        if ((startIndex==0) && (numResults==Integer.MAX_VALUE))
        {
            pstmt = con.prepareStatement(sql.toString());
            for (int i=1; i<=queries; i++)
            {
                pstmt.setString(i, query);
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                usernames.add(rs.getString(1));
            }
        } else {
            pstmt = DbConnectionManager.createScrollablePreparedStatement(con, sql.toString());
            DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults);
            for (int i=1; i<=queries; i++)
            {
                pstmt.setString(i, query);
            }
            rs = pstmt.executeQuery();
            // Scroll to the start index.
            DbConnectionManager.scrollResultSet(rs, startIndex);
            int count = 0;
            while (rs.next() && count < numResults) {
                usernames.add(rs.getString(1));
                count++;
           }
        }
        if (Log.isDebugEnabled())
        {
            Log.debug("Results: " + usernames.size());
            LogResults(usernames);
        }
    }
    catch (SQLException e) {
        Log.error(e.getMessage(), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return new UserCollection(usernames.toArray(new String[usernames.size()]));
}