Spring JDBC and the SqlFunction

In our previous post we saw how to use the JDBC templates to retrieve data. The template included wrappers to return scalar values from execution of SQL queries. For example consider the method from the previous example:

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

The same code can be written using a SqlFunction.

public class CountFunction {

    private DataSource dataSource;

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public int countRows() {
        SqlFunction<Integer> sf = new SqlFunction<Integer>(dataSource,
                "select count(*) from Person");
        sf.compile();
        return sf.run();
    }
}

The class is a wrapper for a query that returns a single row of results. It can any type of result depending on the Type parameter. The default is an int value. As can be seen from the above examples the class is similar to using the queryForXxx methods of the JdbcTemplate. The advantage with SqlFunction is that we don't have to create the JdbcTemplate. Spring does that behind the scenes. The compile method will compile the query and is an abstract inherited method by the SqlFunction class from RdbmsOperation class.
As per the javadocs

Intended to use to call SQL functions that return a single result using a query 
like "select user()" or "select sysdate from dual". It is not intended for calling 
more complex stored functions or for using a CallableStatement to invoke a 
stored procedure or stored function. Use StoredProcedure or SqlCall for this type 
of processing.

I added the above in a DAO implementation

public class ImprovedPersonDAO implements IPersonDAO {

    private CountFunction countFunction;

    public int findTotalPersons() {
        return countFunction.countRows();
    }
//..other methods
}

The bean definition for the above 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>
<!--..other properties-->
</bean>

The datasource must be injected into the CountFunction instance.