Problems using GenerationType.IDENTITY for in-memory databases

[Kristian Aaslund]

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:

Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0 (Build 36 (02/17/2007))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: Sequence "SEQ_GEN_SEQUENCE" not found; SQL statement:
SELECT MAX(NEXT VALUE FOR SEQ_GEN_SEQUENCE) FROM SYSTEM_RANGE(1, 50) [90036-158]Error Code: 90036
Call:SELECT MAX(NEXT VALUE FOR SEQ_GEN_SEQUENCE) FROM SYSTEM_RANGE(1, 50)
Query:ValueReadQuery()

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]

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

CREATE TABLE orders (order_id INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, NAME VARCHAR(255), PRIMARY KEY (order_id))What is your table definition?

[Wouter Oet]

And welcome to the JavaRanch!

[Kristian Aaslund]

Thanks. Happy to visit the Ranch

TopLink generated this create sql: CREATE TABLE status (ID INTEGER NOT NULL, LongDescription VARCHAR(255), Changed TIMESTAMP, Description VARCHAR(255), ChangedBy VARCHAR(255), Name VARCHAR(255), PRIMARY KEY (ID))
The live SQL Server DB uses this: CREATE TABLE [dbo].[status]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, [Description] [varchar](50) NULL, [LongDescription] [varchar](1000) NULL, [Changed] [datetime] NULL, [ChangedBy] [varchar](50) NULL, CONSTRAINT [PK__status__1E2636F2] PRIMARY KEY CLUSTERED (
The entity class looks like this:

@Entity
@Table(name = "status")
@NamedQueries({
    @NamedQuery(name = "Status.findAll", query = "SELECT s FROM Status s"),
    @NamedQuery(name = "Status.findByName", query = "SELECT c FROM Status c WHERE c.name = :name")})

public class Status implements EntityInterface, Serializable {
    @Transient
    private PropertyChangeSupport changeSupport = new PropertyChangeSupport(this);
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "ID")
    private Integer id;
    @Column(name = "Name")
    private String name;
    @Column(name = "Description")
    private String description;
    @Column(name = "LongDescription")
    private String longDescription;
    @Column(name = "Changed")
    @Temporal(TemporalType.TIMESTAMP)
    private Date changed;
    @Column(name = "ChangedBy")
    private String changedBy;
    ...

[Wouter Oet]

That entity class generates the following sql with EclipseLink:
CREATE TABLE status (ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, ChangedBy VARCHAR(255), Description VARCHAR(255), Name VARCHAR(255), LongDescription VARCHAR(255), Changed TIMESTAMP, PRIMARY KEY (ID))
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]

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]

Persistence.xml
<?xml version="1.0" encoding="UTF-8"?> <persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0"> <persistence-unit name="JPAPersistenceUnit" transaction-type="RESOURCE_LOCAL"> <class>jpa.entities.Status</class> <properties> <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver"/> <property name="javax.persistence.jdbc.url" value="jdbc:derby:test;create=true"/> <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/> <property name="eclipselink.ddl-generation.output-mode" value="database"/> <property name="eclipselink.logging.logger" value="logging.slf4j.EclipseLinkToSLF4JAdapter"/> <property name="eclipselink.logging.level" value="INFO"/> <property name="eclipselink.logging.level.sql" value="FINEST"/> </properties> </persistence-unit> </persistence>

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

[Kristian Aaslund]

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:

javax.persistence.jdbc.url=jdbc:h2:mem:hiltest;create=true
javax.persistence.jdbc.driver=org.h2.Driver
eclipselink.target-database=org.eclipse.persistence.platform.database.H2Platform
eclipselink.ddl-generation=drop-and-create-tables
eclipselink.ddl-generation.output-mode=sql-script
eclipselink.create-ddl-jdbc-file-name=createDDL.sql
eclipselink.drop-ddl-jdbc-file-name=dropDDL.sql
eclipselink.application-location=target
eclipselink.logging.level=FINEST
eclipselink.logging.level.sql=FINEST

I will give derby a try with EclipseLink too..

[Kristian Aaslund]

I got it working with EclipseLink and H2 by changing to these config parameters:

eclipselink.ddl-generation=create-tables
eclipselink.ddl-generation.output-mode=both

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

Thanks for your help!

[Wouter Oet]

Glad I could help.