How to join two tables using JPA's JPQL

Question

Ok say I have two tables:

Product_History

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

Products

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

The product JPA object looks like this:

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

I need to write a JPQL query so that I can get the list of products that were updated between two dates. In normal sql I would do something like this:

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

How can I write the equivalent query for JPA? I have tried something like this but with no luck.

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

You have to do a lazy fetch of product_histroy in Product using a @OneToMany join, then use:

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