[TechCorner Challenge #13] - Call Database Connection Using Groovy Script

Task: Create a Groovy script that will connect to a database

This is a solution created for [TechCorner Challenge #13]

Hi,

I did this a while back and it might aid others...

For context, I have several environments, e.g. Dev, Test and Pre-Prod.  Each environment is hooked up to its own database.

Nothing new here, but I use the SoapUI environments functionality to define the separate root URLs for each environment as well as defining the db details.  This enables me to quickly switch between environments and know that where a db connection is required, I'm going to hit the right db.

I found my self in a situation where I was using a Groovy script to connect to the db in a lot of tests.  This meant that if anything changed, I'd need to update the db connection snippet in a lot of places.  This was unmaintainable.  I extracted my snippet and created a Groovy class which lives 'outside' of SoapUI GUI.  The script is in ….\SoapUIRoot\bin\scripts\groovyScripts\org.  I use a lot of external scripts.  I like the maintainability and reuse aspect.

Back to the challenge, here is my groovy class for connecting to a named db connection for an environment.

package groovyScripts.org

import groovy.sql.Sql

class jdbcConnections
{
    jdbcConnections()
    {
        // Empty constructor	
    }	
	
    def getJdbcConnection(connName, log)
    {
        // Get the db connection details for the named connection 
        // in the active environment.  There's probably a more concise way to
        // get to this value.
        def connDetails = messageExchange.modelItem.testStep.testCase.testSuite.project.activeEnvironment.databaseConnectionContainer.getDatabaseConnectionByName(connName);

        def connString = conDetails.getConnectionString()
        def url = 'jdbc:oracle:thin:' +   connString.substring(connString.indexOf('@'),connString.size());
        def userName = connString.replace('jdbc:oracle:thin:', '');
        userName = userName.substring(0,userName.indexOf('/'));
        def password = connString.substring(connString.indexOf('/') + 1,connString.indexOf('@'));

        log.info('Connecting to database ' + conDetails.getName() + '.  Using account ' + userName + ' at URL ' + url );
		
        return Sql.newInstance(url, userName, password, conDetails.getDriver());

    }
}

Using the above, I can get a named connection in any Groovy Test Step or in other external Groovy scripts I call from my SoapUI tests.

Example:-

// Instantiate an object
def jdbcConnectionObj = new groovyScripts.org.jdbcConnections();
def myDbConn = jdbcConnectionObj.getJdbcConnection("dbNameFromEnvironment", log);

// You can now query...
def sqlStatement = "select id, name from customer";
def sqlRecord = myDbConn.rows(sqlStatement);

def customerName = sqlRecord[0]["NAME"];
log.info("First customer name " + customerName);

// When finished, always close...
myDbConn.close();

There's room for improvement by adding error handling etc. but it should be enough to get you going.