How can I correctly implement an Hibernate SQL query starting from an SQL query that count the number of rows?

Question

I am absolutly new in Hibernate and I have the following problem.

I have this standard SQL query:

SELECT count(*) 
FROM TID003_ANAGEDIFICIO anagraficaEdificio 
INNER JOIN TID002_CANDIDATURA candidatura 
  ON (candidatura.PRG_PAR = anagraficaEdificio.PRG_PAR AND candidatura.PRG_CAN = anagraficaEdificio.PRG_CAN)
INNER JOIN TID001_ANAGPARTECIPA anagPartecipa ON(anagPartecipa.PRG_PAR = candidatura.PRG_PAR)
INNER JOIN anagrafiche.TPG1029_PROVNUOIST provNuovIst ON (provNuovIst.COD_PRV_NIS = anagPartecipa.COD_PRV_NIS)
WHERE anagraficaEdificio.FLG_GRA = 1 AND provNuovIst.COD_REG = "SI";

This works fine and return an integer number.

The important thing to know is that in this query the only parameter that can change (inserted by the user in the frontend of a webappplication) is the last one (this one: provNuovIst.COD_REG = "SI").

So, the application on which I am working use Hibernate and the requirement say that I have to implement this query using Hibernate Native SQL, I have found this tutorial:

http://www.tutorialspoint.com/hibernate/hibernate_native_sql.htm

that show this example:

String sql = "SELECT * FROM EMPLOYEE WHERE id = :employee_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Employee.class);
query.setParameter("employee_id", 10);
List results = query.list();

that, from what I have understand (correct me if I am doing wrong assertion), involves the use of an Employee model class. So th prvious query first define the query (using the :param_name syntax for the parameter), then create an SQLQuery Hibernate object, add the class used for the result, set the previous parameter neam and finally obtain a List (that I think Hibernate create as something like an ArrayList) with the retrieved object.

My problem is that I simply I have to obtain an integer value (because I have a SELECT count(*), so I will obtain an integer value and not a set of rows).

So how can I correctly use the Hibernate Native SQL to implement my SQL query into my Hibernate repository class?

Answer 1

Use SQLQuery.uniqueResult to retrieve a single value from the query:

String sql = "SELECT count(*) ...";
SQLQuery query = session.createSQLQuery(sql);
// set parameters...
int count = ((Number)query.uniqueResult()).intValue();