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=?
No more Blub for me, thank you, Vicar.
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.
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
No more Blub for me, thank you, Vicar.
Known is a drop. Unknown is an ocean.
Did you see how Paul cut 87% off of his electric heat bill with 82 watts of micro heaters? |