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.

The Spring Jdbc Template for database access - Tutorial

1. Spring jdbc introduction
2. Spring installation
3. Creating a Database and corresponding pojo class to represent that entity
4. Using jdbc-template to create data access layer 
5. Mapping the resultset to object or class (Though little part is covered in above post, post no. 4)

Mapping object to row in spring using jdbc template

See this tutorial for getting the feel of spring jdbc template.
The example below uses database and corresponding from this post.
Now we can have our object from result set. Either it can be 1 value returned from select query, or we can have multiple rows. So thats where we have to map the object. Spring provides various ways to map result sets to these objects,eg. Flight.

Querying for Single Row

1. Custom RowMapper

class FlightMapper implements RowMapper<Flight> {
       @Override
    public Flight mapRow(ResultSet rs, int index) throws SQLException {
          Flight flight = new Flight();
          flight.setFlightNo(rs.getString(1));
          flight.setCarrier(rs.getString(2));
          flight.setFrom(rs.getString(3));
          flight.setTo(rs.getString(4));
          return flight;
       }

Now this should be passed to queryForObject function.
Pass it to queryForObject() method, the returned result will call your custom mapRow() method to match the value into the properly.

public Flight getFlightInfo(String flightNo) {

String sql = "select * from flights_test where flightno=?"
        Flight flight = (Flight) jdbcTemplate.queryForObject(sql,new Object[]
                {flightNo}, new FlightMapper());   

        return flight;             
    }

2. BeanPropertyRowMapper

In Spring 2.5, comes with a handy RowMapper implementation called ‘BeanPropertyRowMapper’, which can maps a row’s column value to a property by matching their names. Just make sure both the property and column has the same name, e.g property ‘flightNo’ will match to column name ‘FLIGHTNO’ or with underscores ‘FLIGHT_NO’.

Again queryForObject method can be used.

//Similar to above but using BeanPropertyRowMapper

public Flight getFlightInfo(String flightNo) {

String sql = "select * from flights_test where flightno=?"
        Flight flight = (Flight) jdbcTemplate.queryForObject(sql,new Object[]
                {flightNo}, new BeanPropertyRowMapper(Flight.Class)
());   

        return flight;             
    }

Querying for Multiple Rows

Here’s two examples to show how to query or extract multiple rows from database, and convert it into a List.

1. Map it manually

In mutiple return rows, RowMapper is not supported in query() method, you need to map it manually. 

public List<Flight> getAvailableFlights(String carrier) {
        return jdbcTemplate.query("select * from flights_test where carrier = ?", new FlightMapper(), carrier);
    }

2. BeanPropertyRowMapper

The simplest solution is using the BeanPropertyRowMapper class.

    public List<Flight> getAvailableFlights(String carrier) {
        return jdbcTemplate.query("select * from flights_test where 
        carrier = ?", new BeanPropertyRowMapper(Flight.Class), carrier);
    }

Create a flight database

Create a flight database:

create table flights_test(flightno varchar(10), carrier varchar(30), kahase varchar(30), kahatak varchar(30));

insert into flights_test values('JL-120','JET AIRWAYS', 'MUMBAI', 'JAIPUR');
insert into flights_test values('KL-102','KINGFISHER', 'DELHI', 'MUMBAI');
insert into flights_test values('AI-229','INDIAN', 'KOLKATA', 'DELHI');
insert into flights_test values('SP-109','SPICEJET', 'CHENNAI', 'MUMBAI');
insert into flights_test values('GO-120','GO AIR', 'MUMBAI', 'BANGALORE');

kahase in hindi means - from Where,i.e. source of flight
kahatak in hindi means - till where i.e. destination of flight

Spring JDBC introduction

Spring provides a simplification in handling database access with the Spring JDBC Template.

The Spring JDBC Template has the following advantages compared with standard JDBC.

• The Spring JDBC template allows to clean-up the resources automatically, e.g. release the database connections.
• The Spring JDBC template converts the standard JDBC SQLExceptions into RuntimeExceptions. This allows the programmer to react more flexible to the errors. The Spring JDBC template converts also the vendor specific error messages into better understandable error messages.

The Spring JDBC template offers several ways to query the database. queryForList() returns a list of HashMaps. The name of the column is the key in the hashmap for the values in the table.
More convenient is the usage of ResultSetExtractor or RowMapper which allows to translates the SQL result direct into an object (ResultSetExtractor) or a list of objects (RowMapper). Both these methods will be demonstrated in the coding.