Hi
again,
I forgot to attach
the patched files. Sorry about that, attaching them
now.
Regards,
Rafał
From:
eclipselink-users-bounces@xxxxxxxxxxx
[mailto:eclipselink-users-bounces@xxxxxxxxxxx] On Behalf Of Swierzynski,
Rafal
Sent: Thursday, January
14, 2010 1:34 PM
To:
eclipselink-users@xxxxxxxxxxx
Subject: [eclipselink-users] Native SQL
query and custom column mapping problem
Hi EclipseLink users and
developers,
we are using EclipseLink 2.0.0 and
encountered a problem with native SQL support and custom table name mappings.
The problem is that is doesn't always work. Our @Column mappings contain the
'name' attribute, like this (whole source code available as
attachment):
@Id
@Column(name =
"Id")
@GeneratedValue(strategy =
GenerationType.IDENTITY)
private Long
id;
@Column(name =
"Name")
private String
name;
Please notice that the column
names are camel case. Our tests show that it works with SQL Server only,
whereas it fails for HSQLDB, H2 and PostgreSQL (tested only with these, not
sure about others). It throws an exception saying that the id property cannot
be null:
org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary
key read from the row [DatabaseRecord(
=> 1
=> Jason Bourne)] during the execution of the query was detected to
be null. Primary keys must not contain
null.
Query:
ReadAllQuery(referenceClass=Person sql="select pe.* from Person
pe")
at
org.eclipse.persistence.exceptions.QueryException.nullPrimaryKeyInBuildingObject(QueryException.java:863)
at
org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:468)
at
org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:441)
at
org.eclipse.persistence.queries.ObjectLevelReadQuery.buildObject(ObjectLevelReadQuery.java:635)
at
org.eclipse.persistence.queries.ReadAllQuery.registerResultInUnitOfWork(ReadAllQuery.java:838)
at
org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:464)
at
org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:997)
at
org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
at
org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:958)
at
org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:432)
at
org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1021)
at
org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2863)
at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181)
at
org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:453)
at
org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:669)
at
test.NativeQueryTest.test(NativeQueryTest.java:29)
... Removed 22 stack
frames
with the id being 1 in this
example (in the test, it the entity is persisted first, and then re-read). It
doesn't matter if the second level cache is turned off or if I clear() the
entity manager, or both (which I think is a good thing as it is consistent
;d).
After some debugging it turns out
that the driver for SQL Server (net.sourceforge.jtds:jtds:1.2.4 in maven
parlance) returns the tables from the result metadata with mixed case, and it
works fine with the method
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.sortFields(Vector
fields, Vector columnNames). However, it doesn't work with other databases
because: PostgreSQL (driver postgresql:postgresql:8.4-701.jdbc4) returns
metadata in lowercase, whereas HQSLDB (org.hsqldb:hsqldb:1.0.8.10) and H2
(com.h2database:h2:1.1.118) return the metadata in uppercase. There is a
setting to force transforming returned metadata to uppercase for PostgreSQL
(as described here: http://wiki.eclipse.org/EclipseLink/FAQ/JPA/PostgreSQL)
but this only works if the mappings use only uppercase (as if you use the
default, for example), which is not true in our
case.
When the @Id is mapped so that it
works (like all uppercase), there is no exception reported about the id being
null, but the entity may not be completely initialized - the other attribute
is null if second level cache is turned off and I clear() the entity manager
(what basically means that the data is re-read from the database). This might
be a 'silent killer' in some scenarios.
After looking into the code and
debugging, there is a method
org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.setShouldIgnoreCaseOnFieldComparisons(boolean)
and an appropriate getter, and it is actually used by the mentioned
sortFields() method, but I cannot find a way to configure this property in
persistence.xml, I also didn't find (via eclipse IDE's References ->
Project / Workspace) any code that calls it. So, it looks like I must call it
myself, and when I add this call (with the parmeter set to true) to our test
case, it passes. Looks like a solution, but:
1. it ties the code of our JPA
module to EclipseLink because of this call, which we would like to
avoid
2. as it is a static method with a
static field as an implementation, the setting is inherently JVM /
classloader-wide, so every EL persistence unit within the JVM / classloader
would have it set (we have only one unit at this time, but still this might be
a problem for others I suppose)
To fix this, I added another
property called 'eclipselink.jdbc.case-insensitive-columns' which resembles
the 'eclipselink.jdbc.uppercase-columns' property in implementation, and
changed 5 classes slightly to make use of it. Now, the tests work
fine.
My question is: is there any other
way to have support for camel case mappings? I really did my best to find it,
but couldn't. Still, I may be missing something
important.
Also, this is not just a test
case, it actually is our requirement to have mixed case attributes, support
many databases, and use native queries (we have some scenarios that prevents
us from using JPQL only).
For more input, I attach a maven2
project with the sample entity and a test for it. Also, I attach my 'patch'
(the 5 modified files I mentioned). To test it, one would have to compile the
files, replace them in the EL jar and uncomment the last property in
persistence.xml. (I could attach the modified EL jar, but as it is 5mb I think
I should not. If you want me to do it, please provide me with an appropriate
link for upload). Is it possible (if there is no other way to fix our issue)
that the patch be incorporated into EL?
Best
regards,
Rafał
