SPWrapper Plugin for Netbeans

This page provides a step by step tutorial on how to create your first project with SPWrapper and Netbeans.

This tutorial has been written at the beginning of the project, therefore it always refers to Oracle. Now (version 0.8) SPWrapper has been modified and starts supporting other databases. To try it just use your jdbc driver instead of the Oracle one.

Installing the software

First of all you need to install Netbeans version 5.0 and Java version 1.5; Netbeans 5.5 with Jdk 1.6 is fine too. You can download them together at netbeans home site.

After installing Netbeans it is a nice idea (but is not mandatory) to run Tools -> Update Center... from the main menu, to update some components. If you plan to work with spwrapper sources you must install Module development environment version 5.0 update 1.

Then download SPWrapper plugin from sourceforge project page. The binary distribution contains spwrapper.jar and two netbeans modules, extract them from the archive.

Run Netbeans and from the main menu choose Tools -> Module Manager, then click Update... button. You will see the following dialog box

Choose the Install Manually Downloaded Modules radio button, and click Next>. Now fill in the new dialog box choosing the two netbeans modules, as follows.

Then click next and select the module in the upper right corner of the following dialog box.

Click next, the download dialog box appears. Click next again.

Click the Include checkbox; suddenly a certificate key confirmation appears. Check the values with those in the image, accept and click Finish.

The image reports certificates information for the first release.

Now if everything is correct, opening the Database category you should see SPWrapper.

Setting up the database connection

Close the module manager and open the Runtime palette selecting from the main menu Windows -> Runtime. Expand the Databases tree, and right click on the Drivers node. Choose Add Drivers...

Fill in the dialog box as described in this image. You will find the jdbc Oracle driver in your database installation under the jdbc\lib directory. You can also download the driver from the oracle site.

Expand Drivers and right click on Oracle, choose Connect Using...

The above picture is an example of how you should fill in the form, you need an username, a password, the SID host and port of the database. If everything is correct in the runtime palette the new connection will appear. Expand it, you should see your stored procedures, and tables.

Setting up a new project

Now that database connection is checked it is possible to start working with a Java project. From the main menu chose File -> New Project and chose a new Java Application. Fill in the forms as in the following pictures:

A new project appears in Netbeans' project palette.

Before start working on stored procedures we need to add the required jar libraries to the project. Switch to the Files palette and create a new folder under SPWRapperTest (right click on SPWrapperTest and chose new folder)

Copy to the newly created folder the jdbc driver ojdbc14.jar and spwrapper's jar spwrapper.jar. Then switch back to Projects palette, right click SPWrapperTest and chose Properties. Fill in the form as the picture shows.

We will now create a simple stored procedure that sums two numbers and then we will create and use the wrapper.

Creating the wrapper

To add the following stored procedure to your database, switch to the Runtime palette, right click your database connection and choose Execute Command.... Then copy and paste the code and press the Execute button (Ctrl+Shift+E)

create or replace function sum_arguments(a in integer, 
					b in integer) return integer is
  Result integer;
begin
  Result:=a+b;
  return(Result);
end sum_arguments;
            

If you browse you procedures you will see "SUM_ARGUMENTS" appearing (perhaps you need to refresh the view).

Go back to the Projects palette, right click spwrappertest package and chose New File/Folder... then chose Stored Procedure Wrapper in the Java Classes category.

Then chose the just created stored procedure and click next

Fill in the required java class name field, then press next.

SPWrapper has detected the procedure parameters. If you are not satisfied with guessed types you can change them; pressing the help button shows an explanation on how to do this. You can read on supported datatypes in spwrapper documentation

If you need to map special SQL datatypes using java.sql.SQLData interface you should fill this new form. Do not be impatient while you type the class name, after the third key pressed starts a search for the classes that implement SQLData interface amoung your sources and netbeans known sources, so do not be impatient.

We do not need this feature for this tutorial, so leave the form blank and press next.

A simple message tells you that some files will be added to your project

Working with the sources

Three files will appear when you click the finish button: Wrapper.java, Wrapper.spw and buid-impl-spw. As you can immagine Wrapper.java is the java source code that allows you to call the stored procedure.

Wrapper.spw and buid-impl-spw are two ant build script. The former stores the input you provided whithin the wizard, and running it you are able to rebuild the java wrapper. The latter is a master ant script able to rebuild all the wrappers found in the project. Further explanation will be provided at the end of this tutorial.

Open the Wrapper.java file: if you see import error in it you forgot to add the required libraries to the project. Do it and recreate the wrapper.

Build the project (F11), there should be no errors.

Using the wrapper

Now the wraper is ready to be used. Replace the Main.java source code with the following. Make sure to change username, password and connection string.

Run Main.java, you should see the message "5+3=8" appearing in the output window.

package spwrappertest;

import java.sql.*;
import java.util.Properties;

public class Main {

    /** Creates a new instance of Main */
    public Main() {
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws Exception {
        Properties params = new Properties();
        params.setProperty("user","gio");
        params.setProperty("password","facile");
        Wrapper w = new Wrapper("jdbc:oracle:thin:@localhost:1521:orcl",params);
        System.out.print("5+3=");
        System.out.println(w.SUM_ARGUMENTS(5,3));
    }

}
            

Ant makefiles and wrapper rebuild process

As seen before two Ant file where produced.

Open Wrapper.sqw and read it; as you can see the the library paths are relative to the project directory, so it is easy to put these files in CVS and share them with other developers.

If you need you can edit directly spw files, so you can change previous choices without running again the creation wizard.

The build-imp-spw.xml file is located in the nbproject folder, you can edit it if you want to customize stored procedure rebuild process.

For your convenience a main menu action Build -> Rebuild Stored Procs as been added. Selecting it actually runs build-imp-spw.xml that in turn rebuild all wrappers in your project.

That's all!

Please provide me a feedback writing to bricconi <at> netscape.net