Hibernate populate primary key using database trigger

Question

I am using Hibernate 4.1.0.Final with Spring 3

I have the following in Entity class

@Id
@Column(name = "PROJECT_NO")
@GeneratedValue(strategy=GenerationType.TABLE)
private String projectNumber;

Is it possible to use database trigger to populate the primary key of a table? Or I have to use a CustomGenerator for this?

When I tried the above I have the following exception

org.hibernate.id.IdentifierGenerationException: Unknown integral data type
for ids : java.lang.String

Database trigger doesn't have any sequence, it is using

SELECT NVL (MAX (project_no), 0) + 1 FROM projects

Edit 1

@GeneratedValue(generator="trig")
@GenericGenerator(name="trig", strategy="select", 
parameters=@Parameter(name="key", value="projectNo"))

The above throws the following exception

Hibernate: select PROJECT_NO from PROJECTS where PROJECT_NO =?

java.lang.NullPointerException
exception in save null
    at org.hibernate.tuple.entity.AbstractEntityTuplizer.getPropertyValue(AbstractEntityTuplizer.java:645)
    at org.hibernate.persister.entity.AbstractEntityPersister.getPropertyValue(AbstractEntityPersister.java:4268)
    at org.hibernate.id.SelectGenerator$SelectGeneratorDelegate.bindParameters(SelectGenerator.java:138)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:84)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2764)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3275)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)

JB Nizet · Accepted Answer · 2013-02-27 16:20:57Z

3

The problem is that you're using a String instead of a numeric value. Use a Long instead of a String, and your error will disappear.

AFAIK, you can't use a trigger to populate the ID. Indeed, Hibernate would have to retrieve the generated ID, but since it doesn't have an ID, I don't see how it could read back the row it has just inserted (chicken and egg problem).

You could use your SQL query to get an ID before inserting the row, but this strategy is inefficient, and has a risk of duplicate IDs in case of concurrent inserts. So I wouldn't use this strategy. You tagged your post with Oracle. I suggest you use a sequence. that's what they're for.

answered Feb 27, 2013 at 16:20

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jacob Over a year ago

I agree with you that sequence is the way to go, however database guys for this applications is insisting on having a trigger.

Jacob Over a year ago

What if I am using a CustomGenerator to get value from database instead of sql query? Is this inefficient or both are one and same?

Garis M Suero · Accepted Answer · 2013-02-27 16:20:15Z

2

As of this on the Hibernate 3.3 documentation page you can do that.

select

retrieves a primary key, assigned by a database trigger, by selecting the row by some unique key and retrieving the primary key value.

answered Feb 27, 2013 at 16:20

Garis M Suero

8,1997 gold badges49 silver badges68 bronze badges

4 Comments

Garis M Suero Over a year ago

@Polppan as of this question: stackoverflow.com/questions/13085508/… . You can use @GeneratedValue( strategy = "trigger" ) and then have a NaturalID in your Entity

Jacob Over a year ago

Garis, @GeneratedValue( strategy = "trigger" ) is complaining about Type mismatch: cannot convert from String to GenerationType. Any idea about this?

Jacob Over a year ago

Usage of GenerationType.Auto causes the following org.hibernate.exception.SQLGrammarException: ORA-02289: sequence does not exist

Jacob Over a year ago

Garis, I have added an update in my original question as Edit 1, At runtime I am getting null pointer exception, even though trigger exists in table.

Collectives™ on Stack Overflow

Hibernate populate primary key using database trigger

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related