Spring JDBC两种获取Oracle Sequence的步骤

Spring JDBC两种获取Oracle Sequence的方法

http://www.blogjava.net/ahgf/archive/2005/10/14/15505.html

 

前提：

CREATE TABLE Booking (
  id INTEGER NOT NULL,
  date_made DATE,
  reserved_until TIMESTAMP,
  price DECIMAL(15, 2) NOT NULL,
  Purchase_id INTEGER,
  PRIMARY KEY(id))  

create sequence booking_seq 
  start with 1 
  increment by 1 
  nomaxvalue;

方法一：
利用Spring的org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer

OracleSequenceMaxValueIncrementer incr = 
    new OracleSequenceMaxValueIncrementer(dataSource, "booking_seq");
   
public int getNewBookingId() {
  return incr.nextIntValue();
}

针对不同的数据库Spring提供了不同的支持类。

方法二：使用数据库本身的触发器

CREATE OR REPLACE TRIGGER tib_booking BEFORE INSERT
ON BOOKING FOR EACH ROW
DECLARE
    integrity_error  EXCEPTION;
    errno            INTEGER;
    errmsg           CHAR(200);
    dummy            INTEGER;
    FOUND            BOOLEAN;
BEGIN
    --  Column "ID" uses sequence booking_seq 
    SELECT booking_seq.NEXTVAL INTO :NEW.ID FROM dual;
--  Errors handling
EXCEPTION
    WHEN integrity_error THEN
       RAISE_APPLICATION_ERROR(errno, errmsg);
END;

测试：
INSERT INTO BOOKING(date_made,price) VALUES(SYSDATE,22222);
COMMIT;