openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Curtis <curti...@gmail.com>
Subject Re: OpenJPA generate wrong query when using postgres as DBDictionary
Date Fri, 12 Dec 2014 21:55:54 GMT
Nothing obvious jumps out at me, but a proper OpenJPA Junit would make
recreating this problem a much easier task.

Thanks,
Rick

On Thu, Dec 11, 2014 at 8:26 PM, jacksmith <leonliu.mm@gmail.com> wrote:

> Our application is using OpenJPA 2.2 with Oracle, everything is working
> fine.
>
> Now we need to move to postgres database, but we run into a basic issue,
> the
> generated query is wrong, it has extra table in the FROM statement which
> causes cartesian join.
>
> any help will be deeply appreciated. if you need anything else, please let
> me know.
>
> It's also very easy to reproduce the issue, following is the method to
> reproduce it:
> /**
> * with postgres as dbdictionay, this method produces wrong query like (with
> oracle it works fine):
> * SELECT t1.ORGN_ID FROM V_LOCATION t0 , T_DIM_ORGN t1 WHERE
> (t1.CLNT_OBJ_ID
> = ?)
> *
> * the correct query should be like:
> * SELECT t1.ORGN_ID FROM T_DIM_ORGN t1 WHERE (t1.CLNT_OBJ_ID = ?)
> */
> public void getDeptOptionsByUser(String filterId) {
>
>         List<Predicate> predicates = new ArrayList<Predicate>();
>         CriteriaBuilder cb = em.getCriteriaBuilder();
>         CriteriaQuery<Tuple> cq = cb.createTupleQuery();
>
>         /* the following line is purposely added to reproduce the issue.
> our real
> application code is
>         * much more complex and we don't have a way to bypass the issue
>         */
>         Root<LocationHierarchy> loc = cq.from(LocationHierarchy.class);
>
>         Root<DimOrganization> org = cq.from(DimOrganization.class);
>
>         predicates.add(cb.equal(org.get(DimOrganization_.clntObjId),
> "dummy"));
>
>         List<Selection&lt;?>> selections = new
> ArrayList<Selection&lt;?>>();
>
>         selections.add(org.get(DimOrganization_.orgnId).alias("filterId"));
>
>         cq.multiselect(selections).where(predicates.toArray(new
> Predicate[] {}));
>
>         System.out.println("Query String:" + cq.toString());
>         em.createQuery(cq).getResultList();
> }
>
> following is a simplified version of our persistence.xml.
>
> <?xml version="1.0" encoding="UTF-8"?>
> <persistence version="2.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_2_0.xsd">
>
>         <persistence-unit name="dw-persistence" transaction-type="JTA">
>                 <jta-data-source>jdbc/dw</jta-data-source>
>
>                 <class>com.entities.dm.DimOrganization</class>
>                 <class>com.entities.facts.FactWorkAssignment</class>
>                 <class>com.entities.dm.DimWorkLocation</class>
>                 <class>com.entities.dm.LocationHierarchy</class>
>                 <class>com.entities.dm.LocationHierarchyId</class>
>                 <exclude-unlisted-classes>true</exclude-unlisted-classes>
>
>                 <properties>
>                         <property name="openjpa.TransactionMode"
> value="managed" />
>                         <property name="openjpa.ConnectionFactoryMode"
> value="managed" />
>                         <property name="openjpa.jdbc.DBDictionary"
> value="postgres(CrossJoinClause=',')" />
>
>                         <property name="openjpa.Log" value="SQL=TRACE"/>
>                         <property
> name="openjpa.ConnectionFactoryProperties"
> value="PrettyPrint=true, PrettyPrintLineLength=72,PrintParameters=true"/>
>                         <property name="openjpa.Compatibility"
> value="QuotedNumbersInQueries=true" />
>                 </properties>
>         </persistence-unit>
>
>
> </persistence>
>
>
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/OpenJPA-generate-wrong-query-when-using-postgres-as-DBDictionary-tp7587490.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>



-- 
*Rick Curtis*

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message