Sequence Generator Question

[kasim]

Quick question, you are using @SequenceGenerator & @GeneratedValue with a strategy of "Sequence", you are on oracle, and give it a name lets call it "s_temp".

Would you expect for that S_temp to show up in the sequences table in oracle? for some reason i would have thought so but i dont see it there.

is the sequences just internal to hibernate?

(Fwiw i am using JBoss 4.2.1 EJBs)

[oskar.carlstedt]

Hi Kasim!

Sequenzes are not hibernate specific "stuff". Instead sequences are database specific counters. Oracle are using sequnces, but MsSQL, MySQL, etc. are using identity mappings. If you are using MySQL you probably use an auto_increment to get the same feature as an Oracle sequence.

Setting the strategy attribute (of the GeneratedValue-annotation) to AUTO will let Hibernate choose the strategy depending on the underlying database. I.e. Oracle will use sequence, MySQL will use identity.

This is what you shall do to be sure you write an implementation that works for many database providers/brands.

...

@Entity
@Table(name="the_object")
public class TheObject {

 ...

 @Id
 @Column(name="id")
 @GeneratedValue(strategy=GenerationType.AUTO, generator="seqTheObject")
 @SequenceGenerator(name="seqTheObject", sequenceName="SEQ_THE_OBJECT")
 private Long id;

 ...

}

In this case, if you choose to use Oracle that is using sequnces, then you must have created a sequence SEQ_THE_OBJECT declared in your Oracle database schema. You get a create table syntax like:

CREATE TABLE THE_OBJECT (
 ID NUMBER(10) NOT NULL,
 ...
 PRIMARY KEY(ID)
);

CREATE SEQUENCE SEQ_THE_OBJECT START WITH 1 INCREMENT BY 1;

On the other hand, if you choose MySQL as your database provider, then you set the id column to use "AUTO_INCREMENT".

CREATE TABLE THE_OBJECT (
 ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 ...
 PRIMARY KEY(ID)
)

I hope this answered your question.

Cheers
/Oskar

[kasim]

Oskar,

Thanks. yeah i understood the basic ideas about it before.

Before though we were using "SEQUENCE" as our strategy.

I guess the big problem, which i should have stressed is that when it generates the number and stores it in the databse, i can't find where its getting that number from. If i look at Oracle's sequences table, we have sequences named it but the numbers dont seem to be adding up.

Now that i switched from "SEQUENCE" to "AUTO" ... the numbers changed again.

So i guess i am having a hard time finding where tis c connecting to the sequence on the db itself.

[kasim]

Nevermind .. now that i switched it to "Auto" it seems to work although the output on the console is the ame with it doing a next val.