Search

Top 60 Oracle Blogs

Recent comments

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.

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. This time around I have taken the opportunity to switch to Apache Maven 3.6.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 basic example:

$ mvn archetype:generate -DgroupId=de.martin.demo01 -DartifactId=demo01 \
> -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. As per the aforementioned article on Medium I added the following dependency:


  
    com.oracle.database.jdbc
    ojdbc10-production
    19.7.0.0
    pom
  

This should pull in all required JARs. From what I understood reading another source, Oracle’s Maven Central Guide, this is only one of 2 ways of integrating the necessary libraries, have a look at the link for the full story.

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 an Oracle client installation. As far as I know the necessary mkstore utility isn’t part of any instant client download yet. Using the external password store with the JDBC thin driver is a little tricky, but I like challenges :)

The requirement

I would like to connect as “martin” to my single instance database “ORCL” running on a VM with a primary IP of 192.168.56.122 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.

Creating the wallet

In my case the creation of the wallet happened on the database server. To keep things simple I’ll create it in $ORACLE_BASE/admin/$ORACLE_SID/authn. This wallet has nothing to do with other Oracle technology such as Transparent Data Encryption for example – it is purely used for authentication of my application to the database.

[oracle@server1 authn]$ which mkstore
/u01/app/oracle/product/19.0.0/dbhome_1/bin/mkstore
[oracle@server1 authn]$ 
[oracle@server1 authn]$ mkstore -wrl $(pwd) -create
Oracle Secret Store Tool Release 20.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter password:   
Enter password again:   

The next step is to create a credential. To do so I must provide the connect string to the database, matching the entry in tnsnames.ora in lower case. This is the crucial bit: whenever I tried to use an upper case net*8 alias in tnsnames.ora or the wallet, the connection failed with an “invalid username/password” error.

The second parameter to provide is the username I want to connect to. For example:

[oracle@server1 authn]$ mkstore -wrl $(pwd) -createCredential orcl martin
Oracle Secret Store Tool Release 20.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line 
Enter your secret/Password:   
Re-enter your secret/Password:   
Enter wallet password:   
[oracle@server1 authn]$ 

I usually create an ORACLE_SID in upper case for nostalgic reasons, hence it took me a little while to figure the need for lower case identifiers out.

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 the client is /home/martin/tns. After the wallet has been transferred to the client I have the following files in said directory:

[martin@client1 ~]$ ls -1 ~/tns
total 8
cwallet.sso
cwallet.sso.lck
ewallet.p12
ewallet.p12.lck
[martin@client1 ~]$  

The next step is to add a tnsnames.ora file. Remember to create the net*8 alias in lower case. Here is my file in case you need to use something as a reference:

[martin@client1 tns]$ cat tnsnames.ora 
orcl =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.122)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
[martin@client1 tns]$  

Finally you need a file to point the thin client to the wallet location. The way to do is not immediately obvious, however I found it documented for Autonomous Database. Long story short, you need to create another file named ojdbc.properties with the following contents:

oracle.net.wallet_location=(source=(method=file)(method_data=(directory=${TNS_ADMIN})))

This is a big step ahead compared to earlier JDBC versions. Instead of having to provide system properties to the JVM it is now possible to omit those. Oracle introduced EZConnect Plus in 18c and I am totally going to make use of it. Thanks to @martinberx for spotting a copy/paste mistake in the number of closing brackets. The Oracle documentation misses one ;)

With all the files in place the client is ready! In case you wondered, ~/tns now contains the following files:

[martin@client1 tns]$ ls -1
cwallet.sso
cwallet.sso.lck
ewallet.p12
ewallet.p12.lck
ojdbc.properties
tnsnames.ora
[martin@client1 tns]$  

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.

package de.martin.demo01;

import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.OracleConnection;
import java.sql.DatabaseMetaData;

/**
 * The equivalent of a "hello world" type application for connecting to the 
 * Oracle database 
 */
public class App 
{

    final static String ezConnectPlus = "jdbc:oracle:thin:/@orcl?TNS_ADMIN=/home/martin/tns";

    public static void main( String[] args ) throws SQLException
    {
        System.out.println( "Getting ready to connect to the database" );

        OracleDataSource ods = new OracleDataSource();
        ods.setURL(ezConnectPlus);

        try (OracleConnection connection = (OracleConnection) ods.getConnection()) {
            
            // Get the JDBC driver name and version 
            DatabaseMetaData dbmd = connection.getMetaData();       
            System.out.println("Driver Name: " + dbmd.getDriverName());
            System.out.println("Driver Version: " + dbmd.getDriverVersion());
            
            // Print some connection properties
            System.out.println("Default Row Prefetch Value is: " + 
               connection.getDefaultRowPrefetch());
            System.out.println("Database Username is: " + connection.getUserName());
            System.out.println();

            connection.close();
          }   
    }
}

You may have noticed the absence of references to setting username and password. The connection string is now making use of EZConnect Plus:

jdbc:oracle:thin:/@orcl?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.

Running mvn package instructs maven compiles the code and package it.

Running the code

Running the code requires the following JAR files to be in the classpath:

  • ojdbc10.jar
  • oraclepki.jar
  • osdt_cert.jar
  • osdt_core.jar
  • And of course the JAR with the application code created by Maven

As that’s a lot of typing I created symbolic links from my local maven repository in ~/.m2/repository to ~/java/libs. With that done, I can run the code after a call to mvn package:

[martin@client1 maven-example-1]$ java -cp /home/martin/java/libs/ojdbc10.jar:/home/martin/java/libs/oraclepki.jar:/home/martin/java/libs/osdt_cert.jar:/home/martin/java/libs/osdt_core.jar:target/maven-example-1-1.0-SNAPSHOT.jar de.martin.demo01.App
Getting ready to connect to the database
Driver Name: Oracle JDBC driver
Driver Version: 19.7.0.0.0
Default Row Prefetch Value is: 10
Database Username is: MARTIN
[martin@client1 maven-example-1]$

Result! I connected to the database without ever providing a username or password. It is impossible to accidentally commit something that doesn’t exist to a source code repository. I hope this makes for better, and somewhat more secure code.

Summary

This is hopefully the first post in a series of Java-related articles allowing me to better maintain my code. In the first article I shared how to create the Secure External Password Store (aka wallet) to store connection credentials to avoid supplying them in application code. The other change I introduced was the use of Maven as my build tool. I am conscious of the fact that Maven is integrated into many Java development IDEs, yet I still preferred to show its usage on the command line so that everyone can follow along without having to learn a particular IDE’s workflow.

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