Java Hibernate Projections.sqlGroupProjection别名 [英] Java Hibernate Projections.sqlGroupProjection aliasing

问题描述

我有一个使用Hibernate的 Criteria 像这样

  public class学生
 {
私人整数id，discriminatorColumn，discriminatorValue; 
 //为简洁而省略的其他物品
} 
  

这个实体有一个 discriminatorColumn ，其值为1到10（从不 NULL ），每个 discriminatorColumn 有一个 discriminatorValue ，它的值从 1到100 （从不 NULL ）

示例:(学生数据）

  ID DiscriminatorColumn DiscriminatorValue 
 1 1 3 
 2 2 4 
 3 1 13 
  

等等。

我试图得到 SUM > DiscriminatorValue 按 DiscriminatorColumn

分组

我的意思是，在前面的例子中， DiscriminatorColumn = 1 我会收到 SUM = 16 。

  final Criteria criteria = session.createCriteria（Student.class）
 .setProje ction（Projections.sqlGroupProjection（
sum（{alias} .discriminatorValue）as rowCount，
hibernateAlias.discriminatorColumn having rowCount> 0，
 new String [] {rowCount}} ，
 Helper.HIBERNATE_INTEGER_INSTANCE 
））; 
  

这会创建 rightSQL 这个

  select sum（discriminatorValue）as rowCount 
 from student 
 group by discriminator_column 
 having rowCount> 0 
  

我遇到的问题是当我设置 resultSetTransformer 给Student.class我无法找到Hibernate填充 discriminatorValue 列结果的方式 sum discriminatorValue Java字段。

我认为为此目的使用以下参数 new字符串[] {rowCount}}

但我已经尝试了

<$ p $新的字符串[] {discriminatorValue}
新的字符串[] {this.discriminatorValue}
新的字符串[] {{别名} .discriminatorValue}
new String [] {{studentAlias} .discriminatorValue}使用标准中的别名
new String [] {rowCount as discriminatorValue}
new String [] {rowCount a s this_.discriminatorValue}
and So On ..


但似乎不可能我是什么做错了？这是不可能的？ 将这些值设置为一个简单的属性即可从数据库中检索常规。

我写了自己的变量对于这一点，但它是一种讨厌。

解决方案

我已经找到了答案，我会发布在这里能够帮助

  Projections.sqlGroupProjection（sum（{alias} .discriminatorValue）as rowCount，hibernateAlias.discriminatorColumn having rowCount> 0，new String [] {rowCount}，Helper.HIBERNATE_INTEGER_INSTANCE）
  

看起来 rowCount 别名在新字符串[] {rowCount} 仅用于mysql语法，或者被<$ c $忽略c>标准引擎是你需要使用别名来填充你的EntityClass的属性，使用外部别名而不是。

示例：

  yourProjections.add（Projections.alias（Projections.sqlGroupProjection（sum（{别名} .discriminatorValue）a s rowCount，hibernateAlias.discriminatorColumn having rowCount> 0，new String [] {rowCount}，Helper.HIBERNATE_INTEGER_INSTANCE），discriminatorValue））; 
  

只需将 sqlGroupProjections 包装在 Projection.alias 设置别名与您的 Java类中的属性匹配，Hibernate将使用总和结果而不是定制转换器不再需要 ..

我希望可以帮助别人。

I have a Criteria using Hibernate like this

public class Student
{
    private Integer id,discriminatorColumn,discriminatorValue;
    //other stuff omitted for brevity
} 

This entity has a discriminatorColumn which have values from 1 to 10 (never NULL) and each discriminatorColumn has a discriminatorValue which have a value from 1 to 100 (never NULL)

Example: (Student data)

ID DiscriminatorColumn DiscriminatorValue
1            1                  3
2            2                  4
3            1                  13

And so.

I am trying to get a SUM of the DiscriminatorValue grouping by DiscriminatorColumn

I mean, in the previous example if I filter by DiscriminatorColumn = 1 I would receive SUM=16.

final Criteria criteria = session.createCriteria(Student.class)
.setProjection(Projections.sqlGroupProjection(
    "sum({alias}.discriminatorValue) as rowCount",
    "hibernateAlias.discriminatorColumn having rowCount>0",
    new String[]{"rowCount"},
    Helper.HIBERNATE_INTEGER_INSTANCE
 ));

This creates the rightSQL I see something like this

select sum(discriminatorValue) as rowCount
from student
group by discriminator_column
having rowCount>0

The problem i have is when i set the resultSetTransformer to Student.class I could not find the way Hibernate populates the sum of discriminatorValue column result in discriminatorValue Java field.

I think that for that purpose is use the following parameter new String[]{"rowCount"}

But I have tried

new String[]{"discriminatorValue"}
new String[]{"this.discriminatorValue"}
new String[]{"{alias}.discriminatorValue"}
new String[]{"{studentAlias}.discriminatorValue"} using alias in Criteria
new String[]{"rowCount as discriminatorValue"} 
new String[]{"rowCount as this_.discriminatorValue"}
and So On..

But seems impossible what i am doing wrong? It's not possible? Sum the values as set it as a simple Property as retrieving regular from database.

I have written my own transformer for this but it's kind of annoying.

解决方案

I have found the answer by myself and i will post here to be able to help somebody.

Projections.sqlGroupProjection("sum({alias}.discriminatorValue) as rowCount","hibernateAlias.discriminatorColumn having rowCount>0",new String[]{"rowCount"},Helper.HIBERNATE_INTEGER_INSTANCE)

Seems the rowCount aliasing in new String[]{"rowCount"} is just for mysql syntax or is just ignore by Criteria engine is you need to use a alias to populate a property of your EntityClass use outer alias instead.

Example:

yourProjections.add(Projections.alias(Projections.sqlGroupProjection("sum({alias}.discriminatorValue) as rowCount","hibernateAlias.discriminatorColumn having rowCount>0",new String[]{"rowCount"},Helper.HIBERNATE_INTEGER_INSTANCE),"discriminatorValue"));

Just wrap your sqlGroupProjections in a Projection.alias as set the alias match your property in your Java class and Hibernate will populate with the sum result and not custom transformer are not longer required..

I hope helps somebody.

这篇关于Java Hibernate Projections.sqlGroupProjection别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！