Jackcess equivalents for simple SQL SELECT statements

Question

Can someone please explain how I might 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

Have you considered using [UCanAccess](http://ucanaccess.sourceforge.net/site.html)? For more information, look [here](http://stackoverflow.com/q/21955256/2144390). — Gord Thompson, Apr 17 '14 at 12:31
unfortunately i'm developing just a part of the project, i have to use jackcess, the choice it isn't mine — Shiroga, Apr 17 '14 at 13:49

score 3 · Answer 1 · edited May 23 '17 at 12:06

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

In its simplest form you could use the "Searching for a row with a specific column value" example under "Sample code" on the main Jackcess 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 loop through multiple matching rows you could 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 a 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 could use one for or while loop (similar to above) to iterate through the relevant rows in one table, and use an inner for or while loop to iterate through the related rows in the other (related) table. If the two tables have an existing "Relationship" in Access (a.k.a. "foreign key constraint") Jackcess has a Joiner class that may be of assistance, discussed here.

If you require further assistance you will need to try writing some code for yourself and then ask a new question showing the code you are attempting to use and the specific issue you have with it.

i'm sorry but when the code arrive at the "while" condition that you wrote doesn't enter, jump over the "while" block — Shiroga, Apr 18 '14 at 15:55
@Shiroga I have updated my answer. (It was missing the `Table table = ...` in the second example.) — Gord Thompson, Apr 18 '14 at 16:13
@Shiroga re: Comments to your answer (which has been deleted) - Stack Overflow is a "Question and Answer" site; it does not encourage long discussions. The idea is that someone asks *one specific question*, people offer direct answers to that question, the answers attract up/down votes based on their quality, and ideally one of those answers is [accepted](http://meta.stackexchange.com/a/5235/238021). Follow-up questions are discouraged because they detract from the focus of the original question+answer exchange. *Try writing some code* and then create a new question if you still need help. — Gord Thompson, Apr 19 '14 at 19:58
Note, you can simplify the loop a lot using the IterableBuilder: `for(Row row : cursor.newIterable().addMatchPattern("a", "foo")) {`. — jtahlborn, May 02 '14 at 13:48

Jackcess equivalents for simple SQL SELECT statements

1 Answers1