Search This Blog

Monday, 10 September 2012

Spring JDBC and the MappingSqlQuery

In the previous post we saw the SqlQuery class. The MappingSqlQuery class extends the SqlQuery class
public class PersonByIdMappingQuery extends MappingSqlQuery<Person> {

    public PersonByIdMappingQuery(DataSource datasource) {
        super(datasource, "SELECT id, name, age FROM person WHERE id = ?");
        super.declareParameter(new SqlParameter("id", Types.BIGINT));

    public Person mapRow(ResultSet rs, int rowNumber) throws SQLException {
        Person person = new Person();//big int is returned as integer
        person.setId(((Integer) rs.getObject("id")).longValue());
        person.setAge((Integer) rs.getObject("age"));
        return person;

My class takes a data-source as the constructor argument. The MappingSqlQuery class takes the data-source and the SQL query as the parameter. This SQL will be used to create a PreparedStatement. Both are passed to the base class. In our previous post too, when we extended the SqlQuery class, we apssed it with a adatasource and the SQL that needed to be executed.
Any placeholders present in the SQL need to be resolved before the query is executed. Each parameter must be declared using the declareParameter method passing in an SqlParameter. The SqlParameter takes a name and the JDBC type as defined in java.sql.Types. After all parameters have been defined we call the compile() method so the statement can be prepared and later be executed.
public class ImprovedPersonDAO implements IPersonDAO {

    private PersonByIdMappingQuery byIdMappingQuery;

    public Person getPersonById(long personId) {
        Object[] parms = new Object[1];
        parms[0] = Long.valueOf(personId);
        return byIdMappingQuery.execute(parms).get(0);
//remaining methods ...
The bean definition for the class is below:
<bean id="improvedPersonDAO" class="">
    <property name="byIdMappingQuery">
        <bean class="" >
            <constructor-arg index="0" ref="c3pDataSource"/>

No comments:

Post a Comment