java - Jackcess 等效于具有 > ("strictly greater than") 条件的 SQL WHERE 子句

Question

我想搜索日期列的值 > '2014/03/01' 的所有行。

如何做到这一点？如果没有 Date value = '2014/03/01'` 的行，如何在没有完整扫描表的情况下定位光标？

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)) { ... }

Answer 1

您已经创建了一个 IndexCursor，因此不需要进行表扫描。只需使用 IndexCursor#findClosestRowByEntry进行 >= 搜索，然后像这样跳过完全匹配(如果有):

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