JDBC & the Oracle Database: using Maven Central

Over the years I have written a few blog posts about Java and the Oracle database. Most of these posts contained code in some form or another. And I have to admit that I haven’t been following my own advice … I have run into all sorts of issues with the code simply as a result of not storing it centrally and keeping a close eye on versioning. I think it’s about time I correct these issues and re-write my examples. This is going to be a bit of a journey, and it’s entirely my fault for putting up with a mountain of technical debt.

At the end of my code journey I am hoping to have a code repository on Github with all my problems being a thing of the past. There were a few issues I wanted to correct with the code, and this looks like a great opportunity to address them.

UPDATE 07/2021: The code repository does finally exist, you can find it under my github account. I’m not a Java developer as you can probably tell from the code quality, so please let me know via Github issues or Twitter what you think of the code.

Update 06/2022: another major rewrite, bumping JDK to 11, JDBC drivers to 21.5.0 and Maven to 3.8.5. Instructions on how to generate the wallet have been replaced by reference to the generate_wallet.sh script.

A little more security

I really don’t like JDBC code examples where username and password are exposed in code. It is too easy as a developer to simply copy and paste the code into one’s own application. Once it’s in there it doesn’t take much and sensitive credentials end up on Github and are visible to everyone. This is Proper Bad.

So rather than providing username and password in clear text in the Java code, I will make use of the external password store.

Compiling the code

Previously I have used Apache Ant as the build tool. And by previously I mean in 2006… This time around I have taken the opportunity to switch to Apache Maven 3.8.x. Maven is one of the most popular tools in the Java space from what I can tell. Furthermore, it is now possible to pull the necessary JDBC libraries from Maven Central and connecting to my single instance Oracle 19c database. Enough reason for me to give it a try.

Making basic use of Maven is straight forward, at least for my needs. I started off with a very simple example:

$ mvn archetype:generate \
> -DgroupId=de.martin -DartifactId=secure-external-password-store \
> -DarchetypeArtifactId=maven-archetype-quickstart \
> -DarchetypeVersion=1.4 -DinteractiveMode=false

This creates the necessary project structure. Before I can use the Oracle JDBC drivers I have to update the dependencies in pom.xml. I opted to include the complete JDBC driver set to standardise my code base: I’ll use Autonomous Database in a future post, so having a single POM works for me. Pulling in all JDBC driver jar files however might be a security problem.

<!-- Oracle JDBC as per https://www.oracle.com/database/technologies/maven-central-guide.html -->
  <dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11-production</artifactId>
    <version>21.5.0.0</version>
    <type>pom</type>
  </dependency>

This should pull in all required JARs. Make sure to provide the current version as there will be updates over time. Version 21.5.0 was current in June 2022. Including OJDBC production pulls in all the driver jar files you might possibly need, and potentially a few more than that. Please have a look at the Maven Central Guide if you like to import fewer libraries.

To keep things simple for this demo I removed any reference to JUnit. This isn’t a feasible approach for production code! Unit tests are super important and should be a mandatory component of every project.

Preparing the External Password Store

Using the external password store allows me to store username and password in an external entity called a wallet. There is a slight caveat: creating a wallet requires access to a full Oracle client installation. As far as I know the necessary mkstore utility isn’t part of any instant client download.

I would like to connect as user SOE to my Pluggable Database swingbench1 running on a VM with a primary IP of 192.168.56.11 without having to provide either a username or a password. The listener receives incoming connections on port 1521. The Java code will be executed on a workstation with an IP address of 192.168.56.1.

To keep things simple I created a small utility named generate_wallet.sh and stored it in the utilities folder of my Github repository. This takes care of the wallet’s generation. Note that you need to run this script on a client installation (not an instant client!). The script creates a wallet and anything else that’s needed in ~/tns.

Preparing the client

After the wallet has been created, it needs to be securely transferred to the workstation where it’s about to be used. The designated location on my client is /home/martin/tns. Feel free to move the directory to any location you like, and update the app.properties file to reflect the location.

Let there be code!

It turns out the preparations were the hard part. Now for some code to connect to the database. This example is loosely modeled on a Java class in Oracle’s Github repository (DataSourceSample.java).

