Mapping SQL parameters with Spring's BeanPropertySqlParameterSource

If you don't use Spring for your J2EE web application database connectivity, then you should really check it out.

If, instead, you are already using, maybe you don't know a really nice object i discovered some time ago: the BeanPropertySqlParameterSource

The classic way to go in a query (or in an update) is to set the params one by one;
let's take this Person bean as example (id field omitted in this example):

 

 public class Person {
    
    private String name; 
    private String lastname; 
    private Date birthdate;
    private Language motherTongue;
   
    /* Getters and Setters */
   
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public String getLastname() {
        return lastname;
    }
    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public Date getBirthdate() {
        return birthdate;
    }
    public void setBirthdate(Date birthdate) {
        this.birthdate = birthdate;
    }

    public Language getMotherTongue() {
        return motherTongue;
    }
    public void setMotherTongue(Language motherTongue) {
        this.motherTongue = motherTongue;
    }
}

 

and the Language class referenced in it:

 

class Language {
    int id;
    int description;
    
    /* Getters and Setters */
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getDescription() {
        return description;
    }
    public void setDescription(int description) {
        this.description = description;
    }   
}
 

(with Language values like this: {[1 , English] , [2 , Italian], etc...}


Let's say i've filled all the fields in the Person object, and i want to insert it into a database.
Its instance is named "person". Normally i would go with:

 

String sql = "INSERT INTO person_table " + 
             " ( name, lastname, birthday, id_language) " + 
             " VALUES " +
             " ( :name, :lastname, :birthdate, :idLanguage) ";        

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("name", person.getName(), Types.VARCHAR);
params.addValue("lastname", person.getLastname(), Types.VARCHAR);
params.addValue("birthdate", person.getBirthdate(), Types.DATE);
params.addValue("idLanguage", person.getLanguage().getId(), Types.NUMERIC);
return namedJdbcTemplate.update(sql, params);

 

Imagine a big bean with a lot of fields, with every field passed manually... it's not really a good way to go.

With the BeanPropertySqlParameterSource object instead, IF we are taking all the fields from one object (and his inner objects), we can do like this:

 

String sql = "INSERT INTO person_table " + 
             " ( name, lastname, birthday, id_language) " + 
             " VALUES " +
             " ( :name, :lastname, :birthdate, :language.id) ";

BeanPropertySqlParameterSource params = new BeanPropertySqlParameterSource(person);
return namedJdbcTemplate.update(sql, params);

 

Not bad uh ? It will retrieve the specified fields (and types) through Reflection, including the fields value  in the inner objects contained in the main bean. As you can note, the notation is OGNL-like, with the getter of the inner object starting with a dot and a lower case letter, stripping "get" from the method name:

:language.id

means

person.getLanguage().getId();

PRO:

• less code;
• auto-decoding field Type through Reflections, avoiding user errors;
• changes on query or bean automatically managed, without the needs of change param settings

CONS:

• less customization possibilities

Enjoy