Spring and JDBC

I decided to implement the IPersonDAO interface to work with JDBC.

public class SimplePersonDAO extends SimpleJdbcDaoSupport implements IPersonDAO {
    private static final Logger logger = Logger.getLogger(PersonDAO.class);
//remaining methods
}

The class extends the DaoSupport class, thus getting direct access to the SimpleJdbcTemplate. The template has Java 5 features such as autoboxing, generics, and variable parameter lists. (As of Spring 3.1 the class has been deprecated and preference is to be given to  JdbcTemplate and NamedParameterJdbcTemplate.) I implemented the various methods available in the interface:

public List<Person> getAllPersons() {
    logger.debug("getAllPersons from system");
    final List<Person> persons = new ArrayList<Person>(0);
    //access to jdbcTemplate is available
    final List<Map<String, Object>> rows = this.getJdbcTemplate()
            .queryForList("select id, name, age from Person");
    for (final Map<String, Object> row : rows) {
        final Person lineItemToPush = new Person();
        lineItemToPush.setId((Long) (row.get("id")));
        lineItemToPush.setName((String) (row.get("name")));
        lineItemToPush.setAge((Integer) (row.get("age")));
        persons.add(lineItemToPush);
    }
    logger.debug("Total Retrieved items : " + persons.size());
    return persons;
}

The class inherits a getJdbcTemplate() that returns the SimpleJdbcTemplate. The queryForList() method returns a list of map objects. Every map object maps to a row in the database. The map holds a set of key-value pairs where the key is the column name and the value is the column value. The template also supports usage of RowMapper class. The documentation for the class says

An interface used by JdbcTemplate for mapping rows of a java.sql.ResultSet 
on a per-row basis. Implementations of this interface perform the actual 
work of mapping each row to a result object, but don't need to worry about 
exception handling. SQLExceptions will be caught and handled by the calling 
JdbcTemplate.

public Person getPersonById(final long personId) {
    logger.debug("fetching record with  id : " + personId);
    Person personRec= null;

    final Map<String, Object> params = new HashMap<String, Object>();
    params.put("id", personId);
    personRec= simpleJdbcTemplate.queryForObject(
        "select id, name, age  from Person  where id = :id",
        new RowMapper<Person>() {
            @Override
            public Person mapRow(ResultSet rs, int arg1)
                throws SQLException {
                final Person person = new Person();
                person.setId(rs.getLong(1));
                person.setName(rs.getString(2));
                person.setAge(rs.getInt(1));
                return person;
            }
        }, params);
    return lineItemToPush;
}

The template also includes method to return values.

public int findTotalPersons() {
    logger.debug("findTotalPersons: fetching record count ");
    int total = this.simpleJdbcTemplate.queryForInt("select * from PERSON");
    return total;
}

In the next post we shall see an alternative technique for executing such simpler queries.
Consider the below update and delete methods:

public void updatePerson(final Person person) {
    final String query = "update Person set name = '"
            + person.getName() + "', age = " + person.getAge()
            + " where id  = :id";
    final Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("id", person.getId());
    this.simpleJdbcTemplate.update(query, parameters);
}

public void delete(Person person) {
    final String query = "delete from Person where id  = :id";
    final Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("id", person.getId());
    this.simpleJdbcTemplate.update(query, parameters);        
}

The SimpleJdbcTemplate can understand named parameters in SQL. The update method includes the SQL query and the map holding the parameter to use in the query.
The template also provides support for working with prepared statements.

public void save(final Person person) {
    final String query = "insert into Person(name,age) values('"
            + person.getName() + "'," + person.getAge() + " )";
    System.out.println(query);
//    this.simpleJdbcTemplate.update(query); //did not return the id of the record        
    final KeyHolder keyHolder = new GeneratedKeyHolder();        
    jdbcTemplate.update(
        new PreparedStatementCreator() {                
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
                return ps;
            }
        },
        keyHolder);
//       keyHolder.getKey() now contains the generated key
    person.setId(keyHolder.getKey().longValue());        
}

The PreparedStatementCreator is used to create prepared statements. The save method will written the auto-generated identity. However the update method returns he number of affected rows and not the new Identifier. For the id, Spring provides a org.springframework.jdbc.support.KeyHolder class that holds within it the newly generated the identifier. The  Statement.RETURN_GENERATED_KEYS  value is a constant indicating that generated keys should be made available for retrieval
The spring configuration for the bean is as below:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg ref="c3pDataSource" />
</bean>

<bean id="simplePersonDAO" class="com.data.dao.SimplePersonDAO">
    <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

Although PersonDAO extends SimpleJdbcDaoSupport, the class does not take simpleJdbcTemplate as a property. It takes a jdbcTemplate and creates its own internal instance of SimpleJdbcTemplate.