Relational Junction dBase JDBC Driver Documentation

Contents

Installation

Add the file dbfdriver.jar to your classpath or extract the jar file in the directory of the application.

Driver Classes

Description
Classes
Driver class (JDBC API v1.0)
com.relationaljunction.jdbc.dbf.DBFDriver2
Data Source class (JDBC API v2.0)
com.relationaljunction.jdbc.dbf.h2.DBFDataSource2
Connection Pool Data Source class (JDBC API v2.0)
com.relationaljunction.jdbc.dbf.h2.DBFConnectionPoolDataSource2

URL Syntax

The connection URL is jdbc:relationaljunction:dbf:dbfdir, where dbfdir may be the following:

  • absolute or relative path to the local directory containing text files, e.g.:
jdbc:relationaljunction:dbf:c:/mydir/dbffiles
jdbc:relationaljunction:dbf:dbffiles
  • path to the resource directory within the CLASSPATH, e.g.:
jdbc:relationaljunction:dbf:cache://classpath://resources
path to the directory entry within the zip (jar) file, e.g.:
jdbc:relationaljunction:dbf:cache://zip://c:/mydir/dbffiles.zip
jdbc:relationaljunction:dbf:cache://zip://myApp.jar/dbffiles
  • FTP URL to the FTP-server directory (syntax: ftp://user:password@hostname[:port]/[dirpath/]), e.g.:
jdbc:relationaljunction:dbf:ftp://login:password@somesite.com:21/dbffiles
  • FTP URL to a ZIP archive containing DBF files (syntax: cache://zip://ftp://user:password@hostname[:port]/[dirpath/]zipfile), e.g.:
jdbc:relationaljunction:dbf:cache://zip://ftp://login:password@somesite.com:21/archives/dbffiles.zip
  • SFTP URL to the SFTP-server directory (syntax: sftp://user:password@hostname[:port]/[dirpath/], also required third-party libraries Commons VFS, Commons Logging and JSch for this protocol), e.g.:
jdbc:relationaljunction:dbf:sftp://login:password@somesite.com:22/dbffiles
HTTP URL to the web-server directory, e.g.:
jdbc:relationaljunction:dbf:http://www.somesite.com/dbffiles
  • HTTP URL to the dynamic server page (JSP, ASP, PHP, CGI, etc) which returns a DBF file as output. For more information please see useWebParam driver property.
jdbc:relationaljunction:dbf:http://www.somesite.com/out.jsp
  • SMB/CIFS URL to a directory located on a SMB/CIFS server (e.g.: MS Windows share or Samba server, syntax: smb://[user:password@]hostname/share/[dirpath/]):
jdbc:relationaljunction:dbf:cache://smb://your_server/your_share/your_folder
jdbc:relationaljunction:dbf:cache://smb://login:password@your_server/your_share/your_folder
jdbc:relationaljunction:dbf:cache://smb://domain;login:password@your_server/your_share/your_folder

Also, for Visual FoxPro files you can set a path to a database container file (.dbc). DBC allows using column names with more than 10 characters:

  • absolute or relative path to .dbc file
jdbc:relationaljunction:dbf:c:/mydir/database.dbc
jdbc:relationaljunction:dbf:dbffiles/database.dbc

Additionally, a path to some particular DBF file can be specified directly in a SQL query, e.g.:

  • absolute file path to a file, e.g.:
SELECT * FROM "c:/dbffiles/test.dbf"
  • relative path to a file:
SELECT * FROM "subfolder/test.dbf"
  • other URL path:
SELECT * FROM "http://www.somesite.com/dbffiles/test.dbf"
SELECT * FROM "cache://zip://c:/dbffiles.zip/dir/test.dbf"
SELECT * FROM "ftp://login:password@somesite.com:21/dbffiles/test.dbf"

Driver Properties

The driver supports a number of parameters that change default behavior of the driver.

These properties are:

charset is used to specify an encoding in a DBF/VFP file. Possible values are encodings supported in Java (Default is “8859_1” charset)

dbInMemory, dbPath are used to set a driver mode. For more details see driver modes.

extension is used to specify a different file extension (Default is “.dbf“)
If extension set to “.dbf” then both “myTable.dbf” and myTable are valid.
Note: You should stick to one of these table naming types in the SQL queries, i.e. either “myTable.dbf” or myTable.

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.

format is used to specify the DBF format for new tables created by the “CREATE TABLE” operation. Possible values: DBASEIII or VFP (Default is “DBASEIII”)

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

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:dbf:c:/dbffiles?watchFileModifications=true&checkPeriod=3600000

Advanced Properties

emptyStringAsNull. If “emptyStringAsNull” is set to ‘true’, empty strings (i.e. “”) are treated as NULL values. (By default is true).

memoExtension is used to specify a file extension for memo files. (Default is “.dbt” for DBase III/IV files, “.fpt” for FoxPro and Visual FoxPro files).

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. It may be useful for creating indexes and other preparations.

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:dbf:c:/mydir/dbffiles?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.

trimBlanks. If “trimBlanks” is set to ‘true’, the driver trims leading and trailing spaces for string values when reading a DBF file. (By default is true).

useBigDecimalType. If ‘useBigDecimalType’ is set to ‘true’, the driver uses BIGDECIMAL type for all floating point columns such as FLOAT, DOUBLE, CURRENCY, NUMERIC. BIGDECIMAL type is recommended for storing currency values or other values that require arithmetical calculations via SQL. See also Data type mapping (By default is false).

useWebParam is used to specify the name of the web parameter that will be used to transfer a DBF file name in the dynamic server page. For instance, if you specify the following value: ‘tablename’, the driver will access server page using the following HTTP URL:

http://www.site.com/out.jsp?tablename=sometable (where ‘sometable’ is the table specified in the SQL query)
Note: If you specify driver properties directly in the driver URL and server page also has its own parameters, you should separate them with ‘??‘:
jdbc:relationaljunction:dbf:http://www.site.com/out.jsp?param1=value1??useWebParam=tablename&dbInMemory=false

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(“extension”, “.db”); // file extension is .db
props.put(“charset”, “ISO-8859-2”); // file encoding is “ISO-8859-2”

Connection conn = DriverManager.getConnection(“jdbc:relationaljunction:dbf:c:/dbffiles”, props);

2) using a data source class com.relationaljunction.jdbc.dbf.DBFDataSource2:

DBFDataSource2 dbfDS = new DBFDataSource2();
dbfDS.setPath("c:/dbffiles");           // path to a DBF directory
dbfDS.setFileExtension(".db");          // file extension is .db
dbfDS.setCharset("ISO-8859-2");         // file encoding is "ISO-8859-2"
Connection conn = dbfDS.getConnection();

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

Connection conn = 
DriverManager.getConnection("jdbc:relationaljunction:dbf:c:/dbffiles?charset=ISO-8859-2&dbInMemory=false");

Connection Example

This example code shows how the driver is used.

import java.sql.*;
public class DBFDriverTest {
public static void main(String[] args) {
try {
// load the driver into memory
Class.forName("com.relationaljunction.jdbc.dbf.DBFDriver2");
// create a connection. The first command line parameter is assumed to
// be the directory in which the .dbf files are held
Connection conn = DriverManager.getConnection("jdbc:relationaljunction:dbf:" + 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.dbf"");
// 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 DBF 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:dbf:c:/dbffiles?dbInMemory=false&tempPath=c:/tempfiles");

3) With a persistent synchrobase on the hard drive. The synchrobase is created just once and is re-used afterwards. To use this mode set the property dbPath to the file path where is synchrobase will be stored.

Connection conn = DriverManager.getConnection("jdbc:relationaljunction:dbf:c:/dbffiles?dbPath=c:/synchrobases/syncro_db_name&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). You can also use the DROP TABLE

FROM CACHE command to force tables to be removed from the cache.

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.

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:dbf:c:/dbffiles", props);

Data type mapping

The table below demonstrates the mapping scheme between Relational Junction DBF data types and DBF/Visual FoxPro data types:

Relational Junction DBF data type DBF data type JDBC returned type (java.sql.Types.*) Java class used in Relational Junction DBF Driver
Integer NUMERIC ( length <= 9, decimal count = 0 ), INTEGER java.sql.Types.INTEGER java.lang.Integer
Bigint NUMERIC ( length > 9, decimal count = 0 ) java.sql.Types.BIGINT java.lang.Long
Double

(useBigDecimalType = false, default)

NUMERIC (decimal count > 0), FLOAT, CURRENCY, DOUBLE java.sql.Types.DOUBLE java.lang.Double
BIGDECIMAL

(useBigDecimalType = true)

NUMERIC (decimal count > 0), FLOAT, CURRENCY, DOUBLE java.sql.Types.NUMERIC java.math.BigDecimal
VARCHAR CHARACTER java.sql.Types.VARCHAR java.lang.String
DateTIME, DATE DATE (for DBF III/IV files), DATETIME (for VFP files) java.sql.Types.TIMESTAMP java.util.Date
BOOLEAN LOGICAL java.sql.Types.BOOLEAN java.lang.Boolean
MEMO MEMO, GENERAL, PICTURE java.sql.Types.VARCHAR java.lang.String
BLOB GENERAL, PICTURE java.sql.Types.BLOB java.sql.Blob

Note:

While creating Visual FoxPro files (driver property ‘format’ = ‘VFP’), INTEGER, DOUBLE and DATETIME data types will be mapped to the native Visual FoxPro types INTEGER, DOUBLE and DATETIME.

Example of CREATE TABLE statement:

CREATE TABLE test(
int_col INTEGER(5), 
long_col BIGINT(12), 
float_col NUMERIC(15,2),
str_col VARCHAR(10),
dat_col DATETIME,
bool_col BOOLEAN
)

For more information about DBF/Visual FoxPro data types please see the corresponding DBase/XBase/Visual FoxPro documentation.

Supported SQL Syntax

Relational Junction DBF version 5.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.dbf”
  • 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.dbf" GROUP BY a  HAVING AVG(a) > 30
SELECT name FROM "salesreps.dbf" 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.dbf" WHERE region = 'Eastern' AND sales > target ORDER BY city
// ---- SELECT queries with joins ----
SELECT * FROM "prices.dbf" ps JOIN "regions.dbf" regs ON ps.regionid = regs.id JOIN "products.dbf" prod ON prod.prodid = ps.prodid
SELECT * FROM "prices.dbf" ps, "products.dbf" prod, "regions.dbf" regs WHERE ps.regionid = regs.id AND prod.prodid = ps.prodid
// ---- INSERT, UPDATE and DELETE commands ----
INSERT INTO "salesreps.dbf" (name, age, empl_num, sales, title) VALUES ('Henry Smith', 35, 111, NULL, 'Sales Mgr')
DELETE FROM "salesreps.dbf" WHERE NAME LIKE 'Henry%' 
UPDATE "customers.dbf" SET credit_limit = 50000.00 WHERE company = 'Acme Mfg.'
// ---- CREATE TABLE command ----
CREATE TABLE "new_table.dbf" (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.dbf" (int_col);

See also:

Unsupported features

The driver does not support CDX, IDX indices.

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:

Properties props = new java.util.Properties();
props.put("function:formate_date","my_pack.MyFuncs.format_date");
Connection conn = DriverManager.getConnection("jdbc:relationaljunction:dbf:c:/dbffiles", props);
// or append this property to the URL
Connection conn2 = DriverManager.getConnection("jdbc:relationaljunction:dbf:c:/dbffiles
+ "?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.dbf"" );

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();
  • Create indexes using CREATE INDEX … operation
  • 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.
  • If your tables are big (~> 100 MB), use modes with a syncrobase on the hard drive. For more information see Driver modes.
  • 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.8 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