1 Reply Latest reply: Mar 27, 2012 7:49 AM by JamesSutherland RSS

    Can anyexpert pls tell me eqivalent JPA2 Criteria query fo following

    926141
      select * FROM ( select * from customer_info where fname like '%sap%' ) XYZ where lname like '%ln%'

      following sample query


      but how do i add my subquery in criteriaQuery.from instead of customerinfo.class

      thank you very much
      jhon

      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
                     CriteriaQuery<CustomerInfo> criteriaQuery = criteriaBuilder.createQuery(CustomerInfo.class);
                     
                     //========
                     
                     Subquery<CustomerInfo> subquery = criteriaQuery.subquery(CustomerInfo.class);
                Root fromSimpleBean = subquery.from(CustomerInfo.class);
                subquery.select(fromSimpleBean);
                     subquery.where(criteriaBuilder.equal(fromSimpleBean.get("fname"),"jon"));
                     
                     //=========
                     Root<CustomerInfo> from = criteriaQuery.from(CustomerInfo.class);
                     CriteriaQuery<CustomerInfo> select = criteriaQuery.select(from);
                     criteriaQuery.where(criteriaBuilder.equal(from.get("lname"),"ffff"));
                
                     TypedQuery<CustomerInfo> typedQuery = entityManager.createQuery(select);
                
                     List<CustomerInfo> resultList = typedQuery.getResultList();
                     System.out.println("resultList="+resultList);