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.
@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>
* <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.
