Most Java applications interact with a relational data source through a POJO object model that is mapped with an object relational (OR) mapping framework.  Developers don’t have to worry about constructing SQL, but instead use a Java based API provided by the OR mapping framework to apply object oriented design principles to their application models.

However, in some cases, an impedance mismatch occurs between the application model and the underlying relational data model.  One such example is when a requirement calls for a summation, average, count, value from the data store, requiring an SQL expression shown below.

select sum(<column name>) from <table> where <where clause>

or

select  current_datetime from <table> where <where clause>

This results in a single value result, and as such does not readily map to an application model (POJO), but rather to an independent Java primitive type. So, where and how should this data access execution occur in a Java layered application?

How to access relational scalar values can be performed using the generalized utility shown below. It does assume that you are using the Spring framework and a JPA based O/R mapping framework, and that the Spring application is configured to auto wire a JPA entityManager.

public class ScalarFunctionDao<T> {

    @PersistenceContext
    private EntityManager entityManager;
    public T singleResult(String sql) {
        return singleResult(sql, null);
    }
    public List<T> list(String sql) {
        return list(sql, null);
    }
    public T singleResult(String sql, Object… parms) {
        List<T> result = null;
        result = list(sql, parms);
        return result.isEmpty() ? null : result.get(0);
    }
    public List<T> list(String sql, Object… parms) {
        if (parms == null || parms.length == 0) {
            return entityManager.createNativeQuery(sql).getResultList();
        }
        Query query = entityManager.createNativeQuery(sql);
        for (int i = 0; i < parms.length; i++) {
            query.setParameter(i + 1, parms[i]);
        }
        return query.getResultList();
    }
}

This utility provides a mechanism for retrieving a single type or a list of values with the return values being a generic type.
An example: service class implementation that defines methods for summing and averaging for a table column value (shown below).

@Service
public class MyService {
    @Autowired
    ScalarFunctionDao<BigDecimal> scalarDao;
    public Double sum() {
        // sum daily sales, no parameters required
        String sql = “select avg(DAILY_SALES) from STORE_TABLE where id = ?”;
        return scalarDao.singleResult(sql).doubleValue();
    }
    public Double average(Long storeId) {
        // sum average daily sales for store, storeId parameter required
        Long[] params = new Long[] { storeId };
        String sql = “select avg(DAILY_SALES) from STORE_TABLE where id = ?”;
        return scalarDao.singleResult(sql, params).doubleValue();
    }
}

As you can see in the example service implementation above, the scalar DAO utility is auto-wired with a BigDecimal type. This allows the utility to express native SQL scalar functions with and without parameters that result in Big values.

Spring’s auto-wiring capability allows multiple instances to be bound to a specific type for a more type safe implementation. The example below shows how a method can be added to obtain a current timestamp from the database.

@Service
public class MyService {
    @Autowired
    ScalarFunctionDao<BigDecimal> scalarDao;
    @Autowired
    ScalarFunctionDao<Timestamp> timestampDao;
    public Timestamp current() {
        // current timestamp
        String sql = “select current_timestamp from STORE_TABLE”;
        return timestampDao.singleResult(sql);
    }

I hope that you may find this utility useful — I sure have.

— David Pitt, [email protected]