java - hibernate 查询

Question

我很抱歉我的问题可能很愚蠢。我有产品和订单表(具有多对多关系)，还有一个用户表。现在我想通过 user_id 和特殊字段“order_satus”获取产品数量。我可以让两个查询按特殊条件获取订单，然后按顺序获取产品的大小。但这根本不是最佳的。当我使用 JDBCTemplate 时，我进行了很多连接，但只得到一个查询。

这是我的实体:

@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;
}

产品实体

 @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 1

当你给出sql查询时..

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

根据您提供的实体结构，HQL 将是..

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

此查询将返回产品列表 ( List<Product> products )，然后您可以通过 java 代码获取计数，即 productList.size();此尺寸是您需要的产品数量，基于您将作为参数传递的订单 ID 和订单状态，您也可以附加 user.id在哪里导致根据用户过滤您的结果。

这是根据您的需要的产品列表..

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();

此productCount是您需要的产品数量。