JPA و Hibernate در عمقJPA & Hibernate In Depth

CREATE TABLE book (
    id         bigint         NOT NULL,
    title      varchar(255)   NOT NULL,
    published  boolean,
    price      numeric(38,2),
    created_at timestamp(6),
    PRIMARY KEY (id)
);

CREATE SEQUENCE book_seq START WITH 1 INCREMENT BY 50;

CREATE TABLE book (
    id         number(19,0)       NOT NULL,
    title      varchar2(255 char) NOT NULL,
    published  number(1,0),
    price      number(38,2),
    created_at timestamp(6),
    PRIMARY KEY (id)
);

CREATE SEQUENCE book_seq START WITH 1 INCREMENT BY 50;

SELECT nextval('book_seq');                                  -- id fetched before the INSERT

INSERT INTO book (title, published, price, created_at, id)
VALUES ($1, $2, $3, $4, $5);

UPDATE book SET title = $1, published = $2, price = $3, created_at = $4
WHERE  id = $5;                                              -- result of dirty checking

DELETE FROM book WHERE id = $1;

SELECT book_seq.NEXTVAL FROM dual;                           -- id fetched before the INSERT

INSERT INTO book (title, published, price, created_at, id)
VALUES (:1, :2, :3, :4, :5);

UPDATE book SET title = :1, published = :2, price = :3, created_at = :4
WHERE  id = :5;                                              -- result of dirty checking

DELETE FROM book WHERE id = :1;

SELECT b1_0.id, b1_0.created_at, b1_0.price, b1_0.published, b1_0.title
FROM   book b1_0
WHERE  b1_0.id = $1;

SELECT b1_0.id, b1_0.created_at, b1_0.price, b1_0.published, b1_0.title
FROM   book b1_0
WHERE  b1_0.id = :1;

-- without @DynamicUpdate
UPDATE book SET title = $1, published = $2, price = $3, created_at = $4, content = $5
WHERE  id = $6;

-- with @DynamicUpdate
UPDATE book SET title = $1 WHERE id = $2;

-- without @DynamicUpdate
UPDATE book SET title = :1, published = :2, price = :3, created_at = :4, content = :5
WHERE  id = :6;

-- with @DynamicUpdate
UPDATE book SET title = :1 WHERE id = :2;

-- standard and recommended on PostgreSQL 16/17
CREATE TABLE book (
    id    bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title varchar(255) NOT NULL
);
-- older style: CREATE TABLE book (id bigserial PRIMARY KEY, ...);

-- Hibernate uses RETURNING to get the generated value back
INSERT INTO book (title) VALUES ($1) RETURNING id;

-- Oracle 12c and later has real identity columns
CREATE TABLE book (
    id    number(19,0) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title varchar2(255 char) NOT NULL
);

-- Oracle has no standalone RETURNING; the driver uses getGeneratedKeys
-- (in PL/SQL: INSERT INTO book (title) VALUES (:1) RETURNING id INTO :2;)
INSERT INTO book (title) VALUES (:1);

CREATE SEQUENCE book_seq
    START WITH 1
    INCREMENT BY 50
    CACHE 1;                 -- on PostgreSQL, CACHE is per session

SELECT nextval('book_seq');

SELECT sequencename, increment_by, last_value
FROM   pg_sequences WHERE schemaname = current_schema();

CREATE SEQUENCE book_seq
    START WITH 1
    INCREMENT BY 50
    CACHE 100                -- values pre-allocated in instance memory
    NOORDER;                 -- essential on RAC

SELECT book_seq.NEXTVAL FROM dual;

SELECT sequence_name, increment_by, cache_size, last_number
FROM   user_sequences;

SELECT a1_0.id, a1_0.name FROM author a1_0;

-- then N times, once per author
SELECT b1_0.author_id, b1_0.id, b1_0.title FROM book b1_0 WHERE b1_0.author_id = $1;
SELECT b1_0.author_id, b1_0.id, b1_0.title FROM book b1_0 WHERE b1_0.author_id = $1;

SELECT a1_0.id, a1_0.name FROM author a1_0;

-- then N times, once per author
SELECT b1_0.author_id, b1_0.id, b1_0.title FROM book b1_0 WHERE b1_0.author_id = :1;
SELECT b1_0.author_id, b1_0.id, b1_0.title FROM book b1_0 WHERE b1_0.author_id = :1;

SELECT a1_0.id, a1_0.name, b1_0.author_id, b1_0.id, b1_0.title
FROM   author a1_0
JOIN   book   b1_0 ON a1_0.id = b1_0.author_id;

SELECT a1_0.id, a1_0.name, b1_0.author_id, b1_0.id, b1_0.title
FROM   author a1_0
JOIN   book   b1_0 ON a1_0.id = b1_0.author_id;

