java如何使用JPA的JPQL连接两个表

Question

好吧，假设我有两张桌子：

产品历史

product_id  last_update_date
-------------------------------
1           12JAN2012
1           13JAN2012
1           14JAN2012
2           13JAN2012
3           12JAN2012
4           12JAN2012

产品

product_id, product_name
-------------------------
1           Orange
2           Mango
3           Apple
4           Strawberry

产品JPA对象如下所示：

@Entity
@Indexed
@Table(name="Products")
public class Product extends AbstractDomainObject {

    private static final long serialVersionUID = 8619373591913330663L;

    private String product_id;
    private String product_name;

    @Id 
    @Column(name="product_id")
    public Long getProduct_id() {
        return product_id;
    }

    public void setProduct_id(String Product_id) {
        this.Product_id = Product_id;
    }
}

我需要编写一个JPQL查询，以便能够获得在两个日期之间更新的产品列表。在普通sql中，我会执行以下操作：

-- assuming startDate is 13JAN2012 and endDate is 14JAN2012

Select product_name from products where product_id in 
(Select product_id where max(last_update_date) betwen startDate and endDate);

如何编写JPA的等效查询？我试过这样的东西，但运气不好

String queryString = "from Products p, Product_History h where " +
                "product_id = :newId " +
                "and product_id in ( " +
                "Select product_id from product_history + +
                            "where max(last_update_date) between max:startDate and :endDate);

    Query query = entityManager.createQuery(queryString);   
    query.setParameter("newId", 1);
    query.setParameter("startDate", '13JAN2012');   
    query.setParameter("endDate", '14JAN2012'); 

    return query.getResultList();   

Answer 1

您必须使用@OneToMany连接在product中惰性地获取product_histroy，然后使用：

select distinct p from Products p 
where p.productHistory.lastUpdateDate > ? and p.productHistory.lastUpdate < ?