JackEx Equivalents for Simple SQL SELECT Statements

Question

Can someone explain how I can use Jackcess to implement the equivalent of the following SQL queries?

SELECT name FROM table WHERE id = '1'

SELECT name FROM table INNER JOIN table ON table.id = table2.id

Answer 1

Re: your first query (SELECT ... FROM tableName WHERE ...)

In your simplest form, you can use the example "Search for a row with a specific column value" in the "Example code" section on the Jackcess main page here , i.e.

Table table = DatabaseBuilder.open(new File("my.mdb")).getTable("MyTable");
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'");
}

To iterate over multiple matching lines, you can do something like this

Table table = DatabaseBuilder.open(new File("my.mdb")).getTable("MyTable");
Cursor cursor = CursorBuilder.createCursor(table);
while (cursor.findNextRow(Collections.singletonMap("a", "foo"))) {
    Row row = cursor.getCurrentRow();
    System.out.println(String.format(
            "a='%s', SomeFieldName='%s'.", 
            row.get("a"), 
            row.get("SomeFieldName")));
}

or, as @jtahlborn suggests in the comment below,

Table table = DatabaseBuilder.open(new File("my.mdb")).getTable("MyTable");
Cursor cursor = CursorBuilder.createCursor(table);
for (Row row : cursor.newIterable().addMatchPattern("a", "foo")) {
    System.out.println(String.format(
            "a='%s', SomeFieldName='%s'.", 
            row.get("a"), 
            row.get("SomeFieldName")));
}

Re: your second query (SELECT ... FROM table1 INNER JOIN table2 ON ...)

You can use one loop foror while(similar to the above) to iterate over the corresponding rows in one table and use the inner loop foror whileto iterate over the linked rows in another (sibling) table. If two tables have an existing “relationship” in Access (otherwise called a “foreign key constraint”), Jackcess has a Joiner class that can be of help, discussed here .

---

If you need additional help, you need to try writing code for yourself, and then ask a new question , showing the code you are trying to use and the specific problem you are having.