How to call SQL query with aggregate function in JPA/Hibernate

Question

Is it possible to run such a a query like this below in JPA/Hibernate?

select v.country,
v.site,
    SUM(case when s.id = 1 then 1 else 0 end) as Total_SuspectedViolations,
    SUM(case when s.id = 2 then 1 else 0 end) as Total_ConfirmedViolations,
    SUM(case when s.id = 3 then 1 else 0 end) as Total_ConfirmedNoViolations,
    SUM(case when s.id = 4 then 1 else 0 end) as Total_NotDetermined,
    COUNT(*) Total
from violations v
inner join status s
    on v.status_id = s.id
group by v.country, v.site

or

select v.country,
v.site,
    SUM(case when v.status_id = 1 then 1 else 0 end) as Total_SuspectedViolations,
    SUM(case when v.status_id = 2 then 1 else 0 end) as Total_ConfirmedViolations,
    SUM(case when v.status_id = 3 then 1 else 0 end) as Total_ConfirmedNoViolations,
    SUM(case when v.status_id = 4 then 1 else 0 end) as Total_NotDetermined,
    COUNT(*) Total
from violations v
group by v.country, v.site

This works in Oracle DB but when I am using JPA/Hibernate I am getting:

ERROR [PARSER] line 1:311: unexpected token: total
ERROR [PARSER] line 1:375: unexpected token: ON

I am asking because I need to implement some simple statistics based on data in one table "Violations"

Below is my idea:

I need to join two tables and count occurrences some values from second table to achieve effect like statistics table which will have columns:

Result Table:

country varchar(20),
site varchar(20),
suspected_violation long,
confirmed_violation long,
confirmed_no_violation long,
not_determined long,
total long

My Result Table needs to have first two columns (contry and site) comes from first table "Violations" and next 5 columns which will contain numbers (count) of occurrences status_id in "Violations" in each of possible values of id from Status table.

So, I have existing two tables: Violations and Status.

Violations:

id long,
country varchar(20),
site varchar(20),
status_id long, <-- this is the id of status in Status table.
... other columns not important in this case

Status:

id long,
status long Column "status" have values (1-4) which are mapped to string values: Suspected Violation (1), Confirmed Violation (2), Confirmed No Violation (3), Not Determined (4)

In result of my join is to have table which should contain columns:

from Violations table: "Country" and "Site"
from Status table: "Suspected Violation", "Confirmed Violation", "Confirmed No Violation", "Not Determined", "Total" (where this columns are counters of occurrences in Violation table).

UPDATE: As far as I know now there is no need to use join, query can be run on one table only.

Can you suggest me what is the proper way to do that in JPA/Hibernate?

In JPA you can use createNativeQuery

Eelke
– Eelke

2013-03-11 20:25:53 +00:00
Commented Mar 11, 2013 at 20:25 — Eelke
– Eelke, Commented Mar 11, 2013 at 20:25

Roman · Accepted Answer · 2013-03-12 12:33:02Z

Thanks for a suggestion meanwhile I have solved it in this way:

List<ViolationSummaryDTO> result = em.createQuery(
                "SELECT new ViolationSummaryDTO(v.counName, v.siteNumber," +
                    "sum(case when v.pvClStati.clstId = 1 then 1 else 0 end) as suspectedViolation," +
                    "sum(case when v.pvClStati.clstId = 2 then 1 else 0 end) as confirmedViolation," +
                    "sum(case when v.pvClStati.clstId = 3 then 1 else 0 end) as confirmedNoViolation," +
                    "sum(case when v.pvClStati.clstId = 0 then 1 else 0 end) as notDetermined," +
                    "COUNT(*) as total)" +
                " FROM Violation v" +
                " WHERE v.trial.id = " + trialId +
                " GROUP BY v.counName, v.siteNumber", ViolationSummaryDTO.class).getResultList();

The result is the list of objects which are created by hibernate in SELECT new statement.

VKPRO · Accepted Answer · 2013-03-11 18:34:18Z

0

You have to use

session.createSQLQuery

and can use below method to convert the sql result to your required bean

query.setResultTransformer(Transformers.aliasToBean(YourBean.class))

or You can use Projections

edited Mar 11, 2013 at 18:34

answered Mar 11, 2013 at 18:28

VKPRO

1591 gold badge3 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to call SQL query with aggregate function in JPA/Hibernate

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related