openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Fay Wang <fyw...@yahoo.com>
Subject Re: Named query doesn't translate table/column names properly, UNLESS it's run after an identical inline query
Date Tue, 26 Jan 2010 18:08:13 GMT
I was wrong. You problem seems not related to JIRA-859. After digging in a little bit, it appears
that the column name for the field specified in the orm.xml is properly parsed and set for
the field in openjpa 1.2.x. It must be something else that causes the failure...





----- Original Message ----
From: "KARR, DAVID (ATTCINW)" <dk068x@att.com>
To: users@openjpa.apache.org
Sent: Sat, January 23, 2010 11:28:15 AM
Subject: RE: Named query doesn't translate table/column names properly, UNLESS it's run after
an identical inline query

> -----Original Message-----
> From: Fay Wang [mailto:fyw300@yahoo.com]
> Sent: Saturday, January 23, 2010 10:53 AM
> To: users@openjpa.apache.org
> Subject: Re: Named query doesn't translate table/column names
properly,
> UNLESS it's run after an identical inline query
> 
> As you described, it seems that the information specified in the
> orm.xml is not picked up by OpenJPA. This issue seems resolved in
JIRA-
> 859 in the trunk code and 1.3.x branch.

Are you referring to OPENJPA-859, "OpenJPA requires all persistent
fields to be specified on an XML defined entity"?

> ----- Original Message ----
> From: "KARR, DAVID (ATTCINW)" <dk068x@att.com>
> To: users@openjpa.apache.org
> Sent: Fri, January 22, 2010 5:11:19 PM
> Subject: Named query doesn't translate table/column names properly,
> UNLESS it's run after an identical inline query
> 
> I've been running ok with 1.2.1, and I recently installed 1.2.2, which
> ran almost without changes out of the box.
> 
> I decided to start looking into converting some of my inline queries
to
> named queries, thinking it might be more efficient.
> 
> I have a DAOImpl class that currently has an inline query.  I copied
it
> exactly as written to a @NamedQuery annotation above the entity class.
> In the DAOImpl method, I commented out the line that created the query
> and changed it to reference the named query.  When I ran it, I found
> that the generated SQL didn't translate any of the table and column
> names, as described in the orm.xml file.
> 
> Then, I reverted that last change, commenting out the reference to the
> named query and uncommenting the creation of the inline query.  It was
> fine again.
> 
> Then, I tried doing BOTH in the same method.  I first did the inline
> query, and then after it got the results for it, I created another
> query
> using the named query, assigned the same parameter, and retrieved the
> result.  Surprisingly, it worked.  Yes, you read that right.
> 
> I then tried reversing the order, doing the named query first, and
then
> the inline query.  Not surprisingly, that call to the named query
> failed
> to translate properly.
> 
> When the named query goes bad, it ends up as:
> 
> SELECT t0.id, t1.id, t1.description, t1.displayName,
> t1.longDescription,
> t1.seoLabel, t0.creationDate, t0.description, t0.displayName,
> t0.longDescription, t2.CATEGORY_ID, t2.SEQUENCE_NUM, t3.id,
> t3.CATEGORYES_ID, t3.creationDate, t3.description, t3.displayName,
> t3.longDescription FROM Category t0, CategoryES t1, Category_Category
> t2, Category t3 WHERE (t0.id = ?) AND t0.CATEGORYES_ID = t1.id(+) AND
> t0.id = t2.CATEGORY_ID(+) AND t2.CHILDCATEGORIES_ID = t3.id(+) ORDER
BY
> t2.CATEGORY_ID ASC, t2.SEQUENCE_NUM ASC
> 
> When I call it after the inline query, the named query translates
> (correctly) as:
> 
> SELECT t0.CATEGORY_ID, t1.CATEGORY_ID, t1.DESCRIPTION,
t1.DISPLAY_NAME,
> t1.LONG_DESCRIPTION, t1.SEO_LABEL, t0.CREATION_DATE, t0.DESCRIPTION,
> t0.DISPLAY_NAME, t0.LONG_DESCRIPTION, t2.CATEGORY_ID, t3.CATEGORY_ID,
> t3.CREATION_DATE, t3.DESCRIPTION, t3.DISPLAY_NAME, t3.LONG_DESCRIPTION
> FROM cat1.DCS_CATEGORY t0, cat1.ONLINE_DCS_CATEGORY_ES t1,
> cat1.DCS_CAT_CHLDCAT t2, cat1.DCS_CATEGORY t3 WHERE (t0.CATEGORY_ID =
> ?)
> AND t0.CATEGORY_ID = t1.CATEGORY_ID(+) AND t0.CATEGORY_ID =
> t2.CATEGORY_ID(+) AND t2.CHILD_CAT_ID = t3.CATEGORY_ID(+) ORDER BY
> t2.CATEGORY_ID ASC
> 
> I verified that the generated SQL for the named query (when it's
> working) is identical to what I was getting from the inline query,
> which
> isn't surprising, as I copied it exactly.
> 
> The DAO method in question is the following (note that
> "entityClass.getName()" is just
> "com.att.ecom.dynamiccontent.domain.catalog.Category"):
> --------------------------
>     public Category getCategoryWithProducts(String id) {
>         Category    result  = null;
>         Query   query   =
> //            entityManager.createNamedQuery("categoryWithChildren");
>             entityManager.createQuery("select ctgry from " +
> entityClass.getName() + " ctgry " +
>                                       "left join fetch
> ctgry.childCategories " +
>                                       "left join fetch
> ctgry.childProducts " +
>                                       "where ctgry.id = :id");
>         query.setParameter("id", id);
>         result  = (Category) query.getSingleResult();
> 
>         Query   namedQuery  =
> entityManager.createNamedQuery("categoryWithChildren");
>         namedQuery.setParameter("id", id);
>         Category    category    = (Category)
> namedQuery.getSingleResult();
> 
>         return (result);
>     }
> ---------------------------
> 
> The relevant portion of the entity class is this:
> 
> --------------------------------
> @Entity
> @NamedQueries({ @NamedQuery(name = "categoryWithChildren",
>                             query = "select ctgry from
> com.att.ecom.dynamiccontent.domain.catalog.Category ctgry " +
>                                     "left join fetch
> ctgry.childCategories left join fetch ctgry.childProducts " +
>                                     "where ctgry.id = :id") })
> public class Category implements LocaleSpecific {
>     @Id
>     private String  id;
>     @Basic
>     private String  displayName;
>     @Basic
>     private String  description;
>     @Lob
>     private String  longDescription;
>     @OneToOne
>     private CategoryES  categoryES;
>     @Temporal(TemporalType.DATE)
>     private Date    creationDate;
>     @OneToMany
>     @OrderColumn(name = "SEQUENCE_NUM")
>     private List<Category>   childCategories;
>     @OneToMany
>     @OrderColumn(name = "SEQUENCE_NUM")
>     private List<Product>   childProducts;
> }
> ------------------------
> 
> The excerpt from the orm.xml is this:
> ------------------------------
>     <entity name="Category"
> class="com.att.ecom.dynamiccontent.domain.catalog.Category">
>         <table name="DCS_CATEGORY"/>
>         <attributes>
>             <id name="id">
>                 <column name="CATEGORY_ID"/>
>             </id>
>             <basic name="displayName">
>                 <column name="DISPLAY_NAME"/>
>             </basic>
>             <basic name="description">
>                 <column name="DESCRIPTION"/>
>             </basic>
>             <basic name="longDescription">
>                 <column name="LONG_DESCRIPTION"/>
>                 <lob/>
>             </basic>
>             <basic name="creationDate">
>                 <column name="CREATION_DATE"/>
>             </basic>
>             <one-to-many name="childCategories"
> target-entity="Category">
>                 <join-table name="DCS_CAT_CHLDCAT">
>                     <join-column name="CATEGORY_ID"
> referenced-column-name="CATEGORY_ID"/>
>                     <inverse-join-column name="CHILD_CAT_ID"
> referenced-column-name="CATEGORY_ID"/>
>                 </join-table>
>             </one-to-many>
>             <one-to-many name="childProducts" target-entity="Product">
>                 <join-table name="DCS_CAT_CHLDPRD">
>                     <join-column name="CATEGORY_ID"
> referenced-column-name="CATEGORY_ID"/>
>                     <inverse-join-column name="CHILD_PRD_ID"
> referenced-column-name="PRODUCT_ID"/>
>                 </join-table>
>             </one-to-many>
>             <one-to-one name="categoryES" target-entity="CategoryES">
>                 <primary-key-join-column name="CATEGORY_ID"
> referenced-column-name="CATEGORY_ID"/>
>             </one-to-one>
>         </attributes>
>     </entity>
> --------------------
> 
> 
> 
> 


      


Mime
View raw message