Hibernate Join query to fetch the data in Java?

Question

I am new to Hibernate and Java server side. I have mapped MySQL table in Java using javax.persistence annotation. I have two tables:

order_header
In this table, I have
order_number(primary_key),total_order_value,delivery_charge,order_time columns.

order_details
In this table, I have
order_number,product_code,price columns.

In order_header the order_number is primary key.

For Each order_number there are n number of products in order_details table.
How can I combine both these tables?
What is the Query to get the value like Final Output

For Example :

Order Headers Table

Order_number     total_order_value     delivery_charge      order_time
----------------------------------------------------------------------
   25                2550                     50             03:20:50
   36                350                      50             03:20:50
----------------------------------------------------------------------

Order_headers.java

@Entity
@Table(name = "order_headers")
public class Order_headers {

@Id @GeneratedValue
@Column(name = "order_number")
private int order_number;

@Column(name = "total_order_value")
private double order_value;

@Column(name = "delivery_charge")
private String delivery_charge;

@Column(name = "order_time")
private String order_time;

//here getter and setter methods
}

Order_details

Order_number     product_code     price
---------------------------------------
    25               235          1500
    25               240          1000
    36               50           40
    36               96           60
    36               150          200

Order_details.java

@Entity
@Table(name = "order_details")
public class Order_details {

@Column(name = "Order_number")
private int Order_number;

@Column(name = "product_code")
private String product_code;

@Column(name = "price")
private String price;

//here getter and setter methods
}

I don't know how to write the combined query in Hibernate to get the details like final output.

Final Output

Order_number     total_order_value     product_value   delivery_charge      order_time
---------------------------------------------------------------------------------------
   25                2550                   2500             50             03:20:50
   36                350                    300              50             03:20:50
---------------------------------------------------------------------------------------

Please help me to solve this issue.

You dont need to, hibernate will do this for your. Change type of Order_number in Order_details.java to Order_headers and change field to OneToOne relation. When quering the header obect hibernate will automaticly join the details for you. — Rene M., Mar 18 '15 at 10:12
Could you elaborate more on what do you mean by "How to combine and calculate the only products values from order_details table"? And how do you calculate the value for `product_value` in the Final Output example? — S. Pauk, Mar 18 '15 at 10:24
@SergeyPauk I want the Hibernate query to get **Final output**. — Yugesh, Mar 18 '15 at 10:35
Clear, what about the second question, how do you calculate product_value from Final output (could your post the SQL query)? I would assume that it's a sum of prices but in this case order 36 sum of prices should end up as 200 not 300. — S. Pauk, Mar 18 '15 at 10:41
@SergeyPauk am entered wrongly when am type.Whats is query to generate the result set like final output. — Yugesh, Mar 18 '15 at 10:48
Possible duplicate of http://stackoverflow.com/questions/15109109/sql-sum-group-by-two-tables — Junaid, Mar 18 '15 at 10:56

score 2 · Accepted Answer · answered Mar 18 '15 at 11:40

2

you have to add relationship between Order_headers and Order_details one to many in hibernate you can achieve this by following code in your Order_headers.java

@OneToMany(mappedBy = "Order_number", fetch = FetchType.LAZY)
private List<Order_details> orderDetailList;

//add getter and setter methods for orderDetailList.

once you add this code now you can write Criteria like

Criteria criteria = session.createCriteria(Order_headers.class);
criteria.setFetchMode("Order_details",FetchMode.JOIN);
List<Order_headers> list = criteria.list();

or you can Writer HQL like

session.createQuery("from Order_headers cont join cont.Order_details where cont.id=1");

or you can also write SQL Query in case you want to writer SQL Query no need of adding property orderDetailList in Order_headers.java here is SQL

session.createSQLQuery("SELECT * FROM Order_headers order JOIN Order_details details ON order.order_number = details.order_number");

answered Mar 18 '15 at 11:40

Mitul Sanghani

230
1
11

it shows error `No identifier specified for entity: model.Order_details` – Yugesh Mar 18 '15 at 12:01
which option you are using from above three.? i mean Criteria,HQL or SQL.? – Mitul Sanghani Mar 18 '15 at 12:31
Thanks.Just now only am also identify that one.How to give where condition in `Criteria`. Because some columns have null values, so it shows error like `Null value was assigned to a property of primitive type setter` – Yugesh Mar 18 '15 at 12:36
1

By the way every class defined as Entity with @Entity annotation, needs an '@Id' or '@EmbeddedId' property. – Mitul Sanghani Mar 18 '15 at 12:39

Hibernate Join query to fetch the data in Java?

1 Answers1