Problem w/ quoted fields in generated SQL
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:
@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:
@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:
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:
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:
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.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.java: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) ...............................................