Failed to convert property value of type 'net.sourceforge.jtds.jdbc.ClobImpl' to required type 'java.lang.String'

Question

I have the one table in sql server with one field annoation as text data type.

I have used spring jdbc template to get the annotation text field data and then I have used Following API (BaseRowMapper) to map table column to java pojo.

below is my table structure:

While retrieving data I am getting below exception.

org.springframework.beans.ConversionNotSupportedException: Failed to convert property value of type 'net.sourceforge.jtds.jdbc.ClobImpl' to required type 'java.lang.String' for property 'annotation'; nested exception is java.lang.IllegalStateException: Cannot convert value of type [net.sourceforge.jtds.jdbc.ClobImpl] to required type [java.lang.String] for property 'annotation': no matching editors or conversion strategy found
    at org.springframework.beans.BeanWrapperImpl.convertIfNecessary(BeanWrapperImpl.java:464)
    at org.springframework.beans.BeanWrapperImpl.convertForProperty(BeanWrapperImpl.java:495)
    at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:1099)
    at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:884)
    at com.ecw.vascular.model.BaseRowMapper.mapRow(BaseRowMapper.java:39)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:92)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:651)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:639)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:664)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:704)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:179)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:185)
    at com.ecw.vascular.dao.BaseDao.executeQuery(BaseDao.java:113)
    at com.ecw.vascular.dao.ObservationDao.findByPatientAndEncounter(ObservationDao.java:64)

Answer 1

The problem lies on the datatype used in the SQL Server database to store a String of maximum 16 byte.

Text can store up to 2 GB of variable width character string data, so JDBCTemplate uses a CLOB to retrieve the data from that column.

Since the maximum length is 16, one solution can be to change the datatype on the database to a more appropriate varchar.

If this is not an option and since the error mentions a jtds implementation of CLOB, you can try to change the jdbc connection string to

jdbc:jtds:sqlserver://ServerName;**useLOBs=false**;DatabaseName=xxx;instance=xxx

A third-highly not recommended - option would be to use a CLOB instead of a String inside the java bean, with all the relative changes needed to deal with databases LOBs.