JPA CriteriaQuery.multiselect()

#1. Issue

Three row of same data had been shown on a screen.

세 개의 똑같은 데이터 목록이 화면에 표출되었다.

#2. Cause

The query was selecting all columns from root type (in this case, LocationBref).

One of those columns had three different values for each row.

According to data, it was not a defect but it seems to be defect as the column was not shown on the screen.

We could solve this issue by showing the column value on the screen or by selecting only one row as the values of the other columns are same.

I had to choose the second way.

생성된 쿼리를 보니 루트 타입으로부터 모든 컬럼을 조회하고 있었다.

그중에 한 컬럼이 각 행당 세개의 다른 값을 가지고 있었다.

데이터에 따르면 오류가 아니지만 화면에 그 컬럼 값을 보여주지 않고 있었기 때문에 이건 오류로 보이기 충분하다.

화면에 해당 컬럼 값을 보여주거나 해당 컬럼을 제외하고 다른 값을은 모두 같으니 세 개 중 하나의 값만 조회해서 화면에 표출해 주는 방법을 사용할 수 있었다.

나는 하나만 조회하는 방법을 사용해야 했다.

#3. Solve

The query has been created with CriteriaQuery which helps us to create a dynamic one.

To select the columns from the root type which I need on the screen, I have added the code line with criteriaQuery.multiselect(...).

And I have got a RuntimeException.

Caused by: java.lang.RuntimeException: Can not find constructor for "class uk.gov.scotland.afrc.customer.model.domain.LocationBrief" with argument types "[class java.lang.String, class java.lang.String, class java.lang.String, class java.lang.String, class java.lang.String, long, class java.lang.String, long, class java.lang.String, boolean, class java.lang.String, class java.lang.String]" to fill data.

Since I added constructor with the only fields that I need, it has been working fine.

* But after compiling, I needed to make sure the fuse server bundles were refreshed by restarting it.

동적 쿼리 생성하기 위해서 CriteriaQuery를 사용하고 있었다.

화면에서 필요한 컬럼만 루트타입에서 조회하기 위해 criteriaQuery.mulselect 함수를 이용했다.

이 다음에 바로 런타임오류 메세지가 생성자 필요하다고 해서 내가 조회하는 컬럼들로 생성자 생성하니 잘 동작했다.

*그런데 Fuse 서버를 재시작 안 해서 그랬는지 어쨌는지, 계속 같은 에러때문에 하루를 버렸으니(하루 종일 재시작도 몇번 했던 것 같은데 ㅜㅜ), 서버가 꼭 컴파일 된 파일을 제대로 읽을 수 있도록 갱신 해주어야했다.

Create query with criteria.

CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<LocationBrief> criteriaQuery = criteriaBuilder.createQuery(LocationBrief.class);

Root<LocationBrief> root = (Root<LocationBrief>) criteriaQuery.from(LocationBrief.class);
CriteriaHelper<VwLocationBrief> criteriaHelper = criteriaHelperFactory.newCriteriaHelper(criteriaBuilder, root);
criteriaHelper.ignoreCaseLike(CONTACT_NAME, locationAdvancedSearchCriteria.getContactName())
        .ignoreCaseEq(CONTACT_EMAIL, locationAdvancedSearchCriteria.getEmailaddress())
        .ignoreCaseLike(LOCATION_NAME2, locationAdvancedSearchCriteria.getLocationName())
        .ignoreCaseLike(UNIT_NAME, locationAdvancedSearchCriteria.getUnitName())
        .ignoreCaseLike(LOCATION_CODE, locationAdvancedSearchCriteria.getLocationCode())
        .ignoreCaseAndSpaceSuffixLike(POSTCODE, locationAdvancedSearchCriteria.getPostcode() != null ? locationAdvancedSearchCriteria.getPostcode().replaceAll(" ", "") : null)
        .orderBy(LOCATION_NAME2, CriteriaHelper.ASCENDING);

//Here are the columns that I want to select
**criteriaQuery.multiselect(
        root.get(ADDRESS_LINE1),
        root.get(ADDRESS_LINE2),
        root.get(ADDRESS_LINE3),
        root.get(ADDRESS_LINE5),
        root.get("businessName"),
        root.get(BRN_PARAM),        
        root.get(LOCATION_CODE),
        root.get("locationId"),
        root.get(LOCATION_NAME),
        root.get("mainLocation"),   
        root.get(POST_TOWN),
        root.get(POSTCODE))
.distinct(true);**

criteriaQuery.where(criteriaHelper.getPredicates());
if(type != null && type.equals("list")) {
    criteriaQuery.orderBy(criteriaHelper.getOrdering());
}

Here is the Constructor.

public LocationBrief(String addressLine1, String addressLine2,
            String addressLine3, String addressLine5, String businessName,
            long brn, String locationCode, long locationId,
            String locationName, boolean mainLocation, String postTown,
            String postcode) {
        this.addressLine1 = addressLine1;
        this.addressLine2 = addressLine2;
        this.addressLine3 = addressLine3;
        this.addressLine5 = addressLine5;
        this.businessName = businessName;
        this.brn = brn;
        this.locationCode = locationCode;
        this.locationId = locationId;
        this.locationName = locationName;
        this.mainLocation = mainLocation;
        this.postTown = postTown;
        this.postcode = postcode;
    }