[
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 Chris,
The requirement is: if a database vendor preserves case, we want to use this feature (so that tables / columns are seen in the case supported by the vendor for people who will actually look at the database not using our application, for example using some native tools - let's call them DBAs), but if it does not, that's ok (well, it's the client who chose a specific database after all), but still be able to change the database server without having to recompile the mapping java files or create XML files with mapping overrides.
I checked your tip with using quotes and it works (thanks for it; for those who read this thread - the example should be @Column(name = "\"Id\"")), but if we use this approach, we (and the DBAs) will have to use quotes in every single SQL, which can be a nuisance.
The additional setting for case insensitivity seems complementary to the 'force-uppercase' property for PostgreSQL, and still allows to write case insensitive SQLs (without quotes). The case will be merely a cosmetic addition to databases that can preserve it for people who read the schema a lot.
Thank you for looking into the patch and our issue. I will file a bug with the patched files, hope it can be useful.
Best regards,
Rafał
-----Original Message-----
From: eclipselink-users-bounces@xxxxxxxxxxx [mailto:eclipselink-users-bounces@xxxxxxxxxxx] On Behalf Of christopher delahunt
Sent: Friday, January 15, 2010 3:13 PM
To: EclipseLink User Discussions
Subject: 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
>
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
