not persisting in HSQLDB

[Rich Morrison]

I have downloaded and run a few simple examples using:
Eclipse 3.2
Hibernate 3.1.3
HSQLDB 1.8
Windows XP

The examples work but nothing seems to be saved to disk.

Here is the config.xml
=======================

<?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">
 
<!-- Generated by MyEclipse Hibernate Tools.                   -->
<hibernate-configuration>
 
  <session-factory>
  
  <!--  HSQL connection -->
  <property name="dialect">org.hibernate.dialect.HSQLDialect</property>
<property name="show_sql">true</property>
<property name="use_outer_join">false</property>
<property name="jta.UserTransaction">java:comp/UserTransaction</property>

<property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
<!--use the 'file:' format to point to a database not in the project-->
<!--added shutdown=true from comment in blog-->
<!--is supposed to ix bug that prevents data saving to DB-->
<property name="hibernate.connection.url">jdbc:hsqldb:file:c:\temp\productsDB\products_t</property>
<property name="hibernate.connection.username">sa</property>
<property name="hibernate.connection.password"></property>
<property name="hibernate.connection.pool_size">2</property>
  
    <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <!--  thread is the short name for
      org.hibernate.context.ThreadLocalSessionContext
      and let Hibernate bind the session automatically to the thread
    -->
    <property name="current_session_context_class">thread</property>
    <!-- this will show us all sql statements -->
    <property name="hibernate.show_sql">true</property>
    
    <!-- this will create the database tables for us--> 
    <property name="hibernate.hbm2ddl.auto">create</property>   
        
    <mapping resource="tables/Order.hbm.xml" />
    <mapping resource="tables/Product.hbm.xml" />
    <mapping resource="tables/OrderItem.hbm.xml" />
 
  </session-factory>
 
</hibernate-configuration>
 

Here is the java code
=====================
package actions;
 
import java.util.Iterator;
import java.util.List;
 
import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
 
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
 
import tables.Product;
 
public class InsertProduct {
private static Logger log =Logger.getLogger(InsertProduct.class);
public static void main(String[] args) 
throws Exception {
 
// 1. Build a Product
Product p = new Product();
p.setId(20);
p.setName("Grommets");
p.setAmount(24);
p.setPrice(23.45);
System.out.println(p);
 
Session session = null;
Transaction tx = null;
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
session = sessionFactory.openSession();
tx = session.beginTransaction();
 
try {
session.save(p);
tx.commit();
session.flush();
session.close();
 
} catch (HibernateException e) {
e.printStackTrace();
if (tx != null && tx.isActive())
tx.rollback();
}
//
//Now look for the Products just inserted
    String query = "select p from Product as p";
    session = sessionFactory.openSession();
    tx = null;
    tx = session.beginTransaction();
        
    // search and return
    List list = session.createQuery(query).list();
         
    if (list.size() == 0)
    {System.out.println("No products found ");}
        
for (Iterator iter = list.iterator(); iter.hasNext() :wink: 
{
Product p2 = (Product) iter.next();
log.debug(p2);
}
tx.commit();
session.flush();
session.close();
 

 }
}

The code runs and I see "Grommets" from the select statement, but there is nothing saved to the table. If I open the table with HSQLBD console applet, the table is empty.

I tried turning off "hibernate.hbm2ddl.auto" and I tried ";shutdown=true" after the url, based on comments on other sites.

I have also tried creating the tables with HSQLDB before running tests. Same result.

I have edited the HSQLDB properties file and changed the default_table_type to "cached" with no change.

What am I doing wrong ???

TIA,
Rich Morrison

[Mark Spritzler]

Sorry, I didn't look at your mapping and code, it is late at night and I have to wake up early. But more as an FYI, Hypersonic is run in-memory, so when you shut down JBoss all the tables and data will be lost.

Mark

[pascal betz]

hey Mark

HSQLDB can also run in a "file" backed mode:

An In-Process Mode database is started from JDBC, with the database file path specified in the connection URL. For example, if the database name is testdb and its files are located in the same directory as where the command to run your application was issued, the following code is used for the connection:

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:testdb", "sa", "");

see also the HSQLDB doc: http://www.hsqldb.org/web/hsqlDocsFrame.html

it seems the line

<property name="hibernate.connection.url">jdbc:hsqldb:file:c:\temp\productsDB\products_t</property>

indicates that he is using HSQLDB in file mode.

none of this explains why nothing ends up in the DB though ;-)
have you checked the file path ? perhaps try something without directories first ? have you turned on SQL logging in hibernate so you see what SQL statements are beeing sent to the DB ?

pascal

[Rich Morrison]

Mark/Pascal,

Thanks for the replies.

