首先:我正在使用Spring-Boot Data JPAJava 8。我的數(shù)據(jù)庫(kù)中有一個(gè)表,我的實(shí)體定義如下:@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...}現(xiàn)在我得到以下選擇返回這個(gè)結(jié)構(gòu):-----------------------| 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. 對(duì)于結(jié)果,我聽說我應(yīng)該ItemHoursEntity通過使用投影來實(shí)現(xiàn)獲取與默認(rèn)不同的結(jié)果,所以我嘗試創(chuàng)建以下內(nèi)容:@Projection(types = { ItemHoursEntity.class })public interface TotalValuesProjection { public String getYear(); public int getValue(); public HourTypeEnum getType();}現(xiàn)在在我的service班級(jí)中,我嘗試以下方法:@Overridepublic 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;}
2 回答

開心每一天1111
TA貢獻(xiàn)1836條經(jīng)驗(yàn) 獲得超13個(gè)贊
試試這個(gè)代碼,
@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);
如有問題請(qǐng)告知??!

catspeake
TA貢獻(xiàn)1111條經(jīng)驗(yàn) 獲得超0個(gè)贊
這不是一個(gè)可靠的解決方案,而只是大聲思考。它主要取決于 DBMS 和 Hibernate 版本,但在某些情況下,由于區(qū)分大小寫,可能會(huì)發(fā)生這種異常,因此請(qǐng)嘗試在本機(jī)查詢中為別名添加引號(hào),為列名添加表別名,如下所示:
@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根據(jù) Java 約定更改hourValue
屬性 - 'h' 必須為大寫
添加回答
舉報(bào)
0/150
提交
取消