> -----Original Message----- > From: Fay Wang [mailto:fyw...@yahoo.com] > Sent: Tuesday, January 26, 2010 10:08 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 > > 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...
I've created < https://issues.apache.org/jira/browse/OPENJPA-1484> for this. > ----- Original Message ---- > From: "KARR, DAVID (ATTCINW)" <dk0...@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:fyw...@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)" <dk0...@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> > > -------------------- > > > > > > > > > > >
