UnityJDBC User Documentation

UnityJDBC requires a JRE of 1.4 or higher. UnityJDBC will run on a J2SE or J2EE platform. Users of JRE 1.4 must install the Java API for XML Processing (JAXP) by putting them in your CLASSPATH. A JRE of 1.5 or above is recommended.

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 over the Internet.
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 performance is better 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: databaseName.tableName.fieldName
   // Statement must end with a semi-colon ;
   // This example query performs two joins on the client-side (across databases).
   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.  Do not set higher than 80% of memory size.
   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); } // end try-catch-finally block
} //end main()
} // end UnityDemo

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

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.

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);
ExampleHSQL.printResult(rst);

// Example #2: DELETE using native parsing
String databaseName = "mydb";
sql = "DELETE FROM mydb.customer WHERE mydb.customer.id = 51 or mydb.customer.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 (mydb.Customer.id, mydb.Customer.firstname, "
          + " mydb.Customer.lastname, mydb.Customer.street, mydb.Customer.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);
ExampleHSQL.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 XSpec 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.

A major new feature of UnityJDBC 3.0 is the ability to take UnityJDBC queries and feed them into an INSERT INTO statement and 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 XSpec file describing the source.

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 source list file rather than from a single database. That is, all your "integrated" databases really do appear as a single database to your application.

A truly distinctive feature of UnityJDBC is the ability to compare information across databases not just to perform cross-database joins. UnityJDBC uses a MERGE syntax that has no equivalent in SQL. The basic idea is that often the reason to integrate databases is to compare the differences in the data they store either to detect inconsistencies, for use with data synchronization, or to identify areas of overlap between data sources. A MERGE query takes two or more subqueries (which may be on a single source or multiple sources) and combines them using a full outer join and attribute match functions. The full outer join will relate data tuples based on attribute values, and the match functions allow the programmer to compare data between sources on a per field basis and decide which data to keep. A sample of the code in ExampleMerge.java is below.

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

// Show the data in the two tables that we will be integrating
System.out.println("Data in Person table in database 1:");
ResultSet rst = stmt.executeQuery("SELECT * FROM mydb.person;");
printResult(rst);
System.out.println("\nData in Users table in database 2:");
rst = stmt.executeQuery("SELECT * FROM emptydb.users;");
printResult(rst);

// Query #2: All users in database 1 but not database 2
System.out.println("\nAll people in database 1 but not in database 2:");
queryString = "SELECT * FROM mydb.person "
           + " MERGE ON Q1.C1=Q2.C1 EXTRACT ALL FILTER Q2.C1 IS NULL "
           + " SELECT * FROM emptydb.users;";
rst = stmt.executeQuery(queryString);
printResult(rst);

// Query #4: Find the users whose salary is different between the two databases
System.out.println("\nPeople where the salary field is different in the two databases:");
queryString = "SELECT * FROM mydb.person "
            + " MERGE ON userid=id EXTRACT userid, id, firstname, "
            + "             Q1.C4 AS salary_db1, Q2.C4 AS salary_db2 "
            + " FILTER userid IS NOT NULL and id IS NOT NULL and salary_db1!=salary_db2 "
            + " SELECT * FROM emptydb.users;";
rst = stmt.executeQuery(queryString);
printResult(rst);

// Query #5: Produce an "integrated" result of user id, full name, age (max), salary (both)
System.out.println("\nIntegrated table with user id, full name, age (max), salary (both):");
queryString = "SELECT * FROM mydb.person "
            + " MERGE ON userid=id EXTRACT CHOOSENN(userid,id,1) as userid, "
            + "        CHOOSENN(firstname+' '+lastname,fname+' '+lname,1) as fullname, "
            + " MAX(DATEDIFF('y',date(),birthdate),age) as age, GROUPREF(Q1.C4,Q2.C4) as salary"
            + " SELECT * FROM emptydb.users;";
rst = stmt.executeQuery(queryString);
printResult(rst);

// Query #6: Take result produced with previous query and insert into an existing table
System.out.println("\nInsert into a table called AllUsers results from both sources:");
queryString = "INSERT INTO AllUsers (SELECT * FROM mydb.person "
           + " MERGE ON userid=id EXTRACT CHOOSENN(userid,id,1) as userid,"
           + " CHOOSENN(firstname,fname,1) as firstname, CHOOSENN(lastname,lname,1) as lastname,"
           + " MAX(mydb.person.salary,emptydb.users.salary), birthdate, yearHired, deptName"
           + " SELECT * FROM emptydb.users);";
stmt.executeUpdate(queryString);

The MERGE clause matches two subqueries using a join condition. A field in either subquery is referred to by its query number (Q1 or Q2) and its column index starting from 1 (C1, C2, ...). GROUPREF() is an example of a MATCH function. This particular function will group the two values into an ArrayList and keep a reference on what source the values were from. Thus, the MERGE feature allows you to integrate data and track the source (provenance) of the integrating data. The FILTER clause behaves essentially like a HAVING clause in SQL and allows the filtering of rows produced after the integration step. More details on the syntax and use of MERGE queries is in Chapter 6.

These example MERGE queries involve two different people databases. Query #2 shows how you can to determine when records are in one database but not the other. Query #4 shows how records that have different field values in two different databases can be identified. Query #5 shows how to integrate the data into a single query result using MATCH functions. The function CHOOSENN(field1,field2,defaultSource) will select the field value from the defaultSource unless it is NULL. Functions like MAX() and MIN() are also supported. The GROUPREF() function will create an ArrayList of the two field values while also tracking the source of the value. This can be used to display differences to the user as well as tracking the source of the data. Query #6 uses INSERT INTO to take an integrated query result and insert the records into another table. Some of the output generated by this sample code is below.

Data in Person table in database 1:
USERID  FIRSTNAME  LASTNAME  SALARY     BIRTHDATE  ISACTIVE   YEARHIRED
id1     Joe        Smith     55000      1970-01-13 true       2000
id2     Fred       Anders    45000      1963-03-08 true       2001
id3     Steve      Batner    75000      1955-10-23 false      1998
id5     Perry      Crusus    65000      1988-11-11 true       2003
id7     Jason      Deed      35000      1942-05-09 true       2006

Data in Users table in database 2:
ID       FNAME     LNAME     SALARY     AGE        DEPTNAME
id1      Joe       Smithers  53000.0    38         Accounting
id2      Fred      Anders    45000.0    45         Sales
id3      Steve     Batner    85000.0    53         Sales
id4      New       Guy       21000.0    21         Sales
id5      Pery      Crustes   55000.0    20         Owner

All people in database 1 but not in database 2:
USERID FIRSTNAME LASTNAME SALARY BIRTHDATE  ISACTIVE YEARHIRED ID    FNAME  LNAME  SALARY AGE
id7    Jason     Deed     35000  1942-05-09 true     2006      (null)(null) (null) (null) (null)

People where the salary field is different in the two databases:
USERID     ID       FIRSTNAME                 salary_db1 salary_db2
id1        id1      Joe                       55000      53000.0
id3        id3      Steve                     75000      85000.0
id5        id5      Perry                     65000      55000.0

Integrated table with user id, full name, age (keep max), salary (show both with source):
userid     fullname             age   salary
id1        Joe Smith            38    [55000 (mydb), 53000.0 (emptydb)]
id2        Fred Anders          45    [45000 (mydb), 45000.0 (emptydb)]
id3        Steve Batner         53    [75000 (mydb), 85000.0 (emptydb)]
id5        Perry Crusus         20    [65000 (mydb), 55000.0 (emptydb)]
id4        New Guy              21    [<null> (mydb), 21000.0 (emptydb)]
id7        Jason Deed           66    [35000 (mydb), <null> (emptydb)]

Insert into a table called AllUsers integrated results from both sources:
USERID   FIRSTNAME  LASTNAME  SALARY     BIRTHDATE  YEARHIRED  DEPTNAME
id1      Joe        Smith     55000      1970-01-13 2000       Accounting
id2      Fred       Anders    45000.0    1963-03-08 2001       Sales
id3      Steve      Batner    85000.0    1955-10-23 1998       Sales
id5      Perry      Crusus    65000      1988-11-11 2003       Owner
id4      New        Guy       21000.0    (null)     (null)     Sales
id7      Jason      Deed      35000      1942-05-09 2006       (null)               

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 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 one particular algorithm, Early Hash Join, is a new research algorithm currently in the US patent process. It is also possible using these features 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. The bottom line is that you are always in control as the programmer!

The UnityJDBC driver can be used as a data source in any popular graphical query tool. ExampleGUI.java is a simple graphical query tool included in the distribution that contains some sample queries and a basic Java graphical interface. You can customize this code by replacing the source list file and the sample queries. A screenshot of the program is below.

The logical operators of AND, OR, NOT, and XOR are available.

The following comparison operators are available:

The following mathematical operators are supported:

The following mathematical functions are supported:

The following string functions are supported:

Pattern matching is supported using the LIKE operator.

For example, 'abcdef' LIKE 'ab%' is true. The '%' is used to match one or more characters, and '_' is used to match a single character.

Data type conversions are performed using the CAST(x,y) function. The CAST function takes any object as the first parameter and takes a string literal representation of the type to cast to as the second parameter. Note that the type must be put in single quotes as a string literal. Example:

CAST(45, 'VARCHAR') creates '45'

Possible type names are: 'VARCHAR', 'CHAR', 'INT', 'FLOAT', 'DOUBLE', 'DATE', 'TIMESTAMP', 'TIME'.

