Posts Tagged ‘Hibernate’
(long tweet) 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
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
.
(quick tutorial) Migration from MySQL to HSQLDB
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 attributecatalog="..."
- replace the types with fully qualified object types, eg:
long
withjava.lang.Long
,string
withjava.lang.String
,timestamp
withjava.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.
Unable to instantiate default tuplizer… java.lang.NoSuchMethodError: org.objectweb.asm.ClassWriter.
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">
Useful DTD
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:
File | DTD |
---|---|
weblogic-application.xml |
<!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"> |
weblogic-application.xml |
<!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"> |
web.xml |
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd" > |
*.hbm.xml |
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> |
GWT modules |
<!DOCTYPE module SYSTEM "http://google-web-toolkit.googlecode.com/svn/trunk/distro-source/core/src/gwt-module.dtd"> |
GWT UI |
<!DOCTYPE ui:UiBinder SYSTEM "http://dl.google.com/gwt/DTD/xhtml.ent"> |
Tangosol / Oracle Coherence |
<!DOCTYPE coherence SYSTEM "coherence.dtd"> |
Log4J |
<!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.
org.hibernate.HibernateException: identifier of an instance of … was altered from … to 0
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 int
s, float
s, 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
.