Sequences in HSQLDB are a little odd. The syntax isn’t unusual:
CREATE SEQUENCE <sequencename> [AS {INTEGER | BIGINT}]
[START WITH <startvalue>] [INCREMENT BY <incrementvalue>];
But what happens is a little unexpected. HSQLDB generates a SQL select statement to retrieve the nextval for the sequence.
For example, something like this:
CREATE SEQUENCE MY_TABLE_SEQ AS INTEGER START WITH 1 INCREMENT BY 1;
will generate a select statement like this:
SELECT NEXT VALUE FOR MY_TABLE_SEQ FROM dual_ MY_TABLE_SEQ
…which assumes a table named “dual_MY_TABLE_SEQ” with a column named MY_TABLE_SEQ.
After digging around online for this, I found a reference where someone had extended the Hibernate class org.hibernate.dialect.HSQLDialect and overwritten the method getSequenceNextValString(String arg0), and this provided me with the solution I used.
I created a table in HSQLDB called dual, with a single column SEQ.
I extended the class and extended getSequenceNextValString to have it return:select “next value for SEQ from dual”.
Now, in my unit tests, all sequences return the next value from dual. It is a little odd to have a single sequence table- it’s definitely not a production strategy. But these are unit tests, and will be destroyed every time the test is run, so it really doesn’t matter to me.
So what does this have to do with Hibernate? Well, my Hibernate classes use a Generated PK based on a sequence in production, like this:
@Id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "CODE_SEQ") @SequenceGenerator(name="CODE_SEQ", sequenceName = "CODE_SEQ") @Column(name="CODE_SEQ")
and I really wasn’t interested in rewriting my Hibernate classes for unit tests. So this allowed me to use HSQLDB without changing my SequenceGenerator scheme.
![[del.icio.us]](http://images.del.icio.us/static/img/delicious.small.gif){kind=small}
5 Comments
I have one question. I am using HSQLDB for unit testing. By setting “hibernate.hbm2ddl.auto” to true will create tables for us. But how to create sequences? May be it is easy answer but I have not idea.
Thanks.
I’ve only ever used SQL to create sequences with HSQLDB:
http://hsqldb.org/doc/guide/ch09.html#create_sequence-section
Actually, I take that back… I was working on something like that today (http://reverttoconsole.com/archives/160),
and although I haven’t gotten it to work yet, I think you can create sequences with hibernate using something like this (http://docs.jboss.org/ejb3/app-server/HibernateAnnotations/reference/en/html_single/index.html)
ex:
@javax.persistence.TableGenerator(
name=”EMP_GEN”,
table=”GENERATOR_TABLE”,
pkColumnName = “key”,
valueColumnName = “hi”
pkColumnValue=”EMP”,
allocationSize=20
)
@javax.persistence.SequenceGenerator(
name=”SEQ_GEN”,
sequenceName=”my_sequence”
)
package org.hibernate.test.metadata;
@Id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator=”SEQ_STORE”)
public Integer getId() { … }
This seems to be working for me with HSQLDB using annotations:
@Entity
@Table(name=”MEDIA”)
@SequenceGenerator(
name=”media_seq_gen”,
sequenceName=”media_seq”,
allocationSize=20)
public class Media {
@Id
@GeneratedValue(
generator = “media_seq_gen”,
strategy=GenerationType.AUTO
)
@Column(name=”MEDIA_ID”)
int mediaId;
I am using hsqldb with varius schemas and i am trying tha add foreign key from schema to other y show this message error “invalid name schema”,
please can you help me.
thansks
Post a Comment