About Question enthuware.oce-jpad.v6.2.479 :

Moderator: admin

Post Reply
kemosabe
Posts: 9
Joined: Fri Oct 09, 2015 3:52 am
Contact:

About Question enthuware.oce-jpad.v6.2.479 :

Post by kemosabe » Tue Feb 14, 2017 6:33 am

I am confused about option 3 being invalid. You said this was wrong because "order_customerJoin.get("creditCards") will return a Collection". I didn't know this was not allowed.

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);
			}
		}
	}
In the log output, both queries are translated to the same SQL (as do the other correct answers):

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)

admin
Site Admin
Posts: 7186
Joined: Fri Sep 10, 2010 9:26 pm
Contact:

Re: About Question enthuware.oce-jpad.v6.2.479 :

Post by admin » Tue Feb 14, 2017 11:55 pm

As per section 4.4.4 (pg 140) of JPA 2.0 specification, "It is syntactically illegal to compose a path expression from a path expression that evaluates to a collection. For example, if o designates Order, the path expression o.lineItems.product is illegal since navigation to lineItems results in a collection."

I will dig more into this to see how it applies to CriteriaBuilder queries. But sometimes JPA implementations do not strictly follow the specification so if some query works on a particular implementation that doesn't necessarily mean that is legally valid.

HTH,
Paul.

kemosabe
Posts: 9
Joined: Fri Oct 09, 2015 3:52 am
Contact:

Re: About Question enthuware.oce-jpad.v6.2.479 :

Post by kemosabe » Sun Feb 19, 2017 4:01 pm

Thanks Paul for the spec reference and the clarification.

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests