How to use in-mem HSQLDB sequences with unit testing?

Question

My application uses sequences, and I'm trying to set up junit test environment with HSQLDB in-mem database that would have a similar setup. This is how I've configured the sequences and their creation:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" lazy-init="true"
      destroy-method="close">
        <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
        <property name="url" value="jdbc:hsqldb:mem:test"/>
        <property name="username" value="sa" />
        <property name="password" value="" />
    </bean>
    <bean id="logSeqIncrementer" class="org.springframework.jdbc.support.incrementer.HsqlSequenceMaxValueIncrementer">
        <property name="dataSource" ref="dataSource" />
        <property name="incrementerName" value="public.agent_logs_seq" />
    </bean>

    <bean id="offerSeqIncrementer" class="org.springframework.jdbc.support.incrementer.HsqlSequenceMaxValueIncrementer">
        <property name="dataSource" ref="dataSource" />
        <property name="incrementerName" value="public.offers_seq" />
    </bean>
    <jdbc:embedded-database
        id="test"
        type="HSQL">
        <jdbc:script
            location="classpath:/create-ddl.sql" />
    </jdbc:embedded-database>   
</beans>

And contents of create-ddl.sql:

CREATE SEQUENCE public.agent_logs_seq;

Results in

java.sql.SQLException: Sequence already exists in statement

but if I comment that out, I get

java.lang.AssertionError: Unexpected exception:
org.springframework.dao.DataAccessResourceFailureException:
    Could not obtain sequence value; nested exception is
java.sql.SQLException: Sequence not found:
    AGENT_LOGS_SEQ in statement [call next value for public.agent_logs_seq]

The way I'm trying to use the sequence:

DataFieldMaxValueIncrementer incrementer =
    (DataFieldMaxValueIncrementer) context.getBean("logSeqIncrementer");
Integer logId = Integer.valueOf(incrementer.nextIntValue());

Edit: changed the details above so that correct sequence class would be used. It didn't solve the issue, however.

Near the end, you say "but then trying to use it results in ". How do you use the sequence? — fredt, Aug 19 '12 at 21:21
What will be if you try to create tables for sequence, like `create table agents_logs_seq (value identity); insert into agents_logs_seq values(0);` — Slava Semushin, Aug 19 '12 at 21:29
@php-coder as I now changed the sequence class, this should not be necessary, right? — eis, Aug 20 '12 at 08:28

score 3 · Answer 1 · answered Aug 19 '12 at 21:41

3

You have specified org.springframework.jdbc.support.incrementer.HsqlMaxValueIncrementer as the class, however, it seems Spring is using HsqlSequenceMaxValueIncrementer (in the same package) and creates a SEQUENCE, instead of a TABLE.

The HsqlMaxValueIncrementer is an older implementation which is suitable for database platforms that do not support SEQUENCE objects. The HsqlSequenceMaxValueIncrementer is more recent (Spring v. 2.5) and is the more efficient choice for HSQLDB. This should create the SEQUENCE objects you specified and then use NEXT VALUE FOR public.agents_logs_seq internally to retrieve the next sequence value.

answered Aug 19 '12 at 21:41

fredt

24,044
3
40
61

Wow, I'd never would've figured that one out. Thank you. I changed that, but now I'm getting "Caused by: java.sql.SQLException: Sequence already exists in statement [CREATE SEQUENCE public.agent_logs_seq]" for the creation, and when I comment that out, I get "Unexpected exception: org.springframework.dao.DataAccessResourceFailureException: Could not obtain sequence value; nested exception is java.sql.SQLException: Sequence not found: AGENT_LOGS_SEQ in statement [call next value for public.agent_logs_seq]". I'll update the question, too. – eis Aug 20 '12 at 07:52

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

I had a similar problem which is raised here.

There were various solutions offered. The H2 Database seemed interesting. In the end I manually dropped and then create the sequences in a script.

edited May 23 '17 at 11:58

Community

1
1

answered Aug 25 '12 at 08:54

RNJ

15,272
18
86
131

Tested and creating manually in @Before method works. Don't really like going this way, but have to accept, don't have better options for now :) – eis Aug 26 '12 at 19:55

How to use in-mem HSQLDB sequences with unit testing?

2 Answers2