| RE: [eclipselink-users] Native SQL query and custom column mapping problem |
|
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 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ł |
Attachment:
src.zip
Description: src.zip