Implicitly casting bigint postgres datatytpe to Java Long

Question

I am using a native Query to obtain a list of bigint numbers which I then plan to iterate.

@Query(value="SELECT bigint_field FROM complex_innerquery", nativeQuery=true)
Collection<Long> getAllBigIntFieldValues();

If I use the Long datatype as shown above, it throws the following error

java.math.BigInteger cannot be cast to java.lang.Long

It seems like JPA converts bigint from database into BigInteger by default.

Clearly, BigInteger is a giant datatype compared to Long. Although, while storing, my field is of type Long in the entity defined and therefore, there is no chance of losing data while doing the conversion from BigInteger back to Long value, is there any other way by which I can get rid of BigInteger datatype? More specifically, I don't want the methods calling getAllBigIntFieldValues method to explicitly convert BigInteger to LongValue.

Answer 1

You can't do this with Spring Data JPA mechanics.

The relevant part of the source code is JpaQueryExecution

It has a ConversionService and tries to use it to convert the query result to the required type:

if (void.class.equals(requiredType) || requiredType.isAssignableFrom(result.getClass())) {
    return result;
}

return CONVERSION_SERVICE.canConvert(result.getClass(), requiredType) //
        ? CONVERSION_SERVICE.convert(result, requiredType) //
        : result;

Unfortunately, there is no mechanism to add converters to this conversion service.

What you could do though is to use the power of SQL and convert the value in the select into something that will be offered as a Long by the underlying JDBC driver.

For example it should be possible in MySQL to convert the result to an UNSIGNED BIGINT and get a Long

Just noted you mentioned Postgres in the title. BIGSERIAL might be an option there.