Stored Procedure Proxy in Spring Java Framework

The org.springframework.jdbc.object package contains the StoredProcedure class. By extending this class, Spring enables a stored procedure to be proxied by a Java class with a single business method. If you like, you can even define an interface that the stored procedure implements, meaning that you can free your application code from depending on the use of a stored procedure at all.

For example, if there were a stored procedure called “AllTitles” in a movie database to get all of the titles currently available, we would create a StoredProcedure subclass that implements an application-specific interface for use by clients.

 public class AllTitleSproc extends StoredProcedure implements AllTitleLister { private static final String STORED_PROCEDURE_NAME = "AllTitles"; private static final String RESULT_SET_NAME = "titles"; public AllTitleSproc(DataSource dataSource) { setDataSource(dataSource); setSql(STORED_PROCEDURE_NAME); declareParameter( new SqlReturnResultSet( RESULT_SET_NAME, new TitleMapper())); compile(); } public List<Title> listAllTitles() { Map result = execute(new HashMap()); // no input params return (List<Title>) result.get(RESULT_SET_NAME); } private static class TitleMapper implements ParameterizedRowMapper<Title> { public Title mapRow(ResultSet resultSet, int i) throws SQLException { Title t = new Title(resultSet.getLong(1)); t.setName(resultSet.getString(2)); return t; } } }

Notice first that we can achieve portability with stored procedures across databases; all that is required is that the stored procedure name be the same (although we could make this configurable if we wanted in order to further increase portability). Second, notice that the class AllTitleSproc implements an application-specific interface, AllTitleLister:

 public interface AllTitleLister { List<Title> listAllTitles(); }

This allows code that uses this functionality to be completely independent of how movie titles are obtained:

 public class AllTitleListerClient { private AllTitleLister allTitleLister; public void setAllTitleLister(AllTitleLister allTitleLister) { this.allTitleLister = allTitleLister; } public void useLister() { List<Title> titles = allTitleLister.listAllTitles(); for (Title t : titles) { System.out.println(t.getId() + ":" + t.getName()); } }

Since the allTitleLister property is provided via dependency injection, this client code only depends upon the interface and none of its implementation details.

Courtesy: http://www.stevideter.com/2008/03/08/processing-stored-procedure-result-sets-in-spring/

 

Leave a Reply

Your email address will not be published. Required fields are marked *


four + 8 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>