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

Answer 1

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

Answer 2

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.