Implementing a virtual list in Java

Question

I need to fetch many records from an RDBMS in Java (10-20k) my target system expects them to be available as Java List. So I want to implement my code as "Virtual list" where I actually only fetch the records I actually need. I expect SQL like

SELECT * FROM CUSTOMER WHERE COUNTRY="Moldovia"

as parameter and just return what is requested. Most likely the data is requested in batches of 50. Any hints how to do that?

Answer 1

Use OFFSET and LIMIT in your query:

SELECT * FROM CUSTOMER WHERE COUNTRY="Moldovia" LIMIT 50 OFFSET 50

Assuming of course that your SQL dialect allows it. The example will return rows 51-100.

Answer 2

Unless you expect your clients to randomly access the data, you're probably better off returning an Iterator. Also, take a look at ResultSet.setFetchSize: http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#setFetchSize(int)

So something like:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;

public class FooResultSetIterator implements Iterator<Foo>
{
  private final ResultSet resultSet;
  private boolean hasNext;

  FooResultSetIterator(final ResultSet resultSet, final int fetchSize) throws SQLException
  {
    this.resultSet = resultSet;
    this.resultSet.setFetchSize(fetchSize);
    this.hasNext = resultSet.next();
  }

  @Override
  public boolean hasNext()
  {
    return hasNext;
  }

  @Override
  public Foo next()
  {
    final Foo foo = new Foo(resultSet);
    try
    {
      this.hasNext = resultSet.next();
    }
    catch (final SQLException e)
    {
      throw new RuntimeException(e);
    }
    return foo;
  }

  @Override
  public void remove()
  {
    throw new UnsupportedOperationException("Cannot remove items from a ResultSetIterator");
  }

}

class Foo
{
  public Foo(ResultSet resultSet)
  {
    // TODO Auto-generated constructor stub
  }
}