groovy: get your classpath right!

Here’s some basic, basic groovy code with a simple SQL query to retrieve some data from an oracle DB:

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@hostname:1521:testdb", "user", "pass", "oracle.jdbc.driver.OracleDriver")

def query = "select * from tablename where name='Beta 1'"

row = sql.firstRow(query)

println row

However, despite various attempts, I couldn’t even establish a DB connection because of this annoyingly persistent exception:

Caught: java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
        at sql1.run(sql1.groovy:13)

So of course, I immediately googled the rather-descriptive error message, and quickly learnt that Groovy didn't know where to find the oracle jdbc driver. With a few more searches, I discovered that the missing driver was located inside a jar that was called something like “ojdbc14.jar” or something.

I found where the jar was on my file system:
”C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0” contained ojdbc14-10.2.0.1.0.jar that seemed like a good candidate for the jar containing the required oracle jdbc driver. Seemed like I’m making progress, good.

Now how could I tell Groovy/Java to search this path? I figured that I had to include the jar’s file-system path into my Java CLASSPATH, so that groovy would know where to find missing classes.

Okay, so I added “C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0” to my CLASSPATH environment variable, but that didn’t seem to work.

Still no good :-(

Hmmm, “groovy –h” indicates that I can specify a classpath on the command line with the –cp or –classpath parameters. So let’s try that:

$ groovy -classpath C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0  sql1.groovy
Caught: java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
at sql1.run(sql1.groovy:2)

Dammit work already! :P

I realized that I must be missing something very basic. It had to be a total n00b Java/Groovy problem. C’mon it can’t really be so hard to specify a path can it?!?

So I started googling about setting the java CLASSPATH correctly and then I stumbled on why it wasn’t’ working for me:

JARs on the classpath
Java compiler and run-time can search for classes not only in separate files, but also in `JAR' archives. A JAR file can maintain its own directory structure, and Java follows exactly the same rules as for searching in ordinary directories. Specifically, `directory name = package name'. Because a JAR is itself a directory, to include a JAR file in the class search path, the path must reference the JAR itself, not the directory that contains the JAR. This is a very common error.

Suppose I have a JAR jarclasses.jar in directory /jarclasses. The Java compiler look for classes in this jar, we need to specify:

javac -classpath /jarclasses/jarclasses.jar
and not merely the directory jarclasses.

Thank you, http://www.roseindia.net/java/java-classpath.shtml, for saving me from a lot of frustration!
Voila! Finally it works!

$ groovy -classpath C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0\ojdbc14-10.2.0.1.0.jar sql1.groovy
[NAME:Summary, PATH:Snapshot, MAPPINGKEY:26, VERSION:v9]

It’s even easier with the CLASSPATH environment variable set correctly like this:
CLASSPATH=C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0\ojdbc14-10.2.0.1.0.jar;

Now I don’t even have to type the –classpath parameter out on the command line each time:

$ set | findstr CLASSPATH
CLASSPATH=C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0\ojdbc14-10.2.0.1.0.jar;C:\Program Files\Java\jre6\lib\ext\QTJava.zip;.

$ groovy sql1.groovy
[NAME:Summary, PATH:Snapshot, MAPPINGKEY:26, VERSION:v9]