Handling StoredProcedure in SpringORM

StoredProcedure

The StoredProcedure class is a superclass for object abstractions of RDBMS stored procedures. This class is abstract, and its various execute(..) methods haveprotected access, preventing use other than through a subclass that offers tighter typing.

The inherited sql property will be the name of the stored procedure in the RDBMS. Note that JDBC 3.0 introduces named parameters, although the other features provided by this class are still necessary in JDBC 3.0.

Here is an example of a program that calls a function, sysdate(), that comes with any Oracle database. To use the stored procedure functionality one has to create a class that extends StoredProcedure. There are no input parameters, but there is an output parameter that is declared as a date type using the class SqlOutParameter. The execute() method returns a map with an entry for each declared output parameter using the parameter name as the key.

import java.sql.Types;

import java.util.HashMap;

import java.util.Iterator;

import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlOutParameter;

import org.springframework.jdbc.datasource.*;

import org.springframework.jdbc.object.StoredProcedure;

public class TestStoredProcedure {

    public static void main(String[] args)  {

        TestStoredProcedure t = new TestStoredProcedure();

        t.test();

        System.out.println("Done!");

    }

    void test() {

        DriverManagerDataSource ds = new DriverManagerDataSource();

        ds.setDriverClassName("oracle.jdbc.OracleDriver");

        ds.setUrl("jdbc:oracle:thin:@localhost:1521:mydb");

        ds.setUsername("scott");

        ds.setPassword("tiger");

        MyStoredProcedure sproc = new MyStoredProcedure(ds);

        Map results = sproc.execute();

        printMap(results);

    }

    private class MyStoredProcedure extends StoredProcedure {

        private static final String SQL = "sysdate";

        public MyStoredProcedure(DataSource ds) {

            setDataSource(ds);

            setFunction(true);

            setSql(SQL);

            declareParameter(new SqlOutParameter("date", Types.DATE));

            compile();

        }

        public Map execute() {

            // the 'sysdate' sproc has no input parameters, so an empty Map is supplied...

            return execute(new HashMap());

        }

    }

    private static void printMap(Map results) {

        for (Iterator it = results.entrySet().iterator(); it.hasNext(); ) {

            System.out.println(it.next());  

        }

    }

}

Find below an example of a StoredProcedure that has two output parameters (in this case Oracle cursors).

import oracle.jdbc.driver.OracleTypes;

import org.springframework.jdbc.core.SqlOutParameter;

import org.springframework.jdbc.object.StoredProcedure;

import javax.sql.DataSource;

import java.util.HashMap;

import java.util.Map;

public class TitlesAndGenresStoredProcedure extends StoredProcedure {

    private static final String SPROC_NAME = "AllTitlesAndGenres";

    public TitlesAndGenresStoredProcedure(DataSource dataSource) {

        super(dataSource, SPROC_NAME);

        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));

        declareParameter(new SqlOutParameter("genres", OracleTypes.CURSOR, new GenreMapper()));

        compile();

    }

    public Map execute() {

        // again, this sproc has no input parameters, so an empty Map is supplied...

        return super.execute(new HashMap());

    }

}

Notice how the overloaded variants of the declareParameter(..) method that have been used in the TitlesAndGenresStoredProcedure constructor are passedRowMapper implementation instances; this is a very convenient and powerful way to reuse existing functionality. (The code for the two RowMapper implementations is provided below in the interest of completeness.)

Firstly the TitleMapper class, which simply maps a ResultSet to a Title domain object for each row in the supplied ResultSet.

import com.foo.sprocs.domain.Title;

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;

import java.sql.SQLException;

public final class TitleMapper implements RowMapper {

    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

        Title title = new Title();

        title.setId(rs.getLong("id"));

        title.setName(rs.getString("name"));

        return title;

    }

}

Secondly, the GenreMapper class, which again simply maps a ResultSet to a Genre domain object for each row in the supplied ResultSet.

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.foo.domain.Genre;

public final class GenreMapper implements RowMapper {

    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

        return new Genre(rs.getString("name"));

    }

}

If one needs to pass parameters to a stored procedure (that is the stored procedure has been declared as having one or more input parameters in its definition in the RDBMS), one would code a strongly typed execute(..) method which would delegate to the superclass' (untyped) execute(Map parameters) (which has protectedaccess); for example:

import oracle.jdbc.driver.OracleTypes;

import org.springframework.jdbc.core.SqlOutParameter;

import org.springframework.jdbc.object.StoredProcedure;

import javax.sql.DataSource;

import java.util.HashMap;

import java.util.Map;

public class TitlesAfterDateStoredProcedure extends StoredProcedure {

    private static final String SPROC_NAME = "TitlesAfterDate";

    private static final String CUTOFF_DATE_PARAM = "cutoffDate";

    public TitlesAfterDateStoredProcedure(DataSource dataSource) {

        super(dataSource, SPROC_NAME);

        declareParameter(new SqlParameter(CUTOFF_DATE_PARAM, Types.DATE);

        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));

        compile();

    }

    public Map execute(Date cutoffDate) {

        Map inputs = new HashMap();

        inputs.put(CUTOFF_DATE_PARAM, cutoffDate);

        return super.execute(inputs);

    }

}

SqlFunction

The SqlFunction RDBMS operation class encapsulates an SQL "function" wrapper for a query that returns a single row of results. The default behavior is to return anint, but that can be overridden by using the methods with an extra return type parameter. This is similar to using the queryForXxx methods of the JdbcTemplate. The advantage with SqlFunction is that you don't have to create the JdbcTemplate, it is done behind the scenes.

This class is intended to use to call SQL functions that return a single result using a query like "select user()" or "select sysdate from dual". It is not intended for calling more complex stored functions or for using a CallableStatement to invoke a stored procedure or stored function. (Use the StoredProcedure or SqlCall classes for this type of processing).

SqlFunction is a concrete class, and there is typically no need to subclass it. Code using this package can create an object of this type, declaring SQL and parameters, and then invoke the appropriate run method repeatedly to execute the function. Here is an example of retrieving the count of rows from a table:

public int countRows() {

    SqlFunction sf = new SqlFunction(dataSource, "select count(*) from mytable");

    sf.compile();

    return sf.run();

}