Problem using SequenceGenerator

[bryan_castillo]

I'm not sure whats going on here. I'm trying to create an Entity bean for a pre-existing table in DB2 that uses a sequence for creating a new primary key.

Here is my annotated getter method.

@Id
 @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="VNDR_ID_SEQ")
 @SequenceGenerator(name="VNDR_ID_SEQ", sequenceName="VRAP.VNDR_ID_SEQ")
 @Column(name="VNDR_ID")
 public int getId() {
 return id;
 }

When I try to insert a new object, I see in the logs that it gets a value from
the sequence using the right sql with a value of 556.

2006-07-26 16:35:02,819 INFO [STDOUT] Hibernate: values nextval for VRAP.VNDR_ID_SEQ
2006-07-26 16:35:02,869 DEBUG [org.hibernate.id.SequenceGenerator] Sequence identifier generated: 556

Later in the logs it prints the values of the fields before inserting the object, and the id field has a value of 27800.

2006-07-26 16:35:02,899 DEBUG [org.hibernate.pretty.Printer] bcc.entity.Vendor{createUserId=IS94901, updateTime=null, createTime=16:35:02, ndc5Nb=5150, createDate=26 July 2006, updateUserId=IS94901, updateDate=null, name=BryanV, id=27800, subsidiaryOf=Castillo Corp.}

Did I set up my annotations wrong? I expected to have the id set to 556.

Snippet from logs:
------------------------------------------



2006-07-26 16:35:02,819 INFO [STDOUT] Hibernate: values nextval for VRAP.VNDR_ID_SEQ
2006-07-26 16:35:02,869 DEBUG [org.hibernate.id.SequenceGenerator] Sequence identifier generated: 556
2006-07-26 16:35:02,869 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-07-26 16:35:02,869 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
2006-07-26 16:35:02,869 DEBUG [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2006-07-26 16:35:02,869 DEBUG [org.hibernate.id.SequenceHiLoGenerator] new hi value: 556
2006-07-26 16:35:02,869 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] generated identifier: 27800, using strategy: org.hibernate.id.SequenceHiLoGenerator
2006-07-26 16:35:02,889 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] processing flush-time cascades
2006-07-26 16:35:02,899 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] dirty checking collections
2006-07-26 16:35:02,899 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] Flushed: 1 insertions, 0 updates, 0 deletions to 1 objects
2006-07-26 16:35:02,899 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
2006-07-26 16:35:02,899 DEBUG [org.hibernate.pretty.Printer] listing entities:
2006-07-26 16:35:02,899 DEBUG [org.hibernate.pretty.Printer] bcc.entity.Vendor{createUserId=IS94901, updateTime=null, createTime=16:35:02, ndc5Nb=5150, createDate=26 July 2006, updateUserId=IS94901, updateDate=null, name=BryanV, id=27800, subsidiaryOf=Castillo Corp.}

[arnowerr]

I am using PostgreSQL which utilizes sequence generator.
Declaring id

 @Id
 @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "hibernate3_sequence")
 private Long id;

Wiring up sequence generator for all entities in orm.xml

<entity-mappings
 xmlns="http://java.sun.com/xml/ns/persistence/orm"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm orm_1_0.xsd"
 version="1.0">
 <sequence-generator
 name="hibernate3_sequence"
 sequence-name="postmaster_sequence" />
</entity-mappings>

Works like a charm.
Have you tried all available options for SequenceGenerator annotation?

@Target({TYPE, METHOD, FIELD}) @Retention(RUNTIME)
public @interface SequenceGenerator {
String name();
String sequenceName() default "";
int initialValue() default 1;
int allocationSize() default 50;
}

Yet, like I said, I am using PostgreSQL 7.4. Your problem could be DB related.

[bryan_castillo]

I don't think my problem is database related.

Arno, did you see if the next value of your postgres sequence corresponded to the value actually inserted?

I launched jboss in debug mode and attached eclipse remotely to it and found the hibernate class which is applying modifications to the value returned from the database sequence.

The generated id is being generated by org.hibernate.id.SequenceHiLoGenerator. Here is the method.

public synchronized Serializable generate(SessionImplementor session, Object obj)
 throws HibernateException {
 if (maxLo < 1) {
 //keep the behavior consistent even for boundary usages
 long val = ( (Number) super.generate(session, obj) ).longValue();
 if (val == 0) val = ( (Number) super.generate(session, obj) ).longValue();
 return IdentifierGeneratorFactory.createNumber( val, returnClass );
 }
 if ( lo>maxLo ) {
 long hival = ( (Number) super.generate(session, obj) ).longValue();
 lo = (hival == 0) ? 1 : 0;
 hi = hival * ( maxLo+1 );
 if ( log.isDebugEnabled() )
 log.debug("new hi value: " + hival);
 }

 return IdentifierGeneratorFactory.createNumber( hi + lo++, returnClass );
 }

I would like to have the id, generated by org.hibernate.id.SequenceGenerator instead of org.hibernate.id.SequenceHiLoGenerator. Is there a way to specify the class which should be used as the generator?

