Bug in Spring Data JPA: Spring Data returns List<BigInteger> instead of List<Long>

Question

I have DAO implementation over spring-data:

public interface TestDataRepository extends CrudRepository<DpConfigData, Long> {
@Query(value = "select distinct(oid) from unit", nativeQuery = true)
    List<Long> testMethod();
}

And unit test to test menioned DAO:

@Test
public void test(){
    List<Long> testData = dpConfigDataEntityDataRepository.testMethod();
    for (Long oid:testData){
        System.out.print(oid);
    }
}

Running test give strange result - List<Long> testData in runtime is populated by BigInteger instances, not by Long. As result I get ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long

JPA implementation - Hibernate. As DB I use PostgreSQL, unit.oid field has BigInt type on DB layer. It is mapped to Long in case of fetching whole unit, but with custom query as "select distinct ..." something went wrong and it is mapped to BigInteger.

So, my question: what is the cause of such strange behaviour? How to solve/workaround it in elegant way?

Answer 1

Finally I worked around this problem by manual mapping on "service" layer. Example(pseudo code):

public interface TestDataRepository extends CrudRepository<DpConfigData, Long> {
        @Query(value = "select distinct(oid) from unit", nativeQuery = true)
            List<Object> testMethod();
        }
}

then in Service Layer I do manual mapping:

public class TestServiceImpl extends TestService {
    pulic List<Object> testMethod(){
        List<Object> rawList = testDataRepository.testMethod();
        List<Object> resultList = new ArrayList(rawList.size());
        for(Object rw:rawList){
            resultList.add(Long.valueOf(String.valueOf(rw)));
        }
        return resultList;
    }
}

Answer 2

BigInt in postgresql maps to BigInteger because its unsigned

I think your best option is to change oid from Long to BigInteger in your JPA object

Answer 3

This is a issue with Spring data JPA. If in DB the datatype is defined as BigInteger and in JPA query we tries to fetch as Long then it will not give any error , but it set value as BigInteger in Long datatype.

Solutions:

1. Use BigInteger as return type

   @Query(value = "select distinct(oid) from unit", nativeQuery = true) List<BigInteger> testMethod();

   then set the variable as below.
   Long variable = bigIntegerValue.longValue();

2. Use String as return Type and convert to Long

   @Query(value = "select distinct(oid) from unit", nativeQuery = true) List<String> testMethod();

   then set the value as

   Long variable = Long.valueOf(stringValue);

3. Change DB column type to Integer/Number.

4. Get the Value from Entity Object.

   Long variable = dpConfigData.getOid();

   where dpConfigData is object of Entity(DpConfigData.class)

Answer 4

You can try this by using JPQL as below:

public interface TestDataRepository extends 
JpaRepository<DpConfigData, Long> {
@Query(value = "select distinct(u.oid) from unit u")
   List<Long> testMethod();
  }

Just make sure that your Entity Object should also have same data type Long for the given attribute.

Answer 5

This issue seems to be resolved in version 2.1.8

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.8.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>