I tried to recreate this configuration using EclipseLink (with JPA 2.1), and it seems to work fine. I also translated it to JPQL (with some minor change because of the order_customerJoin.creditCards.cardType), and it also works fine as a JPQL query.
What have I missed?
Here is my code:
Code: Select all
public void testVisaCustomerOrderQuery() {
CriteriaBuilder cb = em.getCriteriaBuilder();
String cardType = "VISA";
{
CriteriaQuery<CustomerOrder> cq = cb.createQuery(CustomerOrder.class);
Root custOrderRoot = cq.from(CustomerOrder.class);
Join<CustomerOrder, Customer> order_customerJoin = custOrderRoot.join("customer");
Predicate p = cb.equal(order_customerJoin.get("creditCards").get("cardType"), cardType);
cq.select(custOrderRoot).where(p);
TypedQuery<CustomerOrder> tq = em.createQuery(cq);
List<CustomerOrder> retVal = tq.getResultList();
for (CustomerOrder co : retVal) {
System.out.println(co);
}
}
{
TypedQuery<CustomerOrder> q = em.createQuery(""
+ "SELECT custOrderRoot "
+ "FROM CustomerOrder custOrderRoot "
+ "JOIN custOrderRoot.customer order_customerJoin "
+ "JOIN order_customerJoin.creditCards creditCards "
+ "WHERE creditCards.cardType = '"+cardType+"'",
CustomerOrder.class);
List<CustomerOrder> retVal = q.getResultList();
for (CustomerOrder co : retVal) {
System.out.println(co);
}
}
}
Code: Select all
[EL Fine]: sql: 2017-02-14 12:17:51.836--ServerSession(2052256418)--Connection(1822651119)--Thread(Thread[main,5,main])--SELECT t1.ID, t1.DISCOUNT, t1.CUSTOMER_ID FROM testdb.CUSTOMER t0, testdb.CREDITCARD t2, testdb.CUSTOMERORDER t1 WHERE ((t2.CARDTYPE = ?) AND ((t0.ID = t1.CUSTOMER_ID) AND (t2.CUSTOMER_ID = t0.ID)))
bind => [VISA]
[EL Fine]: sql: 2017-02-14 12:17:51.848--ServerSession(2052256418)--Connection(1822651119)--Thread(Thread[main,5,main])--SELECT ID FROM testdb.CUSTOMER WHERE (ID = ?)
bind => [1]
(id=1,discount=25.0)
(id=4,discount=50.0)
[EL Fine]: sql: 2017-02-14 12:17:52.036--ServerSession(2052256418)--Connection(1822651119)--Thread(Thread[main,5,main])--SELECT t1.ID, t1.DISCOUNT, t1.CUSTOMER_ID FROM testdb.CUSTOMER t0, testdb.CREDITCARD t2, testdb.CUSTOMERORDER t1 WHERE ((t2.CARDTYPE = ?) AND ((t0.ID = t1.CUSTOMER_ID) AND (t2.CUSTOMER_ID = t0.ID)))
bind => [VISA]
(id=1,discount=25.0)
(id=4,discount=50.0)