Add the driver jar files (mdbdriver.jar + required third-party libraries) to your classpath or extract these jars to the directory of your application.
The class name of the driver is jstels.jdbc.mdb.MDBDriver.
The connection URL is jdbc:jstels:mdb:path_to_mdb_file, where path_to_mdb_file is absolute or relative path to the Microsoft Access database (MDB) file, e.g.:
jdbc:jstels:mdb:c:/mdb_directory/test.mdb jdbc:jstels:mdb:mdb_directory/test2.mdb |
Connection Example
The
example code below shows how the driver is used. You can download it
here.
import java.sql.*;
public class MDBDriverTest {
public static void main(String[] args) { try { // load the driver into memory Class.forName("jstels.jdbc.mdb.MDBDriver");
// 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:jstels: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(); } } } |
There are two main modes the driver can run in: the mode with data caching and the mode with data swapping. Running in the first mode (default mode), the driver caches MDB files in the RAM making it possible to achieve maximum performance while processing MDB. Keep in mind that Java Virtual Machine must have enough free memory allotted for storing large tables (use -Xms and -Xmx JVM options). You can also use the DROP TABLE <table name> FROM CACHE command to force tables to be removed from the cache.
The second mode is recommended for processing large MDB files (> 50 MB). To use this mode, set the driver property caching to false. There are also some properties for configuring this mode:
nonTransactSubMode is used to set transaction submode. The submode nonTransactSubMode = true is optimized for using SELECT queries and INSERT operations, it doesn't support transactions. The submode nonTransactSubMode = false completely supports transactions (by default this parameter is true).
tempPath - directory where temporary files will be created (by default it is a OS temporary directory, specified by JVM environment variable "java.io.tmpdir").
maxRecordsInMemoryPerTable
is used to define how many records
should be saved in the RAM for each table (by default - 5000 records).
maxSwapStringSize - size for MEMO columns in swap files (by default - 50
characters).
Example:
Properties props = new java.util.Properties();
props.setProperty("caching",
"false"); // switch to the
swapping mode Connection conn = DriverManager.getConnection("jdbc:jstels:mdb:" + args[0], props); |
StelsMDB data type |
MDB data type |
JDBC returned type (java.sql.Types.*) |
Java class used in StelsMDB |
Integer |
BYTE, INTEGER, LONG INTEGER, AUTONUMBER |
java.sql.Types.INTEGER |
java.lang.Integer |
FLOAT |
FLOAT |
java.sql.Types.FLOAT |
java.lang.Float |
Double |
DOUBLE, DECIMAL, CURRENCY |
java.sql.Types.DOUBLE |
java.lang.Double |
STRING |
TEXT, HYPERLINK, OLE OBJECT |
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.LONGVARCHAR |
java.lang.String |
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 the DOUBLE type with the precision and the scale. In other words, DOUBLE(15,2) will be mapped to DECIMAL with the precision = 15 and the scale = 2.
Example of CREATE TABLE statement:
CREATE TABLE test(
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(15,2), // will be mapped to DECIMAL(15,2) str_col STRING(25), // will be mapped to TEXT with the length = 25 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 )
|
For more information about native MDB data types please see the corresponding MS Access documentation.
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 (') and backslashes (\) in a string constant you should use an escape character (\). For example: 'abcd\'ef\\g'
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 citySELECT * FROM prices ps JOIN regions regs ON ps.regionid = regs.id JOIN products prod ON prod.prodid = ps.prodidINSERT 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.' |
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 ) {
if (d ==
null || format == null)
return
sdf.format(d); |
2) Register the function using the static method jstels.jdbc.mdb.MDBDriver.registerFunction (String funcName, String handler). Where funcName argument is the name by which this method will be called in SQL queries and handler argument is the fully-qualified method name.
For example:
jstels.jdbc.mdb.MDBDriver.registerFunction ( "format_date", "my_pack.MyFuncs.format_date" ) |
Also, you can use the driver property function:<my_func>.
For example:
Properties props = new java.util.Properties(); props.put("function:formate_date","my_pack.MyFuncs.format_date"); ... Connection conn = DriverManager.getConnection("jdbc:jstels:mdb:" + args[0], props);
// or append this property to the URL
Connection conn2 = DriverManager.getConnection("jdbc:jstels:mdb:" + args[0]
+ "?function:formate_date=my_pack.MyFuncs.format_date"); |
3) Call the function in an SQL query
For example:
Statement st = connection.createStatement(); st.execute( "select format_date( date_column , 'yyyy-MM-dd' ) from test" ); |