如何使用子查询而不是连接查询来获取正确的计数与hibernate [英] How to use a subquery instead of a joined query to get correct count with hibernate
问题描述
==== Cat.java ====
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(unique = true,nullable = false)
private String name;
@OneToMany @JoinColumn(name =cat_id)
private List< Kitten>小猫;
==== Kitten.java ====
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(unique = true,nullable = false)
private String name;
包含以下数据
Cat(id,name)
Cat(1,Cat 1)
Cat(2,Cat 2)
,cat_id,name)
小猫(1,1,Kitten 1)
小猫(2,1,小猫2)
小猫(3,2,Kitten 3)
Kitten(3,2,Bad Kit)
所有具有包含kitten的小猫的猫。
list = sess()createCriteria(Cat.class)。 createAlias(kitten,kitten)
.add(Restrictions.like(kitten.name,%kitten%))。
上一个命令不是很好,因为连接我会得到重复的条目,例如计数和maxresult不工作。
我在想这样的东西(但是用Criteria-Api):
来自Cat,其中id在
(选择cat_id来自Kitten,其中名称像'%kitten%')
这不工作,因为hibernate不给我访问cat_id,我不想让它双向对于此查询。
如果您只是添加投影以获取cat ID,喜欢使用。所以你只需要以下:
DetachedCriteria acceptedCatIds = DetachedCriteria.forClass(Cat.class);
acceptedCatIds.createAlias(kitten,kitten)
.add(Restrictions.like(kitten.name,%kitten%))
.setProjection(Projections.id ());
Criteria acceptedCats = session.createCriteria(Cat.class)
acceptedCats.add(Subqueries.propertyIn(id,acceptedCatIds));
assuming I have following two tables (Cats with Kittens):
==== Cat.java ====
@Id @GeneratedValue( strategy = GenerationType.IDENTITY )
private long id;
@Column( unique = true, nullable = false )
private String name;
@OneToMany @JoinColumn( name = "cat_id" )
private List<Kitten> kitten;
==== Kitten.java ====
@Id @GeneratedValue( strategy = GenerationType.IDENTITY )
private long id;
@Column( unique = true, nullable = false )
private String name;
with the following data
Cat(id, name)
Cat(1, "Cat 1")
Cat(2, "Cat 2")
Kitten(id, cat_id, name)
Kitten(1, 1, "Kitten 1")
Kitten(2, 1, "Kitten 2")
Kitten(3, 2, "Kitten 3")
Kitten(3, 2, "Bad Kit")
Now I would like to see all cats which have a kitten whose name contains "kitten".
list = sess().createCriteria( Cat.class ).createAlias( "kitten", "kitten" )
.add( Restrictions.like( "kitten.name", "%kitten%" ) ).list();
The previous command is not very nice because of the join I will get duplicate entries and for example count and maxresult are not working. This is a well documented problem and it is mentioned to use subqueries instead.
I'm thinking of something like this (but with Criteria-Api):
from Cat where id in
(select cat_id from Kitten where name like '%kitten%')
This doesn't work because hibernate doesn't give me access to "cat_id" and I don't want to make it bidirectional just for this query.
Your query, if you just add a projection to get the cat ID, is the subquery you'd like to use. So you just need the following:
DetachedCriteria acceptedCatIds = DetachedCriteria.forClass(Cat.class);
acceptedCatIds.createAlias("kitten", "kitten")
.add(Restrictions.like("kitten.name", "%kitten%" ))
.setProjection(Projections.id());
Criteria acceptedCats = session.createCriteria(Cat.class)
acceptedCats.add(Subqueries.propertyIn("id", acceptedCatIds));
这篇关于如何使用子查询而不是连接查询来获取正确的计数与hibernate的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!