I read some of the comments for both of these generators and it looks like the HiLo version is used for performance. I believe it is able to reserve chunks of sequence numbers at a time. But I was trying to created entity beans for an existing database and still fit the same sequence/id usage pattern of the old code.

[bryan_castillo]

I found some documentation showing how to override the generator, by using hibernate specific annotations.

@Id
 @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="VNDR_ID_SEQ")
 @GenericGenerator(name="VNDR_ID_SEQ", strategy="org.hibernate.id.SequenceGenerator",
 parameters={
 @Parameter(name="sequence", value="VRAP.VNDR_ID_SEQ")
 }
 )
 //@SequenceGenerator(name="VNDR_ID_SEQ", sequenceName="VRAP.VNDR_ID_SEQ")
 @Column(name="VNDR_ID")
 public int getId() {
 return id;
 }

I have these imports for the hibernate annotations.

import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Parameter;

[arnowerr]

Checked my logs.

2006-07-27 16:09:40,003 DEBUG [org.hibernate.jdbc.ConnectionManager] - opening JDBC connection
2006-07-27 16:09:40,004 DEBUG [org.hibernate.SQL] - select nextval ('postmaster_sequence')
2006-07-27 16:09:40,043 DEBUG [org.hibernate.id.SequenceGenerator] - Sequence identifier generated: 1
2006-07-27 16:09:40,043 DEBUG [org.hibernate.jdbc.AbstractBatcher] - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-07-27 16:09:40,043 DEBUG [org.hibernate.jdbc.ConnectionManager] - aggressively releasing JDBC connection
2006-07-27 16:09:40,043 DEBUG [org.hibernate.jdbc.ConnectionManager] - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2006-07-27 16:09:40,045 DEBUG [org.hibernate.id.SequenceHiLoGenerator] - new hi value: 1
2006-07-27 16:09:40,046 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] - generated identifier: 50, using strategy: org.hibernate.id.SequenceHiLoGenerator
2006-07-27 16:09:40,119 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] - processing flush-time cascades

As you see
generated identifier: 50, using strategy: org.hibernate.id.SequenceHiLoGenerator

50 - it is in the database

[bryan_castillo]

If you go to you database now (say through psql) and issue the query

select nextval ('postmaster_sequence')

I'm betting it would not be 51 and will probably be 2.

From your logs:

2006-07-27 16:09:40,004 DEBUG [org.hibernate.SQL] - select nextval ('postmaster_sequence')
2006-07-27 16:09:40,043 DEBUG [org.hibernate.id.SequenceGenerator] - Sequence identifier generated:
1

It looks like the postgres sequence returned 1. But the SequenceHiLoGenerated inserted a value of 50.

[bryan_castillo]

The more I look at the SequenceHiLoGenerator, the more I wonder whether it makes sense to use it be default at all in EJB3. What happens in a cluster? The SequenceHiLoGenerator uses state information when getting sequences. If you have 2 seperate boxes using a SequenceHiLoGenerator from the same database sequence, could you end up reissuing an id more than once? I don't see how the default generator could always create new ID's.

[arnowerr]

it looks like the postgres sequence returned 1. But the SequenceHiLoGenerated inserted a value of 50.

I guess, it's because initialValue - default - 1 and allocationSize - 50. I have test table along with cabin table and this test table (you know, to test connections in pool) receives the first allocation block - 1 to 49.
Cabin receives next 50

@Target({TYPE, METHOD, FIELD}) @Retention(RUNTIME)
public @interface SequenceGenerator {
String name();
String sequenceName() default "";
int initialValue() default 1;
int allocationSize() default 50;
}

[bryan_castillo]

Ok, now I know whats going on.

To summarize, I thought that a generated id coming from a sequence in EJB would be the same as the physical value of the sequence. But the sequence generator actually takes the value from the database sequence and multiplies it by the allocationSize. As long as every program, (or chunk of code) using that sequence uses the same multiplier you will be safe. The SequenceHiLoGenerator keeps the last sequence number in memory and just increments the sequence number in memory, and pulls a new physical sequence when it uses allocationSize numbers since the last pull from the physical sequence.

If I want the generator to produce the same number as the sequence (so the code will not conflict with other non-java code using the sequence), than I can set the allocationSize to 1. Then I will not need a hibernate specific annotation.

This works for me:

 @Id
 @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="VNDR_ID_SEQ")
 @SequenceGenerator(name="VNDR_ID_SEQ", sequenceName="VRAP.VNDR_ID_SEQ", allocationSize=1)
 @Column(name="VNDR_ID")
 public int getId() {
 return id;
 }

[epbernard]

you need to use an allocationSize of 1

and for the records, SequenceHiLoGenerator is perfectly fine in an cluster :-)

[elfuhrer]

I'm using a similar approach:

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

and it ain't working either. Any clue?

[elfuhrer]

Sorry folks, it was my mistake. Nothing related to the generator a stupid method in the create() method.