Spring JDBC and SqlUpdate

In the previous posts we used the JDBC template's update() method to insert, update and delete data.There is another technique available if we do not want to work with the template.

public class PersonSqlUpdate extends SqlUpdate {

    public PersonSqlUpdate(final DataSource ds) {
        setDataSource(ds);//a super call
        setSql("update Person set name = ?, age = ? where id = ?");//a super call
        declareParameter(new SqlParameter(Types.VARCHAR));//a super call
        declareParameter(new SqlParameter(Types.INTEGER));
        declareParameter(new SqlParameter(Types.BIGINT));
        compile();//a super call
    }

    //return number of updated rows
    public int run(final Person person) {
        Object[] params =
            new Object[] {
                person.getName(),
                person.getAge(),
                person.getId()};
        return update(params);
    }
}

The class represents a SQL update operation. It includes all the code necessary for the query. The class here takes a data-source as a constructor parameter. The data-source and the sql query is passed to the parent class. As the query uses indexes instead of names the SqlParameter takes just the sql type as parameter. The calls are made in the order of the indexes. I used the same class to manage deletion and updation.

public class PersonSqlDelete extends SqlUpdate{
    
       public PersonSqlDelete(final DataSource ds) {
            setDataSource(ds);
            setSql("delete from Person where id = ?");
            declareParameter(new SqlParameter(Types.BIGINT));
            compile();
        }

        //return number of updated rows
        public int run(final Person person) {
            Object[] params =
                new Object[] {
                    person.getId()};
            return update(params);
        }
}

public class PersonSqlInsert extends SqlUpdate {

    public PersonSqlInsert(final DataSource ds) {
        setDataSource(ds);
        //for prepared statements the quotations aren't necessary
        setSql("insert into Person(name,age) values(?,?)");
        declareParameter(new SqlParameter(Types.VARCHAR));
        declareParameter(new SqlParameter(Types.INTEGER));
        setReturnGeneratedKeys(true);
        compile();
    }

    // return number of updated rows
    public int run(final Person person) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        Object[] params = new Object[] { person.getName(), person.getAge()};
        update(params, keyHolder);
        return keyHolder.getKey().intValue();// does not support long return types

    }
}

I added the above classes in my DAO instance.
Thus combining the methods that we have defined using the SqlFunction, SqlQuery and MappingSqlQuery the complete DAO class is:

public class ImprovedPersonDAO implements IPersonDAO {

    private CountFunction countFunction;
    private PersonMapper personMapper;
    private PersonByIdMappingQuery byIdMappingQuery;
    private PersonSqlUpdate personSqlUpdate;
    private PersonSqlInsert personSqlInsert;
    private PersonSqlDelete personSqlDelete;

    @Override
    public List<Person> getAllPersons() {
        return personMapper.execute();
    }

    @Override
    public int findTotalPersons() {
        return countFunction.countRows();
    }

    @Override
    public Person getPersonById(long personId) {
        Object[] parms = new Object[1];
        parms[0] = Long.valueOf(personId);
        return byIdMappingQuery.execute(parms).get(0);
    }

    @Override
    public void updatePerson(Person person) {
        personSqlUpdate.run(person);

    }

    @Override
    public void save(Person person) {
        int id = personSqlInsert.run(person);
        person.setId(Long.valueOf(id));

    }

    @Override
    public void delete(Person person) {
        personSqlDelete.run(person);

    }
//..setter getters
}

As can be seen, we do not have the JdbcTemplate injected directly in this class, unlike our earlier example where we had injected the template via XML. The bean definition for the same is :

<bean id="improvedPersonDAO" class="com.data.dao.ImprovedPersonDAO">
    <property name="countFunction">
        <bean class="com.data.dao.utility.CountFunction" >
            <property name="dataSource" ref="c3pDataSource"/>
        </bean>
    </property>
    <property name="personMapper">
        <bean class="com.data.dao.utility.PersonMapper" >
            <property name ="dataSource" ref="c3pDataSource"/>
            <property name="sql" value="select id, name, age from Person"/>
        </bean>
    </property>
    <property name="byIdMappingQuery">
        <bean class="com.data.dao.utility.PersonByIdMappingQuery" >
            <constructor-arg index="0" ref="c3pDataSource"/>
        </bean>
    </property>
    <property name="personSqlInsert">
        <bean class="com.data.dao.utility.PersonSqlInsert" >
            <constructor-arg index="0" ref="c3pDataSource"/>
        </bean>
    </property>
    <property name="personSqlUpdate">
        <bean class="com.data.dao.utility.PersonSqlUpdate" >
            <constructor-arg index="0" ref="c3pDataSource"/>
        </bean>
    </property>
    <property name="personSqlDelete">
        <bean class="com.data.dao.utility.PersonSqlDelete" >
            <constructor-arg index="0" ref="c3pDataSource"/>
        </bean>
    </property>
</bean>