[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Native SQL query and custom column mapping problem
|
Hello Rafał
The problem sounds like one that is described in glassfish bug:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=600
and further in forum post:
http://forums.java.net/jive/thread.jspa?messageID=190092
Questions are what exactly are the database mix case requirements? From
the sounds of your situation, the other databases you are using are not
using case-sensitive fields, and so are not mixed case. This is partly
why they would fail - because they return the column names in upper or
lower case, where EclipseLink using java string comparisons, expects the
case defined in the column. When it searches for "Id" or any other
mixed case field, the returned resultset is returning null. If you want
all your databases to be consistent, you will need to make them also
return the mixed case for the field. This will require creating the
column by wrapping the mixed case name in delimiters - double quotes
usually. EclipseLink will pass the column name it has as is to the
database, so if using a case sensitive field, you would also need to
wrap it in delimiters ie: @Column(name = "/"Id/""). EclipseLink will
correctly interpret the double quote delimiters as of EclipseLink 2.0 (I
don't remember the build), eliminating the need for the workaround
mentioned in point C of the forum post.
The attached patch looks good, and works for a specific case. Please
feel free to file a bug and attach the patch as, for legal reasons, it
is the only way to submit a patch .
Best Regards,
Chris
Swierzynski, Rafal wrote:
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ł
------------------------------------------------------------------------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
