Quickly: Using H2 Database with Glassfish and Toplink

For Glassfish: Configuring sun-resources.xml

To use H2 Database with Glassfish (or Sun AS), you must edit the sun-resources.xml file and configure a JDBC Resource and a JDBC Connection Pool using respectively the tags jdbc-resource and jdbc-connection-pool. Take care with the attribute datasource-classname of jdbc-connection-pool, because you must configure its value to org.h2.jdbcx.JdbcDataSource. If you configure this parameter with other value, like org.h2.Driver (it isn't a javax.sql.Datasource!), you get the error:

Caused by: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build b04-fcs (04/11/2008))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: org.h2.Driver cannot be cast to javax.sql.DataSource
Error Code: 0
        at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:305)
        at oracle.toplink.essentials.jndi.JNDIConnector.connect(JNDIConnector.java:150)
        at oracle.toplink.essentials.sessions.DatasourceLogin.connectToDatasource(DatasourceLogin.java:184)
        at oracle.toplink.essentials.internal.sessions.DatabaseSessionImpl.loginAndDetectDatasource(DatabaseSessionImpl.java:582)
        at oracle.toplink.essentials.ejb.cmp3.EntityManagerFactoryProvider.login(EntityManagerFactoryProvider.java:280)
        at oracle.toplink.essentials.internal.ejb.cmp3.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:229)

So, after configure the sun-resources.xml, I have:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//Sun Microsystems, Inc.//DTD Application Server 9.0 Resource Definitions //EN" "http://www.sun.com/software/appserver/dtds/sun-resources_1_3.dtd">
<resources>
  <jdbc-resource enabled="true" jndi-name="jdbc/TrilhaH2" object-type="user" pool-name="trilhaH2Pool"/>
  <jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false" connection-creation-retry-attempts="0" connection-creation-retry-interval-in-seconds="10" connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0" connection-validation-method="auto-commit" datasource-classname="org.h2.jdbcx.JdbcDataSource" fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="true" lazy-connection-association="false" lazy-connection-enlistment="false" match-connections="false" max-connection-usage-count="0" max-pool-size="32" max-wait-time-in-millis="60000" name="trilhaH2Pool" non-transactional-connections="false" pool-resize-quantity="2" res-type="javax.sql.DataSource" statement-timeout-in-seconds="-1" steady-pool-size="8" validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
    <property name="User" value=""/>
    <property name="Password" value=""/>
    <property name="URL" value="jdbc:h2:tcp://localhost//store/h2/trilha"/>
    <property name="driverClass" value="org.h2.Driver"/>
  </jdbc-connection-pool>
</resources>

H2Platform: H2 Specific Behavior to Toplink

To use H2 Database with Toplink, may be do you want to instruct the Toplink to take full use of H2 SQL/JDBC features. If yes, take a look at H2Platform java source code and read its instructions. And after compile it and place it into the JAR, configure your persistence.xml including the property:
<property name="toplink.target-database" value="oracle.toplink.essentials.platform.database.H2Platform"/> .
Note: The old property toplink.platform.class.name is deprecated - instead, use toplink.target-database.

Warning: H2Platform is inaccurate

The H2Platform class (from the h2-2008-07-28.zip)has a poor and inaccurate implementation. If you want to use IDENTITY data type to (duh!) alto-increment a primary key field, with the H2Platform, it doesn't work! Instead you are obligated to use SEQUENCE. By default, Toplink expected a SEQUENCE with a increment of 50.

Eg., if you have an annotated field like this:

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "TERMINALHISTORY_ID", nullable = false, insertable = true, updatable = false)
    private Long id;

The H2Platform supplied by H2, fails and you should be use the following (exactly) sequence:

CREATE SEQUENCE SEQ_GEN_SEQUENCE start with 0 INCREMENT BY 50;

