Distinc Criteria Hibernate java

1

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")));

I need to do the following. return all Entities2 that satisfy the following filter

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

Today I'm doing two for to solve the problem but not this performative.

    
asked by anonymous 29.11.2017 / 14:02

1 answer

1

I do not know if I understood your question correctly.

I've got this:

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

Using HQL would look like:

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();

Was that really the question?

    
10.12.2017 / 04:34