spring JDBCTemplete

http://static.springsource.org/spring/docs/2.5.x/reference/jdbc.html#jdbc-JdbcTemplate

 

 

 Querying   (SELECT)

 

 

A simple query for getting the number of rows in a relation.

int rowCount = this.jdbcTemplate.queryForInt("select count(0) from t_accrual");

A simple query using a bind variable.

int countOfActorsNamedJoe = this.jdbcTemplate.queryForInt(
        "select count(0) from t_actors where first_name = ?", new Object[]{"Joe"});

Querying for a String.

String surname = (String) this.jdbcTemplate.queryForObject(
        "select surname from t_actor where id = ?", 
        new Object[]{new Long(1212)}, String.class);

 

Querying and populating a single domain object.

Actor actor = (Actor) this.jdbcTemplate.queryForObject(
    "select first_name, surname from t_actor where id = ?",
    new Object[]{new Long(1212)},
    new RowMapper() {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Actor actor = new Actor();
            actor.setFirstName(rs.getString("first_name"));
            actor.setSurname(rs.getString("surname"));
            return actor;
        }
    });

Querying and populating a number of domain objects.

Collection actors = this.jdbcTemplate.query(
    "select first_name, surname from t_actor",
    new RowMapper() {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Actor actor = new Actor();
            actor.setFirstName(rs.getString("first_name"));
            actor.setSurname(rs.getString("surname"));
            return actor;
        }
    });

If the last two snippets of code actually existed in the same application, it would make sense to remove the duplication present in the two RowMapper anonymous inner classes, and extract them out into a single class (typically a static inner class) that can then be referenced by DAO methods as needed. For example, the last code snippet might be better off written like so:

 

 

 

public Collection findAllActors() {
    return this.jdbcTemplate.query( "select first_name, surname from t_actor", new ActorMapper());
}

private static final class ActorMapper implements RowMapper {

    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        Actor actor = new Actor();
        actor.setFirstName(rs.getString("first_name"));
        actor.setSurname(rs.getString("surname"));
        return actor;
    }
}

  Updating   (INSERT/UPDATE/DELETE)

 

 

this.jdbcTemplate.update(
        "insert into t_actor (first_name, surname) values (?, ?)", 
        new Object[] {"Leonor", "Watling"});

this.jdbcTemplate.update(
        "update t_actor set weapon = ? where id = ?", 
        new Object[] {"Banjo", new Long(5276)});

this.jdbcTemplate.update(
        "delete from actor where id = ?",
        new Object[] {new Long.valueOf(actorId)});