How to receive a set from Spring Data JPA NativeQuery? [results in NotReadablePropertyException]

user_5524954 0
Viewed 641

First off: I am using Spring-Boot Data JPA with Java 8.

I have a table in my database where my entity is defined like this:

@Entity
@Table(name="ITEMHOURS")
public class ItemHoursEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private int id;

    @Column(name = "YEAR", nullable = false)
    private String year;

    @Enumerated(EnumType.STRING)
    @Column(name = "STATE", nullable = false)
    private TypeEnum type;

    @Column(name = "HOURVALUE", nullable = false)
    private int hourValue = 0;

    @ManyToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "ITEM_ID")
    private MyItem item;
//.. some more columns, getters, setters...
}

Now I got the following select returning this structure:

-----------------------
| YEAR | Value | Type |
-----------------------

@Query(value = "Select year as year, sum(hourvalue) as value, state as type from ITEMHOURS h where item_id = :item_id group by h.year, h.state", nativeQuery = true)
    public Optional<Collection<TotalValuesProjection>> getYearTotalsForStateByItem(@Param("item_id") int item_id);

It works fine in my MySQL Workbench. For the result, I heard that I should achieve to grab the result different from the default ItemHoursEntity by using a projection, so I tried to create the following:

@Projection(types = { ItemHoursEntity.class })
public interface TotalValuesProjection {
    public String getYear();
    public int getValue();
    public HourTypeEnum getType();
}

Now in my service-class, I try the following:

@Override
public List<ExtendedDataPasser<String, Integer, String>> getTotalAndYearHourValuesForItem(int itemId) {

    Optional<Collection<TotalValuesProjection>> projectionOptional = this.hoursRepository.getYearTotalsForStateByItem(itemId);
    List<ExtendedDataPasser<String, Integer, String>> entityList = new ArrayList<ExtendedDataPasser<String, Integer, String>>();
    projectionOptional.orElseGet(() -> new ArrayList<TotalValuesProjection>()).forEach(entity -> {
        entityList.add(new ExtendedDataPasser<String, Integer, String>(entity.getYear(), entity.getValue(), entity.getType().toString()));
    });
    return entityList;
}

Sadly, this results in the following error:

org.springframework.beans.NotReadablePropertyException: Invalid property 'year' of bean class [java.util.ArrayList]: Could not find field for property during fallback access!
        at org.springframework.data.util.DirectFieldAccessFallbackBeanWrapper.getPropertyValue(DirectFieldAccessFallbackBeanWrapper.java:58) ~[spring-data-commons-2.1.3.RELEASE.jar!/:2.1.3.RELEASE]
        at org.springframework.data.projection.PropertyAccessingMethodInterceptor.invoke(PropertyAccessingMethodInterceptor.java:73) ~[spring-data-commons-2.1.3.RELEASE.jar!/:2.1.3.RELEASE]
        at org.springframework.data.projection.ProjectingMethodInterceptor.invoke(ProjectingMethodInterceptor.java:64) ~[spring-data-commons-2.1.3.RELEASE.jar!/:2.1.3.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
        at org.springframework.data.projection.ProxyProjectionFactory$TargetAwareMethodInterceptor.invoke(ProxyProjectionFactory.java:245) ~[spring-data-commons-2.1.3.RELEASE.jar!/:2.1.3.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
        at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[spring-data-commons-2.1.3.RELEASE.jar!/:2.1.3.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.1.3.RELEASE.jar!/:5.1.3.RELEASE]
        at com.sun.proxy.$Proxy133.getYear(Unknown Source) ~[na:na]

Maybe the full screenshot of the error helps:

enter image description here

This is the exception when I try to access year-attribute first this is the error: (pretty much the same, just year instead of hourValue)

enter image description here

Did I mess something with the mapping from projection to entity?

spring-data-jpa spring java
2 Answers

Try this code,

@Query(value = "Select year, sum(hourvalue) as value, state as type from ITEMHOURS h where item_id = :item_id group by h.year, h.state", nativeQuery = true)
    public Optional<Collection<TotalValuesProjection>> getYearTotalsForStateByItem(@Param("item_id") int item_id);

if any issue inform!!

user_67577710

It's not a solid solution but just a thinking out loud. It mostly depends on the DBMS and Hibernate versions, but in some cases such an exception may be occured due to case-sencetivity, so try to add quotes to aliases in the native query and table alias to column names as follow: 

@Query(value = "Select h.year as \"year\", sum(h.hourValue) as \"value\", h.state as \"type\" from ITEMHOURS h where h.item_id = :item_id group by h.year, h.state", nativeQuery = true)
    public Optional<Collection<TotalValuesProjection>> getYearTotalsForStateByItem(@Param("item_id") int item_id);

UPD change hourValue property according to a Java convention - 'h' must be in the uppercase

user_72141420
Related