20
May
10

Calling Stored Procedures using Spring 2.5 SimpleJdbcCall


The following page describes the process of calling Stored Procedures using Spring 2.5 SimpleJdbcCall. In the examples below I will be using DB2 but you can use any database that supports stored procedures or functions.

Background

The SimpleJdbcCall was introduced in Spring 2.5. It’s goal is to make calling stored procedures as simple as possible. It tries to use JDBC meta-data as much as possible, and it allows the developers to override settings. Using any other class like “JdbcTemplate” or extending from “StoredProcedure” is considered “old school spring”. Update your skill set and read on.

Requirements

  • Java 5 or above
  • Maven 2 (installed and configured) (see right nav on how to install maven)
  • DB2 JDBC Drivers installed in the compile and runtime class path
  • DB2 Database Server with a stored procedure pre-written ready to be called

Create a new Project

Create a project using Maven archetype. Open up the command prompt and navigate to an empty directory.

mvn archetype:generate -DarchetypeArtifactId=maven-archetype-quickstart

groupId: com.test
artifactId: springStoredProcedure

Answer the rest of the questions with defaults “Just hit the enter key”

Open up the pom.xml file and make sure it looks like the one below. In most cases you can just copy and paste the one below into your own.

Configure the project for Database Connectivity

I tried to make the configuration as simple as possible however you would need to go out and download the DB2 Driver files manually and either install them in your maven local repository or have them in the class path when you execute the program. In the example below I have installed them in the local repository by executing the following commands.

mvn install:install-file -DgroupId=com.ibm -DartifactId=db2jcc -Dversion=3.7.73 -Dpackaging=jar -Dfile=c:/db2jcc.jar

mvn install:install-file -DgroupId=com.ibm -DartifactId=db2jcc_license_cu -Dversion=3.7.73 -Dpackaging=jar -Dfile=c:/db2jcc_license_cu.jar

mvn install:install-file -DgroupId=com.ibm -DartifactId=db2jcc_license_cisuz -Dversion=3.7.73 -Dpackaging=jar -Dfile=c:/db2jcc_license_cisuz.jar

Add the following in the dependencies section of the pom.xml file.

pom.xml

	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring</artifactId>
		<version>2.5.6</version>
	</dependency>

	<dependency>
		<groupId>com.ibm</groupId>
		<artifactId>db2jcc</artifactId>
		<version>3.7.73</version>
	</dependency>

	<dependency>
		<groupId>com.ibm</groupId>
		<artifactId>db2jcc_license_cu</artifactId>
		<version>3.7.73</version>
	</dependency>

	<dependency>
		<groupId>com.ibm</groupId>
		<artifactId>db2jcc_license_cisuz</artifactId>
		<version>3.7.73</version>
	</dependency>	

Since we will be using annotations we will need to enable java5.

<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>2.0.2</version>
            <configuration>
            <source>1.5</source>
            <target>1.5</target>
            </configuration>
        </plugin>
    </plugins>
</build>

If you will be calling the stored procedure from a standalone application then you need to put the db2 JDBC driver files in the class path. In order to find the JDBC drivers for the IBM DB2 database just follow this link. If you are using a database other than DB2 you can download the drivers from the vendor’s website. You can use the following link to determine the URL value seen below.

src/main/resources/applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:context="http://www.springframework.org/schema/context"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
<context:annotation-config/>
<context:component-scan base-package="com.test"/>
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.ibm.db2.jcc.DB2Driver"/>
        <property name="url" value="jdbc:db2://servername:port/DBNAME"/>
        <property name="username" value="username"/>
        <property name="password" value="xxxxx"/>
</bean>
</beans>

Inject the Data-source into your Data Manager/DAO object.

Next we define a DataManager class that talks with the database.

src/main/java/com/test/CountryDataManagerImpl.java

package test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;

@Repository("countryDataManager")
public class CountryDataManagerImpl {
 
    private SimpleJdbcCall countryProcedure;
 
    @Autowired
    @Required
    public void setDataSource(DataSource dataSource) {
...
    }
}

Calling stored procedures with output parameters

Define the stored procedure call like this:

    @Autowired
    @Required
    public void setDataSource(DataSource dataSource) {
        String procedureName = "QUALIFIER.PROCNAME";
 
        countryProcedure = new SimpleJdbcCall(dataSource)
        .withoutProcedureColumnMetaDataAccess()
        .withProcedureName(procedureName)
        .declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))
        .declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR));
    }

It is possible to return values from stored procedures using output parameters. You can do this by calling the stored procedure like this…
The following is how its invoked.

    public void readOutParameters() {
        Map result = countryProcedure.execute();
        System.out.println("RETURNCODE: " + result.get("RETURNCODE"));
        System.out.println("RETURNMSG: " + result.get("RETURNMSG"));
    }

Parsing result sets

