UnityJDBC is a Type 4 JDBC driver capable of querying multiple databases in a single SQL query. The driver can be used similar to other JDBC drivers including with query, reporting, and business intelligence tools, application and web servers, or stand-alone Java programs. Internally, UnityJDBC contains a database engine and optimizer allowing it to efficiently join data from source databases to produce a single ResultSet. UnityJDBC supports updating data using results produced from cross-database queries and performs automatic dialect translation to convert queries into the proper dialect. A brief list of the major supported features is below:

The free evaluation version of UnityJDBC is a fully functioning system. The only constraint with the evaluation version is that it will only produce the first 100 results in a ResultSet. The full version has no restrictions. The evaluation version can be downloaded at https://www.unityjdbc.com/download.php. You are free to distribute the evaluation version of the software.

UnityJDBC requires a JRE of 1.6 or higher. UnityJDBC will run on a J2SE or J2EE platform.

UnityJDBC can be downloaded, installed, tested, and configured for your environment in less than 10 minutes. Here are the easy steps:

Once you have downloaded UnityJDBC from https://www.unityjdbc.com/download.php, you will have downloaded a JAR file called UnityJDBC_Trial_Install.jar. First, you need to have Java previously installed in order to install UnityJDBC. Then, you can install UnityJDBC by either double-clicking the JAR file or running the following command: java -jar UnityJDBC_Trial_Install.jar. The installation steps are below.

UnityJDBC will auto-start at the completion of the installation. If it does not, click on the shortcut created. If there is no shortcut, on Windows run initsources.bat and on Linux/Mac run initsources.sh in the installation directory.

UnityJDBC requires information about the data sources being queried in order to validate, optimize, and execute queries against those data sources. All source information is stored in XML files. There are two types of source information files: the sources file and schema files. A sources file with default name sources.xml provides information on all the sources that could be potentially queried. The location of this file is provided via the URL when initializing the driver. Inside the file is information on each source including its connection URL and parameters, JDBC driver, and schema file location. The sample sources file code\test\xspec\UnityDemo.xml is provided in the distribution package. Each data source requires a schema file. The schema file is an XML encoding of the schema information including table and field names, keys, joins, and relation sizes. It is used for validating queries and optimization. Two schema files provided in the distribution are: code\test\xspec\UnityDemoOrder.xml and code\test\xspec\UnityDemoPart.xml.

There are two ways to create your own sources file and associated schema files:

SourceBuilder is a graphical query tool that allows you to define a virtual database consisting of multiple databases including MySQL, PostgreSQL, Oracle, Microsoft SQL Server, Sybase, MongoDB, and others. SourceBuilder can be started using the shortcut produced during the installation, by running initsources.bat or initsources.sh in the installation folder, or directly from the driver using the command java -jar unityjdbc.jar. It is required that the unityjdbc.jar and all JDBC driver JAR files for databases used be in the Java classpath.

When SourceBuilder is first started, a welcome screen is displayed.

SourceBuilder Welcome Screen

To use the sample database and queries included, make sure to start the HSQLDB using the Start Database shortcut or run the script startDB.bat or startDB.sh in the directory sampleDB/hsqldb. Then, select Demonstration->Cross-database Join Test. Click the EXECUTE button to run the query and get results. If an error occurs, verify that the sample database is started and the hsqldb.jar file is in your classpath. You can try several other of the sample queries or write your own.

SourceBuilder Multiple Database Query Example with a Cross-Database Join

To build your own data virtualization of multiple data sources, create a new sources file by selecting File->New Source Group and providing a file name (the default is sources.xml).

Creating a New Source Group with SourceBuilder

Prompt for Name of Sources File

Then, you will add each one of your sources by providing its JDBC connection information. To add a source you need the following information:

An example of adding a MySQL source to the virtual database is below.

Adding a MySQL Data Source to the Data Virtualization

There are also several additional features that can be used to control the extraction.

Below is an example of extracting only the tables that end in 'ER' from an Oracle database. Note how the schema is also supplied as RLAWRENC.

Adding an Oracle Data Source to the Data Virtualization (note use of schema name)

Multiple Database Virtualization and Querying for HSQLDB

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more HSQLDB databases with any other database system. To add a HSQLDB data source, the following information is used:

1. JDBC Driver class name: org.hsqldb.jdbcDriver.

2. JDBC URL: jdbc:hsqldb:hsql://<host address>/<database name>.

An example of adding a HSQLDB source to a data virtualzation is below.

