3

I have tried using both Hsqldb and H2 for unit testing but facing problem with sequence generators. Field declaration looks like this.

@Id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="keyword_seq")
@SequenceGenerator(name="keyword_seq",sequenceName="KEYWORD_ID_SEQ", allocationSize=1)
@Column(name="IM_KEYWORD_ID")
private Long keywordId;

When I try to test simple insert in this table in Hsqldb with following configuration, it gives error

<prop key="hibernate.connection.driver_class">org.hsqldb.jdbc.JDBCDriver</prop>
<prop key="hibernate.connection.url">jdbc:hsqldb:mem:testdb;sql.syntax_ora=true</prop>

Error : 

   Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: KEYWORD_ID_SEQ.NEXTVAL
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)

With H2:

<prop key="hibernate.connection.driver_class">org.h2.Driver</prop>
<prop key="hibernate.connection.url">jdbc:h2:~/test</prop>

Error is :

Hibernate: select KEYWORD_ID_SEQ.nextval from dual
3085 [main] WARN org.hibernate.util.JDBCExceptionReporter  - SQL Error: 42001, SQLState: 42001
3088 [main] ERROR org.hibernate.util.JDBCExceptionReporter  - Syntax error in SQL statement "SELECT KEYWORD_ID_SEQ.NEXTVAL FROM[*] DUAL "; expected "identifier"; SQL statement:

Any idea, how to solve this?

RandomQuestion
  • 6,778
  • 17
  • 61
  • 97

1 Answers1

6

I think the dialect is set wrongly. Can you check your hibernate configuration.

gkamal
  • 20,777
  • 4
  • 60
  • 57
  • I am using follwing. `org.hibernate.dialect.Oracle10gDialect` – RandomQuestion Sep 19 '11 at 15:45
  • 2
    You need to change it to org.hibernate.dialect.HSQLDialect for HSQL and org.hibernate.dialect.H2Dialect for H2. As you notice hibernate is firing Oracle queries against HSQL & H2 database, hence the errors. – gkamal Sep 19 '11 at 15:50
  • I changed to dialect, you mentioned but still getting error. For HSQLDB, error is `user lacks privilege or object not found: KEYWORD_ID_SEQ`. For H2, its `ERROR org.hibernate.util.JDBCExceptionReporter - Sequence "KEYWORD_ID_SEQ" not found; SQL statement:call next value for KEYWORD_ID_SEQ [90036-147]`. Do we need to create some sequences at time of startup ourselves? – RandomQuestion Sep 19 '11 at 15:58
  • 1
    Yes, you need to use hibernate.hbm2ddl.auto to create - so that the database schema is created during startup. – gkamal Sep 19 '11 at 16:03
  • 1
    Thanks a lot gkamal, I am using annotated class so I guess `hibernate.hbm2ddl.auto` is not required. Just changing Dialect made it work. It was some typo earlier or was feeling sleepy. – RandomQuestion Sep 19 '11 at 16:27
  • Great, I think you are already using hbm2ddl.auto, without that it wouldn't work. That is not just for hbm the same flag applies to annotations as well. HSQL / H2 are in memory databases so schema needs to be dynamically setup every time. Also can you please mark my answer as the correct answer. – gkamal Sep 19 '11 at 16:29