Define the stored procedure call like this:

    @Autowired
    @Required
    public void setDataSource(DataSource dataSource) {
        String procedureName = "QUALIFIER.SPNAME1";
 
		countryProcedure = new SimpleJdbcCall(dataSource)
		.withoutProcedureColumnMetaDataAccess()
		.withProcedureName(procedureName)
		.declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))
		.declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR))
		.returningResultSet("countries", new ParameterizedRowMapper<Country>() {
			public Country mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Country country = new Country();
				country.setId(rs.getInt(1));
				country.setName(rs.getString(2));
				return country;
			}
		});	
    }

The following is how its invoked.

    public void readResultSet() {
        Map result = procedureName.execute();
        System.out.println(result.get("countries"));
    }

Parsing Multiple Result sets

Define the stored procedure call like this:

    @Autowired
    @Required
    public void setDataSource(DataSource dataSource) {
        String procedureWithMultipleRS = "QUALIFIER.PROCNAME";
 
        procedureWithTwoResultSet = new SimpleJdbcCall(dataSource)
        .withoutProcedureColumnMetaDataAccess()
        .withProcedureName(procedureWithMultipleRS)
        .declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))
        .declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR))
        .returningResultSet("result1", new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                return rs.getString(1);
            }
        })
        .returningResultSet("result2", new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                return rs.getString(1);
            }
        });
    }

The following is how its invoked.

    public void readMultipleResultSets() {
        Map result = procedureWithTwoResultSet.execute();
        System.out.println("result 1: ");
        System.out.println(result.get("result1"));
 
        System.out.println("result 2:");
        System.out.println(result.get("result2"));
    }

Complete Source

src/main/java/com/test/CountryDataManagerImpl.java

package com.test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

@Repository("countryDataManager")
public class CountryDataManagerImpl {
	
	private SimpleJdbcCall countryProcedure;
	private SimpleJdbcCall procedureWithTwoResultSet;
	
	@Autowired
	@Required
	public void setDataSource(DataSource dataSource) {
		String procedureName = "QUALIFIER.PROCNAME";
		String procedureWithMultipleRS = "QUALIFIER.PROCNAME";
		
		countryProcedure = new SimpleJdbcCall(dataSource)
		.withoutProcedureColumnMetaDataAccess()
		.withProcedureName(procedureName)
		.declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))
		.declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR))
		.returningResultSet("countries", new ParameterizedRowMapper<Country>() {
			public Country mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Country country = new Country();
				country.setId(rs.getInt(1));
				country.setName(rs.getString(2));
				return country;
			}
		});		
		procedureWithTwoResultSet = new SimpleJdbcCall(dataSource)
		.withoutProcedureColumnMetaDataAccess()
		.withProcedureName(procedureWithMultipleRS)
		.declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))
		.declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR))
		.returningResultSet("result1", new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				return rs.getString(1);			
			}
		})		
		.returningResultSet("result2", new ParameterizedRowMapper<String>() {
			public String mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				return rs.getString(1);			
			}
		});		
	}
	
	public void readOutParameters() {
		Map result = countryProcedure.execute();
		System.out.println("RETURNCODE: " + result.get("RETURNCODE"));
		System.out.println("RETURNMSG: " + result.get("RETURNMSG"));
	}
	
	public void readResultSet() {
		Map result = countryProcedure.execute();	
		System.out.println(result.get("countries"));
	}
	
	public void readMultipleResultSets() {
		Map result = procedureWithTwoResultSet.execute();
		System.out.println("result 1: ");
		System.out.println(result.get("result1"));
		
		System.out.println("result 2:");
		System.out.println(result.get("result2"));
	}
	
	public static void main(String args[]) {
		ApplicationContext context = new ClassPathXmlApplicationContext(
				new String[] { "applicationContext.xml" });
		CountryDataManagerImpl countryDataManager = (CountryDataManagerImpl) context
				.getBean("countryDataManager");
		countryDataManager.readOutParameters();
		countryDataManager.readResultSet();
		countryDataManager.readMultipleResultSets();
		
	}
}

src/main/java/com/test/Country.java

package com.test;

public class Country {
	private Integer id;
	private String name;
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Country [name=" + name + "]";
	}
}
Advertisements

2 Responses to “Calling Stored Procedures using Spring 2.5 SimpleJdbcCall”


  1. 1 Jose
    April 18, 2013 at 4:53 pm

    Hi… can you help me with a question? …. how can I call a SP with Inputs and outputs? thanks..! :)

  2. August 11, 2016 at 12:24 pm

    Wondering what the stored procedure QUALIFIER.PROCNAME looked like to return 2 result sets? Was it just 2 separate selects? Otherwise great example. Thank-you!


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 77 other followers

May 2010
S M T W T F S
« Apr   Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Blog Stats

  • 830,821 hits

%d bloggers like this: