Divisions com Hibernate: uso avançado da Criteria API

Existe uma operação, não muito conhecida, mas muitas vezes necessária, em bancos de dados chamada divisão (division). Essa operação representa o seguinte tipo de consulta: Selecione os alunos que fizeram todos os cursos. Selecione os autores em que todos os seus livros têm mais de 200 páginas. E assim por diante.

Esse tipo de consulta precisa de alguns recursos avançados do SQL, então antes de mostrar como implementá-la vamos ver como implementar consultas um pouco mais simples, usando a Subqueries e a DetachedCriteria, que nos possibilitam consultas bastante poderosas usando a api da Criteria.

Bom, vamos começar com três entidades: Aluno, Curso, e um relacionamento de muitos pra muitos entre eles representado pela entidade Matrícula.

Vamos pensar um pouquinho como fazer a seguinte consulta: “Selecionar todos os alunos que estejam cursando Matemática ou Português“. Pensando em banco de dados, podemos fazer um join entre Alunos e Matrículas, e selecionar as linhas em que o curso é matemática ou é português. Precisamos também evitar que a busca retorne alunos repetidos. Vamos fazer isso com Criteria, recebendo a lista dos cursos que eu quero que o aluno esteja cursando algum deles:

public List<Aluno> alunosCursandoAlgumDessesCursos(List<Curso> cursos) {
  Criteria criteria = session.createCriteria(Aluno.class);
  //join com as matrículas
  criteria.createCriteria("matriculas", "m");
  
  //usando a disjunction para fazer um 'ou' entre vários elementos
  Disjunction ou = Restrictions.disjunction();
  for (Curso curso : cursos) {
    ou.add(Restrictions.eq("m.curso", curso);
  }
  criteria.add(ou);

  //eliminando resultados repetidos
  criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
  return criteria.list();
}

Ou podemos fazer algo bem mais interessante, que é usar a restrição in, que retorna verdadeiro se a propriedade dada é igual a algum dos elementos da coleção que passarmos pra ela. Nesse caso trocaríamos o Disjunction por simplesmente:

criteria.add(Restrictions.in("m.curso", cursos));

Bem fácil! Agora vamos mudar só um pouquinho a consulta para: “Selecione todos os alunos que estiverem cursando Português E Matemática“. Poderíamos inocentemente mudar a Disjunction para Conjunction no método anterior. Mas isso não funciona! Por quê? Porque se fizermos isso, estaríamos mudando a consulta para algo do tipo: “Selecione os alunos que tenham uma matrícula que é em Português e em Matemática ao mesmo tempo“. E isso não é possível. Temos que mudar essa consulta para algo do tipo: “Selecione todos os alunos para os quais exista uma matricula no curso Português e exista uma matrícula no curso Matemática“.

Existe uma operação em SQL que faz exatamente isso: o exists. Ela retorna verdadeiro se a subconsulta que estiver depois dela retornar algum resultado. Para fazer isso precisamos então criar subconsultas em Criteria, e o jeito de fazer isso é usando a classe Subqueries, que fabrica Criterions que envolvem a criação de subconsultas.

Para usar qualquer método da Subqueries precisamos de uma DetachedCriteria. Essa DetachedCriteria é um tipo especial de Criteria que não precisa da session do hibernate para ser criada. Dentro dela temos acesso a todos os alias e propriedades da Criteria principal, e o uso é o mesmo que faríamos para Criterias normais.

Já que temos a Subqueries na mão, vamos implementar a consulta, recebendo a lista dos cursos que queremos que o aluno esteja matriculado em todos eles:

public List<Aluno> alunosCursandoTodosEssesCursos(List<Curso> cursos) {
  Criteria criteria = session.createCriteria(Aluno.class, "a");
  Conjunction e = Restrictions.conjunction();
  for (Curso c : cursos) {
    e.add(Subqueries.exists(
      DetachedCriteria.forClass(Matricula.class, "m")
        .setProjection(Projections.id())
        .add(Restrictions.eqProperty("a.id", "m.aluno.id"))
        .add(Restrictions.eq("m.curso",c))));
  }
  criteria.add(e);
  return criteria.list();
}

Ou seja, queremos que exista uma matrícula do aluno da Criteria principal para cada curso da lista passada.

Mas vamos pensar no seguinte: Essa lista de cursos provavelmente veio de outra consulta no banco, por que não usar essa consulta, ao invés da lista de cursos?! O jeito de fazer isso é usando o operador division que falamos no começo do post. Ele é meio complicado de implementar, pois você tem que pensar meio ao contrário do normal. Por exemplo, para implementar a consulta “Selecione os alunos que estão matriculados em todos os cursos” precisamos transformá-la para: “Selecione os alunos para os quais não exista nenhum curso para o qual não exista matrícula desse aluno para esse curso“, ou seja: um aluno que não exista nenhum curso em que ele não esteja matriculado. É estranho mas é assim mesmo que é feito. A Subqueries também possui o método notExists, então podemos fazer a seguinte consulta, que traz os alunos que fazem todos os cursos:

public List<Aluno> alunosCursandoTodosOsCursos() {
  Criteria criteria = session.createCriteria(Aluno.class, "a");
  criteria.add(Subqueries.notExists(
      DetachedCriteria.forClass(Curso.class, "c")
        .setProjection(Projections.id())
        .add(Subqueries.notExists(
            DetachedCriteria.forClass(Matricula.class, "m")
              .setProjection(Projections.id())
              .add(Restrictions.eqProperty("m.curso.id", "c.id"))
              .add(Restrictions.eqProperty("m.aluno.id", "a.id")                
        ))
      ));
  return criteria.list();
}

Não é um bicho de sete cabeças, mas também não é nada trivial. O código fica meio poluído por causa das chamadas estáticas, mas se você fizer o import static dos métodos a coisa melhora um pouquinho.

As restrições que você tinha colocado para buscar a lista de cursos dos métodos anteriores, você pode colocar na DetachedCriteria de Cursos, que vai funcionar do jeito que é esperado. Por exemplo: “Selecione os alunos que estejam matriculados em um curso noturno” vira “Selecione os alunos para os quais não exista algum curso noturno em que ele não esteja matriculado“. Mais ainda: você pode colocar restrições pertinentes na DetachedCriteria da matrícula, que também vai funcionar da forma esperada. Por exemplo: “Selecione os alunos que estejam com a matricula paga em todos os cursos” vira “Selecione os alunos para os quais não existe algum curso em que não exista matrícula paga nesse curso“.

Existem muitos casos em que o operador division salva sua vida então, mesmo que ele seja meio complicadinho, é bom saber que ele existe e ter uma boa referência de como implementá-lo =).

Além da Subqueries, existe outra classe muito útil que fabrica Criterions e Projections relacionados a uma propriedade fixa: a Property. Vale a pena olhar o javadoc do hibernate e ver a quantidade de opções de consultas que temos disponíveis. Existe um bug no hibernate que te obriga a setar uma Projection nas DetachedCriterias quando usadas dentro das Subqueries, se isso não é feito o hibernate nos presenteia com uma NullPointerException.

16 Comentários

  1. George Gastaldi 11/09/2008 at 13:48 #

    Muito bacana esse post. Mas a minha preocupação é se o código ficaria fácil de ser mantido, visto que nem todo desenvolvedor é esperto o bastante para sacar essa jogada.

  2. Tiago Albineli Motta 15/09/2008 at 00:55 #

    No caso de queries mais complicadas, minha preferência fica em utilizar um HQL ou mesmo um SQL diretamente. Esse emaranhado de Criterias me parecem menos legível, ou seria apenas costume.

  3. Lucas Cavalcanti 17/09/2008 at 04:47 #

    Olá,

    George, esse código é meio estranho pra quem não sabe o que está acontecendo… mas é o tipo de código que vc extrai pra um método:

    criteria.add(contemTodosOsCursos());

    private Criterion contemTodosOsCursos() {
    return Subqueries.exists(….);
    }

    Tiago, se a query é construida estáticamente, então o melhor é usar HQL mesmo… mas se ela é dinâmica, criar criterias é bem melhor do que ficar concatenando strings… E além do mais, essa query do division não fica muito mais bonita em HQL:

    from Aluno a where not exists (from Curso c where not exists
    (from Matricula m where m.aluno.id = a.id and m.curso.id = c.id))

    []

  4. Thiago Antonius Oliveira Souza 16/10/2008 at 05:25 #

    Olá, ótimo artigo. Se tratando da criteria API e o uso de projections você poderia sanar está dúvida minha.

    Bom, eu uso o Projections.projectionList().add().add() … para trazer somente as colunas que eu quero em uma consulta.
    Faço isso sem problemas porém quanto tento trazer uma coluna de um objeto relacionado ao principal da pal.

    session.createCriteria(Item.class)
    .createAlias(“categoria”, “categoria”)
    .setProjection(Projections.projectionList()
    .add( Projections.property(“codigo”),”codigo”)
    .add( Projections.property(“descricao”),”descricao”)
    .add( rojections.property(“categoria.descricao”),”categoria.descricao”)

    Could not find setter for categoria.descricao on class br.protjeto.pojo.Item

    Se eu colocasse só categoria ele não dava erro, porém trazia todos os atributos de categoria, mas eu só quero um atributo. Bom, tenho como eu fazer um DTO e colocar um atributo descricaoCategoria, porém ficar fazendo vários DTOs é complicado.

    Já passou por essa situação?

  5. Edson 18/11/2008 at 06:43 #

    Olá, tenho um dúvida em usar hibernate.

    Tenho um tabelão pessoa, com um campo númerico chamado tipo, onde guardo um valor “binário” que identifica o tipo do registro, ex:
    0 = 1 cliente
    1 = 2 fornecedor
    2 = 4 funcionário
    3 = 8 represen

    Caso tenha um cliente e o mesmo também é fornecedor o campo fica com valor 1+ 2 = 3

    uso funções, get_bit() set_bit()… comuns

    Gostaria de saber como selecionar os dados com o hibernate sem usar sql direto

    ex selec: select * from pessoa where get_bit(0,pessoa.tipo,1)

  6. Lucas Cavalcanti 19/11/2008 at 09:59 #

    você pode tentar usar operadores de bit…

    … where (pessoa.tipo & CLIENTE) != 0

    ou algo do tipo… não tenho certeza, mas deve funcionar em hql

  7. Francisco Barroso 21/11/2008 at 07:22 #

    Thiago Antonius, para isso você precisa criar um set na classe pai para trazer os atributos da filha, tipo:

    Tenho um Aluno e preciso trazer o cpf que esta em Pessoal, na classe Aluno coloco o set…
    public void setPessoalCpf(String pessoalCpf)
    {
    if (pessoal == null)
    pessoal = new Pessoal();
    pessoal.setCpf(pessoalCpf);
    }

    e na criteria do Aluno uso:
    p.add(Projections.property(“c.pessoal.cpf”),”pessoalCpf”);

    Acho que esse não é o local correto para responder essa duvida mas não encontrei o email do cara…falou!

  8. Lucas Ferreira 12/01/2009 at 01:42 #

    Achei muito interessante o post, no entanto estou com problemas no SQL que o hibernate gera para o Oracle. O DetachedCriteria ao ser traduzido para SQL dá erro de sintaxe.
    Gostaria de saber se alguém mais teve, ou tem esse problema.

  9. Lucas Cavalcanti 12/01/2009 at 23:39 #

    Tem que tomar cuidado se voce nao está dando o mesmo alias fora e dentro do
    DetachedCriteria… Teoricamente não deve ter problema…

  10. habutre 05/03/2010 at 01:24 #

    Exemplo de notExists foi chave pra resolver um problema aqui.
    Aproveitando em questões de perfomance, qual seria o mais recomendado, HQL ou Criteria? Já vi vários posts contra e a favor de cada um…

  11. Lucas Cavalcanti 07/03/2010 at 16:40 #

    Os dois são meio equivalentes em questão de performance, depende da query que você está fazendo com eles. A diferença é que com HQL você pode ter um controle mais fácil da query SQL que ele vai gerar, na minha opinião, pois já é um pouco parecido com SQL.

  12. Ijimero 19/03/2013 at 10:53 #

    Cara, MUITO OBRIGADO!

    você resolveu um problema que eu estou tendo faz MUITO tempo e sempre estava usando um filtro porco por falta de tempo…

    finalmente consegui achar a solução, e MUITO bem explicada!

    Parabéns, e MUITO OBRIGADO novamente!

    Você salvou a minha vida!

  13. vinicius 12/01/2015 at 23:11 #

    gostaria de saber como é o diagrama de aluno, curso e matricula !

  14. Rafael 18/08/2016 at 09:43 #

    Ta doido… fiz uma confusão danada.

    Se alguém ainda responde os comentários deste post, me ajudem em uma dúvida

    Tenho uma classe chamada “Talhao” e outra classe chamada “Plantio”, a classe Plantio possui um atributo “talhao” que faz referência a Classe “Talhao”. Minha situação é a seguinte, eu preciso cadastrar erradicações que por sua vez possui referência tanto a Talhao quanto a Plantio.

    O numero erradicado inserido no momento do cadastro de Erradicação deve ser abatido na quantidade plantada existente no Plantio, ou seja, eu preciso erradicar e algum plantio! Para isso fiz um selectOneMenu onde devo mostrar os Plantios existentes naquele talhao… como faço isso ?

    Não sei se ficou claro, mas é assim: gerar uma lista que contém todos os plantios de um determinado talhao. Lembrando que “talhao” é um atributo da classe “Plantio”. Valeu!

Deixe uma resposta