Hibernate标准，不同的关联属性 [英] Hibernate criteria, distinct association property
问题描述
class Person {
String firstname，lastname;
地址;
ManyOtherPropertiesAndEntities ...;
}
class地址{
String street;
国家国家;
}
现在，我想查询Person表并 ONLY 居住在不同街道上的人。
也就是说，忽略所有居住在同一条街上的人，并且只返回其中一个人，任何人。
我如何执行这样的查询？
可能使用标准？
Criteria criteria = session.createCriteria（Person.class，person）;
Criteria addressCriteria = criteria.createCriteria（address）
criteria.setProjection（
Projections.distinct（
Projections.projectionList（）。add （Projections.property（address.street））
）
）;
这并不奏效。
我也试过：
projectionList.add （Projections.sqlProjection（DISTINCT ON（address.street），...，columns.toArray（new String [columns.size（）]），types.toArray（new Type [types.size（）]））） ;
但是也没有结果。
>>>>>>>>>>>>>>>>>>>>>>> EDIT <<
我能够运行它，并生成一个实际以纯sql模式返回结果的SQL查询，但似乎返回零hibernate：
List< String> columns = Lists.lst（）;
列表<类型> types = Lists.lst（）;
bondCriteria.setProjection（
Projections.sqlProjection（DISTINCT ON（tvmux2_.polarization）*
，columns.toArray（new String [columns.size（）]）
，types.toArray（new Type [types.size（）]）
）
// Projections.projectionList（）。add（Projections.distinct（Projections.property（极化）））
）;
ResultTransformer resultTransformer = new ResultTransformer（）{
@Override
public List transformList（List collection）{
return null;
}
@Override
public Object transformTuple（Object [] tuple，String []别名）{
return null;
}
};
bondCriteria.setResultTransformer（resultTransformer）;
* 2017和SO还没有包含适当的编辑器，格式的代码，以便缩进和复制粘贴不是一个完整的地狱。随意水平滚动。 *
TH基本生成以下查询：
pre $ 选择DISTINCT ON（tvmux2_.polarization）* from TvChannelBond this_ inner join TvChannel tvchannel1_ on this_.channel = tvchannel1_.id内部连接TvMux tvmux2_ on_mux = tvmux2_.id其中this_.enabled = true by tvmux2_.polarization asc limit 100
返回非休眠模式结果。
但是，由于sqlProjection方法需要补充3个参数，所以我不确定要为第二个和第三个参数添加什么。类型不能是预定义的Hibernate类型，就像DOUBLE，STRING等一样。 调试放入 resultTransformer 时，它将进入长度为零的transformTuple 元组[]和别名[b] 可能与 sqlProjection零长度类型和列列表有关。
<$ c $ c> SELECT p。*
FROM Address a
INNER JOIN Person p ON ...
GROUP BY a.Street
HAVING p.id = MIN（p.id）
这条语句为每个不同的 Street 从地址 Person ，最小值为 id 值。而不是 MIN（p.id），你当然可以使用任何其他的字段和聚合函数，每个街道只能匹配一个人; MAX（p.id）会起作用， MIN（p.lastname）不会超过一个史密斯在街上。
您可以将上述SQL转换为Criteria查询吗？
Say I have at least these two entities:
class Person {
String firstname, lastname;
Address address;
ManyOtherPropertiesAndEntities ...;
}
class Address {
String street;
Country country;
}
Now, I would like to query the Person table and ONLY Persons that live on different streets.
That is, ignore all Persons that live on same street, and return only one of these Person, any one.
How can I perform such a query?
Is that possibly using Criteria?
Criteria criteria = session.createCriteria(Person.class, "person");
Criteria addressCriteria = criteria.createCriteria("address")
criteria.setProjection(
Projections.distinct(
Projections.projectionList().add(Projections.property("address.street"))
)
);
This doesnt really work.
I've also tried to do:
projectionList.add( Projections.sqlProjection("DISTINCT ON ( address.street ), ... ", columns.toArray(new String[columns.size()]), types.toArray(new Type[types.size()])));
But also fruitless.
>>>>>>>>>>>>>>>>>>>>>>>EDIT<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
I was able to get this to run, and generate an SQL query that actually returns results in a pure sql mode, but seems to return zero in hibernate:
List<String> columns = Lists.lst();
List<Type> types = Lists.lst();
bondCriteria.setProjection(
Projections.sqlProjection ("DISTINCT ON ( tvmux2_.polarization ) * "
, columns.toArray (new String[columns.size()])
, types.toArray (new Type[types.size()])
)
// Projections.projectionList().add(Projections.distinct(Projections.property("polarization")))
);
ResultTransformer resultTransformer = new ResultTransformer() {
@Override
public List transformList( List collection ) {
return null;
}
@Override
public Object transformTuple( Object[] tuple, String[] aliases ) {
return null;
}
};
bondCriteria.setResultTransformer(resultTransformer);
* ITS 2017 and SO still hasn't included a proper editor to easily be able to format code so that indentation and copy and paste is not a complete hell. Feel free to scroll horizontally. *
THis generates teh following query basically
select DISTINCT ON ( tvmux2_.polarization ) * from TvChannelBond this_ inner join TvChannel tvchannel1_ on this_.channel=tvchannel1_.id inner join TvMux tvmux2_ on this_.mux=tvmux2_.id where this_.enabled=true order by tvmux2_.polarization asc limit 100
which does return results in a non hibernate mode.
However, since sqlProjection method requires the supplementation of 3 params, I am not sure what to add to the second and third params. Types can not be other than predefined Hibernate types just as DOUBLE, STRING and so on.
When debugging into the resultTransformer, it gets into transformTuple with zero length tuple[] and aliases[].
Might have to do with the sqlProjection zero length types and columns lists.
In SQL, you could do it like this:
SELECT p.*
FROM Address a
INNER JOIN Person p ON ...
GROUP BY a.Street
HAVING p.id = MIN(p.id)
This statement selects for every distinct Street from Address the Person with the minimum id value. Instead of MIN(p.id) you can of course use any other field and aggregate function which will match exactly one person per street; MAX(p.id) will work, MIN(p.lastname) won't if there can be more than one "Smith" in a street.
Can you transform the above SQL to your Criteria query?
这篇关于Hibernate标准，不同的关联属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
