UCanAccess

Getting Started

Note that the distribution comes with a large number of JUnit test cases which illustrate how UCanAccess may be used.

Adding UCanAccess to your Java project

If your project uses Maven you can simply include UCanAccess via the following dependency in your Maven POM:

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.0</version>
</dependency>

Otherwise, see this Stack Overflow answer for details on how to configure your Java project.

Establishing a UCanAccess connection

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
Connection connTemplate = DriverManager.getConnection("jdbc:ucanaccess://<mdb or accdb file path>", user, password);

// for example:
Connection connExample = DriverManager.getConnection("jdbc:ucanaccess://c:/data/myaccessdb.mdb;memory=true");

Connection Properties

memory: set if HSQLDB will store its backing database only in memory. Default is memory=true .

lobScale: to optimize memory occupation when a relevant percent of memory occupation is for OLE (BLOB) data. This may also allow, in many cases, to use the memory=true setting, whilst it was impossible before. It should be set to a value close to the average size of OLE instances. According with the HSQLDB documentation, the values allowed are 1, 2, 4, 8, 16, 32 (the unit is Kb). Default is 2 if memory=true, 32 (HSQLDB default) otherwise.

skipIndexes: in order to minimize memory occupation, it allows skipping the creation of simple (untied to contraints) indexes. It doesn't have effect on referential integrity constraints (i.e., Index Unique, Foreign Key or Primary Key).

newDatabaseVersion: UCanAccess will create a new Access database file in the specified version if this parameter is set and the file specified in the JDBC connection URL does not exist. Valid values for this parameter are: V2000, V2003, V2007, V2010.

openExclusive (replaces the deprecated lockMdb): if openExclusive is enabled ( openExclusive=true ), UCanAccess locks the mdb/accdb file to prevent updates from other processes. Default=false.

showSchema: if true, catalog "PUBLIC" and schema "PUBLIC" and other HSQLDB schemas are shown in the DatabaseMetadata implementation. Default=false. Using showSchema=true is recommended with Squirrel SQL.

inactivityTimeout (since UcanAccess 0.1.1): it is used only with the default memory mode (memory=true). If there are no active connections for the inactivityTimeout period (in minutes) HSQLDB will be temporarily shut down and any file system resources will be released. It will be restarted at the next connection. Setting inactivityTimeout=0 can be used for avoiding this behavior. Default = 2 minutes.

immediatelyReleaseResources (replaces singleConnection which has been deprecated since UCanAccess 3.0.6): it is used for for ETL jobs, scheduled tasks, or "one-shot" use of UCanAccess with only one connection open. All resources (memory and file system) will be released at the closing of the connection. Default=false.

encrypt: it allows HSQLDB files encryption. You can use it in conjunction with memory=false. Default=false.

sysSchema: if true the Access system tables will be loaded in the "sys" readonly schema. Default=false.

ignoreCase: Disables (ignoreCase=true) or enables (ignoreCase=false) the case sensitivity of text comparison. Default is true.
For example:
Connection conn = DriverManager.getConnection("jdbc:ucanaccess://c:/data/myaccessdb.mdb;openExclusive=true;ignoreCase=true");

remap: it allows to remap the paths to one or more external linked databases.
The syntax is:
jdbc:ucanaccess://c:/data/<main db>;remap=<original db1 path>|<new db1 path>&<original i-db path>|<new i-db path>
e.g.,
jdbc:ucanaccess://c:/data/main.mdb;remap=c:\db\linkee1.mdb|C:\pluto\linkee1.mdb&c:\db\linkee2.mdb|C:\pluto\linkee2.mdb
Original path and new path must be separated by '|', different pairs must be separated by '&'.

mirrorFolder: it allows users to set the directory in which the mirror db will be created.
It forces memory=false.
Simply use mirrorFolder=java.io.tmpdir in order to specify the system temp folder for that.

keepMirror: keeps the HSQLDB mirror database after the VM ends so it can be reused by subsequent VM processes.
It forces memory=false.
In other words, using this parameter, the time-expensive process of creating and populating the database hsqldb (with memory=false), is executed only once.
jdbc:ucanaccess://c:/data/main.mdb;keepMirror=c:/data/mirrorName
Reusing the same keepMirror setting (e.g. keepMirror=c:/data/mirrorName) at the next VM process execution allows you to dramatically reduce the time of first connection. It should be used only with very large databases (e.g., 1GB with many binary OLE objects) and when:

• You have to change the default memory=true setting because you can't allocate sufficient JVM heap space (Xmx VM parameter).
• UCanAccess takes too much time to establish the first connection (because it's populating the HSQLDB mirror database).

Caution! - If the access database is modified by a different program (so not using UCanAccess) after the HSQLDB creates the mirror database, UCanAccess recreates and repopulates the whole mirror database for avoiding unchecked misalignments.

columnOrder: use the "DISPLAY" order of columns with SELECT * statements. ("DATA" order is still the default).
jdbc:ucanaccess://c:/data/myaccessdb.mdb;columnOrder=DISPLAY
Note that once a column order has been set on the first connection to a given database, that setting will be the same for all the following connections to that database, in the whole VM life.

concatNulls: controls the behavior of all concatenation operators ( & , + , || ) with null values:
null & 'thing' -> 'thing' . (It gave null prior to version 3.0.0.)
null + 'thing' -> 'thing' . (It gave null prior to version 3.0.0.)
If you want to switch to the old behavior, you have to set the new connection property concatNulls=true .

preventReloading: optimize the reloading of db instances. If and only if you're sure that your db can be accessed by the MS Access GUI and just one JVM instance (using UCanAccess), you can use the connection parameter preventReloading=true . It avoids the data being reloaded when they aren't actually changed by the GUI.

jackcessOpener: in order to use Jackcess Encrypt extension, you can specify a class that implements the net.ucanaccess.jdbc.IJackcessOpenerInterface interface (in that case you need additional jar files in your classpath: a version of jackcess-encrypt.jar and all related dependencies).
The following example shows a custom integration with Jackcess Encrypt for opening a Money (.mny) file: Example for UCanAccess2. Notice that you must use UCanAccess 2.x.x with jackcess-encrypt-2.x.x and all related dependencies

package yourPackage.example;

//imports from Jackcess Encrypt
import io.github.spannm.jackcess.CryptCodecProvider;
import io.github.spannm.jackcess.Database;
import io.github.spannm.jackcess.DatabaseBuilder;
import net.ucanaccess.jdbc.IJackcessOpenerInterface;

import java.io.File;
import java.io.IOException;

public class CryptCodecOpener implements IJackcessOpenerInterface {

    public Database open(File fl, String pwd) throws IOException {
        DatabaseBuilder dbd = new DatabaseBuilder(fl);
        dbd.setAutoSync(false);
        dbd.setCodecProvider(new CryptCodecProvider(pwd));
        dbd.setReadOnly(false);
        return dbd.open();

    }

    // Notice that the parameter setting AutoSync=false is recommended with UCanAccess for performance reasons.
    // UCanAccess flushes the updates to disk at transaction end.
    // For more details about autosync parameter (and related tradeoff), see the Jackcess documentation.
}

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
Connection conn = DriverManager.getConnection("jdbc:ucanaccess:///opt/prova1.mny;jackcessOpener=yourPackage.example.CryptCodecOpener", "sa", password);
...

For old UCanAccess versions (0.x.x, 1.x.x) JackcessOpenerInterface should be implemented in a different way, because of the jackcess API changes from jackcess1 to jackcess2.

package yourPackage.example;

import java.io.File;
import java.io.IOException;
//imports from Jackcess Encrypt
import io.github.spannm.jackcess.CryptCodecProvider;
import io.github.spannm.jackcess.Database;

import net.ucanaccess.jdbc.IJackcessOpenerInterface;

public class CryptCodecOpener implements JackcessOpenerInterface {
    public Database open(File _fl, String _pwd) throws IOException {
        return Database.open(_fl, true, true, null, null, new CryptCodecProvider(_pwd));
    }
}

Creating tables with DDL

Statement st = conn.createStatement();
st.execute("CREATE TABLE example1 (id COUNTER PRIMARY KEY, descr TEXT(400), number NUMERIC(12,3), date0 DATETIME)");
st.execute("CREATE TABLE dkey (c COUNTER, number NUMERIC(23, 5), PRIMARY KEY (C, NUMBER)");
st.execute("CREATE TABLE dtrx (c TEXT, number NUMERIC(23, 5), UNIQUE (C, NUMBER)) ");
st.execute("CREATE TABLE Parent (x AUTOINCREMENT PRIMARY KEY, y TEXT(222))");
st.execute("CREATE TABLE Babe (k LONG, y LONG, PRIMARY KEY(k,y), FOREIGN KEY (y) REFERENCES Parent (x) )");

Also UCanAccess supports create table as (<sql select>) syntax:

st.executeUpdate("CREATE TABLE copy AS (SELECT * FROM example1) WITH DATA ");

Executing queries

The following example shows a full outer join:

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM example3 FULL OUTER JOIN example4 ON (example3.id = example4.id)");

Using Access functions

ResultSet rs1 = st.executeQuery("SELECT IIf(descr='Show must go off', 'tizio', 'caio&sempronio'&'&M A'&' '& Now() & RTrim(' I''m proud of you ')) FROM example1");

// Domain aggregate functions:
ResultSet rs2 = st.executeQuery("select id, DCount('*','t234','1=1') from t234");

More complete examples of access functions implementation are shown in the net.ucanaccess.test.FunctionsTest JUnit test suite.
The following functions are already implemented:

ASC, ATN, CBOOL, CCUR, CDATE, CDBL, CDEC, CINT, CLONG, CSIGN, CSTR, CVAR, DATEADD, DATEDIFF, DATEPART, DATE, DATESERIAL, DATEVALUE, FIX, FORMAT, IIF, INSTR, INSTRREV, ISDATE, ISNUMERIC, INT, IsNull, LEN, MID, MONTHNAME, NOW, NZ, PARTITION, SIGN, SPACE, SQR, STR,, STRING, STRCOMP, STRCONV, STRREVERSE, SWITCH, RND, TIME, TIMESERIAL, VAL, WEEKDAY, WEEKDAYNAME

Aggregate and Domain Functions:

FIRST, LAST, DCOUNT, DAVG, DSUM, DMAX, DMIN, DFIRST, DLAST, DLOOKUP

Financial Functions:

MT, NPER, IPMT, PPMT, RATE, PV, FV, DDB, SYD, SLN

Also you can use the following functions from the hsqldb implementation:

COS, SIN, LTRIM, RTRIM, UCASE, LCASE

Aggregate Functions:

COUNT, AVG, SUM, MAX, MIN, STDEV, STDEVP, VAR, VARP

User-defined Functions

Step 1: implementation of a new function:

public static class XXX {
    @FunctionType(functionName = "justconcat", argumentTypes = {AccessType.TEXT, AccessType.TEXT}, returnType = AccessType.TEXT)
    public static String concat(String _s1, String _s2) {
        return _s1 + " >>>>" + _s2;
    }
}

Step 2: Registration:

UcanaccessConnection uc=(UcanaccessConnection) this.ucaConn;
uc.addFunctions(XXX.class);

Step 3: Trying your new function:

ResultSet rs=st.executeQuery("SELECT justConcat(descr,''&now()) FROM example1");

Using Criteria in LIKE Clause

ResultSet rs = st.executeQuery("SELECT descr FROM example2 WHERE descr LIKE 'P%'");
// access * jolly
rs = st.executeQuery("SELECT descr FROM example2 WHERE descr LIKE 'P*'");
// number and interval patterns
rs = st.executeQuery("SELECT descr FROM example2 WHERE descr LIKE 'P[A-F]###'");
// number pattern
rs = st.executeQuery("SELECT descr FROM example2 WHERE descr LIKE 'C#V##'");

Limit and offset SQL pagination

rs = st.executeQuery("SELECT * FROM example2 order by id desc LIMIT 5 OFFSET 1");
rs = st.executeQuery("SELECT TOP 10 * FROM example2 order by id");

Performing transactions

conn.setAutoCommit(false);
st = this.ucaConn.createStatement();
st.executeUpdate("UPDATE example4 SET descr='Lugo di Romagna'");
st.execute("INSERT INTO example4 (ID, descr) VALUES(5,'DALLAS')");
conn.commit();

Using updatable ResultSet

PreparedStatement ps = super.ucanaccess.prepareStatement(
    "SELECT * FROM T1", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
rs = ps.executeQuery();
rs.next();
rs.updateString(2, "show must go off");
rs.updateRow();

Working with date type

st.execute("INSERT INTO example1 (descr, number, date0) "
    + "VALUES(\"Show must go up and down\" ,-113.55446, #11/22/2003 10:42:58 PM#)");

Working with OLE type

InputStream isDB = rs.getBinaryStream("OleColumn");
File file = new File("mypic.jpg");
OutputStream outFile = new FileOutputStream(file);
byte[] ba = new byte[4096];
while ((len = isDB.read(ba)) != -1) {
    outFile.write(ba, 0, len);
}
out.flush();
out.close();
System.out.println("mypic.jpg was created in " + file.getAbsolutePath());

About ' and " escaping

Both ' and " are valid string delimiters.
So the following SQL statements are correct:

UPDATE TABLE1 SET SURN='D''Ambrogio';(value saved=D'Ambrogio)

UPDATE TABLE1 SET SURN="""D'Ambrogio""";(value saved="D'Ambrogio")

Working with Complex Types

...
PreparedStatement ps = conn.prepareStatement("INSERT INTO TABLE1(ID , MEMO_DATA , APPEND_MEMO_DATA , MULTI_VALUE_DATA , ATTACH_DATA) VALUES (?,?,?,?,?)");
ps.setString(1, "row12");
ps.setString(2, "ciao");
ps.setString(3, "to version");
SingleValue[] svs = new SingleValue[] {new SingleValue("ccc16"), new SingleValue("ccc24")};
// Array of net.ucanaccess.complex.SingleValue

ps.setObject(4, svs);
Attachment[] atcs =
    new Attachment[] {new Attachment(null, "ccc.txt", "txt", "ddddd ddd".getBytes(), new Date(), null), new Attachment(null, "ccczz.txt", "txt", "ddddd zzddd".getBytes(), new Date(), null)};
// Array of net.ucanaccess.complex.Attachment

ps.setObject(5, atcs);
ps.execute();
...

ps = conn.prepareStatement("UPDATE TABLE1 SET APPEND_MEMO_DATA='THE CAT' ");
// APPEND_MEMO_DATA is an append only column: a new net.ucanaccess.complex.Version will be added to the version array

ps.execute();
...

// insert existing file as an attachment
// (note that the parameter value is an Attachment *array*, even for a single attachment)
String attachmentPath = "C:/Users/Gord/Documents/sample.pdf";
byte[] attachmentData = java.nio.file.Files.readAllBytes(Paths.get(attachmentPath));
ps = super.ucanaccess.prepareStatement("UPDATE TABLE1 SET ATTACH_DATA=?");
Attachment[] attachmentArray = new Attachment[] {
    new Attachment(null, "sample.pdf", "pdf", attachmentData, new java.util.Date(), null)
};
ps.setObject(1, attachmentArray);
ps.executeUpdate();

// you cannot use = operator here: use the Equals or the EqualsIgnoreOrder functions instead
ps = conn.prepareStatement("SELECT * FROM Table1 WHERE Equals(ATTACH_DATA,?)");
ps.setObject(1, atc);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    System.out.println(rs.getObject("ATTACH_DATA"));
    // rs.getObject("ATTACH_DATA") will return an array of net.ucanaccess.complex.Attachment
}
...

Filtering on Complex Type Columns (since v2.0.9)

The equality operator (=) can't be used on complex type columns, use the following functions instead.

- Equals. It returns true if the two specified arrays of SingleValue, Attachment or Version are equal to one another, false otherwise. In other words they are equal if all corresponding pairs of elements in the two arrays are equal.

//true for all records whose MULTI_VALUE_COLUMN value equals [value1,value2]
...
PreparedStatement ps = conn.prepareStatement("SELECT * FROM TABLE1 WHERE Equals(MULTI_VALUE_COLUMN,?)");
ps.setObject(1, SingleValue.multipleValue("value1", "value2"));
ResultSet rs = ps.executeQuery();
...

- EqualsIgnoreOrder. It returns true if the two specified arrays of SingleValue, Attachment or Version are equal to one another regardless the order, false otherwise.

//true for all records whose MULTI_VALUE_COLUMN value equals [value1,value2] or [value2,value1]
...
PreparedStatement ps = conn.prepareStatement("SELECT * FROM TABLE1 WHERE EqualsIgnoreOrder(MULTI_VALUE_COLUMN,?)");
ps.setObject(1, SingleValue.multipleValue("value1", "value2"));
ResultSet rs = ps.executeQuery();
...

- Contains. It returns true if the Array of SingleValue, Attachment or Version(column value) passed as first argument contains all the element of the Array passed as second argument.

// true for all records whose MULTI_VALUE_COLUMN value contains both value1 and value2
...
PreparedStatement ps = conn.prepareStatement("SELECT * FROM TABLE1 WHERE Contains(MULTI_VALUE_COLUMN,?) ");
ps.setObject(1, SingleValue.multipleValue("value1", "value3"));
ResultSet rs = ps.executeQuery();
...

Table renaming (since v4.0.0)

st.execute("ALTER TABLE [My old name] RENAME TO [My new name]");

Add new Column to Existing Table (since v4.0.0)

st.execute("ALTER TABLE xxx ADD COLUMN yyy TEXT");

st.execute("ALTER TABLE zzz ADD COLUMN kkk DATETIME NOT NULL DEFAULT now()");

st.execute("ALTER TABLE [222 crazy name] ADD COLUMN [another crazy name] numeric (23,6) default 13.031955 not null");

Add new Index to Existing Table (since v4.0.0)

st.execute("CREATE unique INDEX [index name] on [table name] (a ASC,c ASC )");

st.execute("CREATE INDEX [index name] on bbb (a ASC,c ASC )");

Add Primary Key to Existing Table (since v4.0.0)

st.execute("ALTER TABLE xxx ADD PRIMARY KEY (column1,column2)");

Foreign Key creation (since v4.0.0)

st.execute("ALTER TABLE x ADD CONSTRAINT constr FOREIGN KEY (colx1,colx2) REFERENCES y (coly1, coly2) ON DELETE CASCADE");
// or
st.execute("ALTER TABLE x ADD CONSTRAINT constr FOREIGN KEY (colx1,colx2) REFERENCES y (coly1, coly2) ON DELETE SET NULL");

Counters handling (since v3.0.0)

st.execute("DISABLE AUTOINCREMENT ON TABLE_NAME");
st.execute("ENABLE AUTOINCREMENT ON TABLE_NAME");