SPWrapper

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)
   • Postgresql
   • MySql
   • SqlServer

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.)

Example

set CLASSPATH="c:\my\driver\jdbc.jar"
java.exe -cp spwrapper.jar;%CLASSPATH% net.sourceforge.spwrapper.ui.CLI 
	-u username -p password
	-c "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
			(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

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 
begin 
	i:=i*9; 
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
	</fail>
</target>

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

<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" />
</spwrapper>

</target>
</project>
				

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 class ANTTASKTEST_WRAPPER {

	public ANTTASKTEST_WRAPPER(String connectionString, 
		Properties properties) 
	throws SQLException {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");

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

	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);
			ex.initCause(e);
			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) { 
				statement.setNull(1,Types.DECIMAL);
				} else { statement.setBigDecimal(1,I); }
                
		
			statement.registerOutParameter(1,Types.DECIMAL);//,I_scale);
		executeResult = statement.execute();
			I=statement.getBigDecimal(1);//,I_scale);
				I_wasNull=statement.wasNull();
                
		
        
				
				if (!executeResult) {
					updateCount=statement.getUpdateCount();
				} else {
					resultSet = statement.getResultSet();
				}
				handleComplexResults();
							
			} finally {
			if (statementAutoclose && statement!=null) statement.close();
			if (autoclose) 
				connection.close();
			}
		}
	
	
	
	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.

This table describes override nested element parameters

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

How to use the wrapper

Here is an example of how to use the generated code

...
ANTTASKTEST_WRAPPER w = new ANTTASKTEST_WRAPPER("jdbc/testconnection");
w.set_I(new BigDecimal(9));
w.ANTTASKTEST();
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

...
ANTTASKTEST_WRAPPER w = new ANTTASKTEST_WRAPPER("jdbc/testconnection");
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.

Callbacks

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.

Overloading

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

The java class members

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.

Postrgesql

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

Mysql

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.

SqlServer

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: