[Java] Criteria API Subquery Expression

아래 SQL문을 Java로 구현하려고함

select e 
from sw_version_tb e 
where e.device_component_id = : deviceComponetId and e.device_model_id = : deviceModelId and e.version not in 
                                   (select r.delta_version 
                                    from sw_version_delta_tb r 
                                    where r.device_component_id = : deviceComponentId and r.device_model_id= : deviceModelId and r.sw_version = : swVersion)

1. createNativeQuery를 이용 

String queryStr = "select r from sw_version_tb r where r.device_component_id = : deviceComponentId and r.device_model_id =: deviceModelId and r.version not in "
                + "(select b.delta_version from sw_version_delta_tb b where b.device_component_id= : deviceComponentId and b.device_model_id=deviceModelId and b.sw_version= :swVersion) ";
        Query typedQuery = super.entityManager.createNativeQuery(queryStr);
        typedQuery.setParameter("deviceModelId",deviceModelId);
        typedQuery.setParameter("deviceComponentId",deviceComponentId);
        typedQuery.setParameter("swVersion", swVersion);
        return typedQuery.getResultList();

-> 결과적으로는 안됨. 무엇이 잘못되었는지 모르겠음

2. Criteria 

CriteriaBuilder cb = super.entityManager.getCriteriaBuilder();
CriteriaQuery<SwVersion> c = cb.createQuery(SwVersion.class);
Root<SwVersion> root = c.from(SwVersion.class);    // SW_VERSION_TB
Subquery<String> sq = c.subquery(String.class);
Root<SwVersionDelta> root2 = sq.from(SwVersionDelta.class);    // SW_VERSION_DELTA_TB
sq.select(root2.<String>get("deltaVersion")).where(cb.equal(root2.<String>get("deviceModelId"),  deviceModelId),
        cb.equal(root2.<Integer>get("deviceComponentId"), deviceComponentId),
        cb.equal(root2.<String> get("swVersion"),  swVersion));
        
c.select(root).where((cb.equal(root.<String>get("deviceModelId"),  deviceModelId)),
        cb.equal(root.<Integer>get("deviceComponentId"), deviceComponentId), 
        cb.in(root.get("version")).value(sq).not());
        
TypedQuery<SwVersion> typedQuery = entityManager.createQuery(c);
        
return typedQuery.getResultList();

line 4 ~ line 6 : subquery 내용

line 10 : line 4 ~ line 6에서 subquery 결과를 넣어준다.

line 12 : 여기가 결과적으로 subquery 결과를 넣어주는곳. CriteriaBuilder로 not in을 표현하는 방식임. value(sq)는 즉슨 subquery 결과로 나온 version이 되겠음

reference : https://stackoverflow.com/questions/4483576/jpa-2-0-criteria-api-subqueries-in-expressions/9983123#9983123?newreg=df6a8d4267704dd98e0ef5f6cefad801