Spring数据Jdbc和Oracle21c

Question

对于我最近的任务，我正在开发一个Spring引导应用程序，它将与Oracle 21c数据库连接。

我们感兴趣的oracle发行版的特性是名为OSON的原生JSON数据类型(参见此处：Oracle 21c JSON数据类型 )。

我开发了一种旧的DAO方法来完成这一任务，但我希望使用Spring项目作为数据访问层，理想情况下，只需要最少的额外配置。

实际上，我正在为存储OSON类型的列的映射而奋斗。经过几次尝试，我获得了下面的错误，遵循为数据类型创建自定义转换器的想法。

对如何进行有任何建议吗？

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>

实体类：

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

        @Id
        private Long id;

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

用于转换器的包装器：

@Data
public class JsonObjectWrapper {

    private OracleJsonValue json;

}

带有自定义转换器的Jdbc配置：

@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;
            }
        }
}

错误：

2022-04-07 :47:27.335调试24220 - nio-8080-exec-1 o.s.jdbc.core.JdbcTemplate :执行准备好的SQL查询2022-04-07 :47:27.335调试24220 - nio-8080-exec-1 o.s.jdbc.core.JdbcTemplate :执行准备好的SQL语句的选择“T_BUDGET”作为"ID"，“T_BUDGET”.“预算”从"T_BUDGET" 2022-04-07 09:48:58.006错误24220 - nio-8080-exec-1 o.a.c.c.C.[.[./.dispatcherServlet : Servlet.service() for servlet dispatcherServlet ]上下文中抛出的异常请求处理失败；嵌套异常是org.springframework.data.mapping.MappingException:无法从结果集读取值预算！有根本原因 oracle.jdbc.driver.GeneratedAccessor.getOracleObject(GeneratedAccessor.java:1221) ~ojdbc11 11-21.1.0.jar:21.1.0.0.0 at oracle.jdbc.driver.JsonAccessor.getObject(JsonAccessor.java:200) ~ojdbc11 11-21.1.0.jar:21.1.0.0.0 at oracle.jdbc.driver.GeneratedStatement.getObject(GeneratedStatement.java:的类java.sql.SQLException：无效列类型: getOracleObject未实现( 196) ~ojdbc11 11-21.1.0.0.jar:21.1.0.0.0在oracle.jdbc.driver.GeneratedScrollableResultSet.getObject(GeneratedScrollableResultSet.java:334) ~ojdbc11 11-21.1.0.0.jar:21.1.0.0.0在com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java) ~HikariCP-3.4.5.jar:na在org.springframework.jdbc.support.JdbcUtils.getResultSetValue(JdbcUtils.java:283) ~spring-jdbc-5.3.8.jar:5.3.8

Answer 1

我也有过同样的问题。我用RowMapper做了这样的修正：

1. 为JSON内容创建DTO (在本例中为JsonObjectWrapper)

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

1. 创建实体(在您的情况下是BudgetEntityData)

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

    @Id
    private Long id;

    private JsonContent content;

}

1. 创建自定义RowMapper (在您的情况下可能是JdbcConfig )

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 );
        }
    }

}

1. 在存储库中使用它(在您的案例中没有提到)

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

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

}

注意：--您甚至可以将其与spring-data-jpa简化为：

1. 定义实体

@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;
}

1. 和转换器

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 );
        }
    }
}