Chapter 4. Multiple Database Programming with UnityJDBC

Table of Contents

Using the Sample Programs
Using ExampleQuery.java
Using ExampleUpdate.java
Using ExampleMetadata.java
Using ExampleEngine.java
Using ExampleNoFileConnection.java

Using the Sample Programs

UnityJDBC is a universal query translator. It allows you to develop your programs without worrying about the underlying database. All SQL statements executed with UnityJDBC are translated for the database used. Even if you do not need multiple database queries, data virtualization, or queries that span multiple different databases, UnityJDBC simplifies your development by handling all the issues with SQL dialects. If a function is not supported by your database, UnityJDBC will execute it internally. If you forget a function name for a database, it will translate to the correct function call for the particular source. This allows you the freedom to write your SQL code in a database independent way. You no longer have to make major changes to your code if you change database systems. If you want full control, you can use the UnityJDBC database engine directly in your code to join ResultSets with each other regardless of their source, and perform dynamic filtering, ordering, and analysis.

Sample programs are provided in the directory code. Here is a list of the programs and the features they demonstrate:

  1. ExampleQuery.java - a query example that joins data across two databases

  2. ExampleUpdate.java - demonstrates INSERT/UPDATE/DELETE and how to store a cross-database query result into a table

  3. ExampleMetaData.java - query example showing how to extract metadata information

  4. ExampleEngine.java - an advanced example that shows how users can use the UnityJDBC database engine directly

  5. ExampleNoFileConnection.java - example showing how to configure UnityJDBC in code without using XML files

All of these examples use a local HSQL database that can be started using the script startDB.bat or startDB.sh in the directory sampleDB/hsqldb in the installation folder.

To compile and run any of these sample programs make sure you are in the code directory and execute the following commands:

javac test/ExampleQuery.java

java test.ExampleQuery

If you have CLASSPATH issues, you can explicitly indicate the location of the HSQL JDBC driver and the UnityJDBC driver by:

javac -cp.;../UnityJDBC.jar;../sampleDB/hsqldb/hsqldb.jar test/ExampleQuery.java

java -cp.;../UnityJDBC.jar;../sampleDB/hsqldb/hsqldb.jar test.ExampleQuery

Using ExampleQuery.java

The ExampleQuery.java demonstrates the basic features of the UnityJDBC driver. The code is below.

import java.sql.*;

public class ExampleQuery
{
// URL for sources.xml file specifying what databases to integrate.  
// This file must be locally accessible or available via http URL.
static String url="jdbc:unity://test/xspec/UnityDemo.xml";

public static void main(String [] args) throws Exception
{
Connection con = null;
Statement stmt = null;
ResultSet rst;

try {
   // Create new instance of UnityDriver and make connection
   System.out.println("\nRegistering driver.");
   Class.forName("unity.jdbc.UnityDriver");

   System.out.println("\nGetting connection:  "+url);
   con = DriverManager.getConnection(url);
   System.out.println("\nConnection successful for "+ url);

   System.out.println("\nCreating statement.");
   stmt = con.createStatement();
   // Unity supports scrollable ResultSets, 
   //  but better performance with FORWARD_ONLY
   // stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
   //                            ResultSet.CONCUR_READ_ONLY);

   // A query is exactly like SQL.
   // Attributes should be FULLY qualified: database.table.field 
   // Statement must end with a semi-colon ;
   // This query performs cross-database join on the client-side
   String sql = 
     "SELECT PartDB.Part.P_NAME, OrderDB.LineItem.L_QUANTITY," 
   + " OrderDB.Customer.C_Name, PartDB.Supplier.s_name" 
   + " FROM OrderDB.CUSTOMER, OrderDB.LINEITEM, OrderDB.ORDERS,"
   + "      PartDB.PART, PartDB.Supplier" 
   + " WHERE OrderDB.LINEITEM.L_PARTKEY = PartDB.PART.P_PARTKEY AND"
   + "       OrderDB.LINEITEM.L_ORDERKEY = OrderDB.ORDERS.O_ORDERKEY" 
   + "       AND OrderDB.ORDERS.O_CUSTKEY = OrderDB.CUSTOMER.C_CUSTKEY"
   + "       AND PartDB.supplier.s_suppkey = OrderDB.lineitem.l_suppkey"
   + " AND OrderDB.Customer.C_Name = 'Customer#000000025';";

   // Note: Client's local JVM is used to process some operations.  
   // For large queries, this may require setting a large heap space.
   // JVM command line parameters: 0 -Xms500m -Xmx500m
   // These parameters set heap space to 500 MB.
   rst = stmt.executeQuery(sql);

   System.out.println("\n\nTHE RESULTS:");
   int i=0;
   long timeStart = System.currentTimeMillis();
   long timeEnd;
   ResultSetMetaData meta = rst.getMetaData();

   System.out.println("Total columns: " + meta.getColumnCount());
   System.out.print(meta.getColumnName(1));
   for (int j = 2; j <= meta.getColumnCount(); j++)
      System.out.print(", " + meta.getColumnName(j));
   System.out.println();

   while (rst.next()) {
      System.out.print(rst.getObject(1));
      for (int j = 2; j <= meta.getColumnCount(); j++)
         System.out.print(", " + rst.getObject(j));
      System.out.println();
      i++;
   }

   timeEnd = System.currentTimeMillis();
   System.out.println("Query took: "+
                      ((timeEnd-timeStart)/1000)+" seconds");
   System.out.println("Number of results printed: "+i);
   stmt.close();
   System.out.println("\nOPERATION COMPLETED SUCCESSFULLY!");
}
catch (SQLException ex)
{   System.out.println("SQLException: " + ex);
}
finally
{
   if (con != null)
   try{   con.close();   }
   catch (SQLException ex)
   {   System.out.println("SQLException: " + ex); }
}
}

The UnityJDBC driver behaves exactly like other JDBC drivers. The basic steps for querying a database with a JDBC driver are:

