Oracle DB Integration

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-oracle</artifactId>
</dependency>

spring:
  datasource:
    # Service name (recommended):  @//host:port/service
    url: jdbc:oracle:thin:@//localhost:1521/XEPDB1
    # Older SID form:               jdbc:oracle:thin:@localhost:1521:XE
    # TNS alias or cloud wallet:    jdbc:oracle:thin:@mydb_high?TNS_ADMIN=/path/to/wallet
    username: appuser
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 20000
      max-lifetime: 1500000        # 25 min: keep below any firewall idle timeout

  jpa:
    hibernate:
      ddl-auto: validate
    open-in-view: false
    properties:
      hibernate:
        format_sql: true
        jdbc:
          batch_size: 25
        order_inserts: true
        order_updates: true

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

-- src/main/resources/db/migration/V1__create_sequence_and_products.sql
CREATE SEQUENCE PRODUCTS_SEQ START WITH 1 INCREMENT BY 20 CACHE 100 NOCYCLE;

CREATE TABLE PRODUCTS (
    ID            NUMBER(19)         NOT NULL,
    PRODUCT_NAME  VARCHAR2(200 CHAR) NOT NULL,
    DESCRIPTION   CLOB,
    CATEGORY      VARCHAR2(100 CHAR),
    PRICE         NUMBER(10,2)       NOT NULL,
    ACTIVE        NUMBER(1)          DEFAULT 1 NOT NULL,
    CREATED_AT    TIMESTAMP          DEFAULT SYSTIMESTAMP NOT NULL,
    UPDATED_AT    TIMESTAMP,
    THUMBNAIL     BLOB,
    CONSTRAINT PK_PRODUCTS PRIMARY KEY (ID),
    CONSTRAINT CK_PRODUCTS_ACTIVE CHECK (ACTIVE IN (0, 1))
);

CREATE INDEX IDX_PRODUCTS_CATEGORY ON PRODUCTS (CATEGORY);
CREATE INDEX IDX_PRODUCTS_ACTIVE_PRICE ON PRODUCTS (ACTIVE, PRICE);

CREATE TABLE CATEGORIES (
    ID         NUMBER(19)         NOT NULL,
    NAME       VARCHAR2(100 CHAR) NOT NULL,
    PARENT_ID  NUMBER(19),
    CONSTRAINT PK_CATEGORIES PRIMARY KEY (ID),
    CONSTRAINT FK_CATEGORIES_PARENT FOREIGN KEY (PARENT_ID) REFERENCES CATEGORIES (ID)
);

-- src/main/resources/db/migration/V2__create_procedure.sql
CREATE OR REPLACE PROCEDURE DEACTIVATE_CATEGORY (
    p_category IN  VARCHAR2,
    p_count    OUT NUMBER
) AS
BEGIN
    UPDATE PRODUCTS SET ACTIVE = 0, UPDATED_AT = SYSTIMESTAMP
    WHERE CATEGORY = p_category AND ACTIVE = 1;
    p_count := SQL%ROWCOUNT;       -- no COMMIT here: the caller owns the transaction
END DEACTIVATE_CATEGORY;
/

