Hibernate Community • View topic - Criteria nested select using max of child collection

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Criteria nested select using max of child collection

Page 1 of 1

[ 6 posts ]

Previous topic | Next topic

Author

Message

JennaMilani

Post subject: Criteria nested select using max of child collection

Posted: Mon Sep 05, 2016 5:21 am

Newbie

Joined: Mon Sep 05, 2016 4:18 am
Posts: 3

Hi,
I have:

Code:

@Entity
public class Parent implements Serializable {
   private static final long serialVersionUID = 1L;

   @Id
   private Integer id;
   private String name;
   @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.MERGE)
   @JoinTable(name = "PARENT_CHILD", joinColumns = {@JoinColumn(name = "PARENT_PID")}, inverseJoinColumns = {@JoinColumn(name = "CHILD_PID")})
   @OrderBy("persistenceId DESC")
   private List<Child> childs = new ArrayList<Child>();
}

Code:

@Entity
public class Child implements Serializable{   
   private static final long serialVersionUID = 1L;

   @Id
   private Integer id;   
   private String name;
   private Integer age;
}

----------------------
Parent
----------------------
id |name
----------------------
1 |John
2 |Kevin
3 |David

----------------------
Child
----------------------
id |name |age |parent_id
----------------------
1 |Marc |18 |1
2 |Jim |10 |1
3 |Bob |17 |1
4 |Steve |17 |2
5 |Anne |21 |2
6 |Simon |23 |2
7 |Carl |25 |3
8 |Matt |17 |3
9 |Jason |3 |3

I want only the parent where the last child inserted in DB has 17 years old.

Expeted result is:
1 |John
The 3rd child Bob's has 17 years old.

With hibernate Criteria, what is the solution?

Thanks in advance

Top

vlad

Post subject: Re: Criteria Many to Many inner join: search in last record

Posted: Mon Sep 05, 2016 5:34 am

Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania

First of all, I'd recommend you switch to LAZY instead of EAGER because EAGER is bad for collections.

Code:

@ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.MERGE)

Second, you need to think of the SQL query first prior to trying to translate it to Criteria. What SQL query have you come up with?

Top

JennaMilani

Post subject: Re: Criteria Many to Many inner join: search in last record

Posted: Mon Sep 05, 2016 5:58 am

Newbie

Joined: Mon Sep 05, 2016 4:18 am
Posts: 3

In SQL is:

Code:

SELECT p.*, c.* 
FROM Parent p 
INNER JOIN Parent_Child pc ON p.id = pc.Parent_pid 
INNER JOIN Child c ON c.id = pc.Child_pid
WHERE c.id = (
    SELECT Max(pc1.Child_pid) as id1 
    FROM Parent_Child pc1 
    WHERE pc1.Parent_pid = p.id
) AND c.age = 17;

Thanks

Top

vlad

Post subject: Re: Criteria Many to Many inner join: search in last record

Posted: Mon Sep 05, 2016 6:28 am

Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania

First, this SQL query can be translated to HQL like this:

Code:

SELECT p.
FROM Parent p 
JOIN p.childs c
WHERE c.id = (
    SELECT max(c1.id) a
    FROM Child c1 
    WHERE c1.parent = p
) AND c.age = 17;

For Criteria, try this query:

Code:

DetachedCriteria innerCriteria = DetachedCriteria.forClass(Child.class, "inner")
    .add(Restrictions.eqProperty("inner.parent.id","upper.id"))
    .setProjection(Projections.projectionList().add(Projections.max("inner.id")));

DetachedCriteria outerCriteria= DetachedCriteria.forClass(Parent.class, "upper");
outerCriteria.createAlias("upper.childs", "children");
outerCriteria.add(Subqueries.propertyEq("children.id", innerCriteria ));
outerCriteria.add(Restrictions.eqProperty("children.age", 17));

List<Parent> parents = outerCriteria.getExecutableCriteria(session).list();

Top

JennaMilani

Post subject: Re: Criteria nested select using max of child collection

Posted: Mon Sep 05, 2016 10:33 am

Newbie

Joined: Mon Sep 05, 2016 4:18 am
Posts: 3

Thanks,
I try it.

Jenna

Top

vlad

Post subject: Re: Criteria nested select using max of child collection

Posted: Thu Sep 08, 2016 8:53 am

Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania

It worked, right?

Top

Page 1 of 1

[ 6 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum