paging with JDBC JAVA

Question

I have problem with understanding paging in JDBC java and I hope that one can told me a few things about it, I'm new in java programming and its not easy to understand the functionality of these methods as an example setFetchsize(); getFetchSize(); and so on, I tried to do a table in Mysql about user and then doing paging by getting all information from the table with this statement "SELECT * FROM User", I thought that I will get first 10 rows and then 10 because its default, I must get all information as paging from the database, here is my code, I tried to use LIMIT with it but I still don't understand how its works exactly.

@Override
public List<User> getAll() throws SQLException {

    try (PreparedStatement statement = connection.getConnection()
            .prepareStatement("SELECT * FROM User")) {

        int fetchSize = statement.getFetchSize();

        System.out.println("Statement fetch size : " + fetchSize);

        statement.setFetchSize(50);

        ResultSet result = statement.executeQuery();
        result.setFetchSize(33);
        while (result.next()) {
            list.add(extractUser(result));

        }

    } catch (SQLException e) {

        throw new RuntimeException(e);
    }
    System.out.println(list);
    return list;

}

private User extractUser(ResultSet result) throws SQLException {

    long id = result.getLong(1);
    String userName = result.getString(2);
    String firstName = result.getString(3);
    String lastName = result.getString(4);
    long teamId = result.getLong(5);
    String userStatus = result.getString(6);

    return new User(id, userName, firstName, lastName, teamId, userStatus);

}

score 4 · Answer 1 · answered Dec 24 '16 at 13:40

When JDBC needs to fetch a record from the database it fetches a batch of records around it, to improve performance. The size of this batch can be controlled with setFetchSize. This, however, has no functional impact on the results of the query. You'll still get the same results, regardless of the fetch size.The only thing that would change is the number of records the program fetches on each access (and thus, the number of fetches it needs to perform).

If you want to introduce paging to your application you need to code it yourself, e.g. with a limit clause:

public List<User> getAll(int beginIndex, int pageSize) throws SQLException {

    try (PreparedStatement statement = 
             connection.getConnection().prepareStatement
             ("SELECT * FROM User ORDER BY Username LIMIT ? OFFSET ?")) {

        statement.setInt(1, pageSize);
        statement.setInt(2, begineIndex);

        ResultSet result = statement.executeQuery();
        while (result.next()) {
            list.add(extractUser(result));
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    return list;
}

paging with JDBC JAVA

1 Answers1