Oracle DB Integration

<!-- pom.xml — Oracle JDBC driver (from Oracle Maven repository): -->
<!-- Add to pom.xml repositories section: -->
<repository>
    <id>oracle</id>
    <url>https://maven.oracle.com</url>
</repository>

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>23.3.0.23.09</version>
    <scope>runtime</scope>
</dependency>

<!-- Oracle UCP (Universal Connection Pool) — replaces HikariCP for Oracle: -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ucp11</artifactId>
    <version>23.3.0.23.09</version>
</dependency>

<!-- Or use HikariCP (default Spring Boot pool) with ojdbc11: -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

# ── application.yml: ──────────────────────────────────────────────────
spring:
  datasource:
    url: jdbc:oracle:thin:@//localhost:1521/XEPDB1
    # Or using TNS: jdbc:oracle:thin:@mydb_tns
    # Or using wallet (Cloud): jdbc:oracle:thin:@mydb_high?TNS_ADMIN=/wallet
    username: appuser
    password: ${DB_PASSWORD}
    driver-class-name: oracle.jdbc.OracleDriver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 20000
      max-lifetime: 1800000
      connection-test-query: SELECT 1 FROM DUAL   # Oracle keepalive

  jpa:
    hibernate:
      ddl-auto: validate
    open-in-view: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.OracleDialect
        format_sql: true
        jdbc:
          batch_size: 25

  flyway:
    enabled: true
    locations: classpath:db/migration/oracle

@Entity
@Table(name = "PRODUCTS",       // Oracle defaults to UPPERCASE table names
       schema = "MYSCHEMA")     // Oracle schema (equivalent to MySQL database)
public class Product {

    // Oracle SEQUENCE — preferred over IDENTITY in Oracle:
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
                    generator = "products_seq")
    @SequenceGenerator(
        name = "products_seq",
        sequenceName = "PRODUCTS_SEQ",   // must CREATE SEQUENCE in migration
        allocationSize = 20              // fetch 20 IDs at once
    )
    private Long id;

    // Oracle VARCHAR2 — max 4000 chars (use CLOB for longer):
    @Column(name = "PRODUCT_NAME", nullable = false, length = 200)
    private String name;

    // Oracle CLOB for long text:
    @Lob
    @Column(name = "DESCRIPTION", columnDefinition = "CLOB")
    private String description;

    // Oracle NUMBER(10,2) for BigDecimal:
    @Column(name = "PRICE", nullable = false,
            precision = 10, scale = 2)
    private BigDecimal price;

    // Oracle has no BOOLEAN — use NUMBER(1): ─────────────────────────
    @Column(name = "ACTIVE", nullable = false)
    @Convert(converter = BooleanToNumberConverter.class)
    private boolean active = true;

    // Oracle DATE for timestamps (stores up to seconds):
    @Column(name = "CREATED_AT", nullable = false, updatable = false)
    private LocalDateTime createdAt;

    // Oracle TIMESTAMP for microseconds:
    @Column(name = "UPDATED_AT", columnDefinition = "TIMESTAMP")
    private LocalDateTime updatedAt;

    // Oracle BLOB for binary data:
    @Lob
    @Column(name = "THUMBNAIL", columnDefinition = "BLOB")
    private byte[] thumbnail;
}

// ── Boolean converter — Oracle NUMBER(1) → boolean: ──────────────────
@Converter
public class BooleanToNumberConverter
        implements AttributeConverter<Boolean, Integer> {

    @Override
    public Integer convertToDatabaseColumn(Boolean value) {
        return value != null && value ? 1 : 0;
    }

    @Override
    public Boolean convertToEntityAttribute(Integer value) {
        return value != null && value == 1;
    }
}

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // ── Oracle MERGE (UPSERT): ────────────────────────────────────────
    @Modifying
    @Transactional
    @Query(value = """
            MERGE INTO products p
            USING (SELECT :id AS id, :name AS name, :price AS price FROM DUAL) src
            ON (p.id = src.id)
            WHEN MATCHED THEN
                UPDATE SET p.name = src.name, p.price = src.price,
                           p.updated_at = SYSDATE
            WHEN NOT MATCHED THEN
                INSERT (id, name, price, created_at)
                VALUES (src.id, src.name, src.price, SYSDATE)
            """,
           nativeQuery = true)
    void mergeProduct(@Param("id") Long id,
                      @Param("name") String name,
                      @Param("price") BigDecimal price);

    // ── Oracle CONNECT BY — hierarchical query: ───────────────────────
    @Query(value = """
            SELECT id, name, parent_id, LEVEL AS depth
            FROM categories
            START WITH parent_id IS NULL
            CONNECT BY PRIOR id = parent_id
            ORDER SIBLINGS BY name
            """,
           nativeQuery = true)
    List<Object[]> findCategoryHierarchy();

    // ── Oracle hints for query plan control: ──────────────────────────
    @Query(value = """
            SELECT /*+ INDEX(p IDX_PRODUCTS_CATEGORY) */
                   p.*
            FROM products p
            WHERE p.category = :category
              AND p.active = 1
            ORDER BY p.price ASC
            """,
           nativeQuery = true)
    List<Product> findByCategoryWithHint(@Param("category") String category);

    // ── Oracle analytic functions: ────────────────────────────────────
    @Query(value = """
            SELECT p.*,
                   RANK() OVER (PARTITION BY p.category ORDER BY p.price) AS price_rank,
                   SUM(p.price) OVER (PARTITION BY p.category) AS category_total
            FROM products p
            WHERE p.active = 1
            """,
           nativeQuery = true)
    List<Object[]> findWithAnalytics();
}

