Hibernate, Spring & HSQL: Table not Found Exception

we're using spring, hibernate and hsql to persist a simple user entity. We always get the error "table not found". Do you have any idea what this could be? It seems like the table is not generated or the hsql database is not running at all.

Regards, G.

@Entity
@Table (name="USER")
public class User {


 @Id
 @GeneratedValue(strategy= GenerationType.AUTO)
 @Column(name="ID")
 private Long id;

 @Column(name="NAME", length = 100, nullable = false)
 private String name;

 public User(){}

        //getters and setters ...

by this dao class:

package de.hsrm.mediathek;

import java.util.List;

public class UserDao implements IUserDao{

    private HibernateTemplate hibernateTemplate;

    public void setHibernateTemplate(final HibernateTemplate hibernateTemplate){
        this.hibernateTemplate = hibernateTemplate;
    }

    @Transactional
    public void store(final User user){
        hibernateTemplate.saveOrUpdate(user);
    }

    @Transactional
    public void delete(final Long userId){
        final User user = (User) hibernateTemplate.get(User.class, userId);
        hibernateTemplate.delete(user);
    }

    @Transactional(readOnly = true)
    public User findById(final Long userId){
        return (User) hibernateTemplate.get(User.class, userId);
    }

    @Transactional(readOnly = true)
    public List<User> findAll(){
        return hibernateTemplate.find("from User");
    }

}

Configuration XML:

<?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:tx="http://www.springframework.org/schema/tx"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/tx
           http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">


 <tx:annotation-driven/>

 <bean id="dataSource"
   class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
  <property name="url" value="jdbc:hsqldb:mem:mediathekdb" />
  <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="annotatedClasses">
   <list>
    <value>de.hsrm.mediathek.User</value>
   </list>
  </property>
  <property name="hibernateProperties">
   <props>
    <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
    <prop key="hibernate.hbm2dll.auto">create-drop</prop>
    <prop key="hibernate.show_sql">true</prop>
    <prop key="hibernate.format_sql">true</prop>
    <prop key="hibernate.connection.pool_size">10</prop>  
    <prop 
     key="hibernate.cache.provider_class">
     org.hibernate.cache.HashtableCacheProvider</prop> 
   </props>
  </property>

 </bean>

 <bean id="hibernateTemplate"
  class="org.springframework.orm.hibernate3.HibernateTemplate">
  <property name="sessionFactory" ref="sessionFactory"/>
 </bean>

 <bean id="userDao" class="de.hsrm.mediathek.UserDao">
  <property name="hibernateTemplate" ref="hibernateTemplate" />
 </bean>

 <bean id="transactionManager"
  class="org.springframework.orm.hibernate3.HibernateTransactionManager">
  <property name="sessionFactory" ref="sessionFactory" />
 </bean>

 <!-- Interceptor for hibernate calls to be able to create and close sessions 
 <bean id="hibernateInterceptor"
  class="org.springframework.orm.hibernate3.HibernateInterceptor">
  <property name="sessionFactory"><ref local="sessionFactory"/></property>
 </bean> 
-->
</beans>

Error LOG:

Caused by: java.sql.SQLException: Table not found in statement [insert into User (ID, NAME) values (null, ?)]
 at org.hsqldb.jdbc.Util.throwError(Unknown Source)
 at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
 at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
 at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:528)
 at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:95)
 at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:30)
 ... 63 more

---

The problem is that the property "hbm2dll" is wrongly spelled. It should be "hbm2ddl":

It is:

<prop key="hibernate.hbm2dll.auto">create-drop</prop>

It should be:

<prop key="hibernate.hbm2ddl.auto">create-drop</prop>

---

It's also better to keep an eye on logs for any other errors, as I had a similar problem and found out that create table statement was failing for HSQLDB's DDL generated by Hibernate and Hibernate was silently swallowing it

2011-06-09 10:48:30,722 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - Wrong data type: ALERT_ID in statement [create table ACCOUNT_ALERT (ALERT_ID numeric generated by default as identity (start with 1)]

Later when I changed the following

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name="ALERT_ID")
private BigInteger alertId;

to

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name="ALERT_ID")
private BigInteger alertId;

it worked perfectly.

---

I'd suggest trying to run HSQLDB in server mode using:

java -classpath hsqldb.jar org.hsqldb.server.Server

Check that it's running using Database manager:

java -classpath hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

Next, change your Hibernate URL, so it uses server mode HSQLDB:

<property name="url" value="jdbc:hsqldb:mediathekdb" />

I've had success using this method. It's possible that create-drop doesn't work properly when using a memory only instance.