Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Checking DB Connection Using Groovy

DZone's Guide to

Checking DB Connection Using Groovy

· Java Zone ·
Free Resource

Verify, standardize, and correct the Big 4 + more– name, email, phone and global addresses – try our Data Quality APIs now at Melissa Developer Portal!

Here is a simple Groovy script to verify Oracle database connection using JDBC.

@GrabConfig(systemClassLoader=true)
@Grab('com.oracle:ojdbc6:11g')
url= "jdbc:oracle:thin:@localhost:1521:XE"
username = "system"
password = "mypassword123"
driver = "oracle.jdbc.driver.OracleDriver"

// Groovy Sql connection test
import groovy.sql.*
sql = Sql.newInstance(url, username, password, driver)
try {
  sql.eachRow('select sysdate from dual'){ row ->
    println row
  }
} finally {
  sql.close()
}

This script should let you test connection and perform any quick ad hoc queries programmatically. However, when you first run it, it would likely failed without finding the Maven dependency for JDBC driver jar. In this case, you would need to first install the Oracle JDBC jar into maven local repository. This is due to Oracle has not publish their JDBC jar into any public Maven repository. So we are left with manually steps by installing it. Here are the onetime setup steps:

1. Download Oracle JDBC jar from their site: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html.

2. Unzip the file into C:/ojdbc directory.

3. Now you can install the jar file into Maven local repository using Cygwin.

bash> cd /cygdrive/c/ojdbc
bash> mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11g -Dpackaging=jar -Dfile=ojdbc6-11g.jar

That should make your script run successfully. The Groovy way of using Sql has many sugarcoated methods that you let you quickly query and see data on screens. You can see more Groovy feature by studying their API doc.

Note that you would need systemClassLoader=true to make Groovy load the JDBC jar into classpath and use it properly.

Oh, BTW, if you are using Oracle DB production, you will likely using a RAC configuration. The JDBC url connection string for that should look something like this:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MY_DB)))

Update: 12/07/2012

It appears that the groovy.sql.Sql class has a static withInstance method. This let you run onetime DB work without writing try/finally block. See this example:

@GrabConfig(systemClassLoader=true)
@Grab('com.oracle:ojdbc6:11g')
url= "jdbc:oracle:thin:@localhost:1521:XE"
username = "system"
password = "mypassword123"
driver = "oracle.jdbc.driver.OracleDriver"

import groovy.sql.*
Sql.withInstance(url, username, password, driver) { sql ->
  sql.eachRow('select sysdate from dual'){ row ->
    println row
  }
}

It's much more compact. But be aware of performance if you run it multiple times, because you will open and close the a java.sql.Connection per each call!


I have also collected couple other popular databases connection test examples. These should have their driver jars already in Maven central, so Groovy Grab should able to grab them just fine.
// MySQL database test
@GrabConfig(systemClassLoader=true)
@Grab('mysql:mysql-connector-java:5.1.22')
import groovy.sql.*
Sql.withInstance("jdbc:mysql://localhost:3306/mysql", "root", "mypassword123", "com.mysql.jdbc.Driver"){ sql -> 
  sql.eachRow('SELECT * FROM USER'){ row ->
    println row
  }
}

// H2Database
@GrabConfig(systemClassLoader=true)
@Grab('com.h2database:h2:1.3.170')
import groovy.sql.*
Sql.withInstance("jdbc:h2:~/test", "sa", "", "org.h2.Driver"){ sql -> 
  sql.eachRow('SELECT * FROM INFORMATION_SCHEMA.TABLES'){ row ->
    println row
  }
}



Developers! Quickly and easily gain access to the tools and information you need! Explore, test and combine our data quality APIs at Melissa Developer Portal – home to tools that save time and boost revenue. 

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}