spring boot jpa hibernate hql查询的group by验证失败

w8biq8rn  于 2021-07-03  发布在  Java
关注(0)|答案(1)|浏览(477)

在spring boot应用程序中,我有一个elemententity:

....
@Entity
public class ElementEntity
{
    @Id
    int id;
    Double quantity;
    String form;
    String color;
    String type;
    String description;

    @ManyToOne
    @JoinColumn(name = "form")
    private FormEntity form;
}

我在这里有:

@Entity
 public class FormEntity {
       .....
      @OneToMany
      @JoinColumn(name = "links")
      private List<LinksEntity> links;
    }

我想使用hql生成一个查询,将数量字段的值相加,按颜色分组,并返回 [{qty, color, links}] 我做了hql查询:

@Repository
public interface ElementRepository extends JpaRepository ... {

@Query("SELECT sum(quantity) as qty, color, form.links  FROM ElementEntity" +
        "WHERE type= ?1" +
        "GROUP BY color")
List<Object[]> findAllBy ElementAndGroup(String type);

这给 Validation failed for query for method public abstract findAllByElementAndGroup 这是因为 form.links 这是一个 @OneToMany 关系。
如何修复此查询以便按颜色对数据分组并返回表单链接?

pqwbnv8z

pqwbnv8z1#

问题更新后更新
你可以用 join 在您的查询中。别忘了将列添加到 group by :

@Query("SELECT sum(e.quantity) as qty, f.color, l.id  FROM ElementEntity e " +
        " join e.form f" + 
        " join f.links l " + 
        " WHERE e.type= ?1 " +
        " GROUP BY e.color, l.id")

在上面的代码中,我假设formentity中id字段的名称是 id . 改变 form.id 如果假设不正确。
讨论后更新:

@Query("select sum(e.quantity), f  FROM ElementEntity e join e.form f where e.type = ?1  group by f ")
List<Object[]>findAllByElementAndGroup(String type);
final List<Object[]> aggregatedData = elementRepository.findAllByElementAndGroup("some type");
for (Object[] o : aggregatedData) {
     final Number quantitySum = (Number) o[0];
     final FormEntity form = (FormEntity) o[1];
}

相关问题