Java Jackcess Find Row by More than one column

Question

I'm acessing a MS Access database on Java using the Jackcess Library and I'd like to know how to find a table row by more than one column value.

So far I followed this procedure every time I need to find a row by one column value:

Row row = CursorBuilder.findRow(table, Collections.singletonMap("a", "foo"));

if(row != null) {
   System.out.println("Found row where 'a' == 'foo': " + row);
} else {
   System.out.println("Could not find row where 'a' == 'foo'");
}

I found this on Jackcess website, it does something similar to "SELECT * FROM tablename WHERE a = "foo". What I need is to have more than one "WHERE-Condition".

From what I read on the Jackcess the documentation the FindRowByPrimaryKey or FindRowByEntry seem to do what I need but the examples I find only show it working with one condition.

http://jackcess.sourceforge.net/apidocs/com/healthmarketscience/jackcess/IndexCursor.html#findRowByEntry%28java.lang.Object...%29

The second argument is called entryValues (plural), but I have no idea how can use that.

Thanks in advance

Answer 1

You can specify several criteria by creating a Map with multiple entries for the entryValues like this:

Database db = DatabaseBuilder.open(new File(
        "C:/Users/Gord/Desktop/Database1.accdb"));
Table table = db.getTable("People");
Map<String, Object> criteria = new HashMap<String, Object>();
criteria.put("FirstName", "Jimmy");
criteria.put("LastName", "Hoffa");
Row row = CursorBuilder.findRow(table, criteria);
if (row == null) {
    System.out.println("No row found that matches all criteria.");
}
else {
    System.out.println(String.format("Row found: ID=%d.", row.get("ID")));
}

That will find a row that matches all of the specified criteria (as in ... WHERE FirstName='Jimmy' AND LastName='Hoffa'). To work with more complicated search criteria you might consider using UCanAccess (details here).