USING SPRING’S STOREDPROCEDURE

One very useful portion of the Spring Framework is the StoredProcedure wrapper’s and the RowMapper objects. Together these allow you to call a stored procedure and then parse the result set back into a collection of objects with very little pain. Below is an example of how to do just this for a simple query like a user query.

public class MyStoredProcedure extends StoredProcedure {

    public MyStoredProcedure (DataSource ds, String spname,
            Map map, String sqlOutKey, Integer returnType,
            RowMapper rowmapper) {
        super();
        setDataSource(ds);

        /* resultset has to be declared first over other declare parameters */
        if (rowmapper != null) {
            declareParameter(new SqlReturnResultSet(sqlOutKey, rowmapper));
        }

        if (map != null) {
            Iterator itr = map.keySet().iterator();
            while (itr.hasNext()) {
                String key = (String) itr.next();
                Integer value = (Integer) map.get(key);
                declareParameter(new SqlParameter(key, value.intValue()));
            }
        }

        /*
         * sql out paramter has to be declared based on the order in stored
         * procedures, In all our stored procedures we have it after input
         * parameters
         */
        if (returnType != null) {
            declareParameter(new SqlOutParameter(sqlOutKey, returnType
                    .intValue()));
        }

        setSql(spname);
        compile();
    }
}

Next, we have the Mapper class:

public class UserMapper implements RowMapper {
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setUserId(rs.getString(Constants.USER_ID));
        user.setFirstName(rs.getString(Constants.FIRST_NAME));
        user.setLastName(rs.getString(Constants.LAST_NAME));
        user.setOrganizationName(rs.getString(Constants.ORGANIZATION_NAME));
        return user;
    }
}

Next, we have to query the actual stored procedure from a DAO. Here’s a sample method that would do just such a thing:

public Collection searchUsers(User user) throws Exception {

        Map lhm = new LinkedHashMap(4);
        lhm.put(Constants.USER_ID, new Integer(Types.VARCHAR));
        lhm.put(Constants.FIRST_NAME,new Integer(Types.VARCHAR));
                lhm.put(Constants.LAST_NAME,new Integer(Types.VARCHAR));
                lhm.put(Constants.ORGANIZATION_NAME,new Integer(Types.VARCHAR));

        UserMapper mapper = new UserMapper();

        // Call Stored Procedure
        EntitlementsStoredProcedure proc = new EntitlementsStoredProcedure(
            ds, StoredProcedureConstants.USER_SEL, lhm,
            Constants.RESULTSET, null, mapper);

        // Collect the criteria for the search
        Map map = new LinkedHashMap(4);
        map.put(Constants.USER_ID, user.getUserId());
        map.put(Constants.FIRST_NAME, user.getFirstName());
        map.put(Constants.LAST_NAME, user.getLastName());
        map.put(Constants.ORGANIZATION_NAME, user.getOrganizationName());

        Map results =  proc.execute(map);
        List resultList = (LinkedList)results.get(Constants.RESULTSET);

        //iterate of results list and print
        for (Iterator it=resultList.iterator(); it.hasNext(); ) {
            User user1 = (User)it.next();
            System.out.println(user1);
        }

        return resultList;
    }

That’s all there is to it! This shows just how simple it is to do queries in an object oriented way, and have generic row mappers. There are full object relational mapping solutions, such as Hibernate, that do a great job of solving the working with relational data in an OO way paradigm, but they take a LOT of configuration and can be daunting if you’re not accustomed to working with them. This solution, however, I feel works very well in simpler scenarios. It also allows someone who is used to looking at code to quickly read through and get an idea of how to use this.

One point to note: this gets even simpler when using generics that are introduced in Java 1.5.