java - Hibernate 对 group by 的查询

Question

如何将此 postgreSql 查询转换为 hibernate 查询:

select sum(amount), cat_id, 
       date_trunc('month', createOn) as month,
       date_trunc('year', createOn) as year 
from item 
where owner_id = 1 
group by cat_id , month, year;

我的 Item 类为:

@Column(name="AMOUNT")
private BigDecimal amount;

@ManyToOne
@JoinColumn(name="OWNER_ID")
private Customer owner;


@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name="CAT_ID")
private Category category;

@Temporal(TemporalType.DATE)
@Column(name="CREATEON")
private Date createOn;

我尝试将其转换为:

StringBuffer hql = new StringBuffer()
        .append("Select sum(amount), category, month(createOn) as mon, year(createOn) as year")
        .append(" from Item")
        .append(" where owner=:owner")
        .append(" group by category, mon, year");

    Query query = getQuery(hql.toString())
                    .setParameter("owner", owner);   

    List<Object[]> resultList = query.list();
    List<BundleHolder> bundleHolderList = new ArrayList<BundleHolder>();

    for(Object[] result : resultList) {
        BundleHolder instance = BundleHolder.getInstance();
        instance.setAmount((BigDecimal) result[0]);
        instance.setCategory((Category) result[1]);
        instance.setMonth((int) result[2]);
        instance.setYear((int) result[3]);

        bundleHolderList.add(instance);
    }

当我运行查询时，出现错误:

ERROR: org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: column "category" does not exist

我尝试将类别切换为cat_id或将所有者切换为owner_id(将参数设置为owner.getId())，但这也没有帮助。

Answer 1

您需要将category更改为category.id:

StringBuffer hql = new StringBuffer()
        .append("Select sum(amount), category.id, month(createOn) as mon, year(createOn) as year")
        .append(" from Item")
        .append(" where owner=:owner")
        .append(" group by category.id, mon, year");