Igor  Masternoy Igor Masternoy - 3 months ago 15
Java Question

Hibernate querying

I'm sorry for my maybe foolish question. I have products and orders tables (with many-to -many relationship), also i have an user table. And now I want to get the product count by user_id and by special field "order_satus". I can make two queries get order by special criteria and then get size of product in order. But this is not optimal at all. When i use JDBCTemplate I did a lot of joins and get only one query.

Here are my entities:

@Entity
@Table(name = "shop.order")
public class Order {
@Id
@Column(name = "order_id")
@GeneratedValue(strategy = GenerationType.AUTO)
private long orderId;

private long user_id;

@Column(name = "databegin")
private Date dateBegin;

@Column(name = "dataend")
private Date dateEnd;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "user_id", insertable = false, updatable = false)
private User user;

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "order_product", joinColumns = { @JoinColumn(name = "order_id") }, inverseJoinColumns = { @JoinColumn(name = "product_id") })
private List<Product> products;
}


Product Entity

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

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int product_id;


@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "order_product", joinColumns = { @JoinColumn(name = "product_id") }, inverseJoinColumns = { @JoinColumn(name = "order_id") })
private List<Order> order;

public List<Order> getOrder() {
return order;
}

public void setOrder(List<Order> order) {
this.order = order;
}

@Column
@NotBlank
private String name;

@Column
@Max(value = 250)
private String descr;

@Column
@Max(value = 250)
private String manufacturer;

@Column
private Double price;

@Column
private Byte[] barcode;

@Column
private Byte[] picture;

@ForeignKey(name = "category_id")
private int category_id;

Answer

As you gave sql query..

'select count(*) from produtc p join order ord on ord.id = ? and ord.status = ?' – Igor Masternoy

And according to the Entity structure you gave, the HQL will be..

select ord.products productList from Order ord where ord.id=? and ord.status=?

This query will return you list of products (List<Product> products) and then you can get the count by java code i.e. productList.size(); This size is the product count you need based on order id and order status you will pass as parameter and also you can append user.id in where cause to filter your result as per user.

This is productList as per your need..

Query query = getSession().createQuery("select ord.products productList from Order ord where ord.id=:orderID and ord.status=:orderStatus");
query.setInteger("orderID", orderIDParameter);
query.setString("orderStatus", orderStatusParameter);
List<Product> productList = (List<Product>) query.list();
Integer productCount = productList.size();

This productCount is your product count you need.

Comments