Do you ever needed to use stored procedures from java code? Perhaps you had to write first the stored procedure, then you had to write that boring java code with all those register parameters and getInt/getString etc.

Then perhaps you needed to change some parameters and had to check and correct the java code. And what about double checking that the connection was closed and the exception catched in the right way?

I had to do it a lot of times... then I realized that once you have written and compiled the stored procedure in you database all the needed information about parameters is available. A program could read this information and write for me the needed java code.

SPWrapper is the result of this idea. Currently works with Oracle, and some of its features are available also for Postgresql, Mysql and SqlServer.

Table of Contents

  1. How to use SPWrapper
    1. Netbeans plugin
    2. Command line interface
    3. Before Using Ant
    4. What SPWrapper does for you
    5. Ant file example
    6. Task Parametes
    7. How to use the wrapper
  2. Extending wrapper classes
  3. Custom Type Mappings
  4. Callbacks
  5. Overloading
  6. Where to find examples
  7. What are Tanslator an Introspectors?
  8. Resultset Narrowing
  9. Notes about other dbs (non Oracle)

How to use SPWrapper

Since release 0.3 you can use this program in two ways, with the command line interface or using it through ANT scripts. With a later release (0.5.3) a plugin for Netbeans has been introduced; this plugin allows to create the wrappers easily through a GUI interface, and creates Ant scripts to automatize wrapper rebuild.

SPWrapper plugin for Netbeans

If you use Netbeans as development environment you can download a plugin that will allow you to quickly create wrappers. You can find a complete tutorial on how to install and use the plugin at NBSPWPlugin documentation page

Command line interface

To use SPWrapper's command line interface you need to download spwrapper.jar and the database driver.

Then follow these steps:

  • Set up the classpath to include the JDBC driver
  • Invoke SPWrapper jar with all the needed parameters (connection string, username, stored procedure name, etc.)


set CLASSPATH="c:\my\driver\jdbc.jar"
java.exe -cp spwrapper.jar;%CLASSPATH% net.sourceforge.spwrapper.ui.CLI 
	-u username -p password
			(HOST = localhost)(PORT = 1521)))(CONNECT_DATA =(SID = orcl)))" 
	-sp storedprocedurename -pk my.db.pkg
	-d c:\src\top-directory

With Oracle only you can alternatively copy the database driver (ojdbc14.jar) in the same directory that contains spwrapper.jar and run:

java -jar spwrapper.jar -c "jdbc:oracle:thin:@(...)" -u username -p password
	-sp storedprocedurename -pk my.db.pkg -d c:\src\top-directory

In this case no classpath setup is needed since spwrapper.jar contains a reference to the driver (./ojdbc14.jar) in its manifest. Do not forget to quote the connection string, otherwise you can get errors.

Spwrapper connects to the given database and retives storedprocedurename parameter, then write into c:\src\top-directory a java source that allows you to quickly call the store procedure. The source is placed in the package my.db.pkg.

Using oracle the stored procedure name must be UPPERCASE. See later for more details on procedure names conventions.

In a real project is perhaps better to use Ant scripts to build and rebuild stored procedure wrappers when needed. In the following paragraphs you will find how to do that and other information about SPWrapper parameters and arguments.

Command Line Arguments

-cJDBC connection string to the database yes
-udatabase usernameyes
-pdatabase passwordyes
-spStored procedure name. See later for further details on syntaxyes
-pkThe java package which will belong the generated wrapper yes
-dTop java source directory where wrappers will be written yes
-nDesired java wrapper class name. If it is not provided the class name will be obtained from the stored procedure name.no
-m <param-name>=<datatype>Use -m to override default datypes mappings for stored procedure parameters. See later for details.no
-sj <sql-struct-type>=<java.class.name> Use -sj to provide custom datatype mapping when dealing with sql objects, structs or array. see Custom type mappings for detailsno

Before Using Ant

To use SPWrapper you need to download Ant and a JDBC driver. Please use Ant version 1.6.5 or later. The jdbc driver for Oracle is located into $ORACLEHOME/jdbc/lib, the right jar is ojdbc14.jar. The other databases usually requires you to download additional packages.