SELECT b1_0.author_id, b1_0.id, b1_0.title
FROM   book b1_0
WHERE  b1_0.author_id IN ($1, $2, $3, $4, $5 /* ... up to 25 */);

-- the idiomatic PostgreSQL alternative, which takes a single bind:
SELECT b.author_id, b.id, b.title FROM book b WHERE b.author_id = ANY($1);  -- bigint[]

SELECT b1_0.author_id, b1_0.id, b1_0.title
FROM   book b1_0
WHERE  b1_0.author_id IN (:1, :2, :3, :4, :5 /* ... up to 25 */);

-- Oracle has no ANY(array); the idiomatic equivalent joins a collection type:
-- SELECT b.author_id, b.id FROM book b JOIN TABLE(:ids) t ON b.author_id = t.column_value;

SELECT b1_0.id, b1_0.title, b1_0.price
FROM   book b1_0
ORDER  BY b1_0.id
LIMIT  10 OFFSET 20;

-- PostgreSQL also accepts the standard syntax, fully equivalent:
-- ORDER BY b1_0.id OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

-- Oracle 12c and later: the SQL:2008 standard syntax
SELECT b1_0.id, b1_0.title, b1_0.price
FROM   book b1_0
ORDER  BY b1_0.id
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

-- Oracle 11g and older: two nested ROWNUM layers
-- SELECT * FROM (
--     SELECT inner_.*, ROWNUM rn FROM (
--         SELECT id, title, price FROM book ORDER BY id
--     ) inner_ WHERE ROWNUM <= 30
-- ) WHERE rn > 20;

SELECT b.id, b.title, b.created_at
FROM   book b
WHERE  (b.created_at, b.id) < ($1, $2)     -- standard row-value comparison
ORDER  BY b.created_at DESC, b.id DESC
FETCH  FIRST 20 ROWS ONLY;

SELECT b.id, b.title, b.created_at
FROM   book b
WHERE  (b.created_at, b.id) < ((:1, :2))   -- note: Oracle wants doubled parentheses
ORDER  BY b.created_at DESC, b.id DESC
FETCH  FIRST 20 ROWS ONLY;

CREATE TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id bigint,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer (id)
);

CREATE INDEX ix_orders_customer ON orders (customer_id);

-- note: "order" is a keyword; name the table orders on both engines
CREATE TABLE orders (
    id          number(19,0) PRIMARY KEY,
    customer_id number(19,0),
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer (id)
);

CREATE INDEX ix_orders_customer ON orders (customer_id);

-- what CascadeType.REMOVE really sends: one DELETE per child
SELECT l.id, l.order_id FROM order_line l WHERE l.order_id = $1;
DELETE FROM order_line WHERE id = $1;
DELETE FROM order_line WHERE id = $1;
DELETE FROM orders     WHERE id = $1;

-- the database-level alternative
ALTER TABLE order_line ADD CONSTRAINT fk_line_order
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE;

-- what CascadeType.REMOVE really sends: one DELETE per child
SELECT l.id, l.order_id FROM order_line l WHERE l.order_id = :1;
DELETE FROM order_line WHERE id = :1;
DELETE FROM order_line WHERE id = :1;
DELETE FROM orders     WHERE id = :1;

-- the database-level alternative: Oracle has ON DELETE CASCADE too
ALTER TABLE order_line ADD CONSTRAINT fk_line_order
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE;

-- dangerous: @Lob on PostgreSQL can map to oid (large object)
CREATE TABLE article (id bigint PRIMARY KEY, content oid);

-- what you almost always want:
CREATE TABLE article (id bigint PRIMARY KEY, content text);

-- on Oracle, CLOB is exactly what you want
CREATE TABLE article (id number(19,0) PRIMARY KEY, content clob);

-- VARCHAR2 caps at 4000 bytes by default, so longer text requires CLOB
-- (MAX_STRING_SIZE = EXTENDED raises it to 32767, but it is still a LOB underneath)

CREATE TABLE account (
    id     bigint PRIMARY KEY,
    active boolean NOT NULL DEFAULT false
);

INSERT INTO account (id, active) VALUES (1, true);
SELECT id FROM account WHERE active;          -- usable directly in WHERE

-- Oracle 19c: SQL has no boolean; NUMBER(1) is the common convention
CREATE TABLE account (
    id     number(19,0) PRIMARY KEY,
    active number(1,0) DEFAULT 0 NOT NULL,
    CONSTRAINT ck_account_active CHECK (active IN (0, 1))
);

INSERT INTO account (id, active) VALUES (1, 1);
SELECT id FROM account WHERE active = 1;      -- explicit comparison required

-- Oracle 23ai finally has a real BOOLEAN type:
-- active boolean DEFAULT false NOT NULL

