2

In my Access db table, I have a cmt_data column which contains a string. For example:

Check in the packet TM(12,9) MRSS0319 'Monitoring List Report'.

I also have a List<String> with items such as MRSS0319, TRPP3006, etc. What I'm looking to do is perform a substring match between my List<String> and the table column, but I can't quite figure out how as the examples provided with Jackcess is rather simple. An example I found here shows:

Column col = table.getColumn("town");
cursor.beforeFirst();
while(cursor.moveToNextRow()) {
  if(cursor.currentRowMatches(columnPattern, valuePattern)) {
    // handle matching row here
  }
}

Where the method cursor.currentRowMatches(columnPattern, valuePattern) looks like it could be useful. However according to the documentation it seems that the method only performs string equality matching, so now I'm sort of at a dead end.

Appreciate your help.

Community
  • 1
  • 1
Sameen
  • 729
  • 6
  • 19

1 Answers1

3

Certainly you could create a little method to check the cmt_data value for a match:

public static void main(String[] args) {
    String dbPath = "C:/Users/Public/JackcessTest.accdb";
    try (Database db = DatabaseBuilder.open(new File(dbPath))) {
        Table table = db.getTable("cmt_table");
        Cursor cursor = table.getDefaultCursor();
        cursor.beforeFirst();
        while (cursor.moveToNextRow()) {
            Row row = cursor.getCurrentRow();
            if (stringContainsSpecialValue(row.getString("cmt_data"))) {
                // handle matching row here
                System.out.println(row);
            }
        }
    } catch (Exception e) {
        e.printStackTrace(System.err);
    }
}

private static boolean stringContainsSpecialValue(String str) {
    boolean rtn = false;
    List<String> specialValues = Arrays.asList("MRSS0319", "TRPP3006");
    for (String val : specialValues) {
        if (str.contains(val)) {
            rtn = true;
            break;
        }
    }
    return rtn;
}

You could probably also create a custom ColumnMatcher for your cursor, but that might be overkill.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • great answer, but I'm looking to write back changes into Access if a substring was found. In that case, do I have to do `cursor.updateCurrentRow(row)` ? I also note that example documentation is somewhat lacking for the Jackcess project which is letting the API down. Perhaps a documentation on SO could be opened under `jackcess` tag? Link [here](http://stackoverflow.com/documentation/jackcess) – Sameen Feb 07 '17 at 14:32
  • Yes, you could use `cursor.updateCurrentRow`, or `table.updateRow`, or perhaps `cursor.setCurrentRowValue` to update just one column. As for documentation, I'd leave that for the Jackcess team to decide whether they want to maintain documentation here (instead of, or in addition to, the [cookbook](http://jackcess.sourceforge.net/cookbook.html), the [Javadoc](http://jackcess.sourceforge.net/apidocs/), and the unit tests in the source code). – Gord Thompson Feb 07 '17 at 16:03
  • 1
    I'm using `cursor.setCurrentRowValue(table.getColumn("cmt_data"), newString);` which seems to be working as per requirements. And sure, I've come across those it's just that it would've been better if there were more usage scenario samples, etc. Thanks a lot for your help nonetheless. – Sameen Feb 07 '17 at 16:19