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?

wero · Accepted Answer · 2016-03-08 10:56:37Z

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();

answered Mar 8, 2016 at 10:56

wero

33.1k3 gold badges63 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related