What SPWrapper does for you

SPWrapper provides an ant task, you can use it to generate wrapper classes to call stored procedures. You need only to provide the name of the stored procedure you want to invoke, and SPWrapper will deduce the procedure arguments looking into the database system tables. Then it will write out the necessary java code to call the stored and retrieve the output.

Currently SPWrapper supports many SQL data types. Since the supported list is database dependent you should look at this reference table

Ant file example

Suppose that you would like to invoke this stored procedure

create or replace procedure anttasktest(i in out integer) is 
end anttasktest;

You could use this ant file to write out the Java code you need. Maybe it is not the shortest example file, but it contains useful checks and variable definitions.

<?xml version="1.0" encoding="UTF-8"?>
<project name="performTests" default="all" basedir=".">
<property environment="env" /> <!-- 1 -->  
<property name="oracle-conn-string" value="jdbc:oracle:thin:@(DESCRIPTION=bla bla bla" />
<property name="oracle-user" value="test" />
<property name="oracle-password" value="secret" />
<property name="build-test" value="${basedir}" />		

<target name="env-check"> <!-- 3 --> 
	<fail unless="env.ORACLE_HOME">
		Missing ORACLE_HOME evironment variable cannot locate jdbc driver

<path id="execute-test-path"> <!-- 4 --> 
	<pathelement location="/where/you/keep/spwrapper.jar" />
	<pathelement location="${env.ORACLE_HOME}/jdbc/lib/ojdbc14.jar" />

<target name="all" depends="env-check">

<!-- 5 --> 
<taskdef name="spwrapper" classname="net.sourceforge.spwrapper.ant.SPWrapperTask" 
	classpathref="execute-test-path" />

<!-- 6 --> 
<spwrapper connectionstring="${oracle-conn-string}" password="${oracle-password}" 
	username="${oracle-user}" javapackage="net.sourceforge.spwrapper.test.ant" 
	procedurename="ANTTASKTEST" targetdirectory="${build-test}">
	<override paramname="I" sqltype="DECIMAL" />


Here follows a description of what the script does. Look at comment numbers to find the interesting steps.

At step 1 execution environment is sourced in the script. This is done to get the ORACLE_HOME environment variable, which will later be used to locate the JDBC driver.

At step 2 some necessary properties get defined. build-test represents where output sources will be written. Example: suppose your source are in c:\mysources and you what that generated wrappers belong to the package com.myproject.wrappers: simply assign to build-test the value c:\mysources. This directory must exist when you run the test, the subdirectory needed to respect java packaging specification will be created on the fly.

At step 3 we checks for a correct enviroment. Then at step 4 we define the correct classpath for SPWrapper. Remember to put both jdbc driver jars and SPWrapper.jar.

At step 5 we let ant know of a new tast called spwrapper

Finally at step 6 we invoke spwrapper. Notice that with the override nested element it is possible to force spwrapper to handle the "I" parameter as a Decimal and not a Java int.

Before goin on with spwrapper parameters meaning let's look at the resulting java output

package net.sourceforge.spwrapper.test.ant;

import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.naming.*;
import java.util.logging.*;
import net.sourceforge.spwrapper.OutputMode;
import java.io.*;

	import oracle.jdbc.*;
