On Java Development

All things related to Java development, from the perspective of a caveman.

Using Spring to call a Stored Procedure

without comments


This post shows how to leverage Spring to call a stored procedure to retrieve a result set or update records in a file. Spring makes the process simple enough so that the developer is focused more on the parameters to pass (if any) and calling the methods of the project’s Service and DAO layer.

The Service Method
The method below is taken from a service class that calls the stored procedure named RATINCS that resides on the iSeries.

The stored procedure is designed to retrieve records from file PSLMPOL1. It accepts parameters for Company, Block and Policy which are injected onto the class RATEINSTStoredProcParms designed to hold them. This class and the parameters will be passed to the method of the DAO, named RATEINSTStoredProc.

The method takes steps necessary for determining which stored procedure to call; the one in production library QGPL which explicitly references the production library in which the file resides or the one in the test library TESTLIB, which explicitly references the test library in which the file resides. As can be seen, it does this by pulling the variable named runtimeEnvironment from the bean defined in Spring’s context. The logic then builds the string containing the stored procedure name qualified with the correct library.

With those steps out of the way, the method calls the DAO’s method which then calls the stored procedure, passing the parameters contained within RATEINSTStoredProcParms.

*Caveat* Care should be taken when calling the stored procedures from QGPL. The RPG programs in the production libraries explicitly name the libraries for the files used and therefore cannot rely upon the library list of the production server’s datasource to resolve the location of the files. When developing in the test environment it is unwise to include QGPL in the datasource that is defined by the project’s configuration file. To mitigate the problem of hard-coded library names, the RPG program should be copied to the test library and modified to strip out the library names. Likewise, the SQL script used to create the stored procedure should be copied to the test source file in the test library and modified to explicitly call the RPG program in the test library. Doing so forces the RPG programs to use the library list defined by the datasource (which does not include QGPL for the test environment) to find the test files. This is important if the stored procedure updates records! The datasource for the production server includes QGPL in its library list.

The DAO Method

Below is the full listing of the DAO class named RATEINSTStoredProc . Note that this class extends the StoredProcedure class of Spring’s framework package. Also note how the constructor method accepts the datasource name for the proper connection, i.e. PROD or TEST as well as the name of the stored procedure. The qualified name of the procedure was built by the service class as described above. The constructor also defines the names of the parameters to which the values will be assigned.

The method appropriately named callStoredProc accepts the object containing the parameters provided by the service class. The logic extracts the values and then assigns them to the parameter names defined in the constructor. The parameters are defined as input. Since this stored procedure returns a resultset object, another parameter is definition for it, called outValue which is returned when the logic calls the execute method of Spring’s parent class.

With the execution done, the next step is to process the resultset’s data. This is done with the for-loop shown which simply iterates through the records and places the fields into a corresponding Value Object which is then added to a collection to be returned to the service.

This post showed how to use Spring to call a stored procedure. It also illuminated how run-time environments are managed to ensure the proper stored procedure is called. The class named ApplicationContextManager that is used by the service class to determine this is part of baseproject.

Written by admin

June 4th, 2014 at 4:52 am

Leave a Reply

You must be logged in to post a comment.