Adding a HSQLDB Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for IBM DB2

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more IBM DB2 databases with any other database system. To add an IBM DB2 data source, the following information is used:

1. JDBC Driver class name: com.ibm.db2.jcc.DB2Driver.

2. JDBC URL: jdbc:db2://<host name>/<database name>.

An example of adding an IBM DB2 source to a data virtualzation is below.

Adding an IBM DB2 Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for Microsoft SQL Server

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more Microsoft SQL Server databases with any other database system. To add a Microsoft SQL Server data source, the following information is used:

1. JDBC Driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver.

2. JDBC URL: jdbc:sqlserver://<host>;DatabaseName=<database name>.

An example of adding a Microsoft SQL Server data source to a data virtualzation is below.

Adding a Microsoft SQL Server Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for MySQL

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more MySQL databases with any other database system. To add a MySQL data source, the following information is used:

1. JDBC Driver class name: com.mysql.jdbc.Driver.

2. JDBC URL: jdbc:mysql://<host address>/<database name>.

An example of adding a MySQL source to a data virtualzation is below.

Adding a MySQL Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for Oracle

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more Oracle databases with any other database system. To add an Oracle data source, the following information is used:

1. JDBC Driver class name: oracle.jdbc.driver.OracleDriver.

2. JDBC URL: jdbc:oracle:thin:<user>/<password>@<server>:1521/<service>.

An example of adding an Oracle data source to a data virtualzation is below.

Adding an Oracle Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for PostgreSQL

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more PostgreSQL databases with any other database system. To add a PostgreSQL data source, the following information is used:

1. JDBC Driver class name: org.postgresql.Driver.

2. JDBC URL: jdbc:postgresql://<server>/<database>?user=<userId>&password=<password>.

An example of adding a PostgreSQL source to a data virtualzation is below.

Adding a PostgreSQL Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for Sybase

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more Sybase databases with any other database system. To add a Sybase data source, the following information is used:

1. JDBC Driver class name: com.sybase.jdbc4.jdbc.SybDriver.

2. JDBC URL: jdbc:sybase:Tds:<server>:<port>/<database>?user=<userid>&password=<password>.

An example of adding a Sybase source to a data virtualzation is below.

Adding a Sybase Data Source to the Data Virtualization

Multiple Database Virtualization and Querying for MongoDB

Using UnityJDBC SourceBuilder, it is possible to build a data virtualization of one or more MongoDB databases (collections) with any other database system. Note that a MongoDB JDBC driver is built into UnityJDBC and can be used separately from UnityJDBC directly. For more information go to https://www.unityjdbc.com/mongojdbc To add a MongoDB data source, the following information is used:

1. JDBC Driver class name: mongodb.jdbc.MongoDriver.

2. JDBC URL: jdbc:mongo://<server>:1521/<database>.

An example of adding a MongoDB data source to a data virtualzation is below.

Adding a MongoDB Data Source to the Data Virtualization

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:

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

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); }
}
}

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);

UnityJDBC is integrated into SQuirreL SQL allowing users to build SQL queries that join data from multiple sources directly within SQuirreL. The multiple source query plugin allows SQuirreL users to create a virtual data source that may consist of multiple data sources on different servers and platforms. The user can enter one SQL query to combine and join information from multiple sources.

Benefits:

UnityJDBC can be installed directly as a plug-in through the SQuirreL SQL plug-in interface. It is also possible to download the UnityJDBC distribution and replace the unityjdbc.jar in the plug-in with the latest from UnityJDBC.

The plugin source code, like all of SQuirreL, is released under the GNU Lesser General Public License. The UnityJDBC virtualization driver is released under a commercial license. However, the UnityJDBC driver included in the plugin is fully functioning with no time limits allowing an unlimited number of sources and queries. The only limitation is the size of the result set is limited to the first 100 rows. (Note there is no limit on the number of rows extracted from each source. So select count(*) from table with a 1 million row table is fine as it only returns one result row.) Use LIMIT 100 to get the first 100 results of a query. A full version of the UnityJDBC driver can be purchased as www.unityjdbc.com.

The JasperReports library and community version of JasperReports server does not support data virtualization allowing one SQL query to extract data from multiple databases. UnityJDBC can be used with JasperReports to enable this data virtualization which simplifies the construction of reports, especially reports that would usually use subreports.

Aqua Data Studio can query multiple databases with different SQL queries. However, you cannot query more than one database at the same time in one SQL query. UnityJDBC allows a user to write one query to join, aggregate, and summarize data across any number of databases. It also performs translation of SQL dialects and functions.

