如何从 Spring Data JPA NativeQuery 接收一个集合？

Question

首先：我正在使用Spring-Boot Data JPAJava 8。

我的数据库中有一个表，我的实体定义如下：

@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...

}

现在我得到以下选择返回这个结构：

-----------------------

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

它在我的MySQL Workbench. 对于结果，我听说我应该ItemHoursEntity通过使用投影来实现获取与默认不同的结果，所以我尝试创建以下内容：

@Projection(types = { ItemHoursEntity.class })

public interface TotalValuesProjection {

    public String getYear();

    public int getValue();

    public HourTypeEnum getType();

}

现在在我的service班级中，我尝试以下方法：

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

}

Answer 1

试试这个代码，@Query(value&nbsp;=&nbsp;"Select&nbsp;year,&nbsp;sum(hourvalue)&nbsp;as&nbsp;value,&nbsp;state&nbsp;as&nbsp;type&nbsp;from&nbsp;ITEMHOURS&nbsp;h&nbsp;where&nbsp;item_id&nbsp;=&nbsp;:item_id&nbsp;group&nbsp;by&nbsp;h.year,&nbsp;h.state",&nbsp;nativeQuery&nbsp;=&nbsp;true) &nbsp;&nbsp;&nbsp;&nbsp;public&nbsp;Optional<Collection<TotalValuesProjection>>&nbsp;getYearTotalsForStateByItem(@Param("item_id")&nbsp;int&nbsp;item_id);如有问题请告知！！

Answer 2

这不是一个可靠的解决方案，而只是大声思考。它主要取决于 DBMS 和 Hibernate 版本，但在某些情况下，由于区分大小写，可能会发生这种异常，因此请尝试在本机查询中为别名添加引号，为列名添加表别名，如下所示：@Query(value&nbsp;=&nbsp;"Select&nbsp;h.year&nbsp;as&nbsp;\"year\",&nbsp;sum(h.hourValue)&nbsp;as&nbsp;\"value\",&nbsp;h.state&nbsp;as&nbsp;\"type\"&nbsp;from&nbsp;ITEMHOURS&nbsp;h&nbsp;where&nbsp;h.item_id&nbsp;=&nbsp;:item_id&nbsp;group&nbsp;by&nbsp;h.year,&nbsp;h.state",&nbsp;nativeQuery&nbsp;=&nbsp;true) &nbsp;&nbsp;&nbsp;&nbsp;public&nbsp;Optional<Collection<TotalValuesProjection>>&nbsp;getYearTotalsForStateByItem(@Param("item_id")&nbsp;int&nbsp;item_id);UPD根据 Java 约定更改hourValue属性 - 'h' 必须为大写