I have three related tables like below:
+-------------+---------+------------+
| customer_id | name | surname |
+-------------+---------+------------+
| 1 | Jan | Bielecki |
| 2 | Adam | Bielen |
.....
+----------+--------+---------------------+-------------+
| order_id | amount | date | customer_id |
+----------+--------+---------------------+-------------+
| 1 | 10.23 | 2017-02-15 00:00:00 | 1 |
| 2 | 20.56 | 2017-02-16 00:00:00 | 1 |
| 3 | 30.57 | 2017-02-17 00:00:00 | 2 |
| 4 | 40.52 | 2017-02-18 00:00:00 | 2 |
| 5 | 50.30 | 2017-02-19 00:00:00 | 1 |
.....
+-----------------+-----------+------------+----------+
| order_detail_id | item_name | item_price | order_id |
+-----------------+-----------+------------+----------+
| 1 | item 1 | 2.00 | 1 |
| 2 | item 2 | 2.50 | 1 |
| 3 | item 3 | 3.00 | 1 |
| 4 | item 4 | 4.00 | 2 |
| 5 | item 5 | 5.50 | 2 |
| 6 | item 6 | 7.60 | 3 |
| 7 | item 7 | 5.00 | 3 |
| 8 | item 8 | 3.00 | 4 |
| 9 | item 9 | 7.00 | 4 |
| 10 | item 10 | 8.00 | 4 |
| 11 | item 11 | 2.00 | 5 |
| 12 | item 12 | 2.50 | 5 |
.....
Firstly i'm fighting with connect first and second table. For connect surnames with sum of amount.
I'm trying like this:
select sum(o.amount) as totalSum
from Order as o,
Customer as c
join c.surname as surname
where c.orders:=o.customer
group by o.customer
order by sum(o.amount) desc
with changing to many ways this section: where c.orders:=o.customer
The most common error is NullPointerException.
Before done this in SQL: Table customer_id <-> total_amount
SELECT customer_id,
SUM(amount) as total_amount,
COUNT(amount) as orders_quantity
FROM softhis_db.orders
GROUP BY customer_id;
Table customer_id <-> 3 most exp. orders + dates
SELECT orders.customer_id, orders.amount, orders.date
FROM orders_details
RIGHT JOIN orders
ON orders.order_id = orders_details.order_id
ORDER BY amount DESC
LIMIT 3;
Customer:
@Entity
@Table(name = "customers")
public class Customer {
@Id
@Column(name = "customer_id")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(name = "name", length = 50)
private String name;
@Column(name = "surname", length = 50)
private String surname;
@OneToMany(mappedBy = "customer")
private Set<Order> orders = new HashSet<>();
Order:
@Entity
@Table(name = "orders")
public class Order {
@Id
@Column(name = "order_id")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(name = "date")
private Date date;
@Digits(integer = 5, fraction = 2)
@Column(name = "amount")
private BigDecimal amount;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "customer_id")
private Customer customer;
@OneToMany(mappedBy = "order")
private Set<OrderDetail> ordersDetails = new HashSet<>();
OrderDetail:
@Entity
@Table(name = "orders_details")
public class OrderDetail {
@Id
@Column(name = "order_detail_id")
@GeneratedValue(strategy = GenerationType.AUTO)
private Lon id;
@Column(name = "item_name", length = 50)
private String itemName;
@Digits(integer = 5, fraction = 2)
@Column(name = "item_price")
private BigDecimal itemPrice;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "order_id")
private Order order;
The point is how to do this properly in HQL ? Next step will be searching by surname and getting result like 'my target'.
My target is:
+---------+---------------+
| surname | sum of amount |
+---------+---------------+
|Bielecki | 150.40 |
|Bielen | 130.34 |
......
+-----------------------------------+--------------------+
| surname | 3 most expensive orders | date |
+-----------------------------------+--------------------+
|Bielecki | 120.23 |2017-02-15 00:00:00 |
|Bielecki | 80.20 |2017-02-18 00:00:00 |
|Bielecki | 20.20 |2017-02-19 00:00:00 |
+---------+-------------------------+--------------------+
|Bielen | 190.23 |2017-02-15 00:00:00 |
|Bielen | 80.20 |2017-02-18 00:00:00 |
|Bielen | 20.20 |2017-02-19 00:00:00 |
+---------+-------------------------+--------------------+
.....
I figured out how to translate those SQL queries to HQL. In order:
1.
select o.customer.surname, sum(o.amount) as s from Order as o group by o.customer
2.
select o.customer.surname, o.amount, o.date from Order as o, OrderDetail as od