How to transform this native SQL query to an HQL

Question

So I have this long complexe Native SQLQuery :

  string hql = 
    @"SELECT * 
    FROM 
   (SELECT a.*, rownum r__ 
   FROM  
   (select f2.filmid,
    f2.realisateurid, 
    f2.titre, 
    f2.annesortie, 
    f2.langue, 
    f2.duree, 
    f2.resume, 
    f2.poster, 
    f2.qtytotal, 
    f2.qtydisponible from film f2
   where f2.filmid in (
    select distinct f.filmid
       from film f, filmpays fp, pays p, filmgenre fg, genre g, informationpersonnel director, role r, informationpersonnel actor
       where f.filmid = fp.filmid
       and fp.paysid = p.paysid
       and f.filmid = fg.filmid
       and fg.genreid = g.genreid
       and f.realisateurid = director.personelid
       and f.filmid = r.filmid
       and r.personelid = actor.personelid
       and f.qtydisponible > 0
       and upper(f.titre) LIKE :titre
       and f.annesortie >= :anneeLow AND f.annesortie <= :anneeHigh
       and upper(g.Nomgenre) LIKE :genre
       and upper(f.Langue) LIKE :langue
       and upper(p.Nom) LIKE :pays
       and upper(director.nom) LIKE :realisateur
       and upper(actor.nom) LIKE :acteur)
       order by f2.annesortie DESC, f2.titre) a
        WHERE rownum < ((:page * 8) +1 ))
           WHERE r__ >= (((:page - 1) *8) +1) "; 
/*Begin transaction */
            ITransaction tx = s.BeginTransaction();
            IQuery query = s.CreateQuery(hql);
            query.SetString("titre", "%" + sp.Title.ToUpper() + "%");
            query.SetInt32("anneeLow", sp.YearLow);
            query.SetInt32("anneeHigh", sp.YearHigh);
            query.SetString("pays", "%" + sp.Country.ToUpper() + "%");
            query.SetString("langue", "%" + sp.Lang.ToUpper() + "%");
     query.SetString("genre", "%" + sp.Genre.ToUpper() + "%");
        query.SetString("realisateur", "%" + sp.Director.ToUpper() + "%");
        query.SetString("acteur", "%" + sp.Actor.ToUpper() + "%");
        query.SetInt32("page", page);
        IList<Film> movies = query.List<Film>();

        tx.Commit();

        return movies;

And I'm trying to write in a 100% hql way

something similar to

IList<Cat> moreCats = sess.CreateQuery(
    "from Cat as cat where " + 
    "cat.Name = 'Fritz' or cat.id = :id1 or cat.id = :id2"
).SetInt64("id1", id1)
.SetInt67("id2", id2)
.List<Cat>();

After consulting the hql documentation I have understand how to make a simple small query in hql , but what if I have a complexe select from another select , like my query , how should I proceed ?

Thank you

Answer 1

HQL supports subqueries in where statement, so subquery in the where should not cause troubles.

The subqueries in your from statement does not look to me necessary, you should be able of changing your query for no more having it.

Removing the paging part of the query will probably help. This paging should be done by calling .SetFirstResult(indexCalculatedFromYourPage) and .SetMaxResults(yourPageSize) on the HQL query object.

Of course, you need to have mapped all the required columns on your entities. Most of your query looks already HQL compatible to me.
If you map related entities too, you may then simplify the where subquery by avoiding having to write explicitly the join condition of related tables.

Note: your query looks like a "multi-criteria" search query. Rather than writing one query taking into account all parameters (and supporting them being empty/unspecified), it is usually easier (and better for performances by the way) to write the query dynamically for ignoring parameters which were not supplied. And for this, better use a querying api like queryover or linq-to-nhibernate.

Edit: I now realize you have tagged your question as both nhibernate (.Net) and hibernate (Java). The two api I have linked are for NHibernate. If you are using Java, then there is the hibernate-criteria api, and maybe some other I do not know of.