2

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

João Trindade
  • 61
  • 6

1 Answers1

3

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

Community
  • 1
  • 1
Gord Thompson
  • 98,607
  • 26
  • 164
  • 342
  • Thanks, this is exactly what i needed. I did try to use UCanAccess but I found that it performed considerably slower than the regular jackess implementation. At least that's what happened for me... thanks again! – João Trindade Aug 05 '14 at 15:48
  • 1
    In general you shouldn't have performance problems with ucanaccess, especially if you can use the default in memory working mode (memory=true). But notice that the time of the first connection in the life of the vm process might actually be slow(it mainly depends on the database size). The query time instead should be very fast. – jamadei Aug 06 '14 at 16:29