Create Oracle database JDBC connection in gradle

It is usual  for a Gradle script to get connected to a database like Oracle. Doing this in Gradle may seem pretty simple and straightforward. But sometimes things wont get right. If you are having a hard time to get connected to oracle database, using the java code inside gradle, this tutorial is for you.

Most usual error is not getting the dependencies resolved. The usual way that you will try is as follows:

task connectDB{
          description 'connect to database'


          repositories {
                    mavenLocal()
          }
 
          configurations {
                   compile
          }
 
         dependencies {
                compile com.oracle:ojdbc6:11.2.0.4.0' 
         }

        doLast{

        //java code for database connection

        }

}

The reason why we are picking the dependencies (ojdbc6.jar) from local maven repositories is that it is simply not available in the maven repo due to licensing issues. The above code has a few problems also. Even if you get connected to the database, you will get XML parsing errors while executing queries. For some people, it will not even get connected. It will say the wrong driver version and some errors.

In order to get things right (the way which worked for me) you’ll have to do two things:

  1. Iterate through and load each class in a groovy sql class loader for the driver jar.
  2. Add Oracle XML DB Java API to dependencies. (xdb.jar)

Download both jars and install in maven local repository. Follow the code below

task connectDB {
 description 'connect to database'

 repositories {
  mavenLocal()
 }

 configurations {
  jdbcdriver
  xdb
 }

 dependencies {
  jdbcdriver 'com.oracle:ojdbc6:11.2.0.4.0'
  xdb 'com.oracle:xdb6:1.0'
 }

 doLast {

  configurations.jdbcdriver.files.each {
   groovy.sql.Sql.classLoader.addURL(it.toURI().toURL())
  }
  configurations.xdb.files.each {
    groovy.sql.Sql.classLoader.addURL(it.toURI().toURL())
   }
   //load the jdbc driver and create the connection.
  java.sql.DriverManager.registerDriver(groovy.sql.Sql.classLoader.loadClass("oracle.jdbc.OracleDriver")

   .newInstance())

  java.sql.Connection conn = java.sql.DriverManager.getConnection(db_url, dbUser, dbPassword);
  java.sql.Statement stmt = conn.createStatement();

  < rest of the code >

 }

}

The above code is almost complete and will do the trick for you. It basically loads the correct driver class from the jar so you will not get any issues resolving correct dependencies. I hope this helps.

Share this article:

Leave a Reply

Your email address will not be published. Required fields are marked *