如何在 Hibernate JPA 中使用 EntityManager @FilterJoinTable？

Question

我想限制来自 OneToMany 关系的数据。在我的程序中，老板帐户可以查看给定月份和年份内所有有订单的公司。公司类可以计算这些订单的收入。我使用了@FilterJoinTable，但由于我必须使用EntityManager，所以我不能再这样做了。这是我与 Session 对象一起使用的代码。

Order 类

@Entity(name="order")
public class Order implements Serializable {
private static final long serialVersionUID = 1L;

private Company company;
private int month;
private int year;
[...]

@JoinColumn(name = "company_idcompany", referencedColumnName = "idcompany")
@ManyToOne(optional = false, fetch = FetchType.EAGER)
public Company getCompany() {
    return company;
}

public void setCompany(Company company) {
    this.company = company;
}
[...]

Company 类

@Entity(name="company")
@FilterDef(name="orderFilter", parameters = {
                       @ParamDef(name = "month", type = "int"),
                       @ParamDef(name = "year", type = "int")
                       })
public class Company implements Serializable 
{
private static final long serialVersionUID = 1L;

private List<Order> orderList = new ArrayList<Order>();
[...]

@OneToMany(
        fetch = FetchType.LAZY,
        mappedBy="company"
)
@JoinTable(
        name = "order",
        joinColumns = { @JoinColumn(name = "idcompany") },
        inverseJoinColumns = { @JoinColumn(name = "idorder") }
)
@FilterJoinTable(name="orderFilter", condition=":month = month and :year = year")
public List<Order> getOrderList() {
    return orderList;
}
[...]

get 方法如下所示

public List<Company> get(int month, int year) throws AdException
{
    try
    {
        begin();

        //will get only the orders in given month and year
        Filter filtr = getSession().enableFilter("orderFilter");

        filtr.setParameter("month", month);
        filtr.setParameter("year", year);

        //this criteria didn't work well so I've added a loop below 
        Criteria crit = getSession().createCriteria(Company.class);
        crit.add(Restrictions.isNotEmpty("companyList"));


        List<Company> companys = crit.list();

        Iterator iter = companys.iterator();

        //throwing out the companys that have no orders in given month and year
        while(iter.hasNext())
        {
            Company com = (Company)iter.next();
            if(com.getOrderList().isEmpty())
            {
                companys.remove(com);
                iter = companys.iterator();
            }
        }

        getSession().disableFilter("orderFilter");
        commit();

        return companys;
    }
    catch( HibernateException e )
    {
        rollback();
        throw new AdException("ex message",e);
    }
 }

get 方法仅返回在给定月份和年份有订单的公司。每个公司的 orderList 仅包含给定月份和年份的订单。我知道 get 方法并不漂亮，但它有效。您能告诉我如何解决这个问题吗？我一无所知。

已解决感谢 Joshua Davis

   Query q = em.createQuery("SELECT DISTINCT o.company FROM Order z WHERE o.month=:month AND z.year=:year");

            q.setParameter("month", month);
            q.setParameter("year", year);

            List<Company> companys = q.getResultList();

            for(Company com : companys)
            {
                Query q2 = em.createQuery("from Order where month = :month and year = :year and company.idCompany = :id");
                q2.setParameter("month", month);
                q2.setParameter("year", year);
                q2.setParameter("id", com.getIdCompany());

                com.setOrderList(q2.getResultList());
            }

Answer 1

一种更简单的方法是：

em.createQuery("select o from Order o " +
        "where o.company.id=:id and o.month=:m and o.year=:y")
        .setParameter("id",someCompany.getId())
        .setParameter("y",year)
        .setParameter("m",month)
        .getResultList();

本质上，您正在使用对象图导航来解决使用普通 JPA 查询即可轻松解决的问题。

如果您需要修改列表中的订单，这将透明地工作，因为查询将返回持久实例。如果您需要添加或删除订单，那么您可能需要访问 Firma（公司）中的集合。