I asked this question more than two weeks ago in the EJB 3.0 user forum for JBoss but haven't gotten a reply yet. I hope this isn't considered a "double post" but I'm getting rather desparate and thought maybe this was a more appropriate forum for this question?
I'm using a Progress 10.1 database that requires all queries to have quoted field names, e.g. select "name", "age" from customer.
In my entities, I'm using the back-tick (`) character to make sure field names are quoted. It works fine until I have parent-child relationship in a @OneToMany (bi-directional) setup.
Here are the entities:
Code:
@Entity
@Table(name="pub.customer")
public class Customer implements Serializable
{
@Id
@Column(name="`Cust-no`", nullable=false)
private String custno;
@OneToMany(mappedBy="customer")
private List<CustomerOrder> orders;
...................
}
@Entity
@Table(name="pub.order")
public class CustomerOrder implements Serializable
{
@EmbeddedId
private CustomerOrderPK customerOrderPK;
@Column(name="`Cust-no`", nullable=false, insertable=false, updatable=false)
private String custno;
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="`Cust-no`")
private Customer customer;
.................
}
...and here's the PK class from the CustomerOrder entity, just for the sake of being thorough:
Code:
@Embeddable
public class CustomerOrderPK implements Serializable
{
@Column(name="`Ar-entity`", nullable=false)
private String arentity;
@Column(name="`Order-no`")
private Integer orderno;
.......................
}
I query it in a SLSB like so:
Code:
public Customer getTestCustomer(String custno)
{
String query = "select c from Customer c " +
"left join fetch c.orders " +
"where c.custno = :custno";
Query q = this.em.createQuery(query);
q.setParameter("custno", custno);
return (Customer)q.getSingleResult();
}
Here is the query that is generated at runtime:
Code:
Hibernate:
/* select
c
from
Customer c
left join
fetch c.orders
where
c.custno = :custno */ select
customer0_."Cust-no" as Cust1_160_0_,
orders1_."Ar-entity" as Ar1_161_1_,
orders1_."Order-no" as Order2_161_1_,
customer0_."Name" as Name2_160_0_,
customer0_."Filler1" as Filler3_160_0_,
customer0_."City" as City4_160_0_,
customer0_."St" as St5_160_0_,
customer0_."Zip-code" as Zip6_160_0_,
customer0_."Country" as Country7_160_0_,
customer0_."Telephone" as Telephone8_160_0_,
customer0_."Contact" as Contact9_160_0_,
customer0_."Status-code" as Status10_160_0_,
customer0_."Slsmn-code" as Slsmn11_160_0_,
customer0_."Type-code" as Type12_160_0_,
customer0_."Via-code" as Via13_160_0_,
customer0_."Terr-code" as Terr14_160_0_,
customer0_."Sort-name" as Sort15_160_0_,
customer0_."Priority" as Priority16_160_0_,
customer0_."Comm-code" as Comm17_160_0_,
customer0_."Description" as Descrip18_160_0_,
customer0_."Dest-loc" as Dest19_160_0_,
customer0_."Country-code" as Country20_160_0_,
customer0_."Address" as Address21_160_0_,
customer0_."Partner" as Partner22_160_0_,
customer0_."Residential" as Residen23_160_0_,
customer0_."Gst-code" as Gst24_160_0_,
customer0_."Cust-number" as Cust25_160_0_,
customer0_."sls-contact" as sls26_160_0_,
customer0_."hold-order" as hold27_160_0_,
customer0_."supl-code" as supl28_160_0_,
orders1_."In-entity" as In3_161_1_,
orders1_."Whs-code" as Whs4_161_1_,
orders1_."Cust-no" as Cust5_161_1_,
orders1_."Ship-no" as Ship6_161_1_,
orders1_."Cust-po" as Cust7_161_1_,
orders1_."Order-date" as Order8_161_1_,
orders1_."Order-code" as Order9_161_1_,
orders1_."Slsmn-code" as Slsmn10_161_1_,
orders1_."Via-desc" as Via11_161_1_,
orders1_."Order-disc" as Order12_161_1_,
orders1_."Ship-name" as Ship13_161_1_,
orders1_."Ship-city" as Ship14_161_1_,
orders1_."Ship-st" as Ship15_161_1_,
orders1_."Ship-country" as Ship16_161_1_,
orders1_."Ship-zip" as Ship17_161_1_,
orders1_."Pps-no" as Pps18_161_1_,
orders1_."Invoice-no" as Invoice19_161_1_,
orders1_."Invoice-date" as Invoice20_161_1_,
orders1_."Qty-orig-ord" as Qty21_161_1_,
orders1_."Qty-shipped" as Qty22_161_1_,
orders1_."Qty-open-ord" as Qty23_161_1_,
orders1_."Alloc-qty" as Alloc24_161_1_,
orders1_."Alloc-value" as Alloc25_161_1_,
orders1_."Seq-no" as Seq26_161_1_,
orders1_."Partial-inv" as Partial27_161_1_,
orders1_."Estimate-no" as Estimate28_161_1_,
orders1_."Total-weight" as Total29_161_1_,
orders1_."Sent-by" as Sent30_161_1_,
orders1_."Sent-order" as Sent31_161_1_,
orders1_."Sent-entity" as Sent32_161_1_,
orders1_."Transfer-to" as Transfer33_161_1_,
orders1_."Via-code" as Via34_161_1_,
orders1_."Country-code" as Country35_161_1_,
orders1_."Dest-loc" as Dest36_161_1_,
orders1_."Drop-ship" as Drop37_161_1_,
orders1_."Site-code" as Site38_161_1_,
orders1_."Ship-address" as Ship39_161_1_,
orders1_."Trans-type" as Trans40_161_1_,
orders1_."Trans-mode" as Trans41_161_1_,
orders1_."Ship-site" as Ship42_161_1_,
orders1_."Order-site" as Order43_161_1_,
orders1_."ship-date" as ship44_161_1_,
orders1_."address-no" as address45_161_1_,
orders1_."bev-country" as bev46_161_1_,
orders1_."Route-no" as Route47_161_1_,
orders1_.Cust-no as Cust48_0__, --PROBLEM!!
orders1_."Ar-entity" as Ar1_0__,
orders1_."Order-no" as Order2_0__
from
pub.customer customer0_
left outer join
pub.order orders1_
on customer0_."Cust-no"=orders1_.Cust-no --PROBLEM!!
where
customer0_."Cust-no"=?
As you can see, the field names are quoted but the right-hand side of the join is incorrect...the join field is not quoted, resulting in this exception:
Code:
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: cou
ld not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManag
erImpl.java:647)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:99)
at com.myapp.CustomerBean.getTestCustomer(CustomerBean.java:61)
...............................
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.j
ava:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:80)
... 71 more
Caused by: java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Column "ORDERS1_.CUST" cannot be found or is not specified for query. (13865)
at com.ddtek.jdbc.openedge.client.OpenEdgeClientRequest.prepareProcessReply(Unknown Source)
at com.ddtek.jdbc.openedge.client.OpenEdgeClientRequest.prepare(Unknown Source)
at com.ddtek.jdbc.openedge.OpenEdgeImplStatement.prepare(Unknown Source)
at com.ddtek.jdbc.base.BaseImplStatement.prepare(Unknown Source)
...............................................
Hibernate version: 3.2.0
EDIT - 12/11/2006
I've continued to test this for the past few days...I cannot get a @OneToMany, @OneToOne, or @ManyToMany to work correctly if the table's fields require quoting.
Should I file a bug report? Is this already a known bug? I would be happy to provide a small example project to demonstrate the problem.
I'm sorry to bump but I'm under tremendous pressure to finish a project and I'd rather not have to use Toplink in the place of Hibernate...but it does work correctly in Toplink.
Login
FAQ
Search
