Spring Data JDBC - Clob column to String property in POJO not working

Question

I have an Oracle database with a table that has a DESCRIPTION column of type CLOB.

My POJO looks like this:

import java.util.Date;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;
import org.springframework.data.annotation.Id;

import lombok.Data;

@Data
@Table("MY_ITEMS")
@JsonIgnoreProperties(ignoreUnknown = true)
public class MyItem {

    @Column("ID") @Id Long id;
    @Column("DESCRIPTION") String description;
}

And my repository looks like this

import java.util.List;

import com.myapp.mymodel.MyItem;

import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;

public interface MyItemsRepository extends CrudRepository<MyItem, Long> {

    // other methods deleted...

    @Query(
        "select m.ID"
            + ", m.DESCRIPTION"
        + " from MY_ITEM m "
    )
    List<MyItem> findMyItems();
}

When I call the repository method I get an error saying there is no converter for CLOB to String.

Any help is appreciated.

Answer 1

There's a very nice example here that solves the problem: https://github.com/spring-projects/spring-data-examples/tree/master/jdbc/basics

The solution amounts to adding a configuration that registers a converter that can extract the CLOB data to the String property

import java.sql.Clob;
import java.sql.SQLException;
import java.util.Arrays;

import org.springframework.context.annotation.Configuration;
import org.springframework.core.convert.converter.Converter;
import org.springframework.data.jdbc.core.convert.JdbcCustomConversions;
import org.springframework.data.jdbc.repository.config.AbstractJdbcConfiguration;
import org.springframework.data.jdbc.repository.config.EnableJdbcRepositories;
import org.springframework.data.relational.core.mapping.Embedded.Nullable;

@Configuration
@EnableJdbcRepositories
public class AggregateJdbcConfiguration extends AbstractJdbcConfiguration {

    @Override
    public JdbcCustomConversions jdbcCustomConversions() {

        return new JdbcCustomConversions(Arrays.asList(new Converter<Clob, String>() {

            @Nullable
            @Override
            public String convert(Clob clob) {

                try {

                    return Math.toIntExact(clob.length()) == 0 //
                            ? "" //
                            : clob.getSubString(1, Math.toIntExact(clob.length()));

                } catch (SQLException e) {
                    throw new IllegalStateException("Failed to convert CLOB to String.", e);
                }
            }
        }));
    }
}

Here are a few of my favorite videos on the subject

https://www.youtube.com/watch?v=EaHlancPA14&list=PLsC0nE-wJ1I4ra6KYXTPOXipdrJ_IdhaO&index=3&t=0s

https://www.youtube.com/watch?v=GOSW911Ox6s&list=PLsC0nE-wJ1I4ra6KYXTPOXipdrJ_IdhaO&index=4&t=59s

https://www.youtube.com/watch?v=AnIouYdwxo0&list=PLsC0nE-wJ1I4ra6KYXTPOXipdrJ_IdhaO&index=5&t=2730s

And a couple of blog posts

https://spring.io/blog/2018/09/24/spring-data-jdbc-references-and-aggregates

https://spring.io/blog/2018/09/17/introducing-spring-data-jdbc