Junit testing with in-memory hsqldb, hibernate and liquibase

Question

I'm trying to use an HSQLDB in order to run some integration test. I use Spring. Hibernate is used to set up the database schema. Then I want to insert test data using liquibase.

My problem is that, while hibernate schema creation works fine, I'm getting :

liquibase.exception.DatabaseException: Error executing SQL INSERT INTO PUBLIC.OxAttributeType (id, "alias") VALUES ('1', 'varchar'): user lacks privilege or object not found: alias

When liquibase try to start it's insertion.

I think that it might be cause by the hsqldb beeing closed (and flushed) after hibernate schema creation, but I'm not sure.

Here is my configuration:

database.properties:

jdbc.driverClassName= org.hsqldb.jdbc.JDBCDriver
jdbc.url=jdbc:hsqldb:mem:oxandtestdatabase
jdbc.username: SA
jdbc.password:

Hibernate object:

@Entity
@Table(name = "oxattribute")
public class Oxattribute implements java.io.Serializable {
private Integer id;
private String alias;

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "id", unique = true, nullable = false)
public Integer getId() {
    return this.id;
}

public void setId(Integer id) {
    this.id = id;
}

@Column(name = "alias", nullable = false, length = 30)
public String getAlias() {
    return this.alias;
}
}

Spring configuration:

<bean id="hibernateProperties"
    class="org.springframework.beans.factory.config.PropertiesFactoryBean">
    <property name="properties">
        <props>
            <!-- <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop> -->
            <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.hbm2ddl.auto">create</prop>
        </props>
    </property>
</bean>
<bean id="liquibase" class="liquibase.integration.spring.SpringLiquibase">
    <property name="dataSource" ref="dataSource" />
    <property name="changeLog" value="classpath:liquibase/testData.xml" />
</bean>

So Hibernate is working, I can see in the logs:

Hibernate: create table oxattribute (id integer generated by default as identity (start with 1), alias varchar(30) not null, deletable boolean not null, description varchar(255), idOxAttributeType integer not null, idOxObject integer not null, primary key (id))

But then when liquibase start insertions:

SEVERE 07/08/14 13:59: liquibase: classpath:liquibase/testData.xml: classpath:liquibase/testData.xml::insertMetadata::GCH: Change Set classpath:liquibase/testData.xml::insertMetadata::GCH failed.  Error: Error executing SQL INSERT INTO PUBLIC.OxAttributeType (id, "alias") VALUES ('1', 'varchar'): user lacks privilege or object not found: alias
liquibase.exception.DatabaseException: Error executing SQL INSERT INTO PUBLIC.OxAttributeType (id, "alias") VALUES ('1', 'varchar'): user lacks privilege or object not found: alias

For information, my code was working fine on a mysql database. Thank you for your help, Guillaume

see this http://stackoverflow.com/questions/3805478/internal-hsql-database-complains-about-privileges — dhamibirendra, Aug 07 '14 at 12:19
Hi,I'm running 4.3.6.Final and hsqldb 2.3.2, the last versions possible. And my setter already have the attribute (name = "id"). — Chaps, Aug 07 '14 at 12:35
To me it really looks like I don't control well enough the start and end of the hsqldb. I think it is created for hibernate, then destroy, then a brand new one is created for liquibase inserts. I have no proof about that though... Other error I can imagine would be a problem with the PUBLIC schema? Hibernate and liquibase not using the same schema? But again, no proof of this either. — Chaps, Aug 07 '14 at 13:06
In my case, you are right about the connection being closed. This fixed it for me: jdbc:hsqldb:mem:database;DB_CLOSE_DELAY=-1 — Alaric Dobson, Aug 31 '22 at 12:11

score 3 · Accepted Answer · answered Aug 07 '14 at 12:50

3

Change the name of the column used ("alias" is reserved term). You could try adding backticks "`". @Column(name="`alias`").

answered Aug 07 '14 at 12:50

Maarten Winkels

2,407
16
15

Damn you are right! It's ok in MySQL but is reserved in HSQL! Thanks a lot! – Chaps Aug 07 '14 at 13:38

Junit testing with in-memory hsqldb, hibernate and liquibase

1 Answers1