Distinc Criteria Hibernate java

Question

I have the following context:

class Entidade1
private Long id;
private int commentId;
private int userId;

---

class Entidade2
private Long id;
private String descricao;

- I have the following criteria

Criteria criteria = persistence.createCriteria(Entidade2.class);
criteria.add((Restrictions.eq("descricao", "Teste")));

And I need to do the following. return all Entitys2 that satisfy the filter string

Entidade1.commentId != Entidade2.id and Entidade1.userId = 1

Today I’m doing two for to solve the problem but not this performatic.

Answer 1

I’m not sure I understand your question.

I got that:

SELECT distinct * FROM entidade2 e2
WHERE e2.id not in ( SELECT e1.commentId FROM entidade1 e1 WHERE e1.userId = 1 );

Using HQL would look:

String hql = "select distinct e2 FROM Entidade2 e2 where e2.id not in ( " +
             "select e1.commentId from Entidade1 e1 WHERE e1.userId = 1 )";
Query q = persistence.createQuery( hql );
List<Entidade2> lista = query.list();

Using criteria:

Criteria criteria1 = persistence.createCriteria(Entidade1.class);
criteria1.add( Restrictions.eq("userId", 1));
criteria1.setProjection(Projections.property("commentId"));
List<Integer> sub = criteria1.list();

Criteria criteria2 = persistence.createCriteria(Entidade2.class);
criteria2.add( Restrictions.not(Restrictions.in("id", sub)) );
criteria2.setProjection(Projections.distinct(Projections.property("id")));
List<Entidade2> lista = criteria2.list();

That was the question?