import oracle.sql.*;



	public ANTTASKTEST_WRAPPER(String connectionString, 
		Properties properties) 
	throws SQLException {
		try {

		connection = DriverManager.getConnection(connectionString, properties);
		} catch (SQLException e) {
				"Unable to open connection "+connectionString,e);
			throw e;
		} catch (ClassNotFoundException ne) {
			String message ="unable to load OracleDriver jdbc driver";
			SQLException te =new SQLException(message);
			throw te;
	public ANTTASKTEST_WRAPPER(Connection connection) throws SQLException {
		if (connection==null || connection.isClosed())
			throw new SQLException("Bad Connection State");

	public ANTTASKTEST_WRAPPER(String jndiDataSource) throws SQLException{
		try {
		if (dataSource==null) {
			InitialContext i = new InitialContext();
			dataSource = (DataSource) i.lookup(jndiDataSource);
		connection = dataSource.getConnection();
		} catch (Exception e) {
			log.log(Level.SEVERE,"Error opening datasource "+jndiDataSource,e);
			SQLException ex = new SQLException("Error opening datasource "+jndiDataSource);
			throw ex;
	protected boolean I_setNull=false;
				public void set_I_null(boolean b) { I_setNull = b; }
				protected java.math.BigDecimal I;
				public void set_I(java.math.BigDecimal in) { I = in; }
				protected boolean I_wasNull=false;
                                public boolean was_I_null() { return I_wasNull; }
                                public java.math.BigDecimal get_I() { return I; }
			/*int I_scale=10;
			public void set_I_scale(int scale) { I_scale=scale; }*/
		public void ANTTASKTEST() throws SQLException {
			try {
				resultSet = null;
				statement = connection.prepareCall("{ call ANTTASKTEST(?) }");
			if (I_setNull) { 
				} else { statement.setBigDecimal(1,I); }
		executeResult = statement.execute();
				if (!executeResult) {
				} else {
					resultSet = statement.getResultSet();
			} finally {
			if (statementAutoclose && statement!=null) statement.close();
			if (autoclose) 
	protected void handleComplexResults() throws SQLException {	}
	protected CallableStatement statement=null;
	protected boolean executeResult=false;
	protected int updateCount=-1;
	protected ResultSet resultSet = null;
	private Logger log = Logger.getLogger(ANTTASKTEST_WRAPPER.class.getName());
	protected boolean autoclose=true; //connection
	protected boolean statementAutoclose=true; //set to false to read oracle bfiles

	public boolean isAutoclose() {
		return autoclose;

	public void setAutoclose(boolean autoclose) {
		this.autoclose = autoclose;

	public void setStatementAutoclose(boolean b) { statementAutoclose=b;  }
	public boolean isStatementAutoclose() { return statementAutoclose; }
	protected Connection connection=null;
	public Connection getConnection() { return connection; }
	protected DataSource dataSource=null;
	public boolean isExecuteResult() {
		return executeResult;

	public ResultSet getResultSet() {
		return resultSet;
	public CallableStatement getStatement() {
		return statement;

	public int getUpdateCount() {
		return updateCount;


The written class has three contructor:

  • public ANTTASKTEST_WRAPPER(String connectionString, Properties properties)
  • public ANTTASKTEST_WRAPPER(Connection connection)
  • public ANTTASKTEST_WRAPPER(String jndiDataSource)

In your programs invoke the first if you are writing a stand-alone application and you need to register jdbc drivers. Use the second if you already have an existing database connection but remember to call setAutoclose(false) if the connection must remain opened after the stored procedure invocation. Use the third if you are writing a web application and you have a jndi context available. Read an important note about JNDI connections in SqlJaTor documentation

Usually the wrappers will close their db connection just after invoking the stored procedure. You can use the method setAutoclose to change this behaviour.

SPWrapper supports in, out and in out parameters. For each parameter spwraper writes for you some code that allow to:

  • set the parameter to null - input parameters
  • set the parameter value - input parameters
  • retrieve the parameter result - output parameters
  • detect if the returned value was null - output parameters

The method ANTTASKTEST allows you to invoke the stored procedure.

Task Parametes

This table contains an explanation of spwrapper task parameters. Every parameter is mandatory except javaclassname.

procedurenameThe name of the stored procedure whose wrapper will be generated. Note that the name must be case sensitive equal to database system table content; with Oracle use UPPERCASE names. You can generate wrapper for both functions and procedures, outside or inside a package. To separate package names from procedure names use the character '|', eg. Strange.package.with.dotted.name|myprocedure. Actually spwrapper will look for procedures only within user objects, noone has written any complaints about this behaviour ;)
javaclassnameThis optional parameter allows to specify the desired class name for the wrapper. If it is not specified the name will be generated appending _WRAPPER to the stored procedure name
targetdirectoryWhere sources will be written. SPWrapper will create subdirectories on the fly to comply with java specifications, so assign to targetdirectory your sources root directory
javapackageThe package that generated wrappers will belong to
connectionstringConnection string to the database stored procedure resides in
usernameDatabase connection username
passwordDatabase connection password

This table describes override nested element parameters

paramnameThe name of the parameter whose this override rule refers. Must match db system tables content, so with Oracle user UPPERCASE names. Functions return parameter usually has no name, so use ret for it.
sqltypeThe type you want to map this parameter to. Refer to supported data types for a list of valid values (all UPPERCASE).

This table describes the typeMapping nested element parameters. See Custom Type Mappings for details.

sqltypeThe custom database type that should be mapped to a java type. Must match db system tables content, so with Oracle user UPPERCASE names.
javatypeThis must be the fully qualified name of a java class that implements the java.sql.SQLData interface.

How to use the wrapper

Here is an example of how to use the generated code

w.set_I(new BigDecimal(9));
if (! w.was_I_null()) {
	BigDecimal result = w.get_I();

Since version 0.5 SPWrapper writes out also a method whose arguments are the input and input/output arguments of the stored procedure. Using this method you can avoid calling the set_<arg> call before running the stored procedure.

The input arguments will be used according to these rules

  • if the argument is not an object its walue will be assigned to the stored procedure as is
  • if the argument is an object and its value is null then the set_<arg>_null procedure will be invoked, otherwise the value will be set to the stored procedure argument

According to these rules, the call example can be rewritten as follows

w.ANTTASKTEST(new BigDecimal(9));
if (! w.was_I_null()) {
	BigDecimal result = w.get_I();

Extending wrapper classes

Do not modify the source code written by SPWrapper, extend that class instead and override the methods that you need to modify. In this way your changes will never been overwritten accidentally during project rebuild, and you can easily take advantage of new functionalities provided by new SPWrapper versions.

You want to extend the wrapper code in these cases:

  • You need process return arguments as they are read from the resultset. This happens when reading object from arrays, processing BLOB and CLOB etc.
  • You want to use custom data type mappings
  • On some databases you need to handle complex results such as multiple result sets returned by stored procedures.
  • Yow whant to define you own custom constructors, such as constructors that reads connection parameter from your own configuration classes.

Custom Type Mappings

This functionality has been developed for Oracle: since only standard jdbc code has been used it should work also for the other databases, but no tests have been done about this.

It is possible to return custom data types from stored procedures, such as Objects or Structs. These types can be returned to java programs in a generic, hashtable like, structures or using custom java objects.

To receive or send java classes to store procedure you must implement the java.sql.SQLData interface. This interface allows to serialize and deserialize object from sql data stream

Once you have written custom java classes you have to tell SPWrapper wich classes maps to which sql custom data type. There are various ways to do this:

  • using the ant task nested element <typeMapping>
  • using command line interface -sj parameter
  • using the addTypeMap wrapper method. Call this method for each custom mapping you need before executing the stored procedure call.
  • setting a pre-existing type map with the setTypeMap before executing the stored procedure.
  • override the wrapper class and implement the fillTypeMap method. This is what the ant task and command line interface do; if you need to use both the tecniques remember to call super.fillTypeMap.
  • setting the type map in the connection before passing it to wrapper class constructor

Whichever is the mode you chose you have to provide the sql data type name - remember all uppercase with Oracle - and the name of your class.


SPWrappers prepares some callback functions to intercept these events:

  1. an array argument has been read from the result set
  2. clob and blobs are read from the result set
  3. The first result set has been read and it is possible to use it or to iterate over the remaining result set - useful with sqlserver.

In the cases 1 and 2 for each output argument is created a callback mathod called callback_ARGUMENTNAME. You can extend the wrapper and implement these methods as seen in ArrayCallback.java in the oracle test directory.

For the last case - override the method handleComplexResults. This release - 0.8 - introduced a change in the callback order. Since sql server requires to read first all the result set before reading the output parameters, the handleComplexResults now is fired before all the other callback. No bug was detected in the test case, if you have a problem about this change please report it on sourceforge's tracker.

Where to find examples

If you need some examples you can look at the test directory. You can find examples of type mappings for each supported datatype. Each java test case has a companion xml file containing the needed sql code to run the test.


This functionality is currently available only to Oracle users.

Overloading is a language functionality that allows to declare many function or procedures with different argument types. Overloading is useful when it is necessary to write many similar procedure that deals with different datatypes.

The name of the procedure plus the list of its arguments is called signature

To translate overloaded stored procedures SPWrapper loads from the database the complete list of signatures associated to that procedure name.

Since usually there is only one signature, SPWrapper uses the procedure name as java method name. When there are many signatures the first one is treathed as usual, the others are subjected to renaming.

This happens because it is not always possible to map a sql overloaded procedure to a java overloaded procedure. For instance my_proc(i out integer) and my_proc(i out integer, j out integer) are valid sql procedure signatures, but java does not allow this kind of overloading.

When it is possible to map the overloading to java, the java method has the same name of the procedure. If this is not possible the method name is obtained appending the list of arguments types (with their direction) to the original name.

As you may have noticed SPWrapper writes one or two java methods for each sql signature. There is always a method with no arguments, this is done because there are datatypes, such as Date, that can be set whith different methods (setting a calendar or a date).

To avoid java overloading errors these void argument methods are all but the first one renamed appending the argument types an directions.

A similar operation is done with the procedure arguments: SPWrapper includes in the java class one member for each sql argument. To avoid duplications if many signatures contains the same argument only one variable is generated. If the same argument name is used with different sql types, the java member are made unique appending the sql type to the original name.

This test case is useful to understand what happens. These are the sql stored procedure to wrap in java:

create or replace package test_overload is

  -- Author  : GIOVANNI
  -- Created : 08/01/2006 16.26.24
  -- Purpose : test overload features of spwrapper
  -- the parameter second is used as in and out, and must be translated as io
  -- the parameter first is of different types, three types must be generated
  -- two overload that differs only in output types exists
  procedure test_overload(first in out integer);
  procedure test_overload(first in out date);
  procedure test_overload(first in out integer, second out date);
  procedure test_overload(first out varchar2);
  procedure test_overload(first out varchar2, third out integer);
  procedure test_overload(first out varchar2, second in date);
end test_overload;

When translating test_overload SPWrapper writes out one single java class with many methods, to make it possible to invoke all the signatures

Produced java methods

TEST_OVERLOAD(int arg_FIRST)first signature, use this if do not need to set the argument to null
TEST_OVERLOAD()first signature implementation. Call this if you need to set null the int argument.
TEST_OVERLOAD(java.sql.Date arg_FIRST_DATE)second signature can be transalted in java since differs from the previous
TEST_OVERLOAD__IO_DATE()Second signature implementation
TEST_OVERLOAD__IO_INTEGER_O_DATE(int arg_FIRST) this cannot be translated as TEST_OVERLOAD because there would be a java overloading conflict.
TEST_OVERLOAD__IO_INTEGER_O_DATE()third signature implementation as you can see the suffix is the same as the previous one.
TEST_OVERLOAD__O_VARCHAR2()Output only signature would conflict with first signature implementation.
TEST_OVERLOAD__O_VARCHAR2_O_INTEGER()Two output arguments, another non java mappable signature.
TEST_OVERLOAD__O_VARCHAR2_I_DATE(java.sql.Date arg_SECOND) another conflict

The java class members

protected int FIRSTfirst signature argument
protected java.sql.Date FIRST_DATESecond signature: date
protected java.lang.String FIRST_VARCHAR2only getter methods written for this argument, since is used only as return argument
protected java.sql.Date SECONDIt is used as input and output in different signatures, SPWrapper generates both setter and getter but uses only one member.
protected int THIRD

What are Tanslator an Introspectors?

SPWrapper components are divided into two pieces: translators and introspectors.

A translator is responsible of writing out the wrapper java code. It is generic and its output can be easily modified changing some xml configuration files. SqlJaTor and SPWrapper are two translators.

Introspectors are responsible of reading the database system table and discover which procedures/packages are there and what are their parameters.

Refer to Support new Databases for more inforation about Introspectors

Resultset Narrowing

Some application server registers data sources with their own wrapping pool to implement their own pool management system. Unfortunately having a wrapped class instead of a driver specific class make impossible to call driver specific features, this is especially true for database specific types.

Jboss is one of these Application server, since version 0.5.4 wrapper generated are able to identify JBoss wrapped CallableStatement and obtain a driver specific one instead: the narrowStatement member.

Since version 0.6 also Tomcat is supported.

Introspector programmer should always use narrowStatement when they need driver specific features when mapping datatypes.

Sometimes it is possible that you obtain a class cast exception inside the narrowStatement method: this happened to me both with Jboss and Tomcat when I left the ojdbc.jar jdbc driver both into application server lib directory and WEB-INF/lib. Removing the WEB-INF/lib copy solves the problem.

This happens because the pooling classes are loaded from the application server classloader while the cast is checked against the web application class loader: a cast exception is detected but the code is correct. In this situation some return parameters cannot be extracted and you will probably also get some NullPointerException.

Notes about other database support

This project currently contains two Introspectors, one tailored for Oracle and another one that relies on JDBC metadata description facility. This explain why some functionalities are available only for Oracle.

The standard JDBC functions allows to query a database asking which stored procedures are there and what are their parameters. Unfortunately these functions are not able to:

  • organize procedures and functions into packages
  • Describe overloaded function signatures
  • Always show output parameters types and names

Unfortunately this makes impossible to implement all the features available to Oracle without writing database specific routines.

The following paragraphs describes what was possible to achieve with every database, using JDBC metadata detection.


In the package net.sourceforge.spwrapper.test.postgresql you will find some examples about stored procedure wrapping with Postgres. The SQL code is included into BasicStoredProceduresTest.xml

The jdbc driver unfortunately returns "$1", "$2" etc. as parameter names, this is bad because if you whant to have meaningful parameter you have to provide manually the needed configuration.

Moreover since $n is not a valid name for java, all these parameters are renamed as _dollar_n as you can guess in spwrapper_add_Wrapper class.

But the very bad news concerns procedures that have more than one output parameter. In these cases Postgresql refuses to describe them and only returns one parameter of RECORD type. Due to this lack of description this version of spwrapper is unable to work with these functions


With mysql everithing seems working fine, input, output and input output seems to be handled correctly, and the jdbc driver returns the correct parameter name, so the code is properly generated.

Procedures that returns result sets can be wrapped: by the way the result set is currently returned as is. You have to write yourself the code needed to access the resultset columns. Unfortunately no metadata is available for the result set using the JDBC functions, so is not possible to generate the accessor for you.

You can find some examples in the net.sourceforge.spwrapper.test.mysql.BasicStoredTest class.


This database has some quite surprising behaviours. First of all parameter name have an "@" prepended; this character gets removed from the java sources generated. Another uncommon behaviour is that every ouput parameter is really an INOUT parameter, so you will find some unexpected setter methods generated in the code.

Sql server procedures always returns also a status parameter, which is accessible trough the get_ret method.

It is common for T-SQL procedures to return many result sets as response. If you want to access these records you have to override the handleComplexResults of the generated class, look at MultiselectWrapper example in the net.sourceforge.spwrapper.test.sqlserver package. As usual since the record structure is not available through the jdbc standar function it is not possible to access it and create the accessor for you.

SqlServer provides also Table Functions, funtions that returns tables. If you try to wrap these functions you will get errors, because the jdbc driver describes the output of these function as an output parameter of type NULL. Instead of wrapping them with SPWrapper use SqlJaTor. This is the statement you are looking for:

select * from my_function_table()