QueryDSL Could not determine data type for searched case statement

Question

I have a QueryDSL statement which gives me the exception:

Caused by: org.hibernate.QueryException: Could not determine data type for searched case statement [select distinct rollout, count(bTS), sum(case when bTS.status = ?1 then ?2 else ?3 end), sum(case when (bTS.status = ?4) then ?2 else ?3 end), ((sum(case when (bTS.status = ?4) then ?2 else ?3 end) / ?5) * count(bTS)) as percentage
from com.nsn.nitro.project.data.jpa.domain.RolloutAdmin rolloutAdmin, com.nsn.nitro.project.data.jpa.domain.BTS bTS
  inner join rolloutAdmin.rollout as rollout
  inner join rolloutAdmin.admin as admin
where bTS.rollout.id = rollout.id and admin = ?6
group by rollout
order by percentage asc]

I wonder what is that searched case statement...

Here is the query:

query.from(qRolloutAdmin, qBTS);
query.innerJoin(qRolloutAdmin.rollout, qRollout);
query.innerJoin(qRolloutAdmin.admin, qAdmin);
BooleanBuilder builder = new BooleanBuilder();
builder.and(qBTS.rollout.id.eq(qRollout.id));
builder.and(qAdmin.eq(admin));
query.where(builder);
NumberExpression<Integer> statusPlanned = qBTS.status.when(com.nsn.nitro.project.data.utils.BTSStatus.PLANNED).then(new Integer(1)).otherwise(new Integer(0));
NumberExpression<Integer> statusCompleted = new CaseBuilder().when(qBTS.status.eq(com.nsn.nitro.project.data.utils.BTSStatus.COMPLETED)).then(new Integer(1)).otherwise(new Integer(0));
NumberExpression<Integer> btsNbPlanned = statusPlanned.sum();
NumberExpression<Integer> btsNbCompleted = statusCompleted.sum();
NumberExpression<Integer> btsPercentage = statusCompleted.sum().divide(new Integer(100)).multiply(qBTS.count());
ComparablePath<Integer> percentage = Expressions.comparablePath(Integer.class, "percentage");
NumberExpression<Integer> percentage = btsPercentage.as("percentage").as(percentage);
query.groupBy(qRollout);
query.orderBy(percentage.asc());
QRolloutMeta qRolloutMeta = new QRolloutMeta(qRollout, qBTS.count(), btsNbPlanned, btsNbCompleted, btsPercentage);
List<RolloutMeta> resultList = query.distinct().list(qRolloutMeta);

EDIT: The working statement following Nick's solution:

@Override
@Transactional(readOnly = true)
public Page<RolloutMeta> findMetaByAdmin(Admin admin, Pageable page) {
    JPAQuery query = new JPAQuery(rolloutRepository.getEntityManager());
    QRolloutAdmin qRolloutAdmin = QRolloutAdmin.rolloutAdmin;
    QRollout qRollout = QRollout.rollout;
    QAdmin qAdmin = QAdmin.admin;
    QBTS qBTS = QBTS.bTS;
    query.from(qRolloutAdmin, qBTS);
    query.innerJoin(qRolloutAdmin.rollout, qRollout);
    query.innerJoin(qRolloutAdmin.admin, qAdmin);
    BooleanBuilder builder = new BooleanBuilder();
    builder.and(qBTS.rollout.id.eq(qRollout.id));
    builder.and(qAdmin.eq(admin));
    query.where(builder);
    NumberExpression<Integer> statusPlanned = qBTS.status.when(com.nsn.nitro.project.data.utils.BTSStatus.PLANNED).then(NumberTemplate.ONE).otherwise(NumberTemplate.ZERO);
    NumberExpression<Integer> statusCompleted = new CaseBuilder().when(qBTS.status.eq(com.nsn.nitro.project.data.utils.BTSStatus.COMPLETED)).then(NumberTemplate.ONE).otherwise(NumberTemplate.ZERO);
    NumberExpression<Integer> btsNbPlanned = statusPlanned.sum();
    NumberExpression<Integer> btsNbCompleted = statusCompleted.sum();
    ComparablePath<Integer> percentage = Expressions.comparablePath(Integer.class, "percentage");
    NumberExpression<Integer> btsPercentage = statusCompleted.sum().divide(NumberTemplate.create(Integer.class, "100")).multiply(qBTS.count()).as(percentage);
    query.groupBy(qRollout);
    query.orderBy(percentage.asc());
    QRolloutMeta qRolloutMeta = new QRolloutMeta(qRollout, qBTS.count(), btsNbPlanned, btsNbCompleted, btsPercentage);
    List<RolloutMeta> resultList = query.distinct().list(qRolloutMeta);
    long total = resultList.size();        
    query.offset(page.getOffset());
    query.limit(page.getPageSize());
    resultList = query.list(qRolloutMeta);
    Page<RolloutMeta> rolloutMetas = new PageImpl<RolloutMeta>(resultList, page, total);
    return rolloutMetas;
}

I won't need my native query any longer:

@Override
@Transactional
public Page<RolloutMeta> findMetaByAdmin(Admin admin, Pageable page) {
    Query query = rolloutRepository.getEntityManager().createNativeQuery("SELECT r.id, r.country_id, r.operator_id, r.name, r.description, r.creation_datetime, r.start_datetime, r.end_datetime, count(b.id) as btsNbAll, ifnull(sum(b.status = :statusPlanned), 0) as btsNbPlanned, ifnull(sum(b.status = :statusCompleted), 0) as btsNbCompleted, ifnull(sum(b.status = :statusCompleted), 0) * 100.0 / count(b.id) as btsPercentage FROM rollout r, rollout_admin ra, bts b WHERE b.rollout_id = r.id AND r.id = ra.rollout_id AND ra.admin_id = :adminId GROUP BY r.id ORDER BY btsPercentage ASC");
    query.setParameter("statusPlanned", BTSStatus.PLANNED.getStatus());
    query.setParameter("statusCompleted", BTSStatus.COMPLETED.getStatus());
    query.setParameter("adminId", admin.getId());
    List<RolloutMeta> resultList = new ArrayList<RolloutMeta>();
    List<Object[]> objectList = query.getResultList();
    long total = objectList.size();
    query.setMaxResults(page.getPageSize());
    query.setFirstResult(page.getOffset());
    objectList = query.getResultList();
    for (Object[] object : objectList) {
        Rollout rollout = new Rollout();
        rollout.setId(((BigInteger) object[0]).longValue());
        Long countryId = ((BigInteger) object[1]).longValue();
        Country country = countryRepository.findOne(countryId);
        if (country != null) {
            rollout.setCountry(country);
        }
        Long operatorId = ((BigInteger) object[2]).longValue();
        Operator operator = operatorRepository.findOne(operatorId);
        if (operator != null) {
            rollout.setOperator(operator);
        }
        rollout.setName((String) object[3]);
        rollout.setDescription((String) object[4]);
        rollout.setCreationDatetime(new DateTime((Timestamp) object[5]));
        rollout.setStartDatetime(new DateTime((Timestamp) object[6]));
        rollout.setEndDatetime(new DateTime((Timestamp) object[7]));
        Long btsNbAll = ((BigInteger) object[8]).longValue();
        Integer btsNbPlanned = ((BigDecimal) object[9]).intValue();
        Integer btsNbCompleted = ((BigDecimal) object[10]).intValue();
        Integer btsPercentage = ((BigDecimal) object[11]).intValue();
        resultList.add(new RolloutMeta(rollout, btsNbAll, btsNbPlanned, btsNbCompleted, btsPercentage));
    }
    return new PageImpl<RolloutMeta>(resultList, page, total);
}

Answer 1

I guess you've worked around this by now, but I just came across the same problem. It seems to be related to https://hibernate.atlassian.net/browse/HHH-9343 - Hibernate does something funny with the .when(...).then(...) literal parameters, then can't determine the data type.

Anyway, I found a workaround - changing the Integer literals to Number Expressions worked for me e.g.

NumberExpression<Integer> statusPlanned = qBTS.status.when(com.nsn.nitro.project.data.utils.BTSStatus.PLANNED).then(NumberTemplate.ONE).otherwise(NumberTemplate.ZERO);