-- on PostgreSQL an empty string is a real value, distinct from NULL
INSERT INTO customer (id, middle_name) VALUES (1, '');

SELECT count(*) FROM customer WHERE middle_name = '';      -- 1
SELECT count(*) FROM customer WHERE middle_name IS NULL;   -- 0
SELECT length(middle_name) FROM customer WHERE id = 1;     -- 0

-- on Oracle an empty string IS NULL — converted silently
INSERT INTO customer (id, middle_name) VALUES (1, '');

SELECT count(*) FROM customer WHERE middle_name = '';      -- 0 (comparison is UNKNOWN)
SELECT count(*) FROM customer WHERE middle_name IS NULL;   -- 1
SELECT length(middle_name) FROM customer WHERE id = 1;     -- NULL, not 0

CREATE TABLE event (
    id        bigint PRIMARY KEY,
    day       date,                        -- date only, no time
    happened  timestamp(6),                -- no time zone
    happenedz timestamptz                  -- normalized to UTC; the zone itself is NOT stored
);

SET TIME ZONE 'UTC';
SELECT now(), current_date;

CREATE TABLE event (
    id        number(19,0) PRIMARY KEY,
    day       date,                        -- careful: Oracle DATE has a time part!
    happened  timestamp(6),                -- no time zone
    happenedz timestamp(6) with time zone  -- the offset/region IS stored
);

ALTER SESSION SET TIME_ZONE = 'UTC';
SELECT systimestamp, TRUNC(SYSDATE) FROM dual;

CREATE TABLE doc (id bigint PRIMARY KEY, payload jsonb NOT NULL);
CREATE INDEX ix_doc_payload ON doc USING gin (payload);

SELECT id FROM doc WHERE payload @> '{"status":"ACTIVE"}';
SELECT payload ->> 'status' AS status FROM doc;

-- Oracle 21c and later has a native JSON type
CREATE TABLE doc (id number(19,0) PRIMARY KEY, payload json NOT NULL);
CREATE SEARCH INDEX ix_doc_payload ON doc (payload) FOR JSON;

SELECT id FROM doc WHERE JSON_EXISTS(payload, '$?(@.status == "ACTIVE")');
SELECT JSON_VALUE(payload, '$.status') AS status FROM doc;

-- Oracle 19c: a CLOB with an IS JSON constraint
-- payload clob CONSTRAINT ck_doc_json CHECK (payload IS JSON)

UPDATE account
SET    balance = $1, version = $2
WHERE  id = $3 AND version = $4;
-- column type: version bigint NOT NULL

UPDATE account
SET    balance = :1, version = :2
WHERE  id = :3 AND version = :4;
-- column type: version number(19,0) NOT NULL

-- exclusive lock, waits
SELECT a.id, a.balance, a.version FROM account a WHERE a.id = $1 FOR UPDATE;

-- no waiting: fails immediately with SQLSTATE 55P03 (lock_not_available)
SELECT a.id FROM account a WHERE a.id = $1 FOR UPDATE NOWAIT;

-- skip locked rows (the job-queue pattern)
SELECT j.id FROM job j WHERE j.status = 'READY'
ORDER BY j.id FETCH FIRST 10 ROWS ONLY FOR UPDATE SKIP LOCKED;

-- bounded wait: PostgreSQL has no "FOR UPDATE WAIT n"
SET LOCAL lock_timeout = '3s';

SELECT a.id FROM account a WHERE a.id = $1 FOR SHARE;   -- shared lock

-- exclusive lock, waits
SELECT a.id, a.balance, a.version FROM account a WHERE a.id = :1 FOR UPDATE;

-- no waiting: fails immediately with ORA-00054 (resource busy)
SELECT a.id FROM account a WHERE a.id = :1 FOR UPDATE NOWAIT;

-- skip locked rows (the job-queue pattern)
SELECT j.id FROM job j WHERE j.status = 'READY'
ORDER BY j.id FETCH FIRST 10 ROWS ONLY FOR UPDATE SKIP LOCKED;

-- bounded wait: Oracle has it natively in the statement
SELECT a.id FROM account a WHERE a.id = :1 FOR UPDATE WAIT 3;

LOCK TABLE account IN SHARE MODE;   -- Oracle has no shared ROW lock

-- all four levels accepted, though READ UNCOMMITTED behaves like READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- true transaction-level snapshot
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;     -- real SSI; error 40001
SHOW default_transaction_isolation;

-- Oracle has only two levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- default; statement-level snapshot
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;     -- effectively snapshot isolation; ORA-08177
-- REPEATABLE READ and READ UNCOMMITTED simply do not exist
SELECT sid, username FROM v$session WHERE sid = SYS_CONTEXT('USERENV','SID');

