Re: ResultSet memory usage

> I need to get N first entries with duplicates removed based on one (or
> two) unique column(s). I can't use distinct since I need to select also
> other columns that shouldn't be affected by "distinct".

I can think of (at least) two approaches that might suit your need:

1. Get the keys, then get the data

ResultSet aSet1 =
  aStmt1.executeQuery("SELECT DISTINCT a FROM t WHERE a LIKE "+a);

while (aSet1.next() and !gotEverythingNeeded) {
  ResultSet aSet2 =
    aStmt2.executeQuery("SELECT * FROM t WHERE a =
"+aSet1.getString(1));

  while (aSet2.next() and !gotEverythingNeeded) {
    // ... Get the data
  }
}


2. Get it in small pieces

If the keys aren't unique enough to avoid the second query getting to
big, you can use the LIMIT keyword to sorta kinda implement your own
cursor:

ResultSet aSet = aStmt.executeQuery("SELECT COUNT(*) FROM t");
aSet.next();
int noOfRows = aSet.getInt(1);

int offset = 0;
int rowsPerFetch = 10;

while (offset < noOfRows and !gotEverythingNeeded) {
  aSet = aStmt.executeQuery(
    "SELECT * "+
    "FROM t "+
    "ORDER BY a,b,c "+
    "LIMIT "+rowsPerFetch+" "+
    "OFFSET "+offset);

  while (aSet.next()) {
    // ... Get the data
  }

  aSet.close();
  offset += rowsPerFetch;
}


Please note I haven't tested the code; it need to be refined for your
specific needs, you need to close resultsets etc. It's meant to give
ideas on approaches to the problem.

Yours,
///Jens Carlberg