If you set the increment size to a value less than the expected by Toplink (50), do you get the warning:
The sequence named [SEQ_GEN_SEQUENCE] is setup incorrectly. Its increment does not match its pre-allocation size.
I saw into the H2Platform source code and found this (negligent) method:

    public ValueReadQuery buildSelectQueryForNativeSequence(String seqName, Integer size) {
        return new ValueReadQuery("CALL NEXT VALUE FOR " + getQualifiedSequenceName(seqName));
        // return new ValueReadQuery("SELECT " + getQualifiedSequenceName(seqName) + ".NEXTVAL FROM DUAL");
    }

See? It ignores the Toplink parameter: size.

A few better H2Platform

If you want to use the native H2 support to IDENTITY with Toplink, you must use better H2Platform implementation. You can use this:

/*
 * The contents of this file are subject to the terms
 * of the Common Development and Distribution License
 * (the "License").  You may not use this file except
 * in compliance with the License.
 *
 * You can obtain a copy of the license at
 * glassfish/bootstrap/legal/CDDLv1.0.txt or
 * https://glassfish.dev.java.net/public/CDDLv1.0.html.
 * See the License for the specific language governing
 * permissions and limitations under the License.
 *
 * When distributing Covered Code, include this CDDL
 * HEADER in each file and include the License file at
 * glassfish/bootstrap/legal/CDDLv1.0.txt.  If applicable,
 * add the following below this CDDL HEADER, with the
 * fields enclosed by brackets "[]" replaced with your
 * own identifying information: Portions Copyright [yyyy]
 * [name of copyright owner]
 */
// Copyright (c) 1998, 2006, Oracle. All rights reserved.
package net.marciowb.trilha.dao;

import java.util.*;
import oracle.toplink.essentials.queryframework.*;
import oracle.toplink.essentials.expressions.*;
import oracle.toplink.essentials.internal.databaseaccess.*;

/**
 * This platform provides H2 specific behaviour.
 * Use the following setting to enable this platform:
 * <pre>
 * &lt;property
 *   name="toplink.platform.class.name"
 *   value="oracle.toplink.essentials.platform.database.H2Platform"/>
 * </pre>
 * See also: https://glassfish.dev.java.net/issues/show_bug.cgi?id=4042
 * @author Originally H2 Team
 * @author 2008-08-09 00h07 BSB Marcio Wesley Borges Brasilia-DF,Brazil
 */
public class H2Platform extends DatabasePlatform {
    public H2Platform() {
    }

    protected Hashtable buildFieldTypes() {
        Hashtable fieldTypeMapping;

        fieldTypeMapping = super.buildFieldTypes();
        fieldTypeMapping.put(Boolean.class, new FieldTypeDefinition("TINYINT", false));
        fieldTypeMapping.put(Integer.class, new FieldTypeDefinition("INTEGER", false));
        fieldTypeMapping.put(Long.class, new FieldTypeDefinition("NUMERIC", 19));
        fieldTypeMapping.put(Float.class, new FieldTypeDefinition("REAL", false));
        fieldTypeMapping.put(Double.class, new FieldTypeDefinition("REAL", false));
        fieldTypeMapping.put(Short.class, new FieldTypeDefinition("SMALLINT", false));
        fieldTypeMapping.put(Byte.class, new FieldTypeDefinition("SMALLINT", false));
        fieldTypeMapping.put(java.math.BigInteger.class, new FieldTypeDefinition("NUMERIC", 38));
        fieldTypeMapping.put(java.math.BigDecimal.class, new FieldTypeDefinition("NUMERIC", 38).setLimits(38, -19, 19));
        fieldTypeMapping.put(Number.class, new FieldTypeDefinition("NUMERIC", 38).setLimits(38, -19, 19));
        fieldTypeMapping.put(Byte[].class, new FieldTypeDefinition("BINARY", false));
        fieldTypeMapping.put(Character[].class, new FieldTypeDefinition("LONGVARCHAR", false));
        fieldTypeMapping.put(byte[].class, new FieldTypeDefinition("BINARY", false));
        fieldTypeMapping.put(char[].class, new FieldTypeDefinition("LONGVARCHAR", false));
        fieldTypeMapping.put(java.sql.Blob.class, new FieldTypeDefinition("BINARY", false));
        fieldTypeMapping.put(java.sql.Clob.class, new FieldTypeDefinition("LONGVARCHAR", false));
        fieldTypeMapping.put(java.sql.Date.class, new FieldTypeDefinition("DATE", false));
        fieldTypeMapping.put(java.sql.Time.class, new FieldTypeDefinition("TIME", false));
        fieldTypeMapping.put(java.sql.Timestamp.class, new FieldTypeDefinition("TIMESTAMP", false));

        return fieldTypeMapping;
    }


