What is SPWrapper?

This project aims at developing a set of tools to generate source code to execute SQL statements and stored procedures.

Its majour features are:

• Works only at compile time, you add it to your Ant build scripts or run it from a GUI: it then creates source code for you. There is no runtime penalty while your application runs.
• Multilanguage support has just been introduced! Now you can also generate Python sources for cx_Oracle and pgsql.
• Leverages your database SQL interpreter an your JDBC driver capability to discover the correct mapping for input and output parameters, decreasing the amount of configuration to be written.
• It is possible to tailor, database by database and language by language, the supported datatypes.
• It does not requires changes to be introduced into existing projects.

Project components

• SqlJaTor. Given a SQL statement such as an INSERT, SELECT, or database specific statements such as MERGE, it creates a Java class able to execute it. The constants such as numbers, strings and dates contained in the sample are converted into input parameters. The result set columns are bound to output parameters; the resultset rows are accessible through an iterator. Currently works with Oracle, Postgresql, Mysql and Sqlserver. This component now supports also Python with Oracle or Postgresql, other databases will come soon.
• SPWrapper itself is the first tool developed. Given a stored procedure name it creates a Java class with all the logic needed to pass in and read out the needed parameters. Originally designed for Oracle now starts working also with the other databases. It currently creates only java sources.
• NB-SPWPlugin is a Netbeans 6 plugin that allows to invoke SPWrapper and SqlJaTor from Netbeans IDE. It creates both the java code and ant scripts needed to rebuild the java code when needed. A previous version exists for Netbeans 5.5.

Supported environments

This software is compiled with JDK 1.5, it is not possbile to compile it with previous java versions.

The stored procedure wrapper generator works with Oracle, and with less features also on Postgresql, Mysql and Sqlserver.

SqlJaTor currently works with four databases: Oracle, Postgresql, Mysql and SqlServer.

The generated Java classes can be used in Java applications, libraries or inside application servers. Special care has been taken to support Tomcat and Jboss connection pooling. If you use other servlet engines probably some extra care is needed for the JNDI related code.

Oracle is the first database supported. Specific code has been written to support stored procedure oveloading and Oracle specific datatypes.

The support for three other databases (Postgresql, Mysql and SqlServer) has just been introduced from release 0.7. Currently only type alias has been introduced, very specific types are not available.

Python support has been checked with version 2.5.

Add or modify database support

It is possible to allow SqlJaTor and SPWrapper to work with new databases writing some configuration files. If you are interested to work with a new database or to support a specific datatypes you could read the Add Database Support tutorial.

Don't forget to send us your contibution to make it available to everybody!

Planned next steps

There are a lot of things that can be done; these are those we plan to develop first:

• Make netbeans able to create python sources.
• Add PHP+Mysql support.
• Extend the supported datype list.