2

I want search all rows where value of Date columns > '2014/03/01'.

How can do this? If there are no rows with Date value = '2014/03/01'`, how can I position the cursor without a full scan table?

Table table = db.getTable("Facture");
IndexCursor cursor = CursorBuilder.createCursor(table.getIndex("DateIndex"));

Date search_date = Date('2014/03/01');
for(Row row : cursor.newEntryIterable(search_date)) { ... }
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Phane
  • 201
  • 3
  • 11

1 Answers1

1

You are already creating an IndexCursor so no table scan will be necessary. Just use IndexCursor#findClosestRowByEntry to do a >= search and then skip over the exact matches (if any) like this:

Table table = db.getTable("Members");
String dateColumnName = "DateJoined";
Column dateColumn = table.getColumn(dateColumnName);
IndexCursor cursor = CursorBuilder.createCursor(table.getIndex(dateColumnName));

String searchDateAsString = "2014/03/01";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
Date search_date = sdf.parse(searchDateAsString);
cursor.findClosestRowByEntry(search_date);
if (cursor.isAfterLast()) {
    System.out.println(String.format("There are no rows with %s >= %s", dateColumnName, searchDateAsString));
}
else {
    // we want strictly greater than, so skip over the rows that are equal
    while (search_date.equals(cursor.getCurrentRowValue(dateColumn))) {
        if (!cursor.moveToNextRow()) break;
    }
    if (cursor.isAfterLast()) {
        System.out.println(String.format("There are no rows with %s > %s", dateColumnName, searchDateAsString));
    }
}
// now iterate over the remaining rows
while (!cursor.isAfterLast()) {
    System.out.println(sdf.format(cursor.getCurrentRowValue(dateColumn)));
    cursor.moveToNextRow();
}
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank Gord this is the solution, but i have another problem, my index "dateColumnName" have 2 champs [Date, Code]. Date is a Date and Code is a text. When cursor.findClosestRowByEntry(search_date) is executed,i have got following error : Java::JavaLang::IllegalArgumentException: Wrong number of column values given 1, expected 2. – Phane Sep 17 '14 at 13:07
  • I tried this cursor.findClosestRowByEntry("", search_date), and i have got following error : Java::JavaLang::ClassCastException: java.lang.String cannot be cast to java.lang.Number. – Phane Sep 17 '14 at 13:29
  • @Phane For a compound index on ([Date],[Code]) if you want the find to simply ignore [Code] you could use `cursor.findClosestRowByEntry(search_date, null);`. In your case you want to ultimately get past the specified date so you could use something like `cursor.findClosestRowByEntry(search_date, "zzzzz");` to reduce the number of "search_date.equals" rows you need to skip after the find completes. – Gord Thompson Sep 17 '14 at 14:55