Relational Junction MS Access JDBC Driver Documentation

 
Contents

Installation
Driver Classes
URL Syntax
Driver Properties
Connection Example
Driver Modes
Data type mapping
Supported SQL Syntax
User-defined SQL functions
Working with attachment fields
Performance and other hints
Advanced topics

 

 

Installation

Add the driver jar files (mdbdriver.jar + required third-party libraries) to your classpath or extract these jars to the directory of your application.

 

 

Driver Classes

Description
Classes
Driver class (JDBC API v1.0)
com.relationaljunction.jdbc.mdb.MDBDriver2
Data Source class (JDBC API v2.0)
com.relationaljunction.jdbc.mdb.MDBDataSource2
Connection Pool Data Source class (JDBC API v2.0)
com.relationaljunction.jdbc.mdb.MDBConnectionPoolDataSource2

 

 

URL Syntax

The connection URL is jdbc:relationaljunction:mdb:path_to_mdb_file, where path_to_mdb_file is:

  • an absolute or relative path to a Microsoft Access database (MDB or ACCDB) file, e.g.:
jdbc:relationaljunction:mdb:c:/mdb_directory/test.mdb
jdbc:relationaljunction:mdb:mdb_directory/test2.mdb
jdbc:relationaljunction:mdb:mdb_directory/access2007.accdb
  • path to a file within the CLASSPATH (read-only), e.g.:
jdbc:relationaljunction:mdb:classpath://resources/test.mdb
  • path to a file within a ZIP (JAR) file (read-only), e.g.:
jdbc:relationaljunction:mdb:zip://c:/dir/archive.zip/test.mdb
  • path to a file located on a FTP server (syntax: ftp://user:password@hostname[:port]/[dirpath/]mdbfile), e.g.:
jdbc:relationaljunction:mdb:ftp://login:password@somesite.com:21/mdb_directory/test.mdb
  • SFTP URL to the SFTP-server directory (syntax: sftp://user:password@hostname[:port]/[dirpath/]mdbfile, also required third-party libraries Commons VFS and JSch for this protocol), e.g.:
jdbc:relationaljunction:mdb:sftp://login:password@somesite.com:22/mdb_directory/test.mdb
  • SHTTP URL to a file (read-only), e.g.:
jdbc:relationaljunction:mdb:http://www.somesite.com/mdb_directory/test.mdb
  • SMB/CIFS URL to a file located on a SMB/CIFS server (e.g.: MS Windows share or Samba server, syntax: smb://[user:password@]hostname/share/[dirpath/]mdbfile):
jdbc:relationaljunction:mdb:smb://your_server/your_share/mdb_directory/test.mdb
jdbc:relationaljunction:mdb:smb://login:password@your_server/your_share/mdb_directory/test.mdb
jdbc:relationaljunction:mdb:smb://domain;login:password@your_server/your_share/mdb_directory/test.mdb

 

 

Driver Properties

The driver supports a number of parameters that change default behavior of the driver.
These properties are:
create.To create a new MDB/ACCDB file set this property to “true”. (Default is false)
dbInMemory, dbPathare used to set a driver mode. For more details see driver modes.
format is used to specify a version of the MS Access (Jet) format for files being created. Possible values: access2000, access2003, access2007, access2010 (Default is “access2000”)

ignoreCase. The property sets the case sensitivity for string values. By default the driver are not case sensitive (ignoreCase = true), i.e. SQL queries SELECT * FROM test WHERE string_column = ‘some_value’ and SELECT * FROM test WHERE string_column = ‘SOME_VALUE’ are identical.

logPath is used to set a file path to the log file.

preSQL is used to specify a path to a SQL script that will be executed after creating a driver connection, e.g. c:/sql_script.txt

singletonConnection.If “singletonConnection” is set to “true”, the driver creates only one java.sql.Connection instance for each unique JDBC URL and reuses that connection for all threads. It allows the driver to increase up performance and reduce memory usage in multithreading environments like web/application servers Tomcat, GlassFish, WebSphere, etc. For example JDBC URL with this property may look like the following: jdbc:relationaljunction:mdb:c:/mdb_directory/test.mdb?property1=value1&property2=value2&singletonConnection=true. Also, it is recommended to execute the SQL command “SHUTDOWN” while closing (undeploying) a web application. Default value is false.

watchFileModifications tells the driver to start an additional thread that checks regularly (every second once by default) if the file was modified by some external process. If the file was modified, the driver refreshes it in the cache. (Default value is false). This property is recommended, if your files are modified by an external program / process and you want the changes to be immediately available to the driver. Also, use the property “checkPeriod” to set regularity of the file check. By default it is 1000 (1000 ms). Example: jdbc:relationaljunction:mdb:c:/mdb_directory/test.mdb?watchFileModifications=true&checkPeriod=3600000.
To set the driver properties, you can use one of the following ways:
1) using Properties class:


java.util.Properties props = new java.util.Properties();
props.put("format", "access2007");     
props.put("ignoreCase", "false"); 
Connection conn = DriverManager.getConnection("jdbc:relationaljunction:mdb:c:/test.mdb", props);

2) using a data source class com.relationaljunction.jdbc.mdb.MDBDataSource2:


MDBDataSource2 mdbDS = new MDBDataSource2();
mdbDS.setPath("c:/test.mdb");
mdbDS.setFormat("access2007");
mdbDS.setIgnoreCase("false"); 
Connection conn = mdbDS.getConnection();

3) appending the properties to the URL (multiple properties should be separated by ‘&’ or ‘!’ character):

