Relational Junction XML JDBC Driver Documentation

Contents

 

Getting Started

To process your XML documents via Relational Junction XML JDBC driver, you should do the following easy steps:

  • Add the file xmldriver.jar and other third-party libraries to your classpath or extract the jar file in the directory of the application.
  • Create the schema file and add your table specifications (please see Schema File File for more details).
  • Register the driver in your Java code:
  • Class.forName("com.relationaljunction.jdbc.xml.XMLDriver2");
  • Connect to the driver using java.sql.Connection class.
  • Connection conn = DriverManager.getConnection("jdbc:relationaljunction:xml:c:/xmlfiles/schema.xml");
  • The connection URL of Relational Junction XML is jdbc:relationaljunction:xml:schema_path, where schema_path may be the following:
    ▪ absolute or relative file path to the schema file:
    jdbc:relationaljunction:xml:c:/xmlfiles/schema.xml
    jdbc:relationaljunction:xml:xmlfiles/schema.xml

    ▪ path to the directory entry within the zip (jar) file:
    jdbc:relationaljunction:xml:zip://c:/archive.zip/schema.xml
    ▪ path to the resource directory within the CLASSPATH:
    jdbc:relationaljunction:xml:classpath://resources/schema.xml
    ▪ FTP URL to the schema file:
    jdbc:relationaljunction:xml:ftp://login:password@somesite.com:21/xmlfiles/schema.xml
    ▪ HTTP URL to the schema file:
    jdbc:relationaljunction:xml:http://www.somesite.com/xmlfiles/schema.xml
    ▪ HTTP URL to the dynamic server page (JSP, ASP, PHP, CGI, etc) which returns the schema file as output:
    jdbc:relationaljunction:xml:http://www.somesite.com/out.jsp?param1=value1??dbInMemory=false
    // note: if you specify driver properties directly in the driver URL, and server page
    // has its own parameters as well, you should separate them with ‘??’

    ▪ SMB/CIFS URL to a file located on a SMB/CIFS server (e.g.: MS Windows share or Samba server):
    jdbc:relationaljunction:xml:smb://your_server/your_share/your_folder/schema.xml
  • Execute an SQL query using java.sql.Statement class:
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");

 

Schema File

The schema file is used to define the specifications of tables the driver can later access using SQL queries. Each table is defined with the help of XPath expressions to various elements of an XML document (i.e. tag elements, attributes, etc).
Suppose we have an simple XML document that stores data about the company’s employees:

<?xml version="1.0" encoding="UTF-8"?>
<employees>
<document_name>Employees doc</document_name>
<employee id="1">
<first_name>Bill</first_name>
<last_name>Adams</last_name>
<age>25</age>
<hire_date>12-06-1995</hire_date>
<title>Java programmer</title>
</employee>
<employee id="2">
<first_name>Mary</first_name>
<last_name>Jones</last_name>
<age>32</age>
<hire_date>22-09-2001</hire_date>
<title>Sales manager</title>
</employee>
</employees>

And we want to generate the “employees” table based on it. In this case the table specification may look as follows:


<?xml version="1.0" encoding="UTF-8"?>
<schema><table name="employees" file="employees.xml" path="/employees/employee" constraint = "PRIMARY KEY(id), UNIQUE (lastname), CHECK age > 18">
<!-- The attribute 'name' specifies the name of the table-->
<!-- The attribute 'file' specifies the file path to the XML document. This path may be absolute (e.g.: 'c:/xmlfiles/employees.xml') or relative to the schema file path (e.g.: 'employees.xml' or 'subdir/employees.xml'). Also, you can use the following URLs:
a) HTTP URL: http://www.example.com/rss_feed.xml
b) FTP URL: ftp://user:passw@www.sample.com:21/test/test.xml
c) CLASSPATH URL: classpath://resources/test.xml
d) SERVER PAGE URL:  http://www.somesite.com/php/script.php?table=test.xml
e) ZIP URL:  zip://c:/archive.zip/test.xml
f) SMB/CIFS URL:  smb://your_server/your_share/your_folder/test.xml
g) SFTP URL: sftp://login:password@somesite.com:22/test/test.xml
-->
<!-- The attribute 'path' defines the base path which table entries are based on, i.e. table columns will be based on 'employee' elements.-->
<!-- the 'constraint' attribute specifies a table constraint like a primary key, unique value, etc -->
<column name="documentname" type="VARCHAR" path="/employees/document_name"/>
<!-- The attribute 'name' specifies the name of the column-->
<!-- The attribute 'type' specifies the data type of the column-->
<!-- The attribute 'path' defines XPath to the XML element the column is based on. In a given case the XPath expression '/employees/document_name' is an absolute path from the root of the XML document-->
 <column name="firstname" type="VARCHAR" size="15" path="first_name"/>
