Spring Data Jdbc and Oracle21c

Question

for my latest assignment I'm developing a Spring boot application which will connect with an Oracle 21c database.

The feature of the oracle release we're interested in is the native JSON data type called OSON (reference here: Oracle 21c JSON data type )

I've developed an old fashion DAO approach to accomplish the task, but I would like to use Spring Data JDBC project for the data access layer ideally with minimal extra configuration.

Actually I'm struggling with the mapping of the columns where the OSON type will be stored. After several tries I've obtained the error below following the idea of creating a custom converter for the datatype.

Any suggestion on how to proceed?

pom:

<!-- ORACLE -->
    <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc11-production</artifactId>
        <version>21.1.0.0</version>
        <type>pom</type>
    </dependency>
<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jdbc</artifactId>
    </dependency>

Entity class:

@Table("T_BUDGET")
@Data @NoArgsConstructor
public class BudgetEntityData {

        @Id
        private Long id;

        @Column("BUDGET")
        private JsonObjectWrapper budget;
}

Wrapper used for the converter:

@Data
public class JsonObjectWrapper {

    private OracleJsonValue json;

}

Jdbc configuration with custom converter:

@Configuration
@EnableJdbcRepositories
public class JdbcConfig extends AbstractJdbcConfiguration {

        //utility object used to centralize the use of OracleJsonFactory, not involved in the problem
        private static OracleJsonFactoryWrapper factoryWrapper = new OracleJsonFactoryWrapper(new ObjectMapper()
                .configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false)
                .configure(SerializationFeature.FAIL_ON_EMPTY_BEANS, false),
                 new OracleJsonFactory());

        @Override
        public JdbcCustomConversions jdbcCustomConversions() {
            return new JdbcCustomConversions(Arrays.asList(StringToJsonObjectWrapper.INSTANCE,JsonObjectWrapperToString.INSTANCE));
        }

        @WritingConverter
        enum JsonObjectWrapperToString implements Converter<JsonObjectWrapper, String> {

            INSTANCE;

            @Override
            public String convert(JsonObjectWrapper source) {
                return source.toString();
            }
        }

        @ReadingConverter
        enum StringToJsonObjectWrapper implements  Converter<String, JsonObjectWrapper> {

            INSTANCE;

            @Override
            public JsonObjectWrapper convert(String source) {
                JsonObjectWrapper jsonObjectWrapper = new JsonObjectWrapper();
                OracleJsonValue osonObject = factoryWrapper.createOsonObject(source);
                jsonObjectWrapper.setJson(osonObject);
                return jsonObjectWrapper;
            }
        }
}

Error:

2022-04-07 09:47:27.335 DEBUG 24220 --- [nio-8080-exec-1] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL query 2022-04-07 09:47:27.335 DEBUG 24220 --- [nio-8080-exec-1] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [SELECT "T_BUDGET"."ID" AS "ID", "T_BUDGET"."BUDGET" AS "BUDGET" FROM "T_BUDGET"] 2022-04-07 09:48:58.006 ERROR 24220 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.data.mapping.MappingException: Could not read value BUDGET from result set!] with root cause

java.sql.SQLException: Invalid column type: getOracleObject not implemented for class oracle.jdbc.driver.T4CJsonAccessor at oracle.jdbc.driver.GeneratedAccessor.getOracleObject(GeneratedAccessor.java:1221) ~[ojdbc11-21.1.0.0.jar:21.1.0.0.0] at oracle.jdbc.driver.JsonAccessor.getObject(JsonAccessor.java:200) ~[ojdbc11-21.1.0.0.jar:21.1.0.0.0] at oracle.jdbc.driver.GeneratedStatement.getObject(GeneratedStatement.java:196) ~[ojdbc11-21.1.0.0.jar:21.1.0.0.0] at oracle.jdbc.driver.GeneratedScrollableResultSet.getObject(GeneratedScrollableResultSet.java:334) ~[ojdbc11-21.1.0.0.jar:21.1.0.0.0] at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java) ~[HikariCP-3.4.5.jar:na] at org.springframework.jdbc.support.JdbcUtils.getResultSetValue(JdbcUtils.java:283) ~[spring-jdbc-5.3.8.jar:5.3.8]

Answer 1

I had the very same issue. I fixed it with the RowMapper like this:

1. Create DTO for the JSON content (JsonObjectWrapper in your case)

@Getter
@Setter
public class JsonContent {
    
    private String code;
    private String name;
    
}

2. Create entity (BudgetEntityData in your case)

@Data
@Relation( collectionRelation = "persons" )
public class Person {

    @Id
    private Long id;

    private JsonContent content;

}

3. Create custom RowMapper (probably JdbcConfig in your case)

public class PersonMapper implements RowMapper<Person> {

    static ObjectMapper objectMapper = new ObjectMapper();

    @Override
    public Person mapRow( ResultSet rs, int rowNum ) throws SQLException {
        try {
            var jsonContent = rs.getBytes( 2 );
            var content = objectMapper.readValue( jsonContent, JsonContent.class );

            var person = new Person();
            person.setId( rs.getLong( 1 ) );
            person.setContent( content );
            return person;
        } catch ( IOException e ) {
            throw new RuntimeException( "JSON unmarschalling failed!", e );
        }
    }

}

4. Use it in repository (not mentioned in your case) as

@Repository
public interface PersonRepository extends CrudRepository<Person, Long> {

    @Query( value = "SELECT id, ctnt FROM PERSON", rowMapperClass = PersonMapper.class )
    @Override
    List<Person> findAll();

}

Note: you can even simplify it with the spring-data-jpa as:

1. Define entity

@Entity
@Table( name = Person.TABLE_NAME )
@Data
@Relation( collectionRelation = "persons" )
public class Person {
    static final String TABLE_NAME = "PERSON";
    @Id
    @GeneratedValue
    private Long id;
    @Column( name = "CTNT" )
    @Convert( converter = JsonContentConverter.class )
    private JsonContent content;
}

2. And the converter

public class JsonContentConverter implements AttributeConverter<JsonContent, byte[]> {
    static ObjectMapper objectMapper = new ObjectMapper();
    @Override
    public byte[] convertToDatabaseColumn( JsonContent attribute ) {
        try {
            return objectMapper.writeValueAsBytes( attribute );
        } catch ( JsonProcessingException e ) {
            throw new RuntimeException( "JSON marschalling failed!", e );
        }
    }
    @Override
    public JsonContent convertToEntityAttribute( byte[] jsonContent ) {
        try {
            return objectMapper.readValue( jsonContent, JsonContent.class );
        } catch ( IOException e ) {
            throw new RuntimeException( "JSON unmarschalling failed!", e );
        }
    }
}