Spring Data JDBC（含v1.1.5）是否推荐用于Oracle 18c及企业应用？

Hey there! Let's dive into your questions and work through the Oracle-specific hurdles you're hitting with Spring Data JDBC in your Spring Boot microservices.

1. Should I use Spring Data JDBC with Oracle 18c? #

Absolutely. Spring Data JDBC plays nicely with Oracle 18c—its lightweight, SQL-focused approach is a great fit if you want to avoid the heavyweight ORM overhead of JPA/Hibernate. The catch? You’ll need to handle Oracle-specific data types manually (which you’ve already noticed), but that’s manageable with custom converters.

2. Is Spring Data JDBC v1.1.5 suitable for Oracle enterprise applications? #

Honestly, I’d recommend upgrading to a newer version if possible. v1.1.5 is tied to Spring Boot 2.1.x, which is no longer actively supported. Newer versions (like v2.x+) have better Oracle compatibility, bug fixes, and improved handling of database-specific features. If you’re stuck on v1.1.5 for legacy reasons, it’s usable—but you’ll have to work around the type conversion and identity issues you’ve described.

Custom Converters for oracle.sql.* Types #

Spring Data JDBC doesn’t ship with built-in converters for Oracle’s proprietary types like oracle.sql.TIMESTAMP, TIMESTAMPTZ, DATE, or ROWID. You’ll need to implement Spring’s Converter interface to bridge these types with standard Java types.

Here’s an example for oracle.sql.DATE and java.util.Date:

import oracle.sql.DATE;
import org.springframework.core.convert.converter.Converter;
import java.sql.Timestamp;
import java.util.Date;

// Convert Oracle DATE to Java util.Date
public class OracleDateToUtilDateConverter implements Converter<DATE, Date> {
    @Override
    public Date convert(DATE source) {
        return source != null ? source.timestampValue() : null;
    }
}

// Convert Java util.Date to Oracle DATE
public class UtilDateToOracleDateConverter implements Converter<Date, DATE> {
    @Override
    public DATE convert(Date source) {
        return source != null ? new DATE(new Timestamp(source.getTime())) : null;
    }
}

Register these converters in a Spring configuration class:

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jdbc.core.convert.JdbcCustomConversions;
import java.util.Arrays;

@Configuration
public class OracleJdbcConfig {
    @Bean
    public JdbcCustomConversions jdbcCustomConversions() {
        return new JdbcCustomConversions(Arrays.asList(
            new OracleDateToUtilDateConverter(),
            new UtilDateToOracleDateConverter(),
            // Add converters for TIMESTAMP, TIMESTAMPTZ here
        ));
    }
}

Converting oracle.sql.ROWID to java.lang.Number #

Oracle ROWID is a string-based identifier (it’s a base64-like value representing the physical location of a row), so converting it directly to a Number won’t work. Instead, map it to a String in your entity:

public class YourEntity {
    private String rowId; // Map ROWID to String instead of Number
    
    // Getters and setters
}

If you absolutely need a numeric value, you’d have to parse the ROWID string into a number—but this isn’t recommended, as ROWIDs aren’t designed to be used as numeric identifiers. Stick with String for reliability.

Non-Null Identity After Save #

Spring Data JDBC expects the database to return the generated identity value after an insert. For Oracle 18c (which supports identity columns), make sure your entity and table are set up correctly:

1. Entity annotation:

import org.springframework.data.annotation.Id;
import org.springframework.data.annotation.GeneratedValue;
import org.springframework.data.jdbc.core.mapping.AggregateRoot;

@AggregateRoot
public class YourEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    // Other fields and methods
}

2. Database table creation:

CREATE TABLE your_entity (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- Other columns
);

If you’re using legacy Oracle sequences instead of identity columns, you’ll need a custom BeforeSaveListener to fetch the next sequence value before inserting:

import org.springframework.data.jdbc.core.mapping.event.BeforeSaveEvent;
import org.springframework.data.jdbc.core.mapping.event.JdbcEventListener;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class SequencePreSaveListener implements JdbcEventListener<YourEntity> {
    private final JdbcTemplate jdbcTemplate;

    public SequencePreSaveListener(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void onBeforeSave(BeforeSaveEvent<YourEntity> event) {
        YourEntity entity = event.getEntity();
        if (entity.getId() == null) {
            Long nextId = jdbcTemplate.queryForObject(
                "SELECT your_entity_seq.NEXTVAL FROM DUAL", 
                Long.class
            );
            entity.setId(nextId);
        }
    }
}

内容的提问来源于stack exchange，提问作者bmk1977