I was recently working on the re-configuration of an existing OpenText Documentum content server’s docbase (a docbase is like a database but for documents). Documentum docbases store the documents’ metadata into relational databases and their content on filesystems. Several RDBMS are supported, e.g. Oracle, SQLServer, and the open source PostgreSQL. The tool I was using, the Migration Utility, is a java program that connects to the underlying database through the JDBC API to change object ids and other attributes. So, prior to launching that program, I had to make sure that the database could be reached with the provided connectivity parameters, typically a host name, a port number, and a database name or service name or connect string, plus an account’s credentials, and I needed a tool for that. Like any well-trained monkey I searched it on-line first. Unsurprisingly, lots of such tools are available in source form but quite strangely I could not find a tool that supports more than one RDBMS flavor. In effect, the available utilities either work for, say, Oracle or PostgreSQL, but not for both. Isn’t JDBC supposed to be an universal API to access any vendor’s relational database once its drivers are provided ? Maybe I did not dig deep enough but after a while I decided to write one myself as it would likely take less time than going through hundreds of click baits and other questionable links. However, in order not to reinvent the wheel, I took inspiration of the existing work and only did some very minor modifications. So, here it is, a single generic enough JDBC program to connects to any database whose JDBC drivers are provided. To prove it, it will be tested against 9 different data sources and if this is not conclusive, I don’t know what will be.

The source code can also be found in github here but since the program is really small, I pasted it down below.

The program

Here is the source code of jdbc_tester_generic.java.

// generic test utility for jdbc connections;
// cec@dbi-services;
// works with any jdbc driver since its name is passed as parameter;
// to compile:
//   javac jdbc_tester_generic.java
// to execute, specify the JDBC jar to use to -cp at run-time; see example below;
import java.sql.DriverManager;
import java.sql.*;

public class jdbc_tester_generic {
    public static void main(String[] args) throws ClassNotFoundException {
        System.out.println("The command-line parameters are:");
        for (int i = 0; i < args.length; i++) {
            System.out.println("arg " + i + " = " + args[i]);
        }

        if (args.length != 5) {
           System.out.println("Invalid number of arguments: 5 arguments with the following format are required: driver user_name password url sqlquery");
	       System.out.println("e.g.:");
	       System.out.println("   java -cp postgresql-42.5.1.jar:. jdbc_tester_generic org.postgresql.Driver dmadmin xxx jdbc:postgresql://localhost:5432/mydb \"select 'now in postgresql db: ' || to_char(current_timestamp(0), 'DD/MM/YYYY HH24:MI:SS') as now;\"");
	       System.out.println("   java -cp ojdbc.jar:. jdbc_tester_generic oracle.jdbc.driver.OracleDriver dmadmin dmadmin jdbc:oracle:thin:@//db:1521/pdb1 \"select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual\"");
           return;
        }

        String driver   = args[0];
        String user     = args[1];
        String password = args[2];
        String url      = args[3];
        String sqlStmt = args[4];

        System.out.println("\nLoading driver [" + driver + "]:");
	    try {
	       Class.forName(driver);
	    } catch (ClassNotFoundException e) {
	       System.out.println("JDBC Driver [" + driver + " is not found. Please, append it to -cp or CLASSPATH");
	       e.printStackTrace();
	       System.out.println("Example of driver's syntax: org.postgresql.Driver or oracle.jdbc.driver.OracleDriver");
	       return;
	    }
	    System.out.println("JDBC Driver " + driver + " successfully loaded");

        try {
           System.out.println("\nConnecting to url [" + url + "] as user [" + user + "]:");
           Connection conn = DriverManager.getConnection(url, user, password);
	       if (conn != null) {
	          System.out.println("Successful connection to database");
	       }

           System.out.println("\nExecuting SQL query [" + sqlStmt + "]:");
           Statement statement = conn.createStatement();
           ResultSet resultSet = statement.executeQuery(sqlStmt);

           System.out.println("\nRetrieving the result set:");
           while (resultSet.next()) {
              System.out.println(resultSet.getString(1));
           }

           statement.close();
           conn.close();

           System.out.println("\nJDBC connection successfully tested, quitting ...");
        } catch (SQLException e) {
           System.out.println("Exception occurred connecting to database: " + e.getMessage());
	       e.printStackTrace();
        }
    }
}

As is visible in the import statements at the top, no vendor’s specific libraries are referenced and only calls from the standard JDBC API are used. As is, it should therefore be universal.

To compile it, let’s make sure a JDK is installed:

$ java -version
openjdk version "11.0.17" 2022-10-18 LTS
OpenJDK Runtime Environment Corretto-11.0.17.8.1 (build 11.0.17+8-LTS)
OpenJDK 64-Bit Server VM Corretto-11.0.17.8.1 (build 11.0.17+8-LTS, mixed mode)
$ javac --version
$ javac 11.0.17

Use the command below to compile it:

$ javac jdbc_tester_generic.java
$ ls -l
...
-rw-r--r-- 1 dmadmin dmadmin 2975 Jan 22 14:36 jdbc_tester_generic.java
-rw-rw-r-- 1 dmadmin dmadmin 3463 Jan 22 14:38 jdbc_tester_generic.class

Now that is has been compiled into a .class file, let’s execute it.

Execution

jdbc_tester_generic expects 5 command-line parameters:

driver_name user_name password connection_url sql_query

and complains in a constructive manner if one of them is missing, e.g.:

