CODEWITHSUNDEEP
javaspringhibernatehsqldb
Kros
Kros

Reputation: 848

HSQL org.hsqldb.HsqlException: invalid schema name

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

Upvotes: 9

Views: 21610

Answers (5)

Mikko Maunu
Mikko Maunu

Reputation: 42094

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

Upvotes: 2

user7396627
user7396627

Reputation: 105

I am not sure why this worked, but for me, at least, adding square brackets around the table names and schema did the trick for me:

@Table(name = "schema.tableName")

became

@Table(name = "[schema].[tableName]")

Upvotes: 0

František Hartman
František Hartman

Reputation: 15086

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...

Upvotes: 5

박찬신
박찬신

Reputation: 211

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">
...

Upvotes: 8

Kros
Kros

Reputation: 848

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")

Upvotes: 4

Related Questions