// ── Stored procedure with @Procedure: ────────────────────────────────
@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    // Calls Oracle stored procedure: RECALCULATE_STATS(p_user_id IN NUMBER)
    @Procedure(procedureName = "RECALCULATE_STATS")
    void recalculateStats(Long userId);

    // Procedure with OUT parameter:
    @Procedure(procedureName = "GET_USER_DISCOUNT")
    BigDecimal getUserDiscount(Long userId);
}

// ── Using EntityManager for stored procedures directly: ───────────────
@Service
@RequiredArgsConstructor
public class OracleService {

    private final EntityManager em;

    @Transactional
    public String callProcedure(Long userId) {
        StoredProcedureQuery query = em
            .createStoredProcedureQuery("GET_USER_INFO")
            .registerStoredProcedureParameter("p_user_id", Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter("p_result", String.class, ParameterMode.OUT)
            .setParameter("p_user_id", userId);

        query.execute();
        return (String) query.getOutputParameterValue("p_result");
    }
}

# ── application.yml — Oracle-specific Flyway location: ───────────────
spring:
  flyway:
    locations: classpath:db/migration/oracle
    schemas: MYSCHEMA
    default-schema: MYSCHEMA

# ── Migration file layout: ────────────────────────────────────────────
# src/main/resources/db/migration/oracle/
# ├── V1__create_sequences.sql
# ├── V2__create_products_table.sql
# ├── V3__create_indexes.sql
# └── V4__create_procedures.sql

-- V1__create_sequences.sql:
CREATE SEQUENCE PRODUCTS_SEQ
    START WITH 1
    INCREMENT BY 20
    NOCACHE
    NOCYCLE;

CREATE SEQUENCE ORDERS_SEQ
    START WITH 1
    INCREMENT BY 50
    NOCACHE
    NOCYCLE;

-- V2__create_products_table.sql:
CREATE TABLE PRODUCTS (
    ID              NUMBER(19)      NOT NULL,
    PRODUCT_NAME    VARCHAR2(200)   NOT NULL,
    DESCRIPTION     CLOB,
    PRICE           NUMBER(10,2)    NOT NULL,
    CATEGORY        VARCHAR2(100),
    ACTIVE          NUMBER(1)       DEFAULT 1 NOT NULL,
    CREATED_AT      DATE            DEFAULT SYSDATE NOT NULL,
    UPDATED_AT      TIMESTAMP,
    CONSTRAINT PK_PRODUCTS PRIMARY KEY (ID),
    CONSTRAINT CK_PRODUCTS_ACTIVE CHECK (ACTIVE IN (0, 1))
);

-- V3__create_indexes.sql:
CREATE INDEX IDX_PRODUCTS_CATEGORY ON PRODUCTS (CATEGORY);
CREATE INDEX IDX_PRODUCTS_ACTIVE_PRICE ON PRODUCTS (ACTIVE, PRICE);

-- V4__create_procedures.sql:
CREATE OR REPLACE PROCEDURE RECALCULATE_STATS (
    p_user_id IN NUMBER
) AS
BEGIN
    UPDATE user_stats
    SET order_count = (SELECT COUNT(*) FROM orders WHERE user_id = p_user_id),
        updated_at  = SYSDATE
    WHERE user_id = p_user_id;
    COMMIT;
END RECALCULATE_STATS;
/

# ── docker-compose.yml — Oracle XE for local development: ────────────
services:
  oracle:
    image: container-registry.oracle.com/database/express:21.3.0-xe
    container_name: myapp-oracle
    environment:
      ORACLE_PWD: rootpassword
      ORACLE_CHARACTERSET: AL32UTF8
    ports:
      - "1521:1521"
      - "5500:5500"    # Oracle Enterprise Manager Express
    volumes:
      - oracle_data:/opt/oracle/oradata
    # NOTE: Oracle XE Docker image requires login to container-registry.oracle.com
    # docker login container-registry.oracle.com

volumes:
  oracle_data:

# ── application-dev.yml: ──────────────────────────────────────────────
spring:
  datasource:
    url: jdbc:oracle:thin:@//localhost:1521/XEPDB1
    username: appuser
    password: apppassword

# ── Testcontainers — Oracle (slow startup ~2-3 minutes): ──────────────
<!-- pom.xml: -->
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>oracle-xe</artifactId>
    <scope>test</scope>
</dependency>

@SpringBootTest
@Testcontainers
class ProductRepositoryOracleTest {

    @Container
    static OracleContainer oracle =
        new OracleContainer("gvenzl/oracle-xe:21-slim-faststart")
            .withUsername("testuser")
            .withPassword("testpassword");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", oracle::getJdbcUrl);
        registry.add("spring.datasource.username", oracle::getUsername);
        registry.add("spring.datasource.password", oracle::getPassword);
        registry.add("spring.datasource.driver-class-name",
            () -> "oracle.jdbc.OracleDriver");
    }

    @Autowired
    private ProductRepository productRepository;

    @Test
    void mergeCreatesAndUpdatesProduct() {
        productRepository.mergeProduct(1L, "Widget", new BigDecimal("9.99"));
        productRepository.mergeProduct(1L, "Widget Pro", new BigDecimal("19.99"));

        Optional<Product> found = productRepository.findById(1L);
        assertThat(found).isPresent();
        assertThat(found.get().getName()).isEqualTo("Widget Pro");
    }
}