using multicolumn index in Jackcess to match on first column

Question

I use Jackcess 2.1.5 to read data from an Access 2003 table having a multi-column index on colA and colB. This works fine given a value for colA and colB.

Now in theory such an index can be used to get all the rows matching a value for colA only. But how to do this with Jackcess? I cannot get it to work by using newEntryIterable or EntryIterableBuilder

Table table = access.getTable("tbl");
Index index = table.getIndex("index"); //index spanning two columns
IndexCursor cursor = CursorBuilder.createCursor(index);
for (Row row : cursor.newEntryIterable(val)) { //error: missing argument
for (Row row : cursor.newEntryIterable(val, null)) { //returns rows where colB == null
    //some code
}

Currently I have another index covering colA only. Is this the only solution?

Answer 1

I know this is a little late, but I wanted to add an update. As of the 2.1.7 release, Jackcess now supports partial index lookups. So, from the original question, this line will now work for finding all entries which match on the first column of the two-column index:

for (Row row : cursor.newEntryIterable(val)) {

Answer 2

I just tried the following and it worked for me. For a table named "People"

ID  FirstName  LastName
--  ---------  --------
 1  Gord       Thompson
 2  Jimmy      Hoffa   
 3  Jimmy      Buffett 
 4  Bob        Loblaw  

with an index named "FirstLast" on (FirstName, LastName), the code

Table tbl = db.getTable("People");
IndexCursor cur = CursorBuilder.createCursor(tbl.getIndex("FirstLast"));
Map<String, String> criteria = Collections.singletonMap("FirstName", "Jimmy");
boolean found = cur.findFirstRow(criteria);
if (found) {
    boolean nextRowExists = true;
    do {
        Row r = cur.getCurrentRow();
        System.out.println(r.getString("LastName"));
        nextRowExists = cur.moveToNextRow();
    } while (nextRowExists && cur.currentRowMatches(criteria));
} else {
    System.out.println("(No matches found.)");
}

printed

Buffett
Hoffa

However, subsequent testing with a large file on a network share showed that the above approach is far less efficient than using .newEntryIterable and a separate index on FirstName only. If performance is important then you should keep that additional index just for colA.