Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Using Groovy SQL with Spring configured Datasource to call complicated stored procedures with multiple ResultSets and multiple In and Out Params
url: jdbc:db2://servername:port/DBname
username: dbuser
password: dbpass
jndi-name: java:/comp/env/jdbc/yourDatabase
dependencies {
compile 'org.codehaus.groovy:groovy-all:2.4.12'
compile 'org.springframework.boot:spring-boot-starter-jdbc'
compile ''
testCompile 'org.springframework.boot:spring-boot-starter-test'
testCompile 'org.spockframework:spock-spring:1.2-groovy-2.4'
testCompile 'org.spockframework:spock-core:1.2-groovy-2.4'
testCompile 'com.blogspot.toomuchcoding:spock-subjects-collaborators-extension:1.2.2'
testCompile 'cglib:cglib-nodep:3.2.6'
class GroovySQLWithSpringDAO {
Sql groovySql
List<GroovyRowResult> findByFirstLast(String firstName, String lastName) {
GString statement = """{call FIND_BY_FIRST_LAST_SP (
${firstName}, ${lastName}, 'SYSUSER',
log.debug('calling stored proc: {}', statement)
//This Stored Proc has 3 in params, 4 out params, and 2 resultsets
List<GroovyRowResult> results = groovySql.callWithRows(statement) { Integer sqlCode, String sqlState, String userCode, String feedbackMessage ->
log.debug('DB Call results: {}, {}, {}, {}', sqlCode, sqlState, userCode, feedbackMessage)
if (userCode != '9999') {
throw new RuntimeException("Error received from stored proc $sqlCode : $sqlState : $userCode : $feedbackMessage")
log.debug('results are: {}', results)
return results
// cleans up the Gist by moving imports to the bottom
import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import groovy.util.logging.Slf4j
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Component
@SpringBootTest(webEnvironment = NONE)
class GroovySQLWithSpringDAOIntSpec extends Specification {
GroovySQLWithSpringDAO dao
void 'it should call the db'() {
def results = dao.findByFirstLast('jeff', 'sheets')
results.size() == 2
results[0].last.trim() == 'sheets'
results[1].last.trim() == 'sheets'
results[0].ID == 1001
results[1].ID == 2002
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest
import spock.lang.Specification
import static org.springframework.boot.test.context.SpringBootTest.WebEnvironment.NONE
class SomeDatabaseConfig {
* Groovy Sql is the easiest way to call Stored Procs,
* since Spring Jpa does not support multiple output parameters
* See:
* and:
Sql groovySql(DataSource dataSource) {
new Sql(dataSource)
import groovy.sql.Sql
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import javax.sql.DataSource
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.