Benefits:

This example assumes that the installation of UnityJDBC has already been completed. The following is an example of creating sources and executing multiple database queries with Aqua Data Studio.

UnityJDBC allows RazorSQL users to create a virtual data source that may consist of multiple data sources on different servers and platforms. The user can enter one SQL query to combine and join information from multiple sources.

Benefits:

This example assumes that the installation of UnityJDBC has already been completed, and that the user has already created a data virtualization using the UnityJDBC SourceBuilder. The following is an example of creating sources and executing multiple database queries with RazorSQL.

A few possible setup errors and their resolution:

UnityJDBC supports a cross-database SELECT statement. The SELECT statement has the standard SQL-92 syntax and supports WHERE, ORDER BY, GROUP BY, and HAVING. UnityJDBC supports subqueries on a single database and cross-database including subqueries in the FROM clause and WHERE clause. entire query is on a single database. SQL functions are supported using a function syntax with parameters rather than using SQL keywords and syntax. Table and fields often should be prefixed with the database name they originate from. This database name is provided in the schema file for the data source.

The standard SQL data types are supported. Since UnityJDBC uses the JDBC drivers provided by database vendors, non-standard data types may not be universally supported.

An identifier is a string used to reference a database, table, or field. Identifiers follow the standard SQL rules. Since a UnityJDBC query may span multiple databases, table and field identifiers defined in a data source may not be unique across all data sources. In which case, the database name should be added to the identifier to create a unique system-wide identifier. For instance, consider an order database given the name OrderDB with a table called Orders and fields id and orderDate. The Orders table may be referred to using only Orders or OrderDB.Orders. Similarly, the field id may be referred to as Orders.id or OrderDB.Orders.id. Standard aliasing using AS in the FROM and SELECT clauses is supported. Delimited identifiers are supported by enclosing in double quotes (e.g. "from" or "my field with spaces"). Delimited identifiers must be used for SQL reserved words.

Arithmetic operators +, -, /, %, * are supported as well as generic expressions. Functions are not specified according to SQL keyword syntax but rather as a function identifier with parameters similar to programming languages. The format of functions is: function (param1, param2, ...).

Query: 
SELECT N1.n_nationkey, NP('OrderDB','n_name','varchar') 
FROM OrderDB.Nation N1 WHERE N1.n_nationkey = 1;

Result: (n_name is substituted directly into the query)
SELECT n_nationkey, n_name 
FROM Nation N1 WHERE N1.n_nationkey = 1

Query:
SELECT N1.n_nationkey,  NP('OrderDB','(select n_name from nation n2 
where N1.n_nationkey = N2.n_nationkey)','varchar') as name 
FROM OrderDB.Nation N1 WHERE N1.n_nationkey = 1

Result: 
SELECT N1.N_NATIONKEY, 
(select n_name from nation n2 where N1.n_nationkey = N2.n_nationkey) name
FROM NATION N1 WHERE N1.N_NATIONKEY = 1

Query:
SELECT N2.* 
FROM NP('OrderDB',
 '(select n_name,n_nationkey from nation)','n_name,n_nationkey') N1,
NP('PartDB',
 '(select n_name,n_nationkey from nation)','n_name,n_nationkey') as N2 
where N2.n_nationkey < 2 and N1.n_nationkey = N2.n_nationkey;

Result:
// Substitutes subquery for each of the two data sources (OrderDB and PartDB).  
// The result of the two subqueries is then joined at the UnityJDBC level.
// OrderDB: 
SELECT N2.n_name, N2.n_nationkey
FROM (select n_name,n_nationkey from nation) N2\n WHERE N2.n_nationkey < 2
// PartDB:
SELECT N1.n_nationkey FROM (select n_name,n_nationkey from nation) N1

The SELECT statement supported by UnityJDBC has the following syntax.

