Issue to create jpa - hibernate inner join query

Question

i am new to jpa hibernate, i have an inner join query in sql but not sure how to create the same query in jpa-hibernate syntax:

Basically i have 3 tables:

ORDERTBL(ORDERID(PK),ORDERDATE,ORDERSTATUS), PRODUCT(PRODUCTID(PK),PRODUCT_NAME), ORDERLINKPRODUCTS(ORDERID(PK),PRODUCTID(FK),QUANTITY,ORDERLINKPRODUCTID(FK))

I want to get all the product name from the products tables for a specific order which i should query from ORDERLINKPRODUCT since it contains all the orders.

Please find my Products class below:

@Table(name = "PRODUCTS")
@Entity
public class Products { /**
 *
 */
private static final long serialVersionUID = -3882913429956818022L;

@Column(name = "PRODUCTID")
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int id;
@Column(name = "PRODUCT_NAME")
private String productName;

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getProductName() {
    return productName;
}

public void setProductName(String productName) {
    this.productName = productName;
}

}

Please find my Orderclass below:

    @Table(name = "ORDERTBL")
@Entity
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ORDERID")
private Long id;

@Column(name = "ORDERDATE")
private Date orderdate;

@Column(name = "ORDERSTATUS")
private String orderSatus;

public String getOrderSatus() {
    return orderSatus;
}

public void setOrderSatus(String orderSatus) {
    this.orderSatus = orderSatus;
}

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public Date getOrderdate() {
    return orderdate;
}

public void setOrderdate(Date orderdate) {
    this.orderdate = orderdate;
}

}

Please find my ProductOder class below:

    @Entity
@Table(name = "ORDERLINKPRODUCTS")
public class ProductOder implements Serializable {

/**
 *
 */
private static final long serialVersionUID = 8420487251416906739L;

@Column(name = "ORDERLINKID")
@Id
private int orderLinkId;
@Column(name = "ORDERID")
@GeneratedValue(strategy = GenerationType.AUTO)
private int orderId;
@Column(name = "PRODUCTID")
private int productId;
@Column(name = "QUANTITY")
private int quantity;

public int getOrderLinkId() {
    return orderLinkId;
}

public void setOrderLinkId(int orderLinkId) {
    this.orderLinkId = orderLinkId;
}

public int getOrderId() {
    return orderId;
}

public void setOrderId(int orderId) {
    this.orderId = orderId;
}

public int getProductId() {
    return productId;
}

public void setProductId(int productId) {
    this.productId = productId;
}

public int getQuantity() {
    return quantity;
}

public void setQuantity(int quantity) {
    this.quantity = quantity;
}

}

Please find below my sql query that i want to create in JPA-Hibernate:

SELECT prod.PRODUCT_NAME 
    FROM ORDERTBL ord 
    INNER JOIN ORDERLINKPRODUCTS link ON ord.ORDERID = link.ORDERID
    INNER JOIN products prod ON link.PRODUCTID = prod.PRODUCTID  
    WHERE ord.ORDERID = 1

Please find below my method with no implementation that will contains the jpa-hibernate query:

@Override
@SuppressWarnings("unchecked")
public List<Order> getOrderDetailsById(int id) {
    // TODO Auto-generated method stub
    //String hql = "from Person p, where p.firstName = :firsName and p.password //password";
    //return entityManager.createQuery("select o from Products o")
            //.getResultList();
}

Any suggestion how to do it please?

Thanks in advance.

Re-read the chapters about associations in the Hibernate documentation. If an entity contains the ID of another entity, something's wrong: you should have a ManyToOne/OneToMany association instead. You're missing almost everything Hibernate is and can do if you don't use associations. — JB Nizet
thanks a lot,i did not know, i will read more about association. — user1999453

Erik B Erik B · Accepted Answer · 2013-01-28T15:54:55

Try changing you entities to:

Order

@Entity
@Table(name = "ORDER")
public class Order {

    @Id
    @Column(name = "ORDERID")
    private Long id;

    @Column(name = "ORDERDATE")
    private Date orderdate;

    @Column(name = "ORDERSTATUS")
    private String orderStatus;

    @OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
    private List<OrderItem> items;
}

OrderItem

@Entity
@Table(name = "ORDERITEM")
public class OrderItem {

    @Id
    @Column(name = "ORDERITEMID")
    private Long id;

    @ManyToOne
    @JoinColumn(name = "ORDERID")
    private Order order;

    @ManyToOne
    @JoinColumn(name = "PRODUCTID")
    private Product product;

    @Column(name = "QUANTITY")
    private int quantity;
}

Product

@Entity
@Table(name = "PRODUCT")
public class Product {

    @Id
    @Column(name = "PRODUCTID")
    private Long id;

    @Column(name = "NAME")
    private String name;
}

And the query:

public List<OrderItem> getOrderDetailsById(Long id) {
    Product product = entityManager.find(Product.class, id);
    return product.getOrderItems();
}

public void showProductsInOrder(Long id) {
    List<OrderItem> items = getOrderDetailsById(id);
    for (OrderItem item: items) {
        Product product = item.getProduct();
        System.out.println("Product: " + product.getName() + ", Quantity: " + product.getQuantity());
    }

Database:

ORDER (ORDERID (PK), ORDERDATE, ORDERSTATUS)
ORDERITEM (ORDERITEMID (PK), ORDERID (FK), PRODUCTID (FK), QUANTITY)
PRODUCT (PRODUCTID (PK), NAME)

It's untested, but I hope it will work.

Issue to create jpa - hibernate inner join query

1 Answers