CODEWITHSUNDEEP
javamysqlhibernatejpasubquery
Robin-Hoodie
Robin-Hoodie

Reputation: 4974

Make one query out of two others?

I have never been very good at SQL queries (I've always preferred NoSQL databases, but I need to use a SQL database this time).

I need to research how many seats are sold compared to the amount of total seats in cinema halls. At the moment I can accomplish this using 2 different queries in Hibernate, one for the total amount of seats and the other for the amount of sold seats, and then divising these results.

This is my code :

    Double amountOfSoldTickets = ((Long) session.createQuery("select count(*) from Ticket t where t.vertoning.zaal.cinemacomplex.id = :id").
                setInteger("id", complex.getId()).list().get(0)).doubleValue();
    Double capacity= ((Long) session.createQuery("select sum(z.capaciteit) from Zaal z").list().get(0)).doubleValue();
    return amountOfSoldTickets / capacity;

I thought about using a subquery though I have no idea how to do this in hibernate. If anyone has any idea how to solve this in one query it'd be greatly appreciated.

Some additional info: I let hibernate implicitly join my tables in the first query.

Ticket has a many to one to Vertoning, Vertoning has a many to one to Zaal, Zaal has a many to one to cinemacomplex

Upvotes: 0

Views: 66

Answers (2)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153780

I suggest you run this query:

Long[] amountOfSoldTicketsToCapacity = 
    ((Long[]) session.createQuery(
        "select count(*), z.capaciteit, z.id " +
        "from Ticket t " +
        "inner join t.vertoning v " +
        "inner join v.zaal z " +
        "inner join z.cinemacomplex c " +
        "where c.id = :id " +
        "group by z.capaciteit, z.id "
        )
        .setInteger("id", complex.getId())
        .uniqueResult());
if {
    throw new IllegalArgumentException("There are no tickets sold for Zaal#" + complex.getId());
}       
double capacity = (amountOfSoldTicketsToCapacity.length != 0) ? 
    ((double) amountOfSoldTicketsToCapacity[0]) / amountOfSoldTicketsToCapacity[1] : 0D;

Doing the division in Java is simpler, as in SQL you'd probably have to CAST one operand to NUMERIC.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I encourage you to learn SQL -- and to learn it on a real database instead of through the HQL interface. HQL is useful for what it does, but it misses on some very important SQL functionality.

I believe the following will work in HQL:

select count(*) as SoldSeats,
       (select sum(z.capaciteit) from Zaal z) as Capacity
from Ticket t
where t.vertoning.zaal.cinemacomplex.id = :id;

In just MySQL, you could put these as subqueries in the from clause:

select t.SoldSeats, z.Capacity
from (select count(*) as SoldSeats,
      from Ticket t
      where t.vertoning.zaal.cinemacomplex.id = :id
     ) t
     (select sum(z.capaciteit)  as Capacity
      from Zaal z
     ) z;

Note that if this is inside a loop where you are assigning different values to id, then the whole loop can possibly replaced with SQL.

Upvotes: 1

Related Questions