Connection conn = 
DriverManager.getConnection("jdbc:relationaljunction:mdb:c:/test.mdb?format=access2007&ignoreCase=false");

 

 

Connection Example

The example code below shows how the driver is used.


import java.sql.*;
public class MDBDriverTest {
public static void main(String[] args) {
try {
// load the driver into memory
Class.forName("com.relationaljunction.jdbc.mdb.MDBDriver2");
// create a connection. The first command line parameter is assumed to
// be the MDB database in which data tables are held
Connection conn = DriverManager.getConnection("jdbc:relationaljunction:mdb:" + args[0]);
// create a Statement object to execute the query with
Statement stmt = conn.createStatement();
// execute a query
ResultSet rs = stmt.executeQuery("SELECT * FROM test");
// read the data and put it to the console
for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) {
System.out.print(rs.getMetaData().getColumnName(j) + "t");
}
System.out.println();
while (rs.next()) {
for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) {
System.out.print(rs.getObject(j) + "t");
}
System.out.println();
}
// close the objects
rs.close();
stmt.close();
conn.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}

 

 

Driver Modes

The driver works in the following way: it loads data from files to an intermediate database (also referred to as “synchrobase”) that is used for running SQL queries and synchronizing changes between this database and external MDB/ACCDB files.

The driver can work in the following three modes:
1) With a temporary synchrobase in RAM. The synchrobase is created in RAM and is removed from it after the connection is closed. It is a default mode.

2) With a temporary synchrobase on the hard drive. The synchrobase is created on the HDD every time a connection is opened and deleted after it’s closed. To use this mode, set the driver property dbInMemory to false.

 Connection conn = DriverManager.getConnection("jdbc:relationaljunction:mdb:c:/mdb/test.mdb?dbInMemory=false&tempPath=c:/tempfiles");

In the first mode, all synchrobase data are stored in the system RAM, which ensures maximum performance. Keep in mind that Java Virtual Machine must have enough free memory allotted for storing large tables (use -Xms and -Xmx JVM options).

In the second case, a temporary synchrobase is created on the hard drive. This mode is used for processing large files, since it uses a minimum of RAM. The synchrobase is deleted after the connection is closed.

In the third mode, the synchrobase is created once and is reused afterwards. This mode requires additional files to store a synchrobase on the hard drive.

Note: if your files are modified by an external program / process and you want the changes to be immediately available to the driver, consider using watchFileModificationsproperty. Also, you can use the “RELOAD CACHE” command to refresh tables in the cache manually.

There are also some properties for configuring these modes:

tempPath – directory where temporary files will be created (by default it is a OS temporary directory, specified by JVM environment variable “java.io.tmpdir”). It is recommended to set your own value. For example it can be a directory on a fast SSD disk.

Example:

 
Properties props = new java.util.Properties();
props.setProperty("dbInMemory", "false"); // switch to the second mode (a  temporary synchrobase on the hard drive)
props.setProperty("tempPath", "c:/temp");      
Connection conn = DriverManager.getConnection("jdbc:relationaljunction:mdb:c:/mdb/test.mdb", props);

 

 

Data type mapping

The table below demonstrates the mapping scheme between Relational Junction MDB data types and native MDB data types:

Relational Junction MDB data type MDB data type JDBC returned type (java.sql.Types.*) Java class used in Relational Junction MDB
Integer BYTE, INTEGER, LONG INTEGER, AUTONUMBER java.sql.Types.INTEGER java.lang.Integer
AUTONUMBER (IDENTITY) AUTONUMBER java.sql.Types.BIGINT java.lang.Integer
FLOAT SINGLE java.sql.Types.FLOAT java.lang.Float
Double DOUBLE, DECIMAL (precision < 15) java.sql.Types.DOUBLE java.lang.Double
CURRENCY CURRENCY java.sql.Types.NUMERIC java.lang.BigDecimal
NUMERIC (BIGDECIMAL) DECIMAL (precision > 15) java.sql.Types.NUMERIC java.lang.BigDecimal
STRING (VARCHAR) TEXT, OLE OBJECT java.sql.Types.VARCHAR java.lang.String
HYPERLINK HYPERLINK java.sql.Types.VARCHAR java.lang.String
Date DATE/TIME java.sql.Types.TIMESTAMP java.util.Date
BOOLEAN
YES/NO (BOOLEAN)
java.sql.Types.BOOLEAN java.lang.Boolean
MEMO MEMO java.sql.Types.VARCHAR java.lang.String
OLE (supported only OLE in packages) OLE java.sql.Types.BLOB java.sql.Blob
ATTACHMENT (see Working with attachment fields) ATTACHMENT java.sql.Types.OTHER com.relationaljunction.jdbc.mdb.store.MDBAttachmentContainer

 

Notes:
1) While inserting data to an existing MDB file, StelsMDB keeps original data types and their info that were set initially for columns.

2) If you want to create an MDB column with the native DECIMAL data type, you should specify either the DOUBLE type with the precision and the scale or the NUMERIC type. In other words, DOUBLE(10, 2) and NUMERIC(10, 2) will be mapped to DECIMAL with the precision = 10 and the scale = 2.
Example of CREATE TABLE statement:


CREATE TABLE test(
id AUTONUMBER, 	            // will be mapped to the native MDB type: AUTONUMBER
int_col INTEGER, 	    // will be mapped to the native MDB type: LONG INTEGER
float_col FLOAT, 	    // will be mapped to FLOAT
double_col DOUBLE, 	    // will be mapped to DOUBLE
decimal_col DOUBLE(10, 2),  // will be mapped to DECIMAL(10,2)
numeric_col NUMERIC(20, 2), // will be mapped to DECIMAL(20,2)
currency_col CURRENCY,      // will be mapped to CURRENCY
str_col STRING(25), 	    // will be mapped to TEXT with the length = 25
hyperlink_col HYPERLINK,    // will be mapped to HYPERLINK
datetime_col DATETIME,      // will be mapped to DATE/TIME
bool_col BOOLEAN,	    // will be mapped to YES/NO (BOOLEAN)
memo_col MEMO		    // will be mapped to MEMO
ole_col OLE		    // will be mapped to OLE
)

For more information about native MDB data types please see the corresponding MS Access documentation.

 

 

Supported SQL Syntax

Relational Junction MDB version 2.x uses H2 database as an SQL engine and supports the most part of ANSI/ISO SQL grammar like SELECT, INSERT, UPDATE, DELETE and CREATE statements.

An SQL query must meet the following conditions:

  • It must corespond to the SQL syntax supported by the driver. For detailed information about supported SQL syntax please see the specification here.
  • А column using an SQL reserved word as a name or containing spaces and other delimiters must be quoted in double quotes in a query. For example, SELECT “Date”, “My integer-column” FROM test
  • To use single quotes (‘) within a string constant you should duplicate them, e.g.: SELECT ‘a”bcd”efgh’

Query examples:


// ---- SELECT queries ---
SELECT SUM(a) AS col1, MAX(b) / MAX(c) AS col2 FROM test GROUP BY a  HAVING AVG(a) > 30
SELECT name FROM salesreps WHERE ( rep_office IN ( 22, 11, 12 ) )  OR ( manager IS NULL AND hire_date >= to_date ( '01-05-2002','dd-MM-yyyy' ) OR ( sales > quota AND NOT sales > 600000.0 )
SELECT city, target, sales FROM offices WHERE region = 'Eastern' AND sales > target ORDER BY city
// ---- SELECT queries with joins ----
SELECT * FROM prices ps JOIN regions regs ON ps.regionid = regs.id JOIN products prod ON        prod.prodid = ps.prodid  
SELECT * FROM prices ps, products prod, regions regs WHERE ps.regionid = regs.id AND prod.prodid = ps.prodid
// ---- INSERT, UPDATE and DELETE commands ----
INSERT INTO salesreps (name, age, empl_num, sales, title) VALUES ('Henry Smith', 35, 111, NULL,'Sales Mgr')
DELETE FROM salesreps WHERE NAME LIKE 'Henry%' 
UPDATE customers SET credit_limit = 50000.00 WHERE company = 'Acme Mfg.'
// ---- CREATE TABLE command ----
CREATE TABLE new_table (id AUTONUMBER, int_col INT, long_col LONG, float_col REAL, double_col DOUBLE, str_col VARCHAR(20), date_col DATETIME, bool_col BOOLEAN, num_col DECIMAL(15,2));
// ---- CREATE INDEX command ----
CREATE INDEX i_1 ON new_table (int_col);

See also:

 

 

User-defined SQL functions

You can use your own SQL functions in the driver. To use this feature, you should do the following:
1) Create a static method that will act as an SQL function
Mind that:

  • The method must have the public modifier
  • The number of arguments in the method is arbitrary, but they must belong to one of the java classes specified in the data types table
  • It is advisable to process the null values for arguments in the method code
  • The method can return the null value as well

