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

Managing a MongoDB deployment? Take a load off and live migrate to MongoDB Atlas, the official automated service, with little to no downtime.

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
  }
}



MongoDB Atlas is the easiest way to run the fastest-growing database for modern applications — no installation, setup, or configuration required. Easily live migrate an existing workload or start with 512MB of storage for free.

Topics:

Published at DZone with permission of Zemian Deng, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}