Connect to MS Access from Java

21. January 2013 11:13 by Mrojas in Access  //  Tags: , , , , ,   //   Comments (0)

This is not an scenario that I commonly find, but now with Android and with the spread of Linux base environments an devices you are just don't want to be limited to just one technology.

So I found this library which I found extremely useful and I can recommend: Jackcess

 

The following are some code samples from the Jackcess library

  • Displaying the contents of a table:
    System.out.println(Database.open(new File("my.mdb")).getTable("MyTable").display());
    
  • Iterating through the rows of a table:
    Table table = Database.open(new File("my.mdb")).getTable("MyTable");
    for(Map<String, Object> row : table) {
      System.out.println("Column 'a' has value: " + row.get("a"));
    }
    
  • Searching for a row with a specific column value:
    Map<String, Object> row = Cursor.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'");
    }
    
  • Creating a new table and writing data into it:
    Database db = Database.create(new File("new.mdb"));
    Table newTable = new TableBuilder("NewTable")
      .addColumn(new ColumnBuilder("a")
                 .setSQLType(Types.INTEGER)
                 .toColumn())
      .addColumn(new ColumnBuilder("b")
                 .setSQLType(Types.VARCHAR)
                 .toColumn())
      .toTable(db);
    newTable.addRow(1, "foo");
    
  • Copying the contents of a JDBC ResultSet (e.g. from an external database) into a new table:
    Database.open(new File("my.mdb")).copyTable("Imported", resultSet);
  • Copying the contents of a CSV file into a new table:
    Database.open(new File("my.mdb")).importFile("Imported2", new File("my.csv"), ",");

Remember that if you want to use it android you use some tweaks:

"The following steps will make Jackcess compatible with the Android platform.

  • Set the system property "com.healthmarketscience.jackcess.brokenNio=true"
  • Set the system property "com.healthmarketscience.jackcess.resourcePath=/res/raw/"
  • Copy the *.txt, *.mdb, and *.accdb files from the "com/healthmarketscience/jackcess/" directory in the Jackcess jar to the "/res/raw" Android application directory.
  • Before executing any Jackcess code, set the current Thread's context classloader, e.g. "Thread.currentThread().setContextClassLoader(Database.class.getClassLoader())"."