How to pass SQLXML type to view in Spring MVC?

Question

In my webapp controller I'm getting results from the db, which are of type java.sql.SQLXML. I want to pass it to the view to be returned verbatim (as XML).

The problem is, the data associated with SQLXML is released as soon as I leave JdbcTemplate call. How then should I pass the data to the view using a model?

Answer 1

The simplest solution is to read the data out of the SQLXML object before you leave the JdbcTemplate invocation, and return the data as a byte[], String, DOM or whatever.

If that's not practical (e.g. the data is too large), then you'll need to take steps to keep the connection open beyond the scope of the JdbcTemplate call, and that means using transactions. By opening a transaction before calling JdbcTemplate, the connection becomes bound to that transaction, and will be kept open until you close the transaction. Unfortunately, keeping it open long enough for the view to be rendered requires some gymnastics.

Assuming you don't have transactions already setup, then you'll need a DataSourceTransactionManager bean in your context. You could then write a HandlerInterceptor to manage the transaction, keeping it open long enough for the view to render. Spring doesn't provide convenient interceptors for this out-of-the-box, like it does with JPA/Hibernate/etc, so you'll need to write your own HandlerInterceptor, something like this:

public class TransactionInterceptor extends HandlerInterceptorAdapter {

    private PlatformTransactionManager txManager;

    public void setTxManager(PlatformTransactionManager txManager) {
        this.txManager = txManager;
    }

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        TransactionStatus tx = txManager.getTransaction(new DefaultTransactionDefinition());
        request.setAttribute("tx", tx);
        return true;
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {
        TransactionStatus tx = (TransactionStatus) request.getAttribute("tx");
        txManager.commit(tx);
    }
}

You then configure this interceptor to be invoked when a request calls your controller + view.