SELECT
    * ,
    H.ID_STATUT AS STATUT_ID,
    H.AUTEUR AS AUTEURDERNIEREMODIF,
    H.DT_CREATION AS DATEDERNIEREMODIF,
    max( H.ID_HISTO_BROCHURE )
FROM
    OCC_TREF_BROCHURE B
    INNER JOIN OCC_TREF_HISTO_BROCHURE H
    ON B.ID_BROCHURE = H.ID_BROCHURE
WHERE
    H.ID_STATUT IN ( 1, 2, 3, 4 )
AND H.AUTEUR LIKE "%Dav%"
GROUP BY B.ID_BROCHURE

criteria = session.createCriteria(Brochure.class);

if(statutList!=null && statutList.length>0) {
    criteria.add(Restrictions.in("statutId",statutList));
}

if(libelleLong!=null && libelleLong.trim().length()>0) {
    criteria.add(Restrictions.like("libelleLong","%"+libelleLong+"%"));
}

if(libelleCourt!=null && libelleCourt.trim().length()>0) {
    criteria.add(Restrictions.like("libelleCourt","%"+libelleCourt+"%"));
}

if(auteur!=null && auteur.trim().length()>0) {
    criteria.add(Restrictions.like("auteur","%"+auteur+"%"));
}

if(dateDebut!=null && dateFin!=null) {
    criteria.add(Restrictions.between("dateCreation",dateDebut,dateFin));
}
criteria
    .createAlias("histoBrochure", "histo")
    .add(Restrictions.eqProperty("histo.brochure","brochure.id"))
    .add(Restrictions.like("histo.auteur", "%"+auteurDerniereModif+"%"));
List<Brochure> brochuresList = criteria.list();

SELECT
    *,
    H.ID_STATUT AS STATUT_ID,
    H.AUTEUR AS AUTEURDERNIEREMODIF,
    H.DT_CREATION AS DATEDERNIEREMODIF,
    max(H.ID_HISTO_BROCHURE)

GROUP BY B.ID_BROCHURE

private final static String SQL_BROCHURES_SEARCH="SELECT B.LIBC_BROCHURE,  H.AUTEUR AS AUTEURDERNIEREMODIF, B.AUTEUR, B.DT_CREATION, H.DT_CREATION AS DATEDERNIEREMODIF, B.ID_BROCHURE, max( H.ID_HISTO_BROCHURE ) FROM OCC_TREF_BROCHURE B INNER JOIN OCC_TREF_HISTO_BROCHURE H ON B.ID_BROCHURE = H.ID_BROCHURE";

           StringBuilder query = new StringBuilder(SQL_BROCHURES_SEARCH);
           if(statutList!=null && statutList.length>0){
                if(isFirst){
                    query.append("WHERE WHERE H.ID_STATUT IN ("+statutList+") ");
                    isFirst=false;
                }else{
                    query.append(" AND H.ID_STATUT IN ("+statutList+") ");
                }
            }
           if(libelleLong!=null && libelleLong.length()>0){
                if(isFirst){
                    query.append("WHERE B.LIBL_BROCHURE LIKE %"+libelleLong+"%");
                    isFirst=false;
                }else{
                    query.append(" AND B.LIBL_BROCHURE LIKE %"+libelleLong+"%");
                }
            }
           if(libelleCourt!=null && libelleCourt.length()>0){
                if(isFirst){
                    query.append("WHERE B.LIBC_BROCHURE LIKE %"+libelleLong+"%");
                    isFirst=false;
                }else{
                    query.append(" AND B.LIBC_BROCHURE LIKE %"+libelleLong+"%");
                }
            }
           if(auteur!=null && auteur.length()>0){
                if(isFirst){
                    query.append("WHERE B.AUTEUR LIKE %"+auteur+"%");
                    isFirst=false;
                }else{
                    query.append(" AND B.AUTEUR LIKE %"+auteur+"%");
                }
            }
           if(auteurDerniereModif!=null && auteurDerniereModif.length()>0){
                if(isFirst){
                    query.append("WHERE H.AUTEUR LIKE %"+auteurDerniereModif+"%");
                    isFirst=false;
                }else{
                    query.append(" AND H.AUTEUR LIKE %"+auteurDerniereModif+"%");
                }
            }
           if(dateDebut!=null){
                if(isFirst){
                    query.append(" where date >= '" + dateDebut + "'");
                }else{
                    query.append(" and date >= '" + dateDebut + "'");
                }
                isFirst = false;
               }
               if(dateFin!=null){
                if(isFirst){
                    query.append(" where date <= '" + dateFin + "'");
                }else{
                    query.append(" and date <= '" + dateFin + "'");
                }
                isFirst = false;
               }
         query.append(" GROUP BY H.ID_BROCHURE");
               List<Brochure> brochuresList = (List<Brochure>) session.createSQLQuery(query.toString()).list();
                // Ajout du libelle statut dans l'objet Brochure

List<Brochure> brochuresList = (List<Brochure>) session.createSQLQuery(query.toString()).list();