<!-- The value 'first_name' of the 'path' attribute defines the XPath expression which is relative to the base path '/employees/employee/' defined in the 'table' element. I.e. the full path to this column is '/employees/employee/first_name'-->
<!-- The 'size' attribute specifies the maximum number of characters for the VARCHAR type or total number of digits that can be stored for the BIGDECIMAL type -->
<!-- The 'decimalCount' attribute specifies the maximum number of digits that can be stored to the right of the decimal separator for the BIGDECIMAL type -->
<column name="lastname" type="VARCHAR" size="25" path="last_name"/>
<column name="title" type="VARCHAR" size="15 path="title"/>
<column name="id" type="INTEGER" path="@id"/>
<!-- This 'path' attribute defines the path to the attribute of the XML element-->
 <column name="age" type="INTEGER" path="age"/>
<column name="hiredate" type="DATETIME" path="hire_date"/>
</table>
<!-- you can set local properties for a specific table by using attributes of the same name. Local properties override global properties specified while creating a driver connection -->
<table name="advanced_test" file ="advanced_test.xml" path="/table/rec" decimalFormatInput="$###,###.##|###,###.##" dateFormat="yyyy-MM-dd HH:mm:ss | yyyy-MM-dd | HH:mm:ss" readAPI="XOM">
 <column name="int_col" type="integer" path="@int"/>
<!-- Also, the properties dateFormat, decimalFormatInput, decimalFormatOutput can be set for each column separately. Column properties override table and global properties. -->
 <column name="year_col" type="date" path="year_col" dateFormat="dd/MM/yyyy"/> 
<column name="time_col" type="time" path="time_col" dateFormat="HH:mm"/>
<column name="dec_col" type="numeric" path="dec_col" decimalFormatInput="$###,###.##|###,###.##$;" decimalFormatOutput="$###,###.##"/>
</table>
</schema>

Now, after the table has been specified, we can access it using an SQL query (see Connection Example for more details).

Additional notes on the schema file:

  • The table may be specified without the ‘name’ attribute:
    <table file ="c:/xmlfiles/employees.xml" path="/employees/employee">

    Then, as a table, you have to specify the full path to the XML file in the SQL query: SELECT * FROM “c:/xmlfiles/employees.xml

  • If some XML documents have the same format you can use the file pattern:
    
    <table file ="c:/xmlfiles/??employees*.xml" path="/employees/employee">

    Where the wildcard ‘*’ denotes any string of zero or more characters and the wildcard ‘?’ denotes any single character. In an SQL query you have to specify the full path to one of these files: SELECT * FROM “c:/xmlfiles/myemployees001.xml”

  • You can also set the local driver properties for each table directly in the ‘table’ element (see Driver properties for more details):
    <table name="employees" file ="employees.xml" path="/employees/employee" dateFormat="dd-MM-yyyy" namespaces="empl:http://www.example.com/employees">

 

Driver Properties

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

These properties are:

charset is used to set the character encoding for output. To specify the input encoding you need to add the encoding declaration to the XML file: <?xml version=”1.0″ encoding=”some_charset” ?> (Default charset is “UTF-8”).

