Configure Hibernate to use Oracle's SYS_GUID() for Primary Key

Question

I am looking for a way to get hibernate to use oracle's SYS_GUID() function when inserting new rows. Currently my DB tables have SYS_GUID() as the default so if hibernate simply generated SQL that omited the value it should work.

I have everything working, but it is currently generating the UUID/GUID in code using the system-uuid generator:

@Id
@GeneratedValue(generator = "system-uuid")
@GenericGenerator(name = "system-uuid", strategy = "uuid")
@Column(name = "PRODUCT_ID", unique = true, nullable = false)
public String getId() {
    return this.productId;
}

This is fine, but I would prefer that the guids were generated by the database so they will be sequential and potentially have better performance. Plus I would just like to know how to configure this.

I am using annotations for configuration, but xml configuration examples are awesome as well.

Here is a sample table definition (in case it matters):

CREATE TABLE SCHEMA_NAME.PRODUCT
(
    PRODUCT_ID RAW(16) DEFAULT SYS_GUID() NOT NULL,
    PRODUCT_CODE VARCHAR2(10 CHAR) NOT NULL,
    PRODUCT_NAME VARCHAR2(30 CHAR) NOT NULL,
    PRODUCT_DESC VARCHAR2(512 CHAR)
)

UPDATE:

Mat's sollution of using "guid" worked, here is the sql generated:

Hibernate: 
    select rawtohex(sys_guid()) 
    from dual
Hibernate: 
    insert into PRODUCT
    (PRODUCT_CODE, PRODUCT_DESC, LOB_ID, PRODUCT_NAME, PROVIDER_ID, PRODUCT_ID) 
    values (?, ?, ?, ?, ?, ?)

---

It seems that using the columns default value in an insert is not possible, so the choice is between an application generated guid and a database round trip.

Answer 1

I think you can do it by setting the generator to native. I'm not really sure how to do it in Hibernate but in NHibernate you'd do something like this in the XML:

<id column="PRODUCT_ID">
  <generator class="native"/>
</id>

Answer 2

Hi Ryan. You might be able to use the "guid" generator. See this post from the Hibernate forum. It looks like they added support for Oracle using SYS_GUID() a while back, but the documentation still says they only support SQL Server and MySQL.

I haven't worked with JPA annotations yet, but here is an example using XML configuration:

<id name="PRODUCT_ID">
  <generator class="guid" />
</id>

EDIT: In regards to your second question, I think you are asking why Hibernate can't do something like this:

INSERT INTO PRODUCT (PRODUCT_ID, /* etc */)
SELECT SYSGUID(), /* etc */

The reason is that Hibernate must know what the object's ID is. For example, consider the following scenario:

1. You create a new Product object and save it. Oracle assigns the ID.
2. You detach the Product from the Hibernate session.
3. You later re-attach it and make some changes.
4. You now want to persist those changes.

Without knowing the ID, Hibernate can't do this. It needs the ID in order to issue the UPDATE statement. So the implementation of org.hibernate.id.GUIDGenerator has to generate the ID beforehand, and then later on re-use it in the INSERT statement.

This is the same reason why Hibernate cannot do any batching if you use a database-generated ID (including auto-increment on databases that support it). Using one of the hilo generators, or some other Hibernate-generated ID mechanism, is the only way to get good performance when inserting lots of objects at once.