wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes Problems using GenerationType.IDENTITY for in-memory databases Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Problems using GenerationType.IDENTITY for in-memory databases" Watch "Problems using GenerationType.IDENTITY for in-memory databases" New topic
Author

Problems using GenerationType.IDENTITY for in-memory databases

Kristian Aaslund
Greenhorn

Joined: Jul 21, 2011
Posts: 8
I'm currently trying to establish a test harness for a legacy system backed by SQL Server. I want to use a lightweight in-memory DB to be able to run unit tests quickly and without messing with an entire server-based test DB. I am creating different maven profiles for development, test and production. The legacy system uses TopLink and standard JPA as the ORM-tool.

The problem is that I can't get any of the in-memory DBs to support GenerationType.IDENTITY for the ID column. This is used without problems in the live SQL Server DB.

Both HSQLDB and H2 fails with IDENTITY. Error:
AUTO works fine for H2, but not when switching back to SQL Server again.
I have tried different implementations of the H2Platform as suggested here with no luck.

Apache Derby seems to have a limit of 18 characters for naming constraints during creation of the DB. This lead to a lot of duplicate constraint names, so no go there..

All DBs are latest version available in Maven Central July 21 2011.

Does anyone have tips for where to go next?
- Will switching to Hibernate or EclipseLink help?
- Are there other alternatives I should try?
Wouter Oet
Saloon Keeper

Joined: Oct 25, 2008
Posts: 2700

My quick test shows no problems using eclipselink. The executed sql:

What is your table definition?


"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." --- Martin Fowler
Please correct my English.
Wouter Oet
Saloon Keeper

Joined: Oct 25, 2008
Posts: 2700

And welcome to the JavaRanch!
Kristian Aaslund
Greenhorn

Joined: Jul 21, 2011
Posts: 8
Thanks. Happy to visit the Ranch

TopLink generated this create sql:
The live SQL Server DB uses this:
The entity class looks like this:
Wouter Oet
Saloon Keeper

Joined: Oct 25, 2008
Posts: 2700

That entity class generates the following sql with EclipseLink:

Your sql is missing the IDENTITY part. Apparently it tries to use a sequence that doesn't exist. Maybe it's a bug (note that I just started learning JPA so always be sceptic).
Kristian Aaslund
Greenhorn

Joined: Jul 21, 2011
Posts: 8
Then I need to give that a try.

Can you post your presistence.xml with your EclipseLink config?
Also, what version of EclipseLink are you using?

Thanks!
Wouter Oet
Saloon Keeper

Joined: Oct 25, 2008
Posts: 2700

Persistence.xml


My EclipseLink version is 2.0.0. The EclipseLinkToSLF4JAdapter is a class for adapting EclipseLink-logging to SLF4J logging.
Kristian Aaslund
Greenhorn

Joined: Jul 21, 2011
Posts: 8
I'm trying.
Looks like EclipseLink didn't create the db right. I get "org.h2.jdbc.JdbcSQLException: Table "STATUS" not found" when trying to do a simple select from a unit test.

Here are my config parameters:


I will give derby a try with EclipseLink too..
Kristian Aaslund
Greenhorn

Joined: Jul 21, 2011
Posts: 8
I got it working with EclipseLink and H2 by changing to these config parameters:


Now, let's try if it works against an existing test db ;)

Thanks for your help!
Wouter Oet
Saloon Keeper

Joined: Oct 25, 2008
Posts: 2700

Glad I could help.
 
Don't get me started about those stupid light bulbs.
 
subject: Problems using GenerationType.IDENTITY for in-memory databases