Using Groovy SQL with Spring configured Datasource to call complicated stored procedures with multiple ResultSets and multiple In and Out Params

application-local.yml

spring:
  datasource:
    url: jdbc:db2://servername:port/DBname
    username: dbuser
    password: dbpass
    driver-class-name: com.ibm.db2.jcc.DB2Driver

application-prod.yml

spring:
  datasource:
    jndi-name: java:/comp/env/jdbc/yourDatabase

build.gradle

dependencies {
    compile 'org.codehaus.groovy:groovy-all:2.4.12'
    compile 'org.springframework.boot:spring-boot-starter-jdbc'
    compile 'com.ibm.db2:db2jcc4:+'

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

GroovySQLWithSpringDAO.groovy

@Slf4j
@Component
class GroovySQLWithSpringDAO {
    @Autowired
    Sql groovySql

    List<GroovyRowResult> findByFirstLast(String firstName, String lastName) {
        GString statement = """{call FIND_BY_FIRST_LAST_SP (
            ${firstName}, ${lastName}, 'SYSUSER',
            ${Sql.INTEGER}, ${Sql.VARCHAR}, ${Sql.VARCHAR}, ${Sql.VARCHAR}
        )}"""

        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

GroovySQLWithSpringDAOIntSpec.groovy

@SpringBootTest(webEnvironment = NONE)
class GroovySQLWithSpringDAOIntSpec extends Specification {
    @Autowired
    GroovySQLWithSpringDAO dao

    void 'it should call the db'() {
        when:
        def results = dao.findByFirstLast('jeff', 'sheets')

        then:
        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

SomeDatabaseConfig.groovy

@Configuration
class SomeDatabaseConfig {

    /**
     * Groovy Sql is the easiest way to call Stored Procs,
     *  since Spring Jpa does not support multiple output parameters
     * See: http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html
     * and: https://jira.spring.io/browse/DATAJPA-707
     */
    @Bean
    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