dateFormat is used to specify a format for date/time values. A value of this property is a sequence of date/time formats separated by the ‘|’ character, e.g: “dd.MM.yy | dd.MM | dd”. (Default is “yyyy-MM-dd HH:mm:ss.SSS | yyyy-MM-dd HH:mm:ss | yyyy-MM-dd | HH:mm:ss.SSS | HH:mm:ss“). For more details about date/time format used in the driver please see chapter “time format syntax” in the java.util.SimpleDateFormat class documentation.

decimalFormatInput is used to specify input formats for numbers in a XML file. For instance, you can use this parameter to specify currency values, e.g.: “###,###.##$”. Also, multiple formats can be separated by “|” character, e.g.: “#.#|###,###.#|###,###.#$”. The property may be set for each column separately. For more information about patterns used in this property please see the documentation for java.text.DecimalFormat class.

decimalFormatOutput is used to specify the output format for numbers in a XML file. For example: “###,###.##$”. The property may be set for each column separately. For more information about patterns used in this property please see the documentation for java.text.DecimalFormatSymbols class.

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

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.

namespaceAware is used to enable namespace support when parsing XML documents. Default is “true”. It is not supported for XOM model.

namespaces is used to define XML namespaces used in the XML document (see Handling XML Namespaces for more details )

Advanced Properties:

emptyStringAsNull. If ’emptyStringAsNull’ is set to ‘true’, strings containing only whitespaces (i.e. strings like: “”, ” “, ” rn”, etc) are treated as NULL values. (By default is true).

nullStringInput is a string value that will be treated as the NULL value. (By default it is a regular expression “(?i)null“, i.e. case-insensitive “null”).

nullStringOutput is a string value that will be written into a CSV/Text file for NULL values. (By default is “NULL”).

parameter_xxx is used to pass parameter values to the schema. For more information see advanced topics.

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 table indexes and other preparations.

propertiesFile is used to specify driver properties in a separate external properties file.

readAPI is used to set which XPath engine should be used to process XML files. See XPath engines.

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:xml:c:/xmlfiles/schema.xml?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 whitespaces for string values (By default is 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("dateFormat", "MM.dd.yyyy");
props.put("namespaceAware", "false");
Connection conn = DriverManager.getConnection("jdbc:relationaljunction:xml:schema.xml", props);

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

Connection conn =DriverManager.getConnection("jdbc:relationaljunction:xml:schema.xml?dateFormat=MM.dd.yyyy&namespaceAware=false");

3) using an external properties file:


Connection conn =DriverManager.getConnection("jdbc:relationaljunction:xml:schema.xml?propertiesFile=config.properties");

4) using local properties specified in the schema for some particular file(s) and column(s).

 

Data Types

The driver supports the following data types:

Relational Junction data type JDBC returned type (java.sql.Types.*) Java class used in Relational Junction XML
AUTOINCREMENT, IDENTITY java.sql.Types.BIGINT java.lang.Long
Integer, INT java.sql.Types.INTEGER java.lang.Integer
BIGINT, LONG java.sql.Types.BIGINT java.lang.Long
FLOAT java.sql.Types.FLOAT java.lang.Float
DOUBLE java.sql.Types.DOUBLE java.lang.Double
BIGDECIMAL, DECIMAL, NUMERIC, MONEY, CURRENCY
java.sql.Types.NUMERIC java.math.BigDecimal
STRING, VARCHAR java.sql.Types.VARCHAR java.lang.String
DATETIME, TIMESTAMP java.sql.Types.TIMESTAMP java.util.Date
DATE, YEAR
java.sql.Types.DATE java.sql.Date
TIME java.sql.Types.TIME java.sql.Time
BOOLEAN 
java.sql.Types.BOOLEAN java.lang.Boolean
Notes:
  • DOUBLE and FLOAT values should not be used to represent currency values, because of rounding problems.
  • The BIGDECIMAL type is slower and requires more storage than the FLOAT and DOUBLE types.
  • Parsing and formatting takes longer for the DATETIME (TIMESTAMP) type than the numeric types.
  • Text types are slower to read, write, and compare than numeric types and generally require more storage.

 