SELECT [ALL | DISTINCT ] <exprList>
      [FROM <tableList>]
      [WHERE <condition>] 
      [GROUP BY <exprList>
      [HAVING <condition>] 
      [ORDER BY <expr> [ASC | DESC],...] 
      [LIMIT <expr> [OFFSET <expr>]]

Some examples using the TPC-H schema follow. The database name for these examples is 'OrderDB'.

Return all nations with their key and name:

SELECT OrderDB.Nation.n_nationkey, OrderDB.Nation.n_name 
FROM   OrderDB.Nation;     

Return the nations and their regions. Only return nations in the region name of 'AMERICA'. Note the use of table aliasing using AS.

SELECT N.n_nationkey, N.n_name, R.r_regionkey, R.r_name
FROM OrderDB.Nation as N, OrderDB.Region as R
WHERE N.n_regionkey = R.r_regionkey AND R.r_name = 'AMERICA';

Calculate the number of countries in each region. Only return a region and its country count if it has more than 4 countries in it. Order by regions with most countries.

SELECT R.r_regionkey, R.r_name, COUNT(N.n_nationkey)
FROM OrderDB.Nation as N, OrderDB.Region as R
WHERE N.n_regionkey = R.r_regionkey
GROUP BY R.r_regionkey, R.r_name
HAVING COUNT(N.n_nationkey) > 4
ORDER BY COUNT(N.n_nationkey) DESC;

The INSERT statement supported by UnityJDBC has the following syntax:

INSERT INTO <tbl_name> [(<col_name>,...)] VALUES <exprList>;

Specifying column names is optional. An example is below:

INSERT INTO mydb.Customer (id,firstname,lastname,street,city) 
       VALUES (52,'Fred','Jones','Smith Lane', 'Chicago');

UnityJDBC also supports INSERT INTO ... SELECT with the following syntax:

INSERT INTO <tbl_name> [(<col_name>,...)] VALUES <exprList>
(SELECT <query>);

This is useful for storing query results into another table. Note that this table and all its column must already exist or an error will be returned. Here is an example:

INSERT INTO emptydb.customer (SELECT * FROM mydb.customer);

The UPDATE statement supported by UnityJDBC has the following syntax:

UPDATE <tbl_name> SET col1=expr1, col2=expr2, ... [WHERE <condition>];

An example is below:

UPDATE Employee SET salary=salary*1.10 WHERE age > 50;

The DELETE statement supported by UnityJDBC has the following syntax:

DELETE FROM <tbl_name> [WHERE <condition>];

An example is below:

DELETE FROM Employee WHERE salary > 100000;

The EXPLAIN statement supported by UnityJDBC has the following syntax:

EXPLAIN <query>

The EXPLAIN statement provides an explanation of the how UnityJDBC will execute a given query include the translated queries to be executed on each source, the operations performed by UnityJDBC, and the expected cost of each query operation. Using EXPLAIN is a great way to determine the performance of queries and improve their execution speed.

You can use methods to by-pass or flow through the driver to execute an untranslated query directly on a single source. In the UnityStatement class are these two methods:

ResultSet executeByPassQuery(String dbName, String sql) 
int executeByPassUpdate(String dbName, String sql) 

These methods will execute a query or update on a single source (given by name). The SQL statement provided is not parsed or validated and passed directly to the source driver. There is no overhead in this type of query as it is equivalent to invoking the source's JDBC driver directly.

UnityJDBC supports the majority of the methods in the Driver, Connection, Statement, ResultSet, and ResultSetMetaData interfaces. UnityJDBC supports the PreparedStatement interface but not the CallableStatement interface. UnityJDBC supports native updates using INSERT, DELETE, and UPDATE. It is also possible to use INSERT INTO to insert query results into another table. UnityJDBC does not support transactions across databases. Support for other JDBC methods is also limited by the underlying support of the JDBC driver for each data source. UnityJDBC requires a JDK of 1.6 or higher.

UnityJDBC contains an embedded database engine to join the results produced by executing queries on other JDBC-accessible sources. It requires a JDBC driver for each source to be accessed. The UnityJDBC architecture is the result of years of research and development and has been published in numerous technical and research publications.

Embedded in UnityJDBC is a relational database engine and associated operators of selection, projection, grouping, ordering, and join. You can build your own global query spanning data sources by combining these operators into an execution tree. In the distribution is a file called ExampleEngine.java which demonstrates how to use the engine to build an execution tree. Also in this file is an example on how you can have Unity parse but not execute a global query. UnityJDBC will return its global query and execution plan which you can later execute. This feature gives you the opportunity to modify the global execution plan before execution if desired. It also allows you to track the progress of a global query at the operator level.

UnityJDBC is the product of over 10 years of research and development in database integration and virtualization. UnityJDBC was first released in 2006 and commercial support and development has been ongoing since 2011.

The following features are planned in coming versions. Version 5.0 will be released in May 2021 with new versions released approximately every year. If you have any feature requests, please e-mail [email protected].

The following table summarizes the features of UnityJDBC and the version where they were first introduced.

Please contact [email protected] if you encounter any bugs, issues, or have feature requests.

This document is Copyright 2020 by Unity Data Inc. All rights reserved.