HSQL org.hsqldb.HsqlException: invalid schema name

Question

I am using HSQL to run a number of unit tests on my java application. I am using Spring + Hibernate. I am having a problem when switching from MySQL to HSQL. The tests run perfectly on MySQL but whenever I change to HSQL I get the following exception:

Caused by: org.hsqldb.HsqlException: invalid schema name: LMS
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.SchemaManager.getSchemaHsqlName(Unknown Source)
at org.hsqldb.SchemaManager.getSchemaName(Unknown Source)
at org.hsqldb.Session.getSchemaName(Unknown Source)
at org.hsqldb.SchemaManager.getTable(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readSimpleRangeVariable(Unknown Source)
at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)

My Spring configuration is the following:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver" />
    <property name="url" value="jdbc:hsqldb:file:lms" />
    <property name="username" value="SA"/>
    <property name="password" value=""/>
</bean>


<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />

    <property name="packagesToScan">
        <list>
            <value>com.dreamteam.lms.**.*</value>
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <!--<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>-->
            <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
            <prop key="hibernate.generate_statistics">true</prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.cache.use_second_level_cache">true</prop>
            <prop key="hibernate.cache.use_query_cache">true</prop>
            <prop key="hibernate.cache.provider_class">net.sf.ehcache.hibernate.SingletonEhCacheProvider</prop>
        </props>
    </property>
</bean>

Sample Annotation on one of my classes:

@Entity
@Table(name = "answer", catalog = "lms")
public class Answer implements Cloneable, Serializable, IPojoGenEntity, IAnswer {
.
.

Any insight would be appreciated.

Regards Chris

Answer 1

make "create-schema.sql" file

CREATE SCHEMA lms;

add "dataSourceInitializer" bean

<bean id="dataSourceInitializer" class="org.springframework.jdbc.datasource.init.DataSourceInitializer">
    <property name="dataSource" ref="dataSource" />
    <property name="databasePopulator">
        <bean class="org.springframework.jdbc.datasource.init.ResourceDatabasePopulator">
            <property name="continueOnError" value="true" />
            <property name="scripts">
                <list>
                    <value>classpath:SQL/create-schema.sql</value>
                </list>
            </property>
        </bean>
    </property>
</bean> 

set "depends-on" attribute to "sessionFactory" bean

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" depends-on="dataSourceInitializer">
...

Answer 2

I use following bean to create schema during tests.

public class HSQLSchemaCreator {

    private String schemaName;

    private DataSource dataSource;

    public HSQLSchemaCreator(String schemaName, DataSource dataSource) {
        this.schemaName = schemaName;
        this.dataSource = dataSource;
    }


    @PostConstruct
    public void postConstruct() throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.execute("CREATE SCHEMA " + schemaName + " AUTHORIZATION DBA");
    }

}

spring configuration:

  <bean id="hsqlSchemaCreator" class="....HSQLSchemaCreator">
       <constructor-arg name="schemaName" value="..."/>
       <constructor-arg name="dataSource" ref="dataSource"/>
  </bean>

<!-- Override entityManagerFactory to depend on hsqlSchemaCreator for tests  -->
<bean id="entityManagerFactory"  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" depends-on="hsqlSchemaCreator">

and so on...

Answer 3

Just for the record I managed to solve this one by simply removing the 'catalog' attribute from my Hibernate entities. Hence,

@Entity
@Table(name = "answer", catalog = "lms")

became

@Entity
@Table(name = "answer")

Answer 4

Names for schemas, tables, columns, etc. are not (at least not by default) case sensitive in MySQL. HSQLDB is case sensitive, but it also converts all identifiers in query that are not quoted to the uppercase.

You can quickly test is this your problem by changing schema name to LMS everywhere (first in database). You can find more detailed story about HSQLDB and Hibernate from here: HSQLDB No such table Exception