JPA 2, join criteria query without entity mapping
I have the following tables:
-
customers
-
orders
@Entity public class Customer { String id; } @Entity public class Order { String id; String customerId; }
I have no use to establish an entity’s mapping between those; however I need a query to that should join these two tables:
final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); final CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class); final Root<Customer> root = criteriaQuery.from(Customer.class); final Join<Order, Customer> joinOrder = root.join(Order_.customerId.getName()); // doesn't work final TypedQuery<Customer> queryData = entityManager.createQuery( criteriaQuery .where( criteriaBuilder.lessThan(root.get(Customer_.creationDate), date) // should add a predicate with order properties ) ); return queryData.getResultList(); Is it possible to do something like above with JPA 2 ?
- You can use subquery
CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Customer> customerQuery = cb.createQuery(Customer.class); Root<Customer> customerRoot = customerQuery.from(Customer.class); Subquery<Order> subQuery = customerQuery.subquery(Order.class); Root<Order> orderRoot = subQuery.from(Order.class); //Replace this with the restriction you want to apply to order Predicate predicate= orderRoot.get("xxxxx").in(xxx, xxx); subQuery.select(orderRoot.get("customerId")).where(predicate); customerQuery.select(customerRoot).where(customerRoot.get("id").in(subQuery)); em.createQuery(issueQuery).getResultList(); 
