Skip to content

Instantly share code, notes, and snippets.

Embed
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
spring:
datasource:
url: jdbc:db2://servername:port/DBname
username: dbuser
password: dbpass
driver-class-name: com.ibm.db2.jcc.DB2Driver
spring:
datasource:
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 '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'
}
@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
@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
@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
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.