  1. Load the driver (optional) - This is done by Class.forName("unity.jdbc.UnityDriver");

  2. Make a connection - A connection is made to a database by providing the database URL and other properties including user id and password. This example is using the DriverManager to make the connection (con = DriverManager.getConnection(url);). Note that the URL is of the form jdbc:unity://<path_to_sources_file>. In this case, the URL is jdbc:unity://test/xspec/UnityDemo.xml.This path may be an absolute or relative path on the machine. It is also possible to retrieve encrypted XML files from a network source. The sources file provides the connection information for the individual data sources for use by UnityJDBC.

  3. Execute a statement - UnityJDBC follows the JDBC API for creating statements and executing queries and updates. There are some methods unique to UnityJDBC which are covered in a later section. Standard SQL syntax is supported. The major difference is that tables in different databases can be referenced in the same query. This is accomplished using the syntax database.table to refer to tables and database.table.field to refer to fields. (Note that aliasing using AS is supported.) If full names are not provided, UnityJDBC will attempt to match as appropriate, but it will generate errors if the provided names are not unique.

This file is a good one to modify to start your own program. Simply change the class and file name, the URL to the location of your source list file, and the query executed, and you are done.

Using ExampleUpdate.java

UnityJDBC natively supports INSERT, UPDATE, and DELETE statements on a single database. These statements can be executed in by-pass mode in which case UnityJDBC does not parse or validate the statement and passes it straight to the JDBC driver for the corresponding database. In native mode, UnityJDBC will parse and validate the statement before passing it to the data source. Note that the basic INSERT, UPDATE, and DELETE statements operate only on a single table in SQL, so no cross-database query processing is necessary. A sample of the code in ExampleUpdate.java is below.

Class.forName("unity.jdbc.UnityDriver");
con = DriverManager.getConnection(url);
stmt = con.createStatement();

// Example #1: Basic query
String sql = "SELECT * FROM mydb.Customer;";
rst = stmt.executeQuery(sql);
printResult(rst);

// Example #2: DELETE using native parsing
String databaseName = "mydb";
sql = "DELETE FROM mydb.customer WHERE id = 51 or id=52;";
stmt.executeUpdate(sql);

// Example #3: INSERT (by-pass method)
sql = "INSERT INTO Customer (id,firstname,lastname,street,city) " 
     + " VALUES (51,'Joe','Smith','Drury Lane', 'Detroit')";
((UnityStatement) stmt).executeByPassQuery(databaseName,sql);

// Example #4: INSERT - Unity Parsed
sql = "INSERT INTO mydb.Customer (id, firstname, "
          + " lastname, street, city) "
          + " VALUES (52,'Fred','Jones','Smith Lane', 'Chicago');";
stmt.executeUpdate(sql);

// Example #5: INSERT INTO (SELECT...) across databases
sql = "INSERT INTO emptydb.customer (SELECT * FROM mydb.customer);";
stmt.executeUpdate(sql);

// Prove that we transferred the data
sql = "SELECT * FROM emptydb.Customer;";
rst = stmt.executeQuery(sql);
printResult(rst);

Note that you can use the by-pass feature to execute any statement on a source database that UnityJDBC does not natively support. Experimental results show that the by-pass features adds insignificant overhead compared to calling the source JDBC driver directly. Thus, client code only needs to load and use the UnityJDBC driver directly. This results in more portable code that can be more easily moved between database systems.

When UnityJDBC parses the SQL, you can use table and field references that are prefixed with the database name. This is optional if the table and field names are unique across all databases, otherwise the database name is required. The database name is assigned in the schema file describing the source and does not have to be the same as the system name used by the database system itself. That is, the name can be set by the developer using UnityJDBC.

Multiple source UnityJDBC queries can be used with an INSERT INTO statement to populate a table in the database. This allows a user to write a cross-database query to collect information from multiple sources and then insert the result back into a table in any one of the sources. Currently, the only restriction is that the table that will be inserted into must exist and must be present in the schema file describing the source.

Using ExampleMetadata.java

ExampleMetadata.java demonstrates UnityJDBC's support for the DatabaseMetaData interface. This interface functions exactly according to the standard with the major difference that metadata is returned for all databases in the data virtualization rather than from a single database. That is, all your "integrated" databases really do appear as a single database to your application.

Using ExampleEngine.java

Embedded in the UnityJDBC driver is a complete relational engine. This is required to process cross-database join queries. Most users will not interact with the engine directly, and their only contact with the engine may be to increase the JVM heap sizes for processing large cross-database queries. However, all of the relational operators of selection, projection, and join are available for direct use in your programs. The join algorithms support sources larger than main memory, and allow you the full power of combining ResultSets from multiple databases. It is also possible to explicitly track global query progress on a per operator basis or perform your own optimization of queries after the UnityJDBC optimizer has built an execution tree.

Using ExampleNoFileConnection.java

ExampleNoFileConnection.java demonstrates UnityJDBC's ability to be dynamically configured at run-time including adding, removing, or updating sources. It is possible to dynamically build a virtual database without using XML configuration files by interacting with UnityJDBC through its metadata API.