DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Data
  4. Checking DB Connection Using Groovy

Checking DB Connection Using Groovy

Zemian Deng user avatar by
Zemian Deng
·
Dec. 12, 12 · Interview
Like (0)
Save
Tweet
Share
27.46K Views

Join the DZone community and get the full member experience.

Join For Free

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



Database connection Groovy (programming language)

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

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • 2023 Software Testing Trends: A Look Ahead at the Industry's Future
  • Visual Network Mapping Your K8s Clusters To Assess Performance
  • The Top 3 Challenges Facing Engineering Leaders Today—And How to Overcome Them
  • Top 10 Secure Coding Practices Every Developer Should Know

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: