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.
Fay
----- 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>
--------------------
