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

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