Using Clob with Spring NamedParameterJdbc Template

Question

I need to store large XML data to CLOB and convert back CLOB to String

But the problem is we are using (Spring NamedParameterJdbc template)

SqlParameterSource paramSource = new BeanPropertySqlParameterSource(
            positionResponsesDO);
this.jdbcTemplate.update(sql, paramSource);

Where PositionResponsesDO has get and set property

private Clob xmlData;

Now I need to convert String data (large) to Clob and Clob to String. Please suggest to me the best way.

I can't use File operations as it's WebApp

Answer 1

You can use LobHandler and LobCreator to take Clobs and Blobs and turn them into something else.

The Spring documentation discusses them here.

To convert a clob or blob column to something , you can use a query() method on NamedParameterJdbcTemplate that takes a RowMapper.

LobHandler lobHandler = new DefaultLobHandler();
jdbcTemplate.query(sql, paramSource,
    new RowMapper<Void>() {
        public Void mapRow(ResultSet rs, int i) throws SQLException {
            String clobText = lobHandler.getClobAsString(rs, "clobColumnName");                                                
            byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "blobColumnName");                                                
            return null;
        }
    });