Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate, what to do when i've to create some articulated queries?

 
Luk Cora
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've undertood that hibernate offers a great support for programmers, throught default functions that support the most common queries to perform on a database and mapping it starting from javabeans.

For exemple


and i will found those data inside the database without write any line of sql code.

Ok, but what if i need to retrieve a list products from a database and calculate the total price of them?
The sql code should be:
SELECT *, SUM(price*quantity) AS total FROM orders WHERE id_user=?

Useing hibernate how to face similiar cases? There are some directives to write into the xml file, or i need to write custom methods and insert inside of my sql string??


 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Luca Coraggio wrote:Ok, but what if i need to retrieve a list products from a database and calculate the total price of them?
The sql code should be:
SELECT *, SUM(price*quantity) AS total FROM orders WHERE id_user=?

I don't use Hibernate so somebody else will have to answer the Hibernate-specific questions. But you need to think about what your SQL query is doing and how many rows it returns. You really have two queries here:
This fetches one row for each record in your orders table for this user. If you have 10 orders, you get 10 records.
This fetches a single value - the total order cost - for all the records in your orders table for this user. So if you have 10 orders, you still only get one total.

You can't combine these particular queries into a single SQL statement, so you have basically two options:

  • a) You can split the queries and execute each one in turn i.e. fetch the orders, then fetch the total separately. The problem with this is that you have to execute two queries, and there is a chance that the database entries might change between your first and second query.
  • b) Or you can just fetch the orders, and then calculate the total as you process each row in your Java code i.e. take the (price * quantity) for each record and add it to a "total" variable. You could do the (price*quantity) inside your SQL if you want, then just add these values together to generate the total as you process each row in Java.

  • I would choose option (b) if I were you.

    More generally, you should learn some SQL so you can understand what Hibernate is doing and what your data really looks like in a relational database. If you don't understand the structure of your data, you won't know how to process it properly, whatever tools you are using.
     
    Luk Cora
    Ranch Hand
    Posts: 70
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    chris webster wrote:
    Luca Coraggio wrote:Ok, but what if i need to retrieve a list products from a database and calculate the total price of them?
    The sql code should be:
    SELECT *, SUM(price*quantity) AS total FROM orders WHERE id_user=?

    I don't use Hibernate so somebody else will have to answer the Hibernate-specific questions. But you need to think about what your SQL query is doing and how many rows it returns. You really have two queries here:
    This fetches one row for each record in your orders table for this user. If you have 10 orders, you get 10 records.
    This fetches a single value - the total order cost - for all the records in your orders table for this user. So if you have 10 orders, you still only get one total.

    You can't combine these particular queries into a single SQL statement, so you have basically two options:

  • a) You can split the queries and execute each one in turn i.e. fetch the orders, then fetch the total separately. The problem with this is that you have to execute two queries, and there is a chance that the database entries might change between your first and second query.
  • b) Or you can just fetch the orders, and then calculate the total as you process each row in your Java code i.e. take the (price * quantity) for each record and add it to a "total" variable. You could do the (price*quantity) inside your SQL if you want, then just add these values together to generate the total as you process each row in Java.

  • I would choose option (b) if I were you.

    More generally, you should learn some SQL so you can understand what Hibernate is doing and what your data really looks like in a relational database. If you don't understand the structure of your data, you won't know how to process it properly, whatever tools you are using.


    yes i know what you're saying.. i know that SUM fetches all the rows in one, anyway i already solved this situation placing "group by orders.id" into the sql clause... Anyway i posted this query only for do an exemple without consider if that is or not right
     
    chris webster
    Bartender
    Posts: 2407
    33
    Linux Oracle Postgres Database Python Scala
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Luca Coraggio wrote:yes i know what you're saying.. i know that SUM fetches all the rows in one, anyway i already solved this situation placing "group by orders.id" into the sql clause... Anyway i posted this query only for do an exemple without consider if that is or not right

    Are you sure?

    If you do:

    SELECT ..., SUM(price * quantity) AS total FROM orders WHERE id_user = ? GROUP BY orders.id

    Then you still get one row per Order ID, which I'm assuming is your primary key on the orders table i.e. you get one result row for every order record, same as without the GROUP BY, because your group key is the same as your primary key. In other words, this is the same as doing:

    SELECT ..., (price * quantity) AS total FROM orders WHERE id_user = ?

    If you do something like this:

    SELECT id_user, SUM(quantity * price) AS total FROM orders GROUP BY orders.id_user

    Then you will get one row per User ID (if your user has one order or 10 orders, you still get just one row), with the total for all the orders for that user. This is because you are now telling SQL to add up (SUM) the totals by user (GROUP BY id_user).

    Like I said, I think you need to take some time to learn some SQL. Just throwing GROUP BY into your query may not be doing what you think it's doing.
     
    H Paul
    Ranch Hand
    Posts: 471
    4
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Known is a drop. Unknown is an ocean.


    I do not know a lot. But when in doubt, I just go back to the document to see what it offer.

    Have you looked at the official document to see what Hibernate offer?


    Taking from HIBERNATE - Relational Persistence for Idiomatic Java

    http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/

    Chapter 16. HQL: The Hibernate Query Language

    Hibernate uses a powerful query language (HQL) that is similar in appearance to SQL.
    Compared with SQL, however, HQL is fully object-oriented and understands notions like inheritance, polymorphism and association.

    Chapter 17. Criteria Queries

    Hibernate features an intuitive, extensible criteria query API.

    Chapter 18. Native SQL

    You can also express queries in the native SQL dialect of your database. This is useful if you want to utilize database-specific features such as query hints or the CONNECT keyword in Oracle. It also provides a clean migration path from a direct SQL/JDBC based application to Hibernate.

    Hibernate allows you to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic