Oracle Database – With Spring Boot

Connecting Spring Boot to Oracle is four lines of configuration. Getting it right — so it performs under load, generates keys correctly, and does not surprise you in production — takes a bit more. This post is the checklist.

The driver

Oracle's JDBC driver has been on Maven Central since 2019, so no more manual installs into a local repository. Pick the artifact by JDK version:

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc11</artifactId>   <!-- JDK 11+. ojdbc17 for JDK 17+, ojdbc8 for JDK 8 -->
  <scope>runtime</scope>
</dependency>

Better still, let Spring Boot's dependency management pick the version by using the bill of materials artifact, so the driver, its NLS data and any wallet support stay in step:

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc-bom</artifactId>
  <version>23.5.0.24.07</version>
  <type>pom</type>
  <scope>import</scope>
</dependency>

runtime scope is correct — nothing in your code should import an oracle.* class. If something does, that is a portability problem worth looking at.

The connection URL

Three forms exist and two of them are legacy. Use the service-name form:

jdbc:oracle:thin:@//localhost:1521/FREEPDB1      <- service name. Use this.
jdbc:oracle:thin:@localhost:1521/FREEPDB1        <- same thing, // optional
jdbc:oracle:thin:@localhost:1521:ORCL            <- SID. Legacy; note the colon.
jdbc:oracle:thin:@my_tns_alias                   <- TNS alias, needs tnsnames.ora

The difference between the colon and the slash is the difference between a SID and a service name, and it is the most common cause of ORA-12505 / ORA-12514 on a first connection. Service names are what the listener actually publishes and what RAC and Data Guard rely on — SIDs cannot address a cluster.

Remember the container database point from earlier in this track: connect to the PDB service (FREEPDB1), never the CDB (FREE).

application.yml

spring:
  datasource:
    url: jdbc:oracle:thin:@//localhost:1521/FREEPDB1
    username: shop_svc
    password: ${DB_PASSWORD}
    driver-class-name: oracle.jdbc.OracleDriver     # optional, inferred from the URL
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 10000                    # fail fast, don't hang the request thread
      max-lifetime: 1200000                        # 20 min; stay under any firewall idle timeout
      validation-timeout: 3000
      connection-test-query: SELECT 1 FROM dual     # only needed for very old drivers
      data-source-properties:
        oracle.jdbc.implicitStatementCacheSize: 50  # cache prepared statements per connection
        oracle.net.CONNECT_TIMEOUT: 10000
        oracle.jdbc.ReadTimeout: 60000
  jpa:
    open-in-view: false
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        jdbc:
          batch_size: 50
          fetch_size: 100
          time_zone: UTC
        order_inserts: true
        order_updates: true
        query.in_clause_parameter_padding: true

The settings that actually matter:

implicitStatementCacheSize is Oracle-specific and one of the highest-value settings on the list. It caches parsed statements per physical connection, so repeated queries skip the parse entirely. It defaults to 0 — off.
ddl-auto: validate, never update or create. validate fails fast at startup when the entities and the schema disagree, which is exactly what you want. Schema changes belong in Flyway or Liquibase.
open-in-view: false. The default holds a database connection for the whole request while the view renders. On Oracle, where connections are relatively expensive, that is a throughput ceiling.
max-lifetime under any network idle timeout. Firewalls and Oracle's own SQLNET.EXPIRE_TIME silently drop idle TCP connections, and the pool then hands out a dead one — ORA-03113: end-of-file on communication channel on a query that worked a minute ago.
in_clause_parameter_padding rounds IN lists up to powers of two, so a query with 3, 4 and 5 parameters shares one cursor instead of three. Real shared-pool relief on Oracle.
hibernate.jdbc.time_zone: UTC. Without it, timestamps are converted using the JVM's default zone, and your data changes meaning when a container moves region.

Hibernate 6 detects the Oracle dialect from the connection, so do not set hibernate.dialect. Pinning it to Oracle12cDialect — a name you will find in a lot of old configuration — is now deprecated and loses features. If you must be explicit, it is org.hibernate.dialect.OracleDialect.

An entity

import jakarta.persistence.*;
import lombok.*;

import java.math.BigDecimal;
import java.time.OffsetDateTime;

@Entity
@Table(name = "ORDERS")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString(onlyExplicitlyIncluded = true)
@EqualsAndHashCode(onlyExplicitlyIncluded = true)
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "order_seq_gen")
    @SequenceGenerator(name = "order_seq_gen", sequenceName = "ORDER_SEQ", allocationSize = 50)
    @EqualsAndHashCode.Include
    @ToString.Include
    @Column(name = "ID")
    private Long id;

    @Column(name = "CUSTOMER_ID", nullable = false)
    private Long customerId;

    @ToString.Include
    @Column(name = "TOTAL", nullable = false, precision = 12, scale = 2)
    private BigDecimal total;

    @Column(name = "STATUS", length = 20, nullable = false)
    @Enumerated(EnumType.STRING)
    private OrderStatus status;

    @Column(name = "IS_PAID", nullable = false)
    private boolean paid;                       // maps to NUMBER(1)

    @Column(name = "CREATED_AT", nullable = false)
    private OffsetDateTime createdAt;           // TIMESTAMP WITH TIME ZONE

    @Version
    @Column(name = "VERSION", nullable = false)
    private Long version;                       // optimistic locking
}

Details in there that are deliberate:

@EqualsAndHashCode(onlyExplicitlyIncluded = true) on the id only. Lombok's default generates equals over every field, which on an entity means loading lazy associations inside hashCode() and breaking Set semantics as soon as the entity is persisted and gains an id.
@ToString(onlyExplicitlyIncluded = true) for the same reason: a default toString() on an entity with a lazy collection triggers a LazyInitializationException from a log statement.
@Enumerated(EnumType.STRING). The default is ORDINAL, which stores an array index — reorder the enum and the historical data silently changes meaning.
Uppercase names. Oracle folds unquoted identifiers to uppercase, so that is how the dictionary holds them. Being explicit avoids a class of quoting problems.

allocationSize: the classic Oracle/JPA bug

This one bites nearly everyone once. @SequenceGenerator's allocationSize tells Hibernate how many ids it may hand out per trip to the database. Hibernate assumes the sequence increments by that amount. It must equal the sequence's INCREMENT BY.

-- Matching pair: allocationSize = 50, INCREMENT BY 50
CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 50 CACHE 50 NOCYCLE;

Get it wrong and you get ORA-00001: unique constraint violated under concurrency, on inserts that look perfectly correct:

Sequence	`allocationSize`	Result
`INCREMENT BY 50`	50	Correct. One round trip per 50 inserts.
`INCREMENT BY 1`	50 (JPA's default)	Broken. Hibernate takes value 1 and assumes 1–50 are its own. Another instance takes 2 and assumes 2–51. Collision.
`INCREMENT BY 1`	1	Correct but slow — a round trip per insert.
`INCREMENT BY 50`	1	Correct, and wastes 98% of the id space.

Note that allocationSize defaults to 50, so writing @SequenceGenerator(sequenceName = "ORDER_SEQ") against an INCREMENT BY 1 sequence — the most natural thing to write — is exactly the broken row. Set both numbers explicitly, in the same commit.

Or use IDENTITY, with one caveat

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

Simpler, and it matches GENERATED ALWAYS AS IDENTITY in the DDL. The catch: Hibernate cannot batch inserts for an IDENTITY id, because it must execute each insert to learn the generated key. On a write-heavy path, SEQUENCE with a matched allocation size is meaningfully faster. For ordinary CRUD, IDENTITY is fine and one less thing to get wrong.

Batching writes

batch_size alone is not enough. Hibernate can only batch consecutive statements against the same table, so without order_inserts an interleaved save of orders and order-items produces batches of one:

spring:
  jpa:
    properties:
      hibernate:
        jdbc.batch_size: 50
        order_inserts: true
        order_updates: true
        batch_versioned_data: true    # allow batching for @Version entities too

For a genuine bulk load — hundreds of thousands of rows — skip JPA. JdbcTemplate with batchUpdate is several times faster and uses flat memory:

@RequiredArgsConstructor
@Repository
public class OrderBulkRepository {

    private final JdbcTemplate jdbc;

    public void insertAll(List<Order> orders) {
        jdbc.batchUpdate("""
                INSERT INTO orders (customer_id, total, status, created_at)
                VALUES (?, ?, ?, ?)
                """,
                orders,
                500,                                  // batch size
                (ps, o) -> {
                    ps.setLong(1, o.getCustomerId());
                    ps.setBigDecimal(2, o.getTotal());
                    ps.setString(3, o.getStatus().name());
                    ps.setObject(4, o.getCreatedAt());
                });
    }
}

Calling PL/SQL

Existing Oracle systems keep real logic in packages, and you will have to call it. For a function, a SimpleJdbcCall is the least ceremonial route:

@RequiredArgsConstructor
@Service
public class OrderApiGateway {

    private final JdbcTemplate jdbc;

    public BigDecimal total(long orderId) {
        return new SimpleJdbcCall(jdbc)
                .withCatalogName("ORDER_API")          // the package
                .withFunctionName("TOTAL")
                .executeFunction(BigDecimal.class, orderId);
    }
}

For a procedure with OUT parameters, use withProcedureName("CLOSE_IT") and execute(...), which returns a Map of the outputs. If the procedure returns a SYS_REFCURSOR, declare it with declareParameters(new SqlOutParameter("P_CURSOR", OracleTypes.CURSOR, rowMapper)) — one of the rare cases where an oracle.* import is unavoidable.

Native queries and pagination

Spring Data's Pageable generates OFFSET … FETCH NEXT on Oracle 12c+, so paging works with no special handling. In a native query you write it yourself:

@Query(value = """
        SELECT o.* FROM orders o
        WHERE  o.customer_id = :customerId
        ORDER  BY o.order_date DESC, o.id DESC
        OFFSET :offset ROWS FETCH NEXT :size ROWS ONLY
        """, nativeQuery = true)
List<Order> recentForCustomer(@Param("customerId") Long customerId,
                              @Param("offset") int offset,
                              @Param("size") int size);

The , o.id DESC tiebreaker is not decoration — without it, rows sharing an order_date have no defined order and can appear on two consecutive pages.

Testing against real Oracle

H2's Oracle compatibility mode is a trap: it accepts VARCHAR2 and DUAL, and then diverges on exactly the things this track has been about — empty-string handling, date semantics, MERGE, sequences. Test against Oracle.

@Testcontainers
@SpringBootTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class OrderRepositoryTest {

    @Container
    @ServiceConnection                       // Boot 3.1+: wires the datasource automatically
    static final OracleContainer ORACLE =
            new OracleContainer("gvenzl/oracle-free:23-slim-faststart")
                    .withReuse(true);        // keep it between runs; startup is the slow part

    @Autowired
    private OrderRepository orders;

    @Test
    void persists_and_reads_back() {
        Order saved = orders.save(Order.builder()
                .customerId(1L)
                .total(new BigDecimal("99.95"))
                .status(OrderStatus.NEW)
                .createdAt(OffsetDateTime.now())
                .build());

        assertThat(saved.getId()).isNotNull();
        assertThat(orders.findById(saved.getId())).isPresent();
    }
}

Two things make this practical rather than painful: the faststart image variant ships a pre-created database so it comes up in seconds instead of minutes, and withReuse(true) (plus testcontainers.reuse.enable=true in ~/.testcontainers.properties) keeps the container alive between test runs.

Transactions

@Transactional                                 // read-write, default propagation REQUIRED
public void transfer(long from, long to, BigDecimal amount) { ... }

@Transactional(readOnly = true)                 // skips dirty checking, flags the JDBC connection
public List<Order> findRecent() { ... }

@Transactional(isolation = Isolation.SERIALIZABLE)   // needs retry: expect ORA-08177
public void reconcile() { ... }

@Transactional(timeout = 10)                    // seconds; bounds a runaway statement
public void report() { ... }

Reminders that bite in Oracle specifically:

Oracle supports only READ_COMMITTED and SERIALIZABLE. Asking for REPEATABLE_READ throws at runtime, not at startup.
@Transactional is a proxy, so a call from one method of a bean to another within the same bean bypasses it entirely. Classic silent no-op.
Native DDL inside a transactional method commits it — see the transactions post. Migrations belong in Flyway.
Catch CannotAcquireLockException (Spring's translation of ORA-00060/ORA-00054) and OptimisticLockingFailureException at the boundary and retry the whole unit of work, not the statement.

Health check and connection troubleshooting

management:
  endpoint.health.show-details: always
  health.db.enabled: true

Boot's DataSourceHealthIndicator runs SELECT 1 FROM DUAL on Oracle, so /actuator/health tells you whether the pool can hand out a working connection. The errors you will see most, and what they mean:

Error	Cause
`ORA-12514`	Listener does not know that service. Wrong service name, or the database is still starting.
`ORA-12505`	Same, but you used the SID form against a service name.
`ORA-01017`	Invalid username/password. Also what you get when the password expired.
`ORA-28000`	Account locked — too many failed logins, often a stale password in one instance.
`ORA-00942`	Table does not exist or you cannot see it. Usually a missing schema prefix or grant.
`ORA-01950`	No tablespace quota. The user can create tables but not put rows in them.
`ORA-03113`	Connection died underneath the pool. Lower `max-lifetime`.
`ORA-04068`	Package state discarded — a package was recompiled while sessions held its state. Retry.

That's the track

Twelve posts, from the architecture down to a tested Spring Boot application. The through-line, if there is one: Oracle rewards being explicit. Name your constraints, declare VARCHAR2(n CHAR), write the frame clause, quote the string, set the allocation size, pass the service name. Every one of the gotchas in this track is somewhere Oracle accepted a default and did something reasonable that was not what you meant.