-- with no extra configuration the driver sends 50 separate statements in one packet
INSERT INTO book (title, price, id) VALUES ($1, $2, $3);
INSERT INTO book (title, price, id) VALUES ($1, $2, $3);   -- ... 50 times

-- with reWriteBatchedInserts=true the driver folds them together:
INSERT INTO book (title, price, id)
VALUES ($1,$2,$3), ($4,$5,$6), ($7,$8,$9) /* ... up to 50 tuples */;

-- jdbc:postgresql://db:5432/app?reWriteBatchedInserts=true&prepareThreshold=5

-- the Oracle driver executes one statement with array binding
INSERT INTO book (title, price, id) VALUES (:1, :2, :3);
-- parsed once, 50 value sets in a single round-trip

-- no reWriteBatchedInserts equivalent is needed; standard JDBC batching suffices
-- jdbc:oracle:thin:@//db:1521/ORCLPDB1
-- oracle.jdbc.implicitStatementCacheSize=50

UPDATE book SET price = price * 1.1 WHERE published_year < $1;

-- bounded bulk delete (to avoid one gigantic transaction)
DELETE FROM book
WHERE  id IN (SELECT id FROM book WHERE archived ORDER BY id FETCH FIRST 10000 ROWS ONLY);

UPDATE book SET price = price * 1.1 WHERE published_year < :1;

-- bounded bulk delete (to avoid one gigantic transaction)
DELETE FROM book
WHERE  id IN (SELECT id FROM book WHERE archived = 1 ORDER BY id FETCH FIRST 10000 ROWS ONLY);

INSERT INTO stock (sku, qty) VALUES ($1, $2)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty
RETURNING sku, qty;

-- PostgreSQL 15+ also has the standard MERGE (and MERGE ... RETURNING since 17)

-- Oracle has no ON CONFLICT; MERGE is its standard equivalent
MERGE INTO stock t
USING (SELECT :1 AS sku, :2 AS qty FROM dual) s
ON    (t.sku = s.sku)
WHEN MATCHED THEN UPDATE SET t.qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);

-- Oracle has no RETURNING in plain SQL; only "RETURNING ... INTO" inside PL/SQL

SELECT c.id, c.name,
       SUM(o.amount)                              AS total,
       STRING_AGG(o.code, ', ' ORDER BY o.code)   AS codes,
       COALESCE(c.nickname, c.name)               AS display_name
FROM   customer c JOIN orders o ON o.customer_id = c.id
GROUP  BY c.id, c.name, c.nickname
ORDER  BY total DESC
FETCH  FIRST 20 ROWS ONLY;

SELECT c.id, c.name,
       SUM(o.amount)                                        AS total,
       LISTAGG(o.code, ', ') WITHIN GROUP (ORDER BY o.code) AS codes,
       NVL(c.nickname, c.name)                              AS display_name
FROM   customer c JOIN orders o ON o.customer_id = c.id
GROUP  BY c.id, c.name, c.nickname
ORDER  BY total DESC
FETCH  FIRST 20 ROWS ONLY;

-- the real plan with timings and I/O
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT a.id, a.name FROM author a JOIN book b ON b.author_id = a.id WHERE a.name LIKE 'A%';

-- the most expensive statements
SELECT calls, total_exec_time, mean_exec_time, rows, query
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
FETCH  FIRST 20 ROWS ONLY;

-- the estimated plan
EXPLAIN PLAN FOR
SELECT a.id, a.name FROM author a JOIN book b ON b.author_id = a.id WHERE a.name LIKE 'A%';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- the real plan with execution statistics (closest to EXPLAIN ANALYZE)
SELECT /*+ GATHER_PLAN_STATISTICS */ a.id, a.name
FROM   author a JOIN book b ON b.author_id = a.id WHERE a.name LIKE 'A%';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

-- the most expensive statements
SELECT executions, elapsed_time, elapsed_time/executions AS avg_us, sql_text
FROM   v$sql ORDER BY elapsed_time DESC FETCH FIRST 20 ROWS ONLY;

SELECT pid, state, wait_event_type, wait_event, now() - query_start AS runtime, left(query, 80)
FROM   pg_stat_activity
WHERE  datname = current_database() AND state <> 'idle'
ORDER  BY runtime DESC;

-- blocking locks
SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 60)
FROM   pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

SELECT sid, status, event, seconds_in_wait, sql_id
FROM   v$session
WHERE  username IS NOT NULL AND status = 'ACTIVE'
ORDER  BY seconds_in_wait DESC;

-- blocking locks
SELECT s.sid, s.blocking_session, s.event, s.seconds_in_wait
FROM   v$session s WHERE s.blocking_session IS NOT NULL;