I am having trouble loading data back from my HSQL database. I can save data to the DB. But, if I re-run my main method and try and load the data the DB is empty. Almost like the DB (or at least the table) is being recreated each time. See details below....

Hibernate version: 3

Mapping documents:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<!-- Database connection settings -->
<property name="connection.driver_class">
org.hsqldb.jdbcDriver
</property>
<property name="connection.url">
jdbc:hsqldb:file:mydb
</property>
<property name="connection.username">blah</property>
<property name="connection.password"></property>

<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">1</property>

<!-- SQL dialect -->
<property name="dialect">
org.hibernate.dialect.HSQLDialect
</property>

<!-- Enable Hibernate's automatic session context management -->
<property name="current_session_context_class">thread</property>

<!-- Disable the second-level cache -->
<property name="cache.provider_class">
org.hibernate.cache.NoCacheProvider
</property>

<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>

<!-- Drop and re-create the database schema on startup -->
<property name="hbm2ddl.auto">create</property>

<mapping class="com.myproject.Person" />

</session-factory>

</hibernate-configuration>


Code between sessionFactory.openSession() and session.close():
// I run this bit of code in a main method to store data in the DB
// then I exit my app.
List<Person> people = loadData(); // creates a bunch of Person objects
Session session = getSession();
Transaction tx = session.beginTransaction();
for (Person p : people) {
Object val = session.save(val);
System.out.println("Saved - " + val+ ", " + p.getName());
}

tx.commit();
session.close();

...after I run this code (above) HSQL generates a file, mydb.log (which is about 112kb).

// then i try and run this code and it returns 0 rows (and i can see that
// my DB files are empty
session = getSession();
tx = session.beginTransaction();
people = session.createQuery("from Person p").list();
System.out.println(people.size()) // outputs, 0

...when I run this code mydb.log becomes 0kb.

Name and version of the database you are using: HSQL 1.8.0


Any ideas why the DB (or table) is being recreated over and over?

you already turne show sql on so what does it say?



what sql is generated?

Comment the above line and try again.

Thanks for the help.

1. comment out the "hbm2dll.auto" line in the config file.

seemed to do the trick

Actually...that didn't completely solve my issues. here are the steps I am trying right now.

1. Comment out the hbm2dll.auto line in the config file.

2. Create the database (i.e. use ant script which has a "sql" target in it which does a 'CREATE TABLE ....')

3. Run my code which tries to save a bunch of Person objects.
for (Person p : getPeople())
session.save(p)

This gives me errors:
[ WARN] 25:28 (JDBCExceptionReporter.java:logExceptions:77)
SQL Error: -22, SQLState: S0002

[ERROR] 25:28 (JDBCExceptionReporter.java:logExceptions:78)
Table not found in statement [insert into PEOPLE (NAME, AGE, SEX) values (?, ?, ?)]

[ERROR] 25:28 (AbstractFlushingEventListener.java:performExecutions:301)
Could not synchronize database state with session

org.hibernate.exception.SQLGrammarException: could not insert: [com.myproject.Person]



Now if instead I do the following:

1. Delete the database

2. Uncomment the hbm2dll.auto line in the config, so now I have:
<property name="hbm2dll.auto">create</property>

3. Run my code which tries to save a bunch of Person objects:
for (Person p : getPeople())
session.save(p)

It works just like I want. However, I want to rerun my code to retrieve the Person objects, I have to comment out the hbm2dll.auto line before doing so. Seems like there should be an easier way for this work (i.e. not having to comment/uncomment that line in the config file before re-running the app).

Any ideas?

actually I think switching hbm2dll.auto to "update" may be what I need.

Can you post your hbm file? Looks like something smells in the table to object mapping.

-Nikhil

the file is in my original post with the exception that I now have hbm2dll.auto set to "update".

<property name="hbm2ddl.auto">upate</property>
i thought above must work