Whilst this works, I believe it’s not the best way for a non-library developer to access the “raw” JDBC interface. From what I read it seems to be consensus not to code the JDBC interface directly, but rather use a framework.

Nevertheless, I decided to share the class, after all I needed some code to test authentication via the wallet.

public class SEPS {

    /**
     * EZ Connect Plus string, for example
     * "jdbc:oracle:thin:/@swingbench1?TNS_ADMIN=/home/martin/tns"
     * This string should be defined in app.properties
     */
    private String ezConnectPlusString = null;

    public String getEzConnectPlusString() {
        return ezConnectPlusString;
    }

    /**
     * Read properties from app.properties
     */
    public void init() {
        Properties properties = new Properties();
        URL url = ClassLoader.getSystemResource("app.properties");

        try {
            properties.load(url.openStream());

        } catch (Exception e) {
            e.printStackTrace();
        }
        ezConnectPlusString = properties.getProperty("conf.ezConnectPlusString");
    }

    /**
     * Connnect to the database and print a bit of metadata.
     * 
     * @throws SQLException
     */
    public void connect() throws SQLException {

        OracleDataSource ods = new OracleDataSource();
        ods.setURL(this.ezConnectPlusString);

        try (OracleConnection connection = (OracleConnection) ods.getConnection()) {

            // Grab some randome metadata
            DatabaseMetaData dbmd = connection.getMetaData();

            System.out.println("Driver Name:       " + dbmd.getDriverName());
            System.out.println("Driver Version:    " + dbmd.getDriverVersion());

            System.out.println("Database username: " + connection.getUserName());

            System.out.println();

            connection.close();
        }
    }

    public static void main(String[] args) {
        System.out.println("Getting ready to connect to the database\n");
        SEPS demo = new SEPS();
        try {
            demo.init();
            System.out.println("Got the following connection string: " + demo.getEzConnectPlusString());
            demo.connect();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

You may have noticed the absence of references to setting username and password. The connection string is now making use of EZConnect Plus syntax. Note that the connection string is stored in app.properties.

conf.ezConnectPlusString=jdbc:oracle:thin:/@swingbench1?TNS_ADMIN=/home/martin/tns

If you have used the JDBC thin driver before this JDBC connection string will without a doubt look slightly odd. Oracle 18c introduced the ability to use an extended EZ Connect Syntax, and I combined the syntax from Autonomous Database and the JDBC developer’s guide to end up with this one. The other important detail is the use of ojdbc.properties. This file allows me to specify the wallet location even if there isn’t a full-blown Oracle client installation featuring an $ORACLE_HOME/network/admin/sqlnet.ora file:

$ cat ~/tns/ojdbc.properties 
oracle.net.wallet_location=(source=(method=file)(method_data=(directory=${TNS_ADMIN})))

The utility script takes care of setting the parameters correctly.

Running the code

Running mvn package instructs maven compiles the code and package it. It’s probably easiest to use your IDE to run/debug the code as it should be able to include all necessary JAR files in the classpath. In the case of VS Code, this is what it printed on screen:

$  /usr/bin/env /usr/lib/jvm/java-11-openjdk-amd64/bin/java @/tmp/cp_312vjs1pb7n4zd4zsl9wsxlyp.argfile de.martin.seps.SEPS 
Getting ready to connect to the database

Got the following connection string: jdbc:oracle:thin:/@swingbench1?TNS_ADMIN=/home/martin/tns
Driver Name:       Oracle JDBC driver
Driver Version:    21.5.0.0.0
Database username: SOE

Result! I connected to the database without ever providing a username or password. I also managed to use the most recent JDBC drivers. As soon as Oracle releases new JDBC drivers all you need to do is update the POM and rebuild the code.

Summary

This article should mark the start to a series of updated Java articles detailing how to work with the JDBC drivers on Maven-Central. Compared to earlier attempts, this time around the code makes use of Maven as a the primary build tool. It also features the use of an Oracle Secure External Password Store rather than less secure means of connecting to a system.

Last but not least I would like to thank @gustavojimenez for peer-reviewing this article.

Advertisement