Bug in Spring Data JPA: Spring Data returns List instead of List

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?

Does this need to be a nativeQuery? If no then remove `, nativeQuery = true` and see if this helps. — Robert Niestroj, Jun 23 '15 at 19:31
@RobertNiestroj The code serves only as example. In reality, "select distinct ..." is more complicated, with few "select with joins" and "union" of two data sets. So, I need to use native query. — Oleksandr_DJ, Jun 23 '15 at 19:35
BigInt in postgresql maps to BigInteger because its unsigned - can you change your JPA object? — farrellmr, Jun 24 '15 at 08:48
@farrellmr But oid is mapped to Long correctly in case of getting whole Unit. Why? — Oleksandr_DJ, Jun 24 '15 at 10:09
It must be a different path for a nativequery vs a JPA mapping. An interesting test would be to run "select distinct(oid) from unit where oid = 1" so it returns 1 entry and change the signature to Long testMethod() too see if that worked. Incidentally I had a look if you could change the return type in Query, or define a resultmapping but couldnt see how — farrellmr, Jun 24 '15 at 10:14

score 15 · Answer 1 · edited Mar 29 '18 at 11:30

15

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:

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();
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);
Change DB column type to Integer/Number.
Get the Value from Entity Object.

Long variable = dpConfigData.getOid();

where dpConfigData is object of Entity(DpConfigData.class)

edited Mar 29 '18 at 11:30

SaschaM78

4,376
4
33
42

answered Jan 17 '18 at 10:36

TheSprinter

1,523
17
30

2

In the 2nd approach, the query still results in a BigInteger value. Hence taking 1st approach as the solution. – Mohammed Idris Sep 11 '19 at 11:25
Thanks, In the second approach error - java.math.BigInteger cannot be cast to java.lang.String – Jay Nov 27 '19 at 11:43
Is this behaviour somewhere documented? I'd consider this a bug or at the very least absolutely surprising WTF-behaviour.... I'd like to buy apples, get told I bought apples, but then suddenly hold oranges in my hand... – Frank Hopkins Mar 10 '20 at 16:51
@FrankHopkins this behaviors was not documented anywhere in Spring Docs, not sure now whats there in the doc. I found this bug when I was getting error and then debugging my code. This also found in MYSQL DB – TheSprinter Mar 11 '20 at 09:41
1

For those who imagine "How can Java allows this bug?" https://stackoverflow.com/q/60948445 – Ismail Yavuz Mar 31 '20 at 12:32

score 1 · Answer 2 · answered Jun 24 '15 at 08:49

1

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

answered Jun 24 '15 at 08:49

farrellmr

1,815
2
15
26

score 1 · Accepted Answer · answered Jun 24 '15 at 10:26

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;
    }
}

Interesting approach - another approach would be to do the transform in a custom repository — farrellmr, Jun 25 '15 at 06:56

score 0 · Answer 4 · answered Jun 18 '20 at 21:34

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>

score -1 · Answer 5 · answered Oct 09 '19 at 19:14

-1

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.

answered Oct 09 '19 at 19:14

Amber

1
1

I try this and did not resolve the issue. It is still returning BigDecimal example: List getUrlMappingIdListByUserIdAndRole(@Param("userId") String userId); – Kul Bhushan Prasad Apr 22 '20 at 22:52

Bug in Spring Data JPA: Spring Data returns List instead of List

5 Answers5

Linked