For example:


package my_pack;
public class MyFuncs{
// user-defined SQL function that formats the given argument into a date/time string with   specified format
public static String format_date(java.util.Date d, String format) {
// process the null values
if (d == null || format == null)
return null;
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(format);
// return a function result with java.lang.String type
return sdf.format(d);
}
}

2) Register the function by executing CREATE ALIAS … FOR command. Use an unique name for each function.
For example:

CREATE ALIAS IF NOT EXISTS format_date FOR "my_pack.MyFuncs.format_date(java.util.Date, java.lang.String)"

Also, you can use the driver property function:.

For example:


Statement st = connection.createStatement();
st.execute( "SELECT format_date(date_column ,'yyyy-MM-dd' ) FROM test" );

 

 

Working with attachment fields

MS Access 2007-2010 supports the Attachment field.
Example of reading attachments by the driver:


ResultSet rs = st.executeQuery("SELECT * FROM test");
while (rs.next()) {
// get an attachment container by the getObject() method of the ResultSet interface
com.relationaljunction.jdbc.mdb.store.MDBAttachmentContainer mdbAttachmentContainer = (MDBAttachmentContainer) rs.getObject("attach_col");
for (MDBAttachmentContainer.Attachment attachment : mdbAttachmentContainer.getAttachments()) {
// write a file name to the system output
System.out.println(attachment.getFileName());
// attachment.getData() returns file content in byte[]
saveData(attachment.getData());
}
}

Example of inserting attachments by the driver:


PreparedStatement pst = conn.prepareStatement("INSERT INTO Table1(attach_col) VALUES(?)");
// init a new attachment container
com.relationaljunction.jdbc.mdb.store.MDBAttachmentContainer mdbAttachmentContainer = new MDBAttachmentContainer();
// add some file
mdbAttachmentContainer.addAttachment(new File("myfile.gif"));
// add some file
mdbAttachmentContainer.addAttachment(new File("myfile2.doc"));
// add some file
mdbAttachmentContainer.addAttachment(new File(".xls"));
// insert the attachment container by the setObject() method by the ResultSet interface
pst.setObject(1, mdbAttachmentContainer);
pst.execute();

 

 

Performance and other hints

  • Wherever possible use the java.sql.PreparedStatement class for SELECT, INSERT, UPDATE and DELETE operations
  • For bulk operations use batching, i.e.:

// bulk insertion
PreparedStatement pst = conn.prepareStatement("INSERT INTO test(id, str) VALUES(?,?)");
for (int i = 0; i < 10000; i++) {
pst.setInt(1, i);
pst.setString(2, "string " + i);
pst.addBatch();
}
pst.executeBatch();
  • If your tables are big (~> 100 MB), use modes with a syncrobase on the hard drive. For more information seeDriver modes.
  • Use the same connection (java.sql.Connection instance) for multiple threads.Set the property singletonConnection to “true” for web/application servers like Tomcat, GlassFish, WebSphere, etc.
  • Tables should have primary keys.
  • To check what indexes are used in a query use EXPLAIN command:

Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("EXPLAIN SELECT * FROM test WHERE id = 5");
// print a result set to the output to check the execution plan for a query
...
  • Use org.hibernate.dialect.H2Dialect in Hibernate.
  • Do not forget to close java.sql.ResultSet, java.sql.Statement and java.sql.Connection instances.
  • Use a modern JVM. Using JDK 1.7 can increase up the driver performance up to 50% as compared with JDK 1.5.

 

 

Advanced topics

Using the driver as a client/server (JDBC type 3) via RMI