Oracle #

<!-- Maven — Oracle JDBC tersedia di Maven Central sejak ojdbc8 -->
<!-- Pilih versi berdasarkan Database dan Java yang digunakan -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>  <!-- Java 11+ dan Oracle 12.2+ -->
    <version>23.3.0.23.09</version>
    <!-- Alternatif:
         ojdbc8  → Java 8+ dan Oracle 12.2+
         ojdbc11 → Java 11+ dan Oracle 12.2+
         ojdbc17 → Java 17+ dan Oracle 21c+ (fitur terbaru)
    -->
</dependency>

<!-- Opsional: library tambahan Oracle -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ucp</artifactId>  <!-- Universal Connection Pool Oracle -->
    <version>23.3.0.23.09</version>
</dependency>

// Gradle
implementation 'com.oracle.database.jdbc:ojdbc11:23.3.0.23.09'

-- Jalankan sebagai DBA (SYSDBA)
-- Buat user/skema
CREATE USER toko IDENTIFIED BY "RahasiaKuat123!"
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE, CREATE SESSION TO toko;
GRANT CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO toko;

-- Sambungkan sebagai user toko
CONNECT toko/"RahasiaKuat123!"@localhost:1521/ORCLPDB1

-- Oracle tidak punya AUTO_INCREMENT — gunakan SEQUENCE atau GENERATED AS IDENTITY (12c+)
-- Opsi 1: GENERATED AS IDENTITY (Oracle 12c+, paling mudah)
CREATE TABLE produk (
    id          NUMBER(19)    GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama        VARCHAR2(255) NOT NULL,
    harga       NUMBER(15,2)  NOT NULL,
    stok        NUMBER(10)    DEFAULT 0 NOT NULL,
    kategori    VARCHAR2(100),
    aktif       NUMBER(1)     DEFAULT 1 NOT NULL,  -- 1=true, 0=false (tidak ada BOOLEAN)
    dibuat_pada TIMESTAMP     DEFAULT SYSTIMESTAMP,
    diubah_pada TIMESTAMP     DEFAULT SYSTIMESTAMP
);

-- Opsi 2: SEQUENCE + TRIGGER (Oracle 11g ke bawah)
CREATE SEQUENCE seq_produk_id
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

CREATE TABLE produk_legacy (
    id          NUMBER(19)    PRIMARY KEY,
    nama        VARCHAR2(255) NOT NULL,
    harga       NUMBER(15,2)  NOT NULL,
    stok        NUMBER(10)    DEFAULT 0 NOT NULL,
    kategori    VARCHAR2(100),
    aktif       NUMBER(1)     DEFAULT 1 NOT NULL,
    dibuat_pada DATE          DEFAULT SYSDATE,
    diubah_pada DATE          DEFAULT SYSDATE
);

-- Trigger untuk auto-increment dan update timestamp
CREATE OR REPLACE TRIGGER trg_produk_bi
BEFORE INSERT ON produk_legacy
FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        :NEW.id := seq_produk_id.NEXTVAL;
    END IF;
    :NEW.dibuat_pada := SYSDATE;
    :NEW.diubah_pada := SYSDATE;
END;
/

CREATE OR REPLACE TRIGGER trg_produk_bu
BEFORE UPDATE ON produk_legacy
FOR EACH ROW
BEGIN
    :NEW.diubah_pada := SYSDATE;
END;
/

INSERT INTO produk (nama, harga, stok, kategori) VALUES ('Laptop ProBook', 12000000, 5, 'Elektronik');
INSERT INTO produk (nama, harga, stok, kategori) VALUES ('Mouse Wireless', 150000, 20, 'Aksesori');
INSERT INTO produk (nama, harga, stok, kategori) VALUES ('Keyboard Mekanikal', 450000, 15, 'Aksesori');
COMMIT;

// Thin — SID (nama instance database, format lama)
String urlSID = "jdbc:oracle:thin:@localhost:1521:ORCL";

// Thin — Service Name (format modern, lebih fleksibel)
String urlService = "jdbc:oracle:thin:@//localhost:1521/ORCLPDB1";

// Thin — TNS descriptor (untuk konfigurasi kompleks: RAC, failover)
String urlTNS = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
              + "(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)))";

// Thin — Oracle Cloud (Autonomous Database) dengan Wallet
String urlCloud = "jdbc:oracle:thin:@namadb_high?TNS_ADMIN=/path/to/wallet";

// OCI — butuh Oracle Instant Client di mesin
String urlOCI = "jdbc:oracle:oci:@//localhost:1521/ORCLPDB1";

String username = "toko";
String password = "RahasiaKuat123!";

try (Connection conn = DriverManager.getConnection(urlService, username, password)) {
    System.out.println("Terhubung ke Oracle: "
        + conn.getMetaData().getDatabaseProductVersion());
} catch (SQLException e) {
    System.err.println("Koneksi gagal: " + e.getMessage());
}

try (Connection conn = DriverManager.getConnection(urlService, username, password)) {

    // Oracle 12c+: sintaks standar SQL (direkomendasikan)
    String sqlModern = """
        SELECT id, nama, harga, stok
        FROM produk
        WHERE kategori = ? AND aktif = 1
        ORDER BY harga DESC
        FETCH FIRST ? ROWS ONLY
        """;

    // Oracle 11g ke bawah: ROWNUM (untuk paginasi butuh subquery)
    String sqlLegacy = """
        SELECT id, nama, harga, stok FROM (
            SELECT id, nama, harga, stok, ROWNUM rn
            FROM (
                SELECT id, nama, harga, stok
                FROM produk
                WHERE kategori = ? AND aktif = 1
                ORDER BY harga DESC
            )
            WHERE ROWNUM <= ?    -- batas atas
        )
        WHERE rn > ?             -- batas bawah (untuk paginasi)
        """;

    // Paginasi Oracle 12c+ dengan OFFSET FETCH
    String sqlPaginasi = """
        SELECT id, nama, harga, stok
        FROM produk
        WHERE aktif = 1
        ORDER BY id
        OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
        """;

    try (PreparedStatement ps = conn.prepareStatement(sqlPaginasi)) {
        ps.setInt(1, 10); // lewati 10 baris
        ps.setInt(2, 5);  // ambil 5 baris

        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.printf("%-5d %-25s Rp%,.2f%n",
                    rs.getLong("id"),
                    rs.getString("nama"),
                    rs.getDouble("harga"));
            }
        }
    }
}

// Cara 1: Sequence.NEXTVAL langsung di INSERT
String sql1 = "INSERT INTO produk_legacy (id, nama, harga, stok, kategori) "
            + "VALUES (seq_produk_id.NEXTVAL, ?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(urlService, username, password);
     PreparedStatement ps = conn.prepareStatement(sql1)) {

    ps.setString(1, "SSD 1TB");
    ps.setBigDecimal(2, new java.math.BigDecimal("750000"));
    ps.setInt(3, 30);
    ps.setString(4, "Penyimpanan");
    ps.executeUpdate();
}

// Cara 2: RETURNING INTO — dapatkan ID yang baru dibuat (Oracle-specific)
String sql2 = "INSERT INTO produk (nama, harga, stok, kategori) "
            + "VALUES (?, ?, ?, ?) "
            + "RETURNING id INTO ?";  // RETURNING INTO: Oracle-specific

try (Connection conn = DriverManager.getConnection(urlService, username, password)) {

    // Perlu cast ke OraclePreparedStatement untuk RETURNING INTO
    oracle.jdbc.OraclePreparedStatement ps =
        (oracle.jdbc.OraclePreparedStatement) conn.prepareStatement(sql2);

    ps.setString(1, "RAM 32GB");
    ps.setBigDecimal(2, new java.math.BigDecimal("1200000"));
    ps.setInt(3, 12);
    ps.setString(4, "Komponen");

    // Daftarkan kolom output
    ps.registerReturnParameter(5, java.sql.Types.BIGINT);
    ps.executeUpdate();

    // Baca nilai yang dikembalikan
    try (ResultSet rs = ps.getReturnResultSet()) {
        if (rs.next()) {
            System.out.println("ID baru: " + rs.getLong(1));
        }
    }
    ps.close();
}

// Cara 3: Statement.RETURN_GENERATED_KEYS (cara standar JDBC)
String sql3 = "INSERT INTO produk (nama, harga, stok, kategori) VALUES (?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(urlService, username, password);
     PreparedStatement ps = conn.prepareStatement(sql3,
         new String[]{"id"})) {  // sebutkan nama kolom yang ingin dikembalikan

    ps.setString(1, "Webcam HD");
    ps.setBigDecimal(2, new java.math.BigDecimal("350000"));
    ps.setInt(3, 25);
    ps.setString(4, "Aksesori");
    ps.executeUpdate();

    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (keys.next()) {
            System.out.println("ID baru: " + keys.getLong(1));
        }
    }
}

// ANTI-PATTERN: insert string kosong, mengharapkan "" tapi dapat NULL
String sql = "INSERT INTO produk (nama, kategori) VALUES (?, ?)";
ps.setString(1, "Laptop");
ps.setString(2, "");  // Oracle mengkonversi ini menjadi NULL!

// Cek hasil:
String kategori = rs.getString("kategori");
System.out.println(kategori == null); // true — meskipun kamu insert ""

// BENAR: gunakan NULL secara eksplisit jika memang tidak ada nilai
ps.setNull(2, Types.VARCHAR); // eksplisit — lebih jelas niatnya

// Atau gunakan nilai placeholder jika tidak boleh null
ps.setString(2, kategori.isEmpty() ? "UMUM" : kategori);

-- Buat package untuk mengelompokkan prosedur terkait (praktik terbaik Oracle)
CREATE OR REPLACE PACKAGE pkg_produk AS
    -- Deklarasi tipe REF CURSOR untuk result set
    TYPE t_cursor IS REF CURSOR;

    PROCEDURE get_by_kategori(
        p_kategori  IN  VARCHAR2,
        p_cursor    OUT t_cursor
    );

    PROCEDURE buat_produk(
        p_nama      IN  VARCHAR2,
        p_harga     IN  NUMBER,
        p_stok      IN  NUMBER,
        p_kategori  IN  VARCHAR2,
        p_id_baru   OUT NUMBER,
        p_pesan     OUT VARCHAR2
    );

    PROCEDURE update_stok(
        p_id        IN  NUMBER,
        p_jumlah    IN  NUMBER,   -- positif = tambah, negatif = kurangi
        p_berhasil  OUT NUMBER    -- 1=berhasil, 0=gagal
    );
END pkg_produk;
/

CREATE OR REPLACE PACKAGE BODY pkg_produk AS

    PROCEDURE get_by_kategori(
        p_kategori  IN  VARCHAR2,
        p_cursor    OUT t_cursor
    ) AS
    BEGIN
        OPEN p_cursor FOR
            SELECT id, nama, harga, stok
            FROM produk
            WHERE kategori = p_kategori
              AND aktif = 1
            ORDER BY harga DESC;
    END get_by_kategori;

    PROCEDURE buat_produk(
        p_nama      IN  VARCHAR2,
        p_harga     IN  NUMBER,
        p_stok      IN  NUMBER,
        p_kategori  IN  VARCHAR2,
        p_id_baru   OUT NUMBER,
        p_pesan     OUT VARCHAR2
    ) AS
        v_count NUMBER;
    BEGIN
        -- Cek duplikasi nama
        SELECT COUNT(*) INTO v_count
        FROM produk
        WHERE UPPER(nama) = UPPER(p_nama) AND aktif = 1;

        IF v_count > 0 THEN
            p_id_baru := -1;
            p_pesan   := 'Produk dengan nama "' || p_nama || '" sudah ada';
            RETURN;
        END IF;

        INSERT INTO produk (nama, harga, stok, kategori)
        VALUES (p_nama, p_harga, p_stok, p_kategori)
        RETURNING id INTO p_id_baru;

        p_pesan := 'Produk berhasil dibuat dengan ID ' || TO_CHAR(p_id_baru);
        COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            p_id_baru := -1;
            p_pesan   := 'Error: ' || SQLERRM;
    END buat_produk;

    PROCEDURE update_stok(
        p_id        IN  NUMBER,
        p_jumlah    IN  NUMBER,
        p_berhasil  OUT NUMBER
    ) AS
        v_stok_saat_ini NUMBER;
    BEGIN
        SELECT stok INTO v_stok_saat_ini
        FROM produk
        WHERE id = p_id AND aktif = 1
        FOR UPDATE;  -- lock baris untuk update

        IF v_stok_saat_ini + p_jumlah < 0 THEN
            p_berhasil := 0;  -- stok tidak mencukupi
            RETURN;
        END IF;

        UPDATE produk
        SET stok = stok + p_jumlah
        WHERE id = p_id;

        p_berhasil := 1;
        COMMIT;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            p_berhasil := 0;
        WHEN OTHERS THEN
            ROLLBACK;
            p_berhasil := 0;
    END update_stok;

END pkg_produk;
/

import java.sql.*;

// Panggil pkg_produk.get_by_kategori yang mengembalikan REF CURSOR
String call = "{call pkg_produk.get_by_kategori(?, ?)}";

try (Connection conn = DriverManager.getConnection(urlService, username, password);
     CallableStatement cs = conn.prepareCall(call)) {

    cs.setString(1, "Elektronik");

    // Daftarkan parameter OUT bertipe REF CURSOR
    cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

    cs.execute();

    // Baca REF CURSOR sebagai ResultSet
    try (ResultSet rs = (ResultSet) cs.getObject(2)) {
        System.out.println("Produk Elektronik:");
        while (rs.next()) {
            System.out.printf("  [%d] %-25s Rp%,.2f (stok: %d)%n",
                rs.getLong("id"),
                rs.getString("nama"),
                rs.getDouble("harga"),
                rs.getInt("stok"));
        }
    }
}

// Panggil pkg_produk.buat_produk
String call = "{call pkg_produk.buat_produk(?, ?, ?, ?, ?, ?)}";

try (Connection conn = DriverManager.getConnection(urlService, username, password);
     CallableStatement cs = conn.prepareCall(call)) {

    // Parameter IN
    cs.setString(1, "Monitor 4K");
    cs.setBigDecimal(2, new java.math.BigDecimal("4500000"));
    cs.setInt(3, 3);
    cs.setString(4, "Elektronik");

    // Parameter OUT
    cs.registerOutParameter(5, Types.NUMERIC);   // p_id_baru
    cs.registerOutParameter(6, Types.VARCHAR);    // p_pesan

    cs.execute();

    long idBaru = cs.getLong(5);
    String pesan = cs.getString(6);

    System.out.println("Pesan: " + pesan);
    if (idBaru > 0) {
        System.out.println("Produk baru ID: " + idBaru);
    } else {
        System.err.println("Gagal membuat produk");
    }
}

-- Fungsi Oracle mengembalikan nilai langsung (berbeda dari prosedur)
CREATE OR REPLACE FUNCTION fn_hitung_nilai_stok(p_kategori IN VARCHAR2)
    RETURN NUMBER AS
    v_total NUMBER;
BEGIN
    SELECT SUM(harga * stok) INTO v_total
    FROM produk
    WHERE kategori = p_kategori AND aktif = 1;

    RETURN NVL(v_total, 0);  -- NVL = COALESCE Oracle
END fn_hitung_nilai_stok;
/

// Fungsi Oracle dipanggil dengan ? = {call fungsi(...)}
String call = "{? = call fn_hitung_nilai_stok(?)}";

try (Connection conn = DriverManager.getConnection(urlService, username, password);
     CallableStatement cs = conn.prepareCall(call)) {

    // Daftarkan return value terlebih dahulu
    cs.registerOutParameter(1, Types.NUMERIC);
    cs.setString(2, "Elektronik");

    cs.execute();

    double nilaiStok = cs.getDouble(1);
    System.out.printf("Nilai stok Elektronik: Rp%,.2f%n", nilaiStok);
}

// Tambah kolom CLOB ke tabel
// ALTER TABLE produk ADD deskripsi CLOB;

// Tulis CLOB
String sqlInsert = "UPDATE produk SET deskripsi = ? WHERE id = ?";

try (Connection conn = DriverManager.getConnection(urlService, username, password);
     PreparedStatement ps = conn.prepareStatement(sqlInsert)) {

    String teksP anjang = "Deskripsi sangat panjang... ".repeat(1000); // > 4000 karakter

    // Cara 1: setString — untuk teks pendek (< 4000 karakter)
    // ps.setString(1, teksPanjang);

    // Cara 2: setClob — untuk teks sangat panjang
    java.io.Reader reader = new java.io.StringReader(teksPanjang);
    ps.setCharacterStream(1, reader, teksPanjang.length());
    ps.setLong(2, 1L);
    ps.executeUpdate();
}

// Baca CLOB
String sqlSelect = "SELECT deskripsi FROM produk WHERE id = ?";

try (Connection conn = DriverManager.getConnection(urlService, username, password);
     PreparedStatement ps = conn.prepareStatement(sqlSelect)) {

    ps.setLong(1, 1L);
    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            java.sql.Clob clob = rs.getClob("deskripsi");
            if (clob != null) {
                // Baca sebagai String
                String isi = clob.getSubString(1, (int) clob.length());
                System.out.println("Panjang deskripsi: " + isi.length());

                // Atau baca sebagai stream (untuk teks sangat besar)
                try (java.io.Reader reader = clob.getCharacterStream()) {
                    char[] buffer = new char[4096];
                    int terbaca;
                    StringBuilder sb = new StringBuilder();
                    while ((terbaca = reader.read(buffer)) != -1) {
                        sb.append(buffer, 0, terbaca);
                    }
                }
                clob.free(); // lepaskan resource CLOB
            }
        }
    }
}

// ALTER TABLE produk ADD gambar BLOB;

// Tulis BLOB (gambar/file biner)
String sqlInsert = "UPDATE produk SET gambar = ? WHERE id = ?";

try (Connection conn = DriverManager.getConnection(urlService, username, password);
     PreparedStatement ps = conn.prepareStatement(sqlInsert)) {

    byte[] dataBiner = java.nio.file.Files.readAllBytes(
        java.nio.file.Path.of("gambar/laptop.jpg"));

    // setBytes untuk file kecil
    ps.setBytes(1, dataBiner);
    // setBinaryStream untuk file besar
    // ps.setBinaryStream(1, new java.io.ByteArrayInputStream(dataBiner), dataBiner.length);

    ps.setLong(2, 1L);
    ps.executeUpdate();
}

// Baca BLOB
String sqlSelect = "SELECT gambar FROM produk WHERE id = ?";

