2 Replies Latest reply on Feb 12, 2010 9:53 AM by Adam Warski

    Doubts about Queries

    get boa Newbie

      Hi y'all

      I've got a @OneToOne relationship of these tables: Lacre(table name is SVE_LACRE ), Setor(table name = SVE_SETOR), both are @Audited and Revisoes(table name=SVE_LACRE_REVISOES) as my custom @RevisionEntity. I was trying to do a query per date range and Setor code.

      The first thing i did was to do this query only per date range as showed below, it worked fine but i checked the sql on the output and I didn't understand much. Below follows the query and the output:

      [[Query]]

      AuditReader reader = AuditReaderFactory.get(hibernateSession);

      AuditQuery query = reader.createQuery().forRevisionsOfEntity(Lacre.class, false, false);

      query.add(AuditEntity.revisionProperty("data").between(dataInicialTmp.getTime(), dataFinalTmp.getTime()));

      and the output:

      [[Output]]

      select

              lacre_aud0_.OID_COD_LACRE as OID1_33_0_,

              lacre_aud0_.VERSAO_REVISAO as VERSAO2_33_0_,

              revisoes1_.OID_REVISAO as OID1_22_1_,

              lacre_aud0_.REVTYPE as REVTYPE33_0_,

              lacre_aud0_.DES_CANCELAMENTO as DES4_33_0_,

              lacre_aud0_.DES_MOTIVO_SUBSTITUICAO as DES5_33_0_,

              lacre_aud0_.DES_OPERADOR as DES6_33_0_,

              lacre_aud0_.VE_PLACA as VE7_33_0_,

              lacre_aud0_.DES_SERVICO as DES8_33_0_,

              lacre_aud0_.DES_SESSAO as DES9_33_0_,

              lacre_aud0_.IDT_SITUACAO as IDT10_33_0_,

              lacre_aud0_.VE_UF as VE11_33_0_,

              lacre_aud0_.OID_COD_REMESSA as OID12_33_0_,

              lacre_aud0_.COD_ORGAO as COD13_33_0_,

              revisoes1_.DTA_REVISAO as DTA2_22_1_

          from

              SVE_LACRE_AUD lacre_aud0_,

              SVE_LACRE_REVISOES revisoes1_,

              SVE_LACRE_REVISOES revisoes2_

          where

              lacre_aud0_.VERSAO_REVISAO=revisoes2_.OID_REVISAO

              and lacre_aud0_.REVTYPE<>?

              and revisoes2_.DTA_REVISAO>=?

              and revisoes2_.DTA_REVISAO<=?

              and lacre_aud0_.VERSAO_REVISAO=revisoes1_.OID_REVISAO

          order by

              lacre_aud0_.VERSAO_REVISAO asc

       

      the question is: Why not just to use the "between" to compare these dates and use only one revision table instead of two(revisoes1_, revisoes2_)?

      Second thing is, What if i wanted to add the Setor restriction? I tryid by adding query.add(AuditEntity.property("setor.codigo").eq("1231")); but it does not recognize this property "setor.codigo".

      Is there any trick to do it?

      Thanks in advance