@Entity
@Table(name = "PRODUCTS")        // no schema attribute: the connected user owns the tables
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "products_seq")
    @SequenceGenerator(name = "products_seq",
                       sequenceName = "PRODUCTS_SEQ",
                       allocationSize = 20)          // must equal INCREMENT BY in the migration
    private Long id;

    @Column(name = "PRODUCT_NAME", nullable = false, length = 200)
    private String name;

    @Lob
    @Column(name = "DESCRIPTION")                    // String + @Lob -> CLOB
    private String description;

    @Column(name = "CATEGORY", length = 100)
    private String category;

    @Column(name = "PRICE", nullable = false, precision = 10, scale = 2)
    private BigDecimal price;

    @Column(name = "ACTIVE", nullable = false)       // boolean -> NUMBER(1)
    private boolean active = true;

    @CreationTimestamp
    @Column(name = "CREATED_AT", nullable = false, updatable = false)
    private LocalDateTime createdAt;

    @UpdateTimestamp
    @Column(name = "UPDATED_AT")
    private LocalDateTime updatedAt;

    @Lob
    @Column(name = "THUMBNAIL")                      // byte[] + @Lob -> BLOB
    private byte[] thumbnail;

    // getters and setters omitted
}

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

    @Modifying
    @Transactional
    @Query(value = """
            MERGE INTO PRODUCTS p
            USING (SELECT :id AS src_id, :name AS src_name, :price AS src_price FROM DUAL) src
            ON (p.ID = src.src_id)
            WHEN MATCHED THEN
                UPDATE SET p.PRODUCT_NAME = src.src_name,
                           p.PRICE = src.src_price,
                           p.UPDATED_AT = SYSTIMESTAMP
            WHEN NOT MATCHED THEN
                INSERT (ID, PRODUCT_NAME, PRICE)
                VALUES (src.src_id, src.src_name, src.src_price)
            """, nativeQuery = true)
    void mergeProduct(@Param("id") Long id,
                      @Param("name") String name,
                      @Param("price") BigDecimal price);

    // Hierarchy: roots first, children below their parent, siblings sorted by name
    // Row layout: [ID, NAME, PARENT_ID, DEPTH] -- convert numbers through Number
    @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();

    // Analytic functions: rank within category and category total
    @Query(value = """
            SELECT p.ID, p.PRODUCT_NAME, p.CATEGORY, p.PRICE,
                   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();

    // Optimizer hint: use only after checking statistics and the execution plan.
    // Maps full entities, so it needs every column.
    @Query(value = """
            SELECT /*+ INDEX(p IDX_PRODUCTS_CATEGORY) */ p.*
            FROM PRODUCTS p
            WHERE p.CATEGORY = :category AND p.ACTIVE = 1
            ORDER BY p.PRICE, p.ID
            """, nativeQuery = true)
    List<Product> findByCategoryWithHint(@Param("category") String category);
}

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

    // Derived query: Hibernate generates OFFSET / FETCH for Oracle 12c+
    Page<Product> findByCategory(String category, Pageable pageable);

    // Native query: needs ORDER BY (with a tie-breaker) and a countQuery
    @Query(value = "SELECT * FROM PRODUCTS WHERE ACTIVE = 1 ORDER BY PRICE, ID",
           countQuery = "SELECT COUNT(*) FROM PRODUCTS WHERE ACTIVE = 1",
           nativeQuery = true)
    Page<Product> findActivePage(Pageable pageable);
}

// Usage
Page<Product> page = repository.findActivePage(PageRequest.of(0, 20));

// Spring Data: IN parameter bound by position, single OUT value returned
@Procedure(procedureName = "DEACTIVATE_CATEGORY")
Integer deactivateCategory(String category);

// EntityManager: use when you need several OUT parameters
@Service
public class ProductMaintenanceService {

    @PersistenceContext
    private EntityManager em;

    @Transactional
    public int deactivate(String category) {
        StoredProcedureQuery query = em.createStoredProcedureQuery("DEACTIVATE_CATEGORY")
            .registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
            .registerStoredProcedureParameter(2, Integer.class, ParameterMode.OUT)
            .setParameter(1, category);

        query.execute();
        return (Integer) query.getOutputParameterValue(2);
    }
}

# docker-compose.yml
services:
  oracle:
    image: gvenzl/oracle-xe:21-slim-faststart
    container_name: myapp-oracle
    environment:
      ORACLE_PASSWORD: sysadminpassword     # password for SYS and SYSTEM
      APP_USER: appuser
      APP_USER_PASSWORD: apppassword
    ports:
      - "1521:1521"
    volumes:
      - oracle_data:/opt/oracle/oradata
    healthcheck:
      test: ["CMD", "healthcheck.sh"]
      interval: 10s
      timeout: 5s
      retries: 30

volumes:
  oracle_data:

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

<!-- pom.xml -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>oracle-xe</artifactId>
    <scope>test</scope>
</dependency>

// Test class
@SpringBootTest
@Testcontainers
class ProductRepositoryOracleTest {

    @Container
    @ServiceConnection
    static OracleContainer oracle = new OracleContainer("gvenzl/oracle-xe:21-slim-faststart");

    @Autowired
    private ProductRepository productRepository;

    @BeforeEach
    void cleanTable() {
        productRepository.deleteAll();   // @SpringBootTest does not roll back
    }

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

        Product found = productRepository.findById(1L).orElseThrow();
        assertThat(found.getName()).isEqualTo("Widget Pro");
        assertThat(productRepository.count()).isEqualTo(1);
    }
}

-- Quick checks in SQL*Plus or any SQL client
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM DUAL;           -- which container am I in?
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;     -- which schema am I in?
SELECT table_name FROM user_tables;                            -- which tables do I own?
SELECT privilege FROM user_sys_privs;                          -- which system privileges do I have?
SELECT version, success FROM "flyway_schema_history";          -- failed migrations show success = 0