Jonathan Lalou's blog

Posts Tagged ‘Hibernate’

Stacktrace

org.hibernate.tool.hbm2ddl.SchemaExport - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'type=InnoDB' at line 1

Quick fix

In JPA configuration, replace:
datasource.dialect = org.hibernate.dialect.MySQLInnoDBDialect
with:
datasource.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
Actually, Java driver for MySQL 5.5+ is not backward compatible with the SQL dialect MySQLInnoDBDialect.

Case

I got the project described in MK Yong’s website. This projects is a sample code of JSF + Spring + Hibernate. The laying DB is a MySQL. For many reasons, I’d rather not to detail, I prefered to a HSQLDB instead of the MySQL.

(Notice: the zip you can download at MK Yong’s website contains many errors, not related to the persistance layer but to JSF itself.)

How to migrate any project from MySQL to HSQLDB?

Solution

You have to follow these steps:

Maven

In the pom.xml, replace:

<!-- MySQL database driver -->
        <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
        </dependency>

with:

        <!-- driver for HSQLdb -->
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.2.8</version>
        </dependency>

By the way, you can add Jetty plugin to have shorter development cycles:

            <plugin>
                <groupId>org.mortbay.jetty</groupId>
                <artifactId>maven-jetty-plugin</artifactId>
                <configuration>
                    <webApp>${basedir}/target/jsf.war</webApp>
                    <port>8088</port>
                </configuration>
            </plugin>

Persistence

Properties

Replace the content of db.properties file with:

jdbc.driverClassName=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:hsql://localhost:9001
jdbc.username=sa
jdbc.password=

Hibernate Mapping

In the *.hbm.xml files:

• in the tag <class>, remove the attribute catalog="..."
• replace the types with fully qualified object types, eg:
  • long with java.lang.Long,
  • string with java.lang.String,
  • timestamp with java.util.Date,
  • etc.

Hibernate Properties

For the property of key hibernate.dialect, replace the value: org.hibernate.dialect.MySQLDialectorg.hibernate.dialect.HSQLDialect with the value: org.hibernate.dialect.HSQLDialect.
To match my needs, I tuned Hibernate properties a bit more, but it may not be needed in all situations:

        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hbm2ddl.auto">create-drop</prop>
                <prop key="hibernate.hbm2ddl.auto">create-drop</prop>
                <prop key="connection.pool_size">1</prop>
                <prop key="current_session_context_class">thread</prop>
                <prop key="cache.provider_class">org.hibernate.cache.NoCacheProvider</prop>
            </props>
        </property>

Run

Run the HSQLDB server. IMHO, the quickest is to run the following Maven command:

mvn exec:java -Dexec.mainClass="org.hsqldb.Server"

But you may prefer the old school java -cp hsqldb-XXX.jar org.hsqldb.Server ;-).

Tip! To get a GUI to check the content of the DB instance, you can run:

mvn exec:java -Dexec.mainClass="org.hsqldb.util.DatabaseManager"

Then build and launch Jetty:

mvn clean install jetty:run-exploded

Here you can see the great feature of HSQLDB, that will allow you to create, alter and delete tables on the fly (if hibernate.hbm2ddl.auto is set to create-drop), without any SQL scripts, but only thanks to HBM mapping files.

Case

On running a web application hosted on Jetty, I get the following stracktrace:

Nested in org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in ServletContext resource [/WEB-INF/classes/config/spring/beans/HibernateSessionFactory.xml]: Invocation of init method failed; nested exception is org.hibernate.HibernateException: Unable to instantiate default tuplizer [org.hibernate.tuple.entity.PojoEntityTuplizer]:
java.lang.NoSuchMethodError: org.objectweb.asm.ClassWriter.<init>(I)V

Unlike what I immediatly thought at first glance, the problem is not induced by the Tuplizer ; the actual error is hidden at the bottom: java.lang.NoSuchMethodError: org.objectweb.asm.ClassWriter.

Here are some of the dependencies:

org.hsqldb:hsqldb:jar:2.2.8:compile
org.springframework:spring:jar:2.5.6:compile
org.hibernate:hibernate:jar:3.2.7.ga:compile
javax.transaction:jta:jar:1.0.1B:compile
 |  +- asm:asm-attrs:jar:1.5.3:compile
 |  \- asm:asm:jar:1.5.3:compile

Fix

Main fix

The case is a classic problem of inherited depencencies. To fix it, you have to excluse ASM 1.5.3, and replace it with more recent version. In the pom.xml, you would then have:

    <properties>
        <spring.version>3.1.0.RELEASE</spring.version>
        <hibernate.version>3.2.7.ga</hibernate.version>
        <asm.version>3.1</asm.version>
    </properties>
...
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate</artifactId>
            <version>${hibernate.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>asm</groupId>
                    <artifactId>asm</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>asm</groupId>
                    <artifactId>asm-attrs</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>asm</groupId>
            <artifactId>asm</artifactId>
            <version>${asm.version}</version>
        </dependency>

Other improvements

I took the opportunity to upgrade Spring 2.5 to Spring 3.1 (cf the properties above).
Besides, I modified the *.hbm.xml files to use object types, rather than primary types, eg replacing:

<id name="jonathanId" type="long">

with:

<id name="jonathanId" type="java.lang.Long">

DTDs are useful when your XML editor take them in account: detecting errors, suggestions, complete statements… For instance, I save much time with IntelliJ IDEA automatic completion ; unlike, Eclipse amazingly does not implement this feature.

Here is a list of some widely used DTDs:

<!DOCTYPE weblogic-application PUBLIC "-//BEA Systems, Inc.//DTD WebLogic Application 7.0.0//EN" "http://www.oracle.com/technology/weblogic/weblogic-application/1.1/weblogic-application.xsd">

<!DOCTYPE weblogic-application PUBLIC "-//BEA Systems, Inc.//DTD WebLogic Application 7.0.0//EN" "http://www.oracle.com/technology/weblogic/weblogic-application/1.1/weblogic-application.xsd">

<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<!DOCTYPE module SYSTEM "http://google-web-toolkit.googlecode.com/svn/trunk/distro-source/core/src/gwt-module.dtd">

<!DOCTYPE ui:UiBinder SYSTEM "http://dl.google.com/gwt/DTD/xhtml.ent">

<!DOCTYPE coherence SYSTEM "coherence.dtd">

<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

Tangosol and Log4J DTDs are included within their distribution JARs: you have to extract them or to give their path to IntelliJ IDEA.

Case

Stracktrace

org.hibernate.HibernateException: identifier of an instance of lalou.jonathan.domain.Foo was altered from 183740934 to 0

Sometimes, the error is slightly different: was altered from XXXX to null

Here is a part of Hibernate mapping:

<id name="fooId" column="fooId">
  <generator class="seqhilo">
    <param name="sequence">JL_Foo_SEQ</param>
    <param name="max_lo">10</param>
  </generator>
</id>

Here is the Java code:

Foo sourceFoo = FooDAO.findById(xxxx);
Foo foo = new Foo();
foo = BeanUtils.copyProperties(sourceFoo);
foo.setFooId(null);
FooDAO.createFoo(foo);

Explanation

Using new Foo() instantiates an object of type Foo, with all its fields initialized at null (or zero for ints, floats, etc.). Writing explicitly foo.setFooId(null) removes the object from Hibernate current session.

Fix

Don’t set explicitly the fooId! Leave Hibernate initialize default values, and set handly other values, without using BeanUtils.