$ java -cp . jdbc_tester_generic
The command-line parameters are:
Invalid number of arguments: 5 arguments with the following format are required: driver user_name password url sqlquery
e.g.
java -cp postgresql-42.5.1.jar:. jdbc_tester_generic org.postgresql.Driver dmadmin xxx jdbc:postgresql://localhost:5432/mydb "select 'now in postgresql db: ' || to_char(current_timestamp(0), 'DD/MM/YYYY HH24:MI:SS') as now;"
java -cp ojdbc.jar:. jdbc_tester_generic oracle.jdbc.driver.OracleDriver dmadmin dmadmin jdbc:oracle:thin:@//db:1521/pdb1 "select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual"

The command-line parameters that makes the utility truly universal are the JDBC drivers’ name and the connection URL; once supplied, any RDBMS can be “pinged” if the given target-specific URL is correct. Let the tests begin now !

Testing with a PostgreSQL database

Let’s test it first with a PostgreSQL database. Let’s download its JDBC drivers:

$ wget https://jdbc.postgresql.org/download/postgresql-42.5.1.jar
$ ls -lrt
…
-rw-r--r-- 1 dmadmin dmadmin 2975 Jan 22 14:36 jdbc_tester_generic.java
-rw-rw-r-- 1 dmadmin dmadmin 3463 Jan 22 14:38 jdbc_tester_generic.class
-rw-r--r-- 1 dmadmin dmadmin 1046770 Jan 22 14:56 postgresql-42.5.1.jar

Let’s suppose there is already a postgres server running locally:

$ ps -ef | grep postgr
…
dmadmin 1674739 1 0 14:57 ? 00:00:00 /u01/dctm/repo02/postgresql/bin/postgres -D /u01/dctm/repo02/postgresql/data -k /tmp
…

On which port:

$ grep 'port = ' /u01/dctm/repo02/postgresql/data/postgresql.conf
port = 5422

Let’s suppose the PostgreSQL database repo02 exists with an user’s account dmadmin/xxx:

host: localhost
port: 5422
database name: repo02
credentials: dmadmin/xxx

The command to use for connecting is:

$ java -cp postgresql-42.5.1.jar:. jdbc_tester_generic org.postgresql.Driver dmadmin xxx jdbc:postgresql://localhost:5422/repo02 "select 'now in postgresql db: ' || to_char(current_timestamp(0), 'DD/MM/YYYY HH24:MI:SS') as now;"
The command-line parameters are:
arg 0 = org.postgresql.Driver
arg 1 = dmadmin
arg 2 = xxx
arg 3 = jdbc:postgresql://localhost:5422/repo02
arg 4 = select 'now in postgresql db: ' || to_char(current_timestamp(0), 'DD/MM/YYYY HH24:MI:SS') as now;

Loading driver [org.postgresql.Driver]:
JDBC Driver org.postgresql.Driver successfully loaded

Connecting to url [jdbc:postgresql://localhost:5422/repo02] as user [dmadmin]:
Successful connection to database

Executing SQL query [select 'now in postgresql db: ' || to_char(current_timestamp(0), 'DD/MM/YYYY HH24:MI:SS') as now;]:

Retrieving the result set:
now in postgresql db: 22/01/2023 14:58:08

JDBC connection successfully tested, quitting …

where org.postgresql.Driver is taken from the documentation. If too lazy or impatient, just do that:

$ jar vtf the drivers_jar_file | egrep 'Driver.*class'

One of the returned lines is the driver class.

Note the URL’s syntax: jdbc:postgresql://localhost:5422/repo02, no leading @ sign.

Testing with an Oracle database

Let’s now test it with an Oracle database. Let’s download the drivers:

$ wget https://download.oracle.com/otn-pub/otn_software/jdbc/218/ojdbc11.jar
$ ls -lrt
…
-rw-rw-r-- 1 dmadmin dmadmin 5181682 Dec 7 21:20 ojdbc11.jar
-rw-r--r-- 1 dmadmin dmadmin 2975 Jan 22 14:36 jdbc_tester_generic.java
-rw-rw-r-- 1 dmadmin dmadmin 3463 Jan 22 14:38 jdbc_tester_generic.class
-rw-r--r-- 1 dmadmin dmadmin 1046770 Jan 22 14:56 postgresql-42.5.1.jar

Let’s suppose there is an Oracle database server running remotely with the following connectivity data:

host: 192.168.0.21
port: 1521
service name: pdb1
credentials: repo01/repo01

The pdb1 service name we use points to a pluggable database with the same name inside a container database named orcl. The account repo01 is only defined in pdb1.

The command to use is:

$ java -cp ojdbc11.jar:. jdbc_tester_generic oracle.jdbc.driver.OracleDriver repo01 repo01 jdbc:oracle:thin:@//192.168.0.21:1521/pdb1 "select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual"
The command-line parameters are:
arg 0 = oracle.jdbc.driver.OracleDriver
arg 1 = repo01
arg 2 = repo01
arg 3 = jdbc:oracle:thin:@//192.168.0.21:1521/pdb1
arg 4 = select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual

Loading driver [oracle.jdbc.driver.OracleDriver]:
JDBC Driver oracle.jdbc.driver.OracleDriver successfully loaded

Connecting to url [jdbc:oracle:thin:@//192.168.0.21:1521/pdb1] as user [repo01]:
Successful connection to database

Executing SQL query [select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual]:

Retrieving the result set:
now in Oracle db: 22/01/2023 15:31:37

JDBC connection successfully tested, quitting ...

Note the URL’s syntax: jdbc:oracle:thin:@//192.168.0.21:1521/pdb1, with a leading @ sign.

Continue to Part II.

Post Views: 2,832
Thumbnail [60x60]
by
Cesare Cervini