Mark, I think that "memory" tables (from the HSQLDB preperties) are saved to disk. That's how I interpret the HSQLDB manual (page 6).

Pascal, yes I am using file mode in the connection. Could the path be wrong ? I think it could but I don't get any error in the log. I do have the show SQL option on and the generated SQL looks all right to me based on other examples I have seen in sample projects.

But maybe the path is wrong. I am not very familiar with "relative" path coding. In the Eclipse java project, the HSQLDB jar is included in the classpath as an external jar. The jar is in a folder in C:\Program Files\hsqldb. Is the path is relative to the location of hsqldb.jar ?

Still struggling,
Rich Morrison

[Santosh Ramachandrula]

comment this out or use validate instead of create

 <!-- this will create the database tables for us-->     <property name="hibernate.hbm2ddl.auto">create</property>

[Mykola Makhin]

I'm having same problem. Did you find the solution?

P.S. Guys, do you even read?

Mark Spritzler wrote:But more as an FYI, Hypersonic is run in-memory, so when you shut down JBoss all the tables and data will be lost.

Rich Morrison wrote:jdbc:hsqldb:file:c:\temp\productsDB\products_t

Santosh Ramachandrula wrote:comment this out or use validate instead of create

 <!-- this will create the database tables for us-->     <property name="hibernate.hbm2ddl.auto">create</property>

Rich Morrison wrote:I tried turning off "hibernate.hbm2ddl.auto" and I tried ";shutdown=true" after the url, based on comments on other sites.

[Mykola Makhin]

P.P.S. Some details:
Versions (from maven pom.xml):
<dependency> <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>2.2.4</version> </dependency> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> <type>jar</type> <scope>compile</scope> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate</artifactId> <version>3.5.4-Final</version> <type>pom</type> <scope>compile</scope> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>3.5.4-Final</version> <type>jar</type> <scope>compile</scope> </dependency>

Spring config (spring version is irrelevant I suppose):
<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="org.hsqldb.jdbcDriver" /> <property name="url" ref="jdbc:hsqldb:file:/Users/blah/blahdb/db" /> <property name="username" value="sa" /> <property name="password" value="" /> </bean> <bean id="mySessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> <property name="dataSource" ref="myDataSource" /> <property name="configurationClass" value="org.hibernate.cfg.AnnotationConfiguration" /> <property name="configLocation"> <value>classpath:hibernate.cfg.xml</value> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop> <prop key="hibernate.hbm2ddl.auto">create</prop> </props> </property> </bean>

The hibernate.cfg.xml:
<?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> <mapping package="x.mvmn.blah" /> <mapping class="x.mvmn.blah.model.impl.BlahImpl" /> </session-factory> </hibernate-configuration>

The x.mvmn.blah.model.impl.BlahImpl:

package x.mvmn.blah.model.impl;

import javax.persistence.Basic;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import x.mvmn.blah.model.Blah;

@Entity
@Table(name="Blah")
public class BlahImpl implements Blah {
	
	private long id;
	private String name;

	@Id
	@GeneratedValue(strategy=GenerationType.TABLE)
	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}
	
	@Basic
	public String getName() {
		return name;
	}
	
	public void setName(String name) {
		this.name = name;
	}
	
	public String toString() {
		return id+":"+name;
	}
}

Running code

	public static void main(String[] args) {
		ClassPathXmlApplicationContext cpXmlAppContext = new ClassPathXmlApplicationContext("blah-spring-context.xml");
		SessionFactory mySessionFactory = (SessionFactory) cpXmlAppContext.getBean("mySessionFactory");
		Session session = mySessionFactory.openSession();
		BlahImpl tag = new BlahImpl();
		tag.setName("ABC");
		session.save(tag);
		tag = new BlahImpl();
		tag.setName("ABC");
		session.save(tag);
		tag = new BlahImpl();
		tag.setName("ABC");
		session.save(tag);
		session.flush();
		SQLQuery query = session.createSQLQuery("select * from Blah");
		List<?> result = query.list();
		for (Object rs : result) {
			System.out.println(rs);
		}
		session.close();
		mySessionFactory.close();
	}

With hibernate.hbm2ddl.auto set to create outputs this:
[Ljava.lang.Object;@12b27c38
[Ljava.lang.Object;@3f94a1f
[Ljava.lang.Object;@63713b42

With hibernate.hbm2ddl.auto set to validate, complains that table doesn't exist.

[Mykola Makhin]

Ok, I've found the solution on StackOverflow: http://stackoverflow.com/questions/3551988/hsqldb-and-hibernate-jpa-not-persisting-to-disk

To make HSQL save data to disk immidiately I've added these properties to JDBC DB URL:
;shutdown=true;hsqldb.write_delay=false;

Seems to be working fine now.