The following date functions are supported:

The following aggregate functions are supported:

For queries on a single database, UnityJDBC parses functions and passes them directly to the database engine for execution. Thus, all functions that can be executed at the source are available. UnityJDBC and user-defined functions are used only when applying functions to data after it is extracted from the sources. UnityJDBC will parse queries containing functions that it itself cannot process in its internal database engine. These functions are passed down to the database engine and executed locally. Only functions that require inputs from more than one database are processed in the UnityJDBC database engine. All other functions are passed down to the sources.

UnityJDBC supports user-defined functions (UDFs). Adding your own user-defined function is easy. There are three different types of functions: tuple functions, aggregate functions, and MATCH functions. A tuple function operates on one tuple at a time for its data and includes functions like SUBSTRING() and ABS(). An aggregate function is used in GROUP BY queries and aggregates an expression (usually a column) across multiple rows in a group to produce a single value. Examples include MAX() and COUNT(). MATCH functions are unique to UnityJDBC. They allow expressions/columns to be related across data sources. MATCH functions include MAX() and MIN() but also GROUP(), CHOOSE(), and others. More information on these functions can be found in the section on MATCH functions.

To create a tuple function, you must create a Java class that extends the Function class. A template example is in the file F_Function_Template.java. This class must implement a constructor, an evaluate() method, and provide information on the parameters it requires. Once completed, as long as this function is available in the CLASSPATH, UnityJDBC will search for it when called. Similar templates are available for aggregate functions, A_Aggregrate_Template.java, and MATCH functions, M_Merge_Template.java. Sample code is provided in the directory unity/functions.

The available MATCH functions are below. For illustration, consider that the match function will be applied to a single tuple with attributes (C,A,B) with values (c,1,2). Attribute A comes from data source 1 and attribute B comes from data source 2. Attribute C is a common attribute used to relate (join) the records across sources. In the result column is shown the tuple produced after the match function is applied (and assuming the common attribute C is also requested in the MATCH list).

There are two distinct types of MATCH functions. The regular type is similar to aggregate functions and can compute maximum, minimum, sum, and averages. The second type records the source of the value selected and have 'ref" in their name. The result of these functions is a Java object called AttributeValueSource. This object contains two publicly accessible values: an Object value representing the value returned from the database and a GQDatabaseRef source object representing information on the database source where the attribute was obtained. In Java code, this attribute is retrieved by calling getObject(columnId) and then casting to an AttributeValueSource object. Knowing the source is valuable in many comparison situations when relating data across sources. Note that the regular functions do not track the source of their values, so it is recommended that you always use the ref functions when source is important. However, mixing the function types is allowed with the caveat that you may get source 'UNKNOWN' returned by functions that do not track their source.

The group functions return an ArrayList of values that can be retrieved and manipulated using the getObject(columnId) call on the ResultSet.

The following is some examples on how to use the MERGE operator. We will consider merging two employee databases. Each employee database has an Employee table with an id and a name. Not all employees are in both databases, and we want to create a master employee database. Some fields are different between the two databases. Our two databases are as follows:

Here is our query:

SELECT E.empId, E.empName, E.job  FROM EmpDB1.Employee AS E
MERGE ON Q1.C1=Q2.C2 EXTRACT chooseNN(Q1.C1,Q2.C1,1), chooseNN(Q1.C2,Q2.C2,2), Q1.C3, Q2.C3
SELECT Emp.id, Emp.name, Emp.age FROM EmpDB2.employee AS Emp

The ON Q1.C1=Q2.C2 will perform a full outer join of the two inputs. The result before applying MATCH functions will be:

In our EXTRACT clause, we have indicated we want to keep the empId attribute (Q1.C1) unless it is null then use the id attribute (Q2.C1), the job attribute (Q1.C3), the age attribute (Q2.C3), and combine the two name attributes using chooseNN with default source to 1 (always take the name in the second source (name) unless it is null). The result after applying EXTRACT is:

Note that the name of employee id 1 is 'Joseph' instead of 'Joe' because we indicated to the system we wanted to use the value in attribute name instead of empName whenever possible.

Let's modify the query so that we keep both names and record what source they come from. In that case, we will use groupref instead of chooseNN:

SELECT E.empId, E.empName, E.job  FROM EmpDB1.Employee AS E
MERGE ON Q1.C1=Q2.C2 EXTRACT chooseNN(Q1.C1,Q2.C1,1), groupref(Q1.C2,Q2.C2), Q1.C3, Q2.C3
SELECT Emp.id, Emp.name, Emp.age FROM EmpDB2.employee AS Emp

The result is:

Now as a programmer you have access to an ArrayList in the name attribute containing all the values from the sources and information on each source. You can then use that information to selectively display your required view.