try (Connection conn = DriverManager.getConnection(urlService, username, password);
     PreparedStatement ps = conn.prepareStatement(sqlSelect)) {

    ps.setLong(1, 1L);
    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            java.sql.Blob blob = rs.getBlob("gambar");
            if (blob != null) {
                byte[] data = blob.getBytes(1, (int) blob.length());
                java.nio.file.Files.write(java.nio.file.Path.of("output/laptop.jpg"), data);
                blob.free();
            }
        }
    }
}

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class OraclePool {

    private static final HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();

        config.setJdbcUrl("jdbc:oracle:thin:@//localhost:1521/ORCLPDB1");
        config.setUsername("toko");
        config.setPassword("RahasiaKuat123!");
        config.setDriverClassName("oracle.jdbc.OracleDriver");

        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(30_000);
        config.setIdleTimeout(600_000);
        config.setMaxLifetime(1_800_000);

        // Oracle: query validasi koneksi yang sangat ringan
        config.setConnectionTestQuery("SELECT 1 FROM DUAL");

        // Property khusus Oracle
        config.addDataSourceProperty("oracle.net.CONNECT_TIMEOUT", "10000");
        config.addDataSourceProperty("oracle.jdbc.ReadTimeout", "60000");
        config.addDataSourceProperty("defaultRowPrefetch", "50"); // ambil 50 baris per fetch

        config.setPoolName("Oracle-Pool");

        dataSource = new HikariDataSource(config);
    }

    public static java.sql.Connection getConnection() throws java.sql.SQLException {
        return dataSource.getConnection();
    }
}

<!-- Maven -->
<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>

spring:
  datasource:
    url: jdbc:oracle:thin:@//localhost:1521/ORCLPDB1
    username: toko
    password: RahasiaKuat123!
    driver-class-name: oracle.jdbc.OracleDriver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 30000
      connection-test-query: SELECT 1 FROM DUAL
      pool-name: Oracle-Pool
      data-source-properties:
        oracle.net.CONNECT_TIMEOUT: 10000
        defaultRowPrefetch: 50

  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: false
    open-in-view: false
    database-platform: org.hibernate.dialect.OracleDialect
    properties:
      hibernate:
        dialect: org.hibernate.dialect.OracleDialect
        format_sql: true
        jdbc:
          batch_size: 50
          fetch_size: 50
        default_schema: TOKO   # skema default (nama user Oracle, uppercase)

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Table(name = "PRODUK",  // Oracle: nama tabel uppercase
       schema = "TOKO")  // Oracle: nama skema = nama user
public class Produk {

    @Id
    // Oracle 12c+: GENERATED AS IDENTITY — gunakan IDENTITY
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // Oracle < 12c: pakai SEQUENCE
    // @Id
    // @GeneratedValue(strategy = GenerationType.SEQUENCE,
    //                 generator = "seq_produk")
    // @SequenceGenerator(name = "seq_produk",
    //                    sequenceName = "SEQ_PRODUK_ID",
    //                    allocationSize = 1)
    // private Long id;

    @Column(nullable = false, length = 255)
    private String nama;

    @Column(nullable = false, precision = 15, scale = 2)
    private BigDecimal harga;

    @Column(nullable = false)
    private Integer stok = 0;

    @Column(length = 100)
    private String kategori;

    // Oracle tidak punya BOOLEAN — pakai NUMBER(1)
    @Column(nullable = false, columnDefinition = "NUMBER(1) DEFAULT 1")
    private Integer aktif = 1;  // 1=aktif, 0=nonaktif

    // Getter pembantu untuk konversi boolean
    public boolean isAktif() { return aktif != null && aktif == 1; }
    public void setAktifBoolean(boolean aktif) { this.aktif = aktif ? 1 : 0; }

    @Lob  // Oracle CLOB
    @Column(name = "DESKRIPSI", columnDefinition = "CLOB")
    private String deskripsi;

    @Column(name = "DIBUAT_PADA", updatable = false)
    private LocalDateTime dibuatPada;

    @Column(name = "DIUBAH_PADA")
    private LocalDateTime diubahPada;

    @PrePersist
    protected void onCreate() {
        dibuatPada = LocalDateTime.now();
        diubahPada = LocalDateTime.now();
    }

    @PreUpdate
    protected void onUpdate() {
        diubahPada = LocalDateTime.now();
    }

    // Konstruktor, getter, setter
    public Produk() {}
    public Produk(String nama, BigDecimal harga, int stok, String kategori) {
        this.nama = nama; this.harga = harga; this.stok = stok; this.kategori = kategori;
    }
    // ... getter dan setter
}

@Repository
public interface ProdukRepository extends JpaRepository<Produk, Long> {

    // Method name — sama seperti database lain
    List<Produk> findByAktif(Integer aktif); // aktif=1 untuk yang aktif
    List<Produk> findByKategoriAndAktif(String kategori, Integer aktif);

    // JPQL — portable di semua database
    @Query("SELECT p FROM Produk p WHERE p.aktif = 1 ORDER BY p.harga DESC")
    List<Produk> findSemuaAktifUrutHarga();

    // Native query Oracle — gunakan sintaks Oracle
    @Query(value = """
        SELECT id, nama, harga, stok
        FROM toko.produk
        WHERE aktif = 1
          AND UPPER(nama) LIKE UPPER('%' || :kata || '%')
        ORDER BY harga DESC
        FETCH FIRST :limit ROWS ONLY
        """, nativeQuery = true)
    List<Object[]> cariNative(@Param("kata") String kata, @Param("limit") int limit);

    // Panggil stored procedure Oracle via @Procedure
    @Procedure(name = "pkg_produk.update_stok")
    Integer updateStok(@Param("p_id") Long id, @Param("p_jumlah") Integer jumlah);

    // Native query dengan ROWNUM (Oracle 11g dan sebelumnya)
    @Query(value = """
        SELECT * FROM (
            SELECT p.*, ROWNUM rn
            FROM (SELECT id, nama, harga FROM produk WHERE aktif = 1 ORDER BY id) p
            WHERE ROWNUM <= :max
        ) WHERE rn > :min
        """, nativeQuery = true)
    List<Object[]> findWithRownum(@Param("min") int min, @Param("max") int max);

    // Statistik
    @Query(value = """
        SELECT kategori, COUNT(*) jumlah, SUM(stok) total_stok, AVG(harga) rata_harga
        FROM produk
        WHERE aktif = 1
        GROUP BY kategori
        ORDER BY jumlah DESC
        """, nativeQuery = true)
    List<Object[]> statistikPerKategori();
}

// ANTI-PATTERN: asumsi "" dan NULL berbeda seperti di MySQL/SQL Server
String sql = "SELECT * FROM produk WHERE kategori = ?";
ps.setString(1, "");
// Di Oracle: setString(1, "") sama dengan setNull(1, Types.VARCHAR)
// Query akan mencari produk dengan kategori IS NULL, bukan kategori = ''

// BENAR: gunakan IS NULL secara eksplisit
String sqlNull = "SELECT * FROM produk WHERE kategori IS NULL";
// Atau: gunakan NVL untuk menangani null
String sqlNVL = "SELECT * FROM produk WHERE NVL(kategori, 'UMUM') = ?";

// ANTI-PATTERN: literal di query menyebabkan hard parse setiap kali (mahal di Oracle)
String sql = "SELECT * FROM produk WHERE id = " + id;
// Oracle membuat execution plan baru untuk setiap nilai id yang berbeda

// BENAR: bind variable memungkinkan soft parse (reuse execution plan)
String sql = "SELECT * FROM produk WHERE id = ?";
ps.setLong(1, id);
// Oracle menggunakan execution plan yang sama untuk semua nilai id

// Oracle punya batasan jumlah open cursor per session
// Default: 300 cursor per session (bisa dikonfigurasi)

// ANTI-PATTERN: tidak tutup ResultSet atau Statement
ResultSet rs = ps.executeQuery(); // buka cursor
// ... tanpa rs.close() → cursor leak → eventually "ORA-01000: maximum open cursors exceeded"

// BENAR: selalu gunakan try-with-resources
try (PreparedStatement ps = conn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {
    // cursor otomatis ditutup
}