title: Aggregation in Hibernate / JPA
body: <p>Let's say I have an entity such as  </p>
  
  
  
  <pre><code>@Entity
  @Table(name = "t_entity")
  Entity {
  
     @Id
     @GeneratedValue
     @Column(name = "id")
     int id;
  
     @Column(name = "name")
     String name;
  
     @Column(name = "version")
     int version;
  
     @Column(name = "parameter")
     String parameter;
  }
  </code></pre>
  
  <p>Now, I need to fetch latest versions of entities having given names and specific parameter value. In plain SQL I'd write something like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select id, name, max(version) version, parameter
  from entity 
  where name in ('foo', 'bar') and parameter = 'baz'
  group by name, id
  </code></pre>
  
  <p>But, how do I do that with Hibernate (preferably using Criteria API) ?</p>
  
  <p>UPD:</p>
  
  <p>First of all, as it was stated in comments, my example query is incorrect. The correct version of what I was trying to achieve looks more like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select e0.id, e0.name, e0.version, e0.parameter from entity e0 
  right join (select name, max(version) mv from entity where name in ('foo', 'bar') group by name) e1 
  on e0.name = e1.name and e0.versioin = e1.mv
  where e0.parameter = 'baz'
  </code></pre>
  
  <p>My best working version so far involves two separate requests:</p>
  
  <pre class="lang-java prettyprint-override"><code>CriteriaBuilder cb = sessionFactory.getCriteriaBuilder();
  
  CriteriaQuery&lt;Tuple&gt; lvq = cb.createQuery(Tuple.class);
  Root&lt;Entity&gt; lvr = lvq.from(Entity.class);
  
  lvq.multiselect(lvr.get("name").alias("name"), cb.max(lvr.get("version")).alias("mv"));
  lvq.where(lvr.get("name").in("foo", "bar"));
  lvq.groupBy(lvr.get("name"));
  
  List&lt;Tuple&gt; lv = sessionFactory.getCurrentSession().createQuery(lvq).getResultList();
  
  CriteriaQuery&lt;Entity&gt; cq = cb.createQuery(Entity.class);
  Root&lt;Entity&gt; root = cq.from(Entity.class);
  
  List&lt;Predicate&gt; lvp = new LinkedList&lt;&gt;();
  for (Tuple tuple : lv) {
      lvp.add(cb.and(
          cb.equal(root.get("version"), tuple.get("mv")),
          cb.equal(root.get("name"), tuple.get("name"))));
  }
  
  cq.select(root).where(cb.and(
          cb.equal(root.get("parameter"),"baz"), 
          cb.or(lvp.toArray(new Predicate[lvp.size()])));
  
  return sessionFactory.getCurrentSession().createQuery(cq).getResultList();
  </code></pre>
  
  <p>Btw, probably I was not clear enough about which Criteria API I wanna use, so it's JPA.</p>
  
  <p>Thank you guys for answers, they've pushed forward to create ugly, but at least working solution.<br>
  I will appreciate any suggestions how to improve the code for such a task.</p>