Chuan Chuan Law

DevOps | Software Automation | Continuous Integration

Category: Gradle & Grails

Accessing Oracle Database From Test Suite

Good test suites need to be able to access direct the database. Below is how I do it with my test suite in Groovy in Gradle framework accessing a Oracle database:

(1) Connect the database in your code

class TestDB {

    static void main(String[] args) {

        def sql = Sql.newInstance('', 'username', 'password', 'oracle.jdbc.driver.OracleDriver')


(2) The special case with Oracle database is that is no ready to used library that we can download from Maven. I solve the problem by:

  • Download a copy of Oracle driver
  • Commit as part of the source code. I put under the Resource folder

file structure


  • In build.gradle, add the external Jar file as part of dependencies during compilation:
dependencies {
    compile 'org.codehaus.groovy:groovy-all:2.4.3'
    compile 'junit:junit:4.12'
    compile files('src/main/resources/ojdbc6.jar')


How To Replace Selenium With Integration Test

The trend and best practice in test automation is to move away from writing lots of functional test in Selenium. So, we want to try to replace high level Selenium test with Integration and Unit tests as much as possible.

Pros of Integration test:

  • Easier to write in comparison to Unit test (no need mocking)
  • Able to utilize test data in test database
  • Able to eliminate the need of some Selenium test

Cons of Integration test:

  • Data reliability issue due to dependency on test data

Grails testing framework provide a lot of convenience to achieve the above. The scenario below shows I eliminate the need of Selenium test:

  1. In DataSource.groovy change test to look point at test database
test {
    dataSource {
        username = "adam"
        password = "eve"
        url = ''

2.  Create an Integration test repo via command. The command will create a repo called “TestRepo” under “test” directory in the project file structure.

$ grails create-integration-test TestRepo

3.  Example of integration that:

  • Finds a book using its unique Id
  • Checks that the book title is correct
class BookSpec extends IntegrationSpec {

    void "a book is returned"() {

        //Finding the item ID in the test database that you have defined in DataSource
        given:"we find a book with id"
          def bookId=Item.get(15102876765285400414)

        //Call Book object by passing the unique Id
        when: "we call the find function"
          def book=Book.findByItem(bookId)

        //Assert the Book object returned
        then: "book will be returned"
          Assert.assertTrue(book.title.equals("The Happy Pig"))

4. Example of integration test that checks for UI. The test below:

  • Finds the book using Id
  • Check that the book displays a link named Buy Now

class BookTagLibTests extends GroovyPagesTestCase {

    void testShowBuyBook(){

        //Find the book using Id
        def bookId=Item.get(15110576705972615055)

        //Checks that the book displays Buy Now link which will activate a JavaScript function upon click
        applyTemplate('<item:bookBanner item="${item}"/>', [item:bookId] ).contains("<a id='book-buy-${}' class='btn carf na' href='javascript:buyBook(\"${}\")' data-original-title='' title=''>Buy Now</a>")

Both examples above shows that we could replace certain Selenium scenarios with Integration test, including those that involves UI.

Extending PACT Framework – Global Data Setup & Data Cleanup

In every testing framework we do data setup and clean up. Below is how I do it with Gradle framework:

Global Data Setup

Pact framework provides very good random test data generation with PactBodyBuilder. However, in some cases where our API involves data creation, we will need to remember the data we created, so we can use it across multiple tests, and then deleting it afterwards.

We can do this by using System Property in Gradle. Under test section in build.gradle, include this:

    systemProperty 'RandomNum', new Random(1000)

In the Pact Consumer test where you need to create the data, do this:

  • Define the random number data that you want to generate and remember
class InsertCFCertConsumerTests {

    def ran=System.getProperty("RandomNum")

    void "Insert CF Certificate" (){
  • In the Pact body, include your random number
    requestId ran
  •   Of course, include this random number in the 2nd part of the Consumer test (service run)
VerificationResult result = {

   def client = new RESTClient('http://localhost:1237/') 
   def body = new JsonBuilder([requestId: ran]) 
   def cf_response = '/v1/carfacts/',requestContentType: 'application/json',headers:['Content-Type': 'application/json'],body:body.toPrettyString())
  • The same random number ran can be called across multiple tests


To clean up data from database, create a task in build.gradle.

Clean up via database

  • Connect to the database
task cleanUp<<{
    gradle.class.classLoader.addURL(new File('src/main/resources/ojdbc6.jar').toURI().toURL())
    def sql = Sql.newInstance('', 'USERNAME', 'PASSWORD', 'oracle.jdbc.driver.OracleDriver')


ojdbc6.jar is the Oracle database driver downloaded from the Oracle site . External file can be included during compilation by adding the following line under dependencies:

dependencies {
     compile files('src/main/resources/ojdbc6.jar')
  • Write the SQL to delete the data you want
String sqlDelete = 'delete from Table where id='VN000000000000001''
  • Execute the query


Clean up via API call

Sometimes, we could call a DELETE via API to do data clean up.

  • Get Id via database and delete via API call
task cleanUp<<{

   String sqlGetId= 'select id from Table where id='VN000000000000001''
   def delElas=new RESTClient('') 
   def cfId=0 sql.eachRow(sqlGetId) { 
     println delElas.delete(path:"/id_checker/${cfId}") 

However,  please note that the above will cause build task to fail as the data that you want to delete will not exist. Workaround for this is to exclude this task while building via command gradle build -x <name of clean up task>.

Getting Selenium Test Running On Gradle Framework

Gradle + Selenium is probably not a popular framework yet when I was developing it as I find it difficult to look for relevant resources online. There were bits and pieces of information here and there which I then glue then up together.

My build.gradle contains the following implementations:

(1) Add cucumberRuntime under Configurations

configurations {
cucumberRuntime {
extendsFrom testRuntime


(2) Source sets

srcDirs ‘src/test’


(3) Compile test output

task testJar(type: Jar){
include ‘**/*.class’
exclude ‘**/*StepDef*.class’


(4) Glue everything together

task cucumber() {
    dependsOn assemble, compileTestJava
    doLast {
        javaexec {
            main = "cucumber.api.cli.Main"
            classpath = configurations.cucumberRuntime + sourceSets.main.output + sourceSets.test.output
            args = ['-f', 'json:target/cucumber-report.json','--plugin','pretty', '--glue', 'src/test/groovy/au/com/story/steps', 'src/test/groovy/au/com/story/features']

© 2019 Chuan Chuan Law

Theme by Anders NorenUp ↑