Spring JPA - "java.lang.IllegalArgumentException: Projection type must be an interface!" (using native query)

Question

I'm trying to retrieve a timestamp date from an oracle database but the code is throwing:

java.lang.IllegalArgumentException: Projection type must be an interface!

I'm trying to use native query because the original query is way to complex to use Spring JPA methods or JPQL.

My code is similar to this one below (Sorry, can't paste the original one due to company policy).

Entity:

@Getter
@Setter
@Entity(name = "USER")
public class User {

    @Column(name = "USER_ID")
    private Long userId;

    @Column(name = "USER_NAME")
    private String userName;

    @Column(name = "CREATED_DATE")
    private ZonedDateTime createdDate;
}

Projection:

public interface UserProjection {

    String getUserName();

    ZonedDateTime getCreatedDate();
}

Repository:

@Repository
public interface UserRepository extends CrudRepository<User, Long> {

    @Query(
            value = "   select userName as userName," +
                    "          createdDate as createdDate" +
                    "   from user as u " +
                    "   where u.userName = :name",
            nativeQuery = true
    )
    Optional<UserProjection> findUserByName(@Param("name") String name);
}

I'm using Spring Boot 2.1.3 and Hibernate 5.3.7.

Answer 1

I had this same issue with a very similar projection:

public interface RunSummary {

    String getName();
    ZonedDateTime getDate();
    Long getVolume();

}

I do not know why, but the issue is with ZonedDateTime. I switched the type of getDate() to java.util.Date, and the exception went away. Outside of the transaction, I transformed the Date back to ZonedDateTime and my downstream code was not affected.

I have no idea why this is an issue; if I don't use projection, the ZonedDateTime works out-of-the-box. I'm posting this as an answer in the meantime because it should be able to serve as a workaround.

---

According to this bug on the Spring-Data-Commons project, this was a regression caused by adding support for optional fields in the projection. (Clearly it's not actually caused by that other fix -- since that other fix was added in 2020 and this question/answer long predates it.) Regardless, it has been marked as resolved in Spring-Boot 2.4.3.

Basically, you couldn't use any of the Java 8 time classes in your projection, only the older Date-based classes. The workaround I posted above will get around the issue in Spring-Boot versions before 2.4.3.

Answer 2

When you call method from projection interface spring takes the value it received from the database and converts it to the type that method returns. This is done with the following code:

if (type.isCollectionLike() && !ClassUtils.isPrimitiveArray(rawType)) { //if1
    return projectCollectionElements(asCollection(result), type);
} else if (type.isMap()) { //if2
    return projectMapValues((Map<?, ?>) result, type);
} else if (conversionRequiredAndPossible(result, rawType)) { //if3
    return conversionService.convert(result, rawType);
} else { //else
    return getProjection(result, rawType);
}

In the case of getCreatedDate method you want to get java.time.ZonedDateTime from java.sql.Timestamp. And since ZonedDateTime is not a collection or an array (if1), not a map (if2) and spring does not have a registered converter (if3) from Timestamp to ZonedDateTime, it assumes that this field is another nested projection (else), then this is not the case and you get an exception.

There are two solutions:

1. Return Timestamp and then manually convert to ZonedDateTime
2. Create and register converter

public class TimestampToZonedDateTimeConverter implements Converter<Timestamp, ZonedDateTime> {
    @Override
    public ZonedDateTime convert(Timestamp timestamp) {
        return ZonedDateTime.now(); //write your algorithm
    }
}

@Configuration
public class ConverterConfig {
    @EventListener(ApplicationReadyEvent.class)
    public void config() {
        DefaultConversionService conversionService = (DefaultConversionService) DefaultConversionService.getSharedInstance();
        conversionService.addConverter(new TimestampToZonedDateTimeConverter());
    }
}

Spring Boot 2.4.0 update:

Since version 2.4.0 spring creates a new DefaultConversionService object instead of getting it via getSharedInstance and I don't know proper way to get it other than using reflection:

@Configuration
public class ConverterConfig implements WebMvcConfigurer {
    @PostConstruct
    public void config() throws NoSuchFieldException, ClassNotFoundException, IllegalAccessException {
        Class<?> aClass = Class.forName("org.springframework.data.projection.ProxyProjectionFactory");
        Field field = aClass.getDeclaredField("CONVERSION_SERVICE");
        field.setAccessible(true);
        GenericConversionService service = (GenericConversionService) field.get(null);

        service.addConverter(new TimestampToZonedDateTimeConverter());
    }
}

Answer 3

A new attribute converter can be created to map column type to desired attribute type.

@Component
public class OffsetDateTimeTypeConverter implements 
              AttributeConverter<OffsetDateTime, Timestamp> {

    @Override
    public Timestamp convertToDatabaseColumn(OffsetDateTime attribute) {
       //your implementation
    }

    @Override
    public OffsetDateTime convertToEntityAttribute(Timestamp dbData) {
       return dbData == null ? null : dbData.toInstant().atOffset(ZoneOffset.UTC);
    }

}

In the projection, it can be used like below. This is an explicit way of calling the converter. I couldn't find how to register it automatically so that you don't need to add @Value annotation every time you need.

@Value("#{@offsetDateTimeTypeConverter.convertToEntityAttribute(target.yourattributename)}")
OffsetDateTime getYourAttributeName();

Answer 4

I had the same issue with Spring Boot v2.4.2
I wrote this ugly hack that fixed it for me:

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;

import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.event.EventListener;
import org.springframework.core.convert.support.DefaultConversionService;
import org.springframework.data.convert.Jsr310Converters;
import org.springframework.data.util.NullableWrapperConverters;

@Configuration
public class JpaConvertersConfig {

    @EventListener(ApplicationReadyEvent.class)
    public void config() throws Exception {
        Class<?> aClass = Class.forName("org.springframework.data.projection.ProxyProjectionFactory");
        Field field = aClass.getDeclaredField("CONVERSION_SERVICE");
        field.setAccessible(true);
        Field modifiers = Field.class.getDeclaredField("modifiers");
        modifiers.setAccessible(true);
        modifiers.setInt(field, field.getModifiers() & ~Modifier.FINAL);
        DefaultConversionService sharedInstance = ((DefaultConversionService) DefaultConversionService.getSharedInstance());
        field.set(null, sharedInstance);
        Jsr310Converters.getConvertersToRegister().forEach(sharedInstance::addConverter);
        NullableWrapperConverters.registerConvertersIn(sharedInstance);
    }
}

Answer 5

You have declared userId field as Long in the entity but in UserProjection getUserId method return type is String. which is mismatching so Change

String getUserId();

to

Long getUserId();

Answer 6

I never got interface to work and not sure if it is supported with ZonedDateTime though no reason for not supporting occurs in my mind.

For that I created a class that I use with projection (of course this could implement that interface but for the sake of simplicity I left it out).

@Getter
@Setter
@AllArgsConstructor
public class UserProjection {
    private String userName;
    private ZonedDateTime createdDate;
}

This requires JPQL because using NEW operator in the query, so like:

@Query(value = " SELECT NEW org.example.UserProjection(U.userName, U.createdDate) "
        + " FROM USER U " // note that the entity name is "USER" in CAPS
        + " WHERE U.userName = :name ")

Answer 7

The problem that is spring data jpa cannot convert some types from database into java types. I had almost the same issue when tried to get boolean as result and database returns number.

Look at more at: https://github.com/spring-projects/spring-data-commons/issues/2223 https://github.com/spring-projects/spring-data-commons/issues/2290