Hibernate / Oracle: how to do sequence execution in insert statement instead of two queries

Question

I come from a jdbc background, and I decided to teach myself how to use sleep mode. So I made a table with the sequence:

CREATE TABLE TST_PERSON( ID NUMBER, NAME VARCHAR(30), SURNAME VARCHAR(30) ); CREATE SEQUENCE TST_PERSON_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER NOCYCLE ; 

and my java code to display:

 @Entity @Table(name="TST_PERSON") public class Person { @Id @GeneratedValue(strategy=GenerationType.AUTO,generator="pers_seq") @SequenceGenerator(name="pers_seq",sequenceName="TST_PERSON_SEQ") private Long id; @Column(name="NAME") private String name; @Column(name="SURNAME") private String surname; ... getters and setters ... } 

My hibernation configuration:

  <?xml version='1.0' encoding='utf-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property> <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:sid</property> <property name="hibernate.connection.username">root</property> <property name="hibernate.connection.password">root</property> <property name="hibernate.connection.pool_size">10</property> <property name="show_sql">true</property> <property name="dialect">org.hibernate.dialect.Oracle10gDialect</property> <property name="hibernate.current_session_context_class">thread</property> <mapping class="com.domain.Person"/> </session-factory> </hibernate-configuration> 

But when I look at the SQL generated by the Hibernate logs when calling session.save:

 Hibernate: select TST_PERSON_SEQ.nextval from dual Hibernate: insert into TST_PERSON (NAME, SURNAME, id) values (?, ?, ?) 

I thought there might be some reasons for caching, but when I try to save through a loop, I get the same result.

So the question is how to get hibernate to include the sequence as part of the insert statement (this is what I intended to happen). In terms of versions I use hibernate 4.1.8

Answer 1

This is actually impossible. Take a simple example:

 @SequenceGenerator(name="pers_seq",sequenceName="TST_PERSON_SEQ",allocationSize=10) 

The distribution size is equivalent to "INCREMENT BY 10". If you regroup an ID search query using the insert statement, you will need to detect when the 10th identifier (TST_PERSON_SEQ.nextval) needs to be retrieved from the database at the same time as the insert.

In addition, batch query processing on insertion is performed for statements in the same table.

If you want to save some I / O, set a higher distribution size and use JDBC batch mode (the order_inserts property is ultimately true). That way, you will have a search ID request every 10 inserts for allocSize set to 10.