Spring JPA repository casting error when using JPQL

Question

I have a PagingAndSorting JPA repository declared. I am using the @Query annotation.

I am getting an exception when I call the get() method on an Optional object from the findById(id) method of the repository.

The weird thing is it only happens when I use JPQL.

The code works if my query is native:

    @Override
public BatchDto findById(String id) {
    Optional<Batch> findResult =  this.batchRepository.findById(id);
    if (!findResult.isPresent()) return null;
    Batch entity = findResult.get();  **<-------- Cast Exception Here**
    BatchDto dto = this.mapper.toDto(entity, BatchDto.class);
    List<BatchTransaction> transactions = entity.getTransactions();
    dto.setTransactionDtos(mapper.toListDto(transactions, TransactionDto.class));
    return dto;
}

Inspecting the findResult object with a breakpoint - I can see:

Optional[net.domain.data.batch@4b8bb6f]

when I have nativeQuery = true in the @Query annotation.

@Query(value = Sql.FindBatchById, nativeQuery = true)

Here is the query being used:

SELECT DISTINCT(B.batchNumber), COUNT(B.batchNumber) as TransactionCount FROM BATCH B WHERE B.batchReferenceNumber = :id GROUP BY B.batchNumber

However if I change it to JPQL and remove the nativeQuery=true attribute - the findResult is

Optional[[Ljava.lang.Object;@76e04327].

and I get a ClassCastException:

java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to net.domain.data.batch

So bottom line - this works when specify nativeQuery=true and fails when I try to use JPQL.

I would prefer not to specify nativeQuery as we will eventually port this db to Oracle.

What's the content of the method this.batchRepository.findById(id);? You not show the JPQL. — Dherik
The query returns two columns, and not an instance of Batch. So you get back a array of objects. That's expected. Why do you think it should return an instance of Batch? It would only do that if your query was select b from Batch b where .... — JB Nizet
So why does casting this using natvieSql work? It maps the columns from the ResultSet successfully into a Batch object. — JBenn
Because SQL doesn't have the notion of an entity, whereas JPQL does. So, when you explitly choose NOT to select an entity using JPQL, but to select two values, it won't select an entity, but return two values. — JB Nizet

Yuriy Tsarkov Yuriy Tsarkov · Accepted Answer · 2018-07-02T12:53:46

First of all the query shown below doesn't return a single Batch instance. Since there are distinct and count aggregate functions, the query will return a List of aggregates. To be able to read that statistics you can add appropriate method into the batchRepository. Something like this:

@Query("SELECT DISTINCT(B.batchNumber) as dist, COUNT(B.batchNumber) as cnt FROM BATCH B GROUP BY B.batchNumber")
  List<Map<Long, Long>> findStatistics();

and then iterate through the list.

UPD

If the id parameter exactly guarantee that will return a single record, you can change a return type to a Map

@Query("SELECT DISTINCT(B.batchNumber) as dist, COUNT(B.batchNumber) as cnt FROM BATCH B WHERE B.batchReferenceNumber = :id GROUP BY B.batchNumber")
      Map<Long, Long> findStatisticsById(@Param("id") Long id);

Spring JPA repository casting error when using JPQL

1 Answers