Conversion From String to Clob and Clob to String

Question

I am trying to save XML response which comes as StrignBuffer to method. I need to save this string data to Sql as CLOB and retrieve the same.

The response can be upto 3-4Kb, which should be retrieved to StringBuffer again

Can any one give me tutorial or sample code which doesn't include db specific methods/jars.

I can change db column type if CLOB is not suitable or any other better alternative.

Please suggest.

Solution 1

You can create a CLOB by calling connection.createClob(), then you can populate it with methods such as setString, setCharacterStream or setAsciiStream.

Create a PreparedStatement and call its setClob() to store your CLOB in the databae.

Retrieving the data is just the same, read it from a ResultSet and call getCharacterStream, getAsciiStream or getSubStream on it.

Solution 2

For NamedParameterJdbcTemplte:

MapSqlParameterSource paramSource = new MapSqlParameterSource();
paramSource.addValue("clob", "a long long text", Types.CLOB);
namedTemplate.update(INSERT_STMT, paramSource);

For JdbcTemplate:

<bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
    <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
</bean>

and:

private @Autowire LobHandler lobHandler;
jdbcTemplate.update("insert into customer(id,name,comments) "
    + "values (?, ?, ?)", new PreparedStatementSetter() {
  public void setValues(PreparedStatement ps) throws SQLException {
    ps.setLong(1, 2L);
    ps.setString(2, "Jon");
    lobHandler.getLobCreator().setClobAsString(ps, 3, "Clob data");
  }
});

To extract String from ResultSet:

 inc.setDetail(lobHandler.getClobAsString(rs, "DETAIL"));

Look to official docs: http://docs.spring.io/spring/docs/3.0.x/reference/jdbc.html#jdbc-lob

Comments

[RaceBase]

I am using Spring JDBC for jdbc operations.

[Amir Pashazadeh]

Use a PreparedStatementCallback and a PreparedStatementCreator then.