Supported SQL Syntax

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

An SQL query must meet the following conditions:

  • It must correspond 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, e.g.: 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’

Examples of SELECT queries:

// ---- SELECT queries ---
SELECT * FROM employees WHERE title = 'Java programmer' ORDER BY last_name
SELECT name FROM salesreps WHERE (rep_office IN (22, 11, 12)) OR (manager IS NULL AND hire_date >= parsedatetime('01-05-2002','dd-MM-yyyy') OR (sales > quota AND NOT sales > 600000.0)
SELECT city, target, sales FROM "offices.xml" WHERE region = 'Eastern' AND sales > target ORDER BY city
// ---- SELECT queries with join ----
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 WHERE prod.prodid = ps.prodid
// ---- INSERT, UPDATE and DELETE commands ----
INSERT INTO employees (firstname, lastname, title, id, hiredate) VALUES('John', 'Doe', 'Web admin', 3, parsedatetime('07:02:2007','dd:MM:yyyy'))
DELETE FROM employees WHERE lastname LIKE 'Henry%' 
UPDATE "c:/xmlfiles/customers.xml" SET credit_limit = 50000.00 WHERE company = 'Acme Mfg.'
// ---- CREATE INDEX command ----
CREATE INDEX i_1 ON new_table (int_col)

Note: writing operations (INSERT, UPDATE, DELETE) are supported only for XML documents with simple hierarchy of elements.

See Also:

 

Connection Example

This complete example shows how the driver is used. You can download this example with the driver package here.
The driver connection code:


import java.sql.*;
public class DriverTest {
public static void main(String[] args) {
try {
// load the driver into memory
Class.forName("com.relationaljunction.jdbc.xml.XMLDriver2");
// create a connection. The first command line parameter is assumed to
// be the file path to the schema file
Connection conn = DriverManager.getConnection("jdbc:relationaljunction:xml:" + args[0]);
// create a Statement object to execute the query with
Statement stmt = conn.createStatement();
// execute a query
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
// read 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();
}
}
}

The specification for the table “employees” used in the SQL query above:


<?xml version="1.0" encoding="UTF-8"?>
<schema>
<table name="employees" file ="employees.xml" path="/employees/employee" dateFormat="dd-MM-yyyy">
<!-- In this example the file "employees.xml" is located in the same directory where the schema is-->
<!-- The attribute "dateFormat" sets a local date format used in the specified XML file-->
<column name="documentname" type="string" path="/employees/document_name"/>
<column name="firstname" type="string" path="first_name"/>
<column name="lastname" type="string" path="last_name"/>
<column name="title" type="string" path="title"/>
<column name="id" type="integer" path="@id"/>
<column name="age" type="integer" path="age"/>
<column name="hiredate" type="datetime" path="hire_date"/>
</table> 
</schema>

The XML file “employees.xml”:


<?xml version="1.0" encoding="UTF-8"?>
<employees>
<document_name>Employees doc</document_name>
<employee id="1">
<first_name>Bill</first_name>
<last_name>Adams</last_name>
<age>25</age>
<hire_date>12-06-1995</hire_date>
<title>Java programmer</title>
</employee>
<employee id="2">
<first_name>Mary</first_name>
<last_name>Jones</last_name>
<age>32</age>
<hire_date>22-09-2001</hire_date>
<title>Sales manager</title>
</employee>
<employee id="3">
<first_name>Dan</first_name>
<last_name>Roberts</last_name>
<age>30</age>
<hire_date>01-11-1999</hire_date>
<title>Project manager</title>
</employee>
<employee id="4">
<first_name>Paul</first_name>
<last_name>Crauz</last_name>
<age>24</age>
<hire_date>02-05-2002</hire_date>
<title>C++ programmer</title>
</employee>
</employees>

The result of the test:

DOCUMENT NAME FIRSTNAME LASTNAME TITLE ID AGE HIREDATE
Employees doc Bill Adams Java programmer 1 25 1995-06-12 00:00:00.0
Employees doc Mary Jones Sales manager 2 32
2001-09-22 00:00:00.0
Employees doc Dan Roberts Project manager 3 30 1999-11-01 00:00:00.0
Employees doc Paul Crauz C++ programmer 4 24 2002-05-02 00:00:00.0

 

Handling XML Namespaces

By default, XML parsing is namespace aware in the driver. You can change this with the namespaceAware property (it is supported only for the SAX mode). In order to process XML documents that use namespaces, you must specify each namespace using the driver property namespaces. The value format for this property is the following:
prefix1:namespace1|prefix2:namespace2| …
where namespace is a URI identifier of the namespace used in a XML document and prefix is a prefix identifier for use in XPath expressions in the driver schema file. The prefix may be arbitrary.

The table specification for the “employees.xml”:

<?xml version="1.0" encoding="UTF-8"?>
<schema>
<table name="employees" file="employees.xml" path="/empl:employees/empl:employee" dateFormat="dd-MM-yyyy" namespaces="empl:http://www.example.com/employees">
<!-- Notice that in a given case all XPath expressions must use the prefix 'empl:' -->
<column name="firstname" type="string" path="empl:first_name"/>
<column name="lastname" type="string" path="empl:last_name"/>
<column name="title" type="string" path="empl:title"/>
<column name="id" type="integer" path="@id"/>
<column name="age" type="integer" path="empl:age"/>
<column name="hiredate" type="datetime" path="empl:hire_date"/>
</table> 
</schema>

Note: the reserved “xml:” namespace (http://www.w3.org/XML/1998/namespace) should not be specified in the “namespaces” property, since it is already declared in a XML engine. In turn elements belonging to this namespace should be addressed with “xml:” prefix in the driver schema, e.g. : <column name = “id” type = “VARCHAR” path = “@xml:id” />

 

XPath engines

The driver supports two different XPath engines for reading data from XML.
The first is based on the SAX (Simple API for XML) model to read XML files (further referred to as “SAX”). This engine is used by default. The second is based on XOM (XML Object Model) to read XML files (further referred to as “XOM”). The main advantage of SAX is that it uses the minimum amount of operating memory to evaluate XPath expressions. The drawback is that it does not fully support the XPath syntax. For example, it does not support preceding axes. XOM supports the XPath syntax practically fully, but requires a specific amount of operating memory to process XML. The memory that is used depends on the size of the XML file and therefore Java Virtual Machine must have enough free memory allotted for processing large XML files (use -Xms and -Xmx JVM options).

To specify precisely which engine will be applied, use the parameter “readAPI” in the “table” element of the driver schema. It has accordinly two values: “SAX” and “XOM”.
Hint: use XOM for complex XPath expressions. For example, if you need to use preceding axes. For simple XPath expressions, use SAX.

Let’s look at the following XML structure:


<?xml version="1.0" encoding="UTF-8"?>
<catalogue>
<author id="1">
<name>Isaac Asimov</name>
<books>
<book>
<name>The Stars, Like Dust</name>
<genre>Science fiction</genre>
<price>44.95</price>
</book>
<book>
<name>Pebble in the Sky</name>
<genre>Science fiction</genre>
<price>24.95</price>
</book>
</books>
</author>
<author id="2">
<name>Stanislaw Lem</name>
<books>
<book>
<name>Solaris</name>
<genre>Science fiction</genre>
<price>39.95</price>
</book>
<book>
<name>Return from the Stars</name>
<genre>Science fiction</genre>
<price>34.95</price>
</book>
</books>
</author>
</catalogue>

As you can see, the “author” element and the “book” element are connected by relation 1:n. We can use the following table descriptions to extract data for authors and books:


<?xml version="1.0" encoding="UTF-8"?>
<schema>
<table name="books" file ="books.xml" path="/catalogue/author/books/book" readAPI="XOM">
<column name="author_id" path="../../@id" type="INT" />
<column name="name" path="name" type="VARCHAR" size="50" />
<column name="genre" path="genre" type="VARCHAR" size="30" />
<column name="price" path="price" type="BIGDECIMAL" />
</table> 
<table name="authors" file ="books.xml" path="/catalogue/author" readAPI="SAX">
<column name="id" path="@id" type="INT" />
<column name="name" path="name" type="VARCHAR" size="50" />
</table> 
</schema>

“SELECT * FROM books” gives the following result:

AUTHOR_ID NAME GENRE PRICE
1 The Stars, Like Dust Science fiction 44.95
1 Pebble in the Sky Science fiction 24.95
2 Solaris Science fiction 39.95
3 Return from the Stars Science fiction 34.95

“SELECT * FROM authors” gives:

AUTHOR_ID NAME
1 Isaac Asimov
2 Stanislaw Lem

The join of two tables “SELECT a.name as author_name, b.name as book_name, genre, price FROM authors a INNER JOIN books b WHERE a.id = b.author_id” gives:

AUTHOR_NAME BOOK_NAME GENRE PRICE
Isaac Asimov The Stars, Like Dust Science fiction 44.95
Isaac Asimov Pebble in the Sky Science fiction 24.95
Stanislaw Lem Solaris Science fiction 39.95
Stanislaw Lem Return from the Stars Science fiction 34.95

As you can see, we used the “id” attribute as a foreign key to connect the tables “author” and “books”. When doing this we had to use the preceding axe in the books table. To do this, the “readAPI” attribute was set to “XOM”. The authors table is very simple and it is sufficient to use the SAX.

Note: there is workaround for the SAX engine to get preceding elements. For preceding elements you use should use absolute XPaths starting with slash “/”. The previous example can be rewritten as following:

<?xml version="1.0" encoding="UTF-8"?>
<schema>
<!-- workaround for the SAX engine to use preceding elements. "readAPI" attribute is set to "SAX" value. -->
<table name="books" file ="books.xml" path="/catalogue/author/books/book" readAPI="SAX">
<!-- the preceding element is specified by the absolute XPath '/catalogue/author/@id'-->
<column name="author_id" path="/catalogue/author/@id" type="INT" />
<column name="name" path="name" type="VARCHAR" size="50" />
<column name="genre" path="genre" type="VARCHAR" size="30" />
<column name="price" path="price" type="BIGDECIMAL" />
</table> 
<table name="authors" file ="books.xml" path="/catalogue/author" readAPI="SAX">
<column name="id" path="@id" type="INT" />
<column name="name" path="name" type="VARCHAR" size="50" />
</table> 
</schema>

 

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 XML 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:xml:c:/xml/schema.xml?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:xml:c:/xml/schema.xml?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 <table name> 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. For example it can be a directory on a fast SSD disk.

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.

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:xml:c:/xml/schema.xml", props);

 

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 class and 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:<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:relationaljunction:xml:c:/xmlfiles/schema.xml", props);
// or append this property to the URL
Connection conn2 = DriverManager.getConnection("jdbc:relationaljunction:xml:c:/xmlfiles/schema.xml"
+ "?function:formate_date=my_pack.MyFuncs.format_date");

3) Call the function in an SQL query

For example:

Statement st = conn.createStatement();
st.execute("select format_date(date_column, 'yyyy-MM-dd') from test);

 

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 and/or use the constraint attribute in the “table” tag.
  • 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 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

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

2) Using external parameters in the schema

You can use named parameters {@parameter_name} in table descriptions in the schema. Then a parameter value should be passed via the driver property “parameter_xxx”, where “xxx” is a parameter name.

For example (with all parameters on one line):
jdbc:relationaljunction:xml:c:/xmlfiles/schema.xml
&parameter_filePath=c:/xmlfiles/products.xml
&parameter_productsPath=/products/product
&parameter_columnName1=id
&parameter_columnName2=name

Example of a table description with external parameters:

<table name="products" file ="{@filePath}" path ="{@productsPath}">
<column name="{@columnName1}" type="IDENTITY" path="id"/>
<column name="{@columnName2}" type="VARCHAR" path="name"/>
<column name="price" type="INTEGER" path="price"/>
</table>