java - 如何编写带有联接的动态 JPA 查询？

Question

我希望在 JPA 中实现动态查询，例如:

public Collection getOrderReportByUserName(String userName, Integer scripID, String orderStatus, String orderType) 
{     
    String strQuery = "Select o,t from OrderStock o,TradeStock t where o.userName.userName = "+ userName +" and t.userName.userName = "+ userName;
    if(scripID > 0)
    {
        strQuery += " and o.scripID.scripID = " + scripID;
    }
    if(orderStatus != null)
    {                        
        if(orderStatus.equals("Executed"))
        {
            strQuery += " and o.OrderID = t.OrderID and o.OrderStatus = " + orderStatus;
        }
        else
        {
            if(scripID > 0 && orderType != null)
            {
                String sQuery = "Select o from OrderStock o where o.UserName = " + userName +" and o.ScripID = "+ scripID+" and o.BuySell = "+ orderType;
                Collection<OrderStock> os = em.createQuery(sQuery).getResultList();
                Iterator it = os.iterator();
                Boolean ok = false;
                while(it.hasNext())
                {
                    OrderStock osObj  = (OrderStock)it.next();
                    Integer pending = osObj.getPendingQuantity();
                    Integer order = osObj.getOrderQuantity(); 
                    if(pending > 0 && ((order-pending) != 0))
                    {
                        ok = true;
                        break;
                    }
                }
                if(ok == true)
                {
                    strQuery += " and o.OrderID = t.OrderID and o.OrderStatus = " + orderStatus;
                }
                else
                {
                    strQuery += " and o.OrderStatus = " + orderStatus;
                }
            }
        }
    }
    if(orderType != null)
    {
        strQuery += " and o.BuySell = " + orderType;
    }
    Collection c = em.createQuery(strQuery).getResultList();
    return c;
}

我希望将结果绑定(bind)到数据表，但正如您所看到的，我想返回一个由 2 个表组成的集合 - orderStock 和 tradeStock。那么如何在我的数据表中访问这个集合呢？如何设置生成的动态查询的参数？

Answer 1

• 要参数化您的查询，请参阅 this article .

• getOrderReportByUserName 方法返回的集合将包含 Object[2] 元素。第一个元素将是 OrderStock 对象的实例，第二个元素将是 TradeStock 对象的实例。要保留收到的实体，您应该使用 EntityManager 类的 persist 或 merge 方法。

• 如果您使用的是 JPA 2.0，则可以使用 Criteria API构造类型安全的查询。它比硬编码 JPQL 查询要好得多。