Missing Sequences in HSQL for testing

Question

I have a Oracle database where I have to use sequences for the primary key. This all works well as I have control over the sequence number. My problem is with my tests. Using Spring I create an HSQL db and test against this. This database is constructed by looking at all my entities. All my entities for the sake of working with Oracle have a sequence name specified. The trouble is that when I construct the HSQL db it cannot find the sequence (which I expect) My tests pass but I end up with lots of cruft in the log. The log is filled with these sort of messages.

WARN JDBCExceptionReporter:233 - SQL Error: -5501, SQLState: 42501
ERROR JDBCExceptionReporter:234 - user lacks privilege or object not found: GENDERS_SEQ

Does anyone know how I can remove these spurious errors? Can I get HSQL to ignore the sequences. Interestingly in the tests I can insert into the HSQL database so it must be using its own internal primary key generator.

Anyone any ideas on how I can remove this cruft from the log?

Thanks

score 3 · Accepted Answer · answered Aug 09 '12 at 08:12

I solved this by manually creating sequences as part of my test script. Not ideal as I would rather Spring/HSQL combination set this up. My code is:

for (String sequence : sequences) {
    entityManager.createNativeQuery("DROP SEQUENCE " + sequence + " IF EXISTS").executeUpdate();
    entityManager.createNativeQuery("CREATE SEQUENCE " + sequence + " as INTEGER").executeUpdate();
}

where sequences is a list of string which are the sequence name.

I used this is the @BeforeClass method for each test class. Not ideal but it does solve the problem

score 1 · Answer 2 · answered Aug 09 '12 at 08:28

1

Try the H2 database instead; it has a feature called "Compatibility Modes" in which it behaves like Oracle or HSQL.

When selecting the Oracle mode, you should be able to initialize the database with the same scripts (or Hibernate setup) that you use for your production.

answered Aug 09 '12 at 08:28

Aaron Digulla

321,842
108
597
820

Thanks Aaron, I'll have a look at this at lunchtime – RNJ Aug 09 '12 at 09:32

score -2 · Answer 3 · edited May 23 '17 at 12:13

-2

You can build your own in-memory-db via script : how-to-initialize-in-memory-hsqldb-using-script-via-spring.

But I prefer using arquillian for integration testing with data.

Disclosure : not working for arquillian.

edited May 23 '17 at 12:13

Community

1
1

answered Aug 05 '12 at 17:25

R-E-L

137
3

How does this help me set the sequences on the DB? Perhaps I am missing something here. Sorry if I am – RNJ Aug 08 '12 at 12:24

Missing Sequences in HSQL for testing

3 Answers3

Linked