    // public boolean isHSQL() {
    //     return true;
    // }

    public boolean isH2() {
        return true;
    }

    public boolean supportsForeignKeyConstraints() {
        return true;
    }

    public ValueReadQuery buildSelectQueryForNativeSequence(String seqName, Integer size) {
        return new ValueReadQuery("CALL NEXT VALUE FOR " + getQualifiedSequenceName(seqName));
        // return new ValueReadQuery("SELECT " + getQualifiedSequenceName(seqName) + ".NEXTVAL FROM DUAL");
    }

    public boolean supportsNativeSequenceNumbers() {
        return true;
    }

    protected String getQualifiedSequenceName(String seqName) {
        if (getTableQualifier().equals("")) {
            return seqName;
        } else {
            return getTableQualifier() + "." + seqName;
        }
    }

    public boolean supportsSelectForUpdateNoWait() {
        return true;
    }

    protected ExpressionOperator todayOperator() {
        return ExpressionOperator.simpleFunctionNoParentheses(ExpressionOperator.Today, "SYSDATE");
    }

    protected void initializePlatformOperators() {
        super.initializePlatformOperators();
        addOperator(ExpressionOperator.simpleMath(ExpressionOperator.Concat, "||"));
    }

    public boolean shouldUseJDBCOuterJoinSyntax() {
        return false;
    }

    
/** @return <code>true</code> = Of course! */
    public boolean supportsIdentity() {
        return true;
    }

    /**
     * Now we implement the another missing method to support 
     * IDENTITY data type field.
     * @return  What it was lacking.
     */
    public ValueReadQuery buildSelectQueryForIdentity() {
        ValueReadQuery selectQuery = new ValueReadQuery();
        selectQuery.setSQLString("SELECT IDENTITY()");
        return selectQuery;
    }
}

To use it, remember to config correctly your persistence.xml supplying the expected property, like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
  <persistence-unit name="TrilhaPU" transaction-type="RESOURCE_LOCAL">
    <provider>oracle.toplink.essentials.PersistenceProvider</provider>
    <class>net.marciowb.trilha.dao.Fleet</class>
    <class>net.marciowb.trilha.dao.Organization</class>
    <class>net.marciowb.trilha.dao.Terminal</class>
    <class>net.marciowb.trilha.dao.TerminalConfiguration</class>
    <class>net.marciowb.trilha.dao.TerminalHistory</class>
    <class>net.marciowb.trilha.dao.UserAccount</class>
    <class>net.marciowb.trilha.dao.Vehicle</class>
    <properties>
      <property name="toplink.jdbc.user" value=""/>
      <property name="toplink.jdbc.password" value=""/>
      <property name="toplink.jdbc.url" value="jdbc:h2:tcp://localhost//store/h2/trilha"/>
      <property name="toplink.jdbc.driver" value="org.h2.Driver"/>
      <property name="toplink.target-database" value="net.marciowb.trilha.dao.H2Platform"/>
    </properties>
  </persistence-unit>
</persistence>

If you wish, download the above H2Platform code here.

Thanks to

• jassi_saggu: Re: jdbc MS SQL 2005 not working with Glassfishv2 w/persistency
• データソースの設定（H2 Database編）