JPA 2, join criteria query without entity mapping

Question

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 ?

exactly. Customer and Order are tied with order.customerId, and I want to add on predicate the Order entity — Fabrizio Stellato, Jul 10 '20 at 09:51

Kavithakaran Kanapathippillai · Accepted Answer · 2020-07-10T10:00:57.450

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();

JPA 2, join criteria query without entity mapping

1 Answers1