CODEWITHSUNDEEP
javajakarta-eejpa
z22
z22

Reputation: 10083

How to write a dynamic JPA query with a join?

I wish to implement a dynamic query in JPA, something like:

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

I wish to bind the result to a datatable, but as you can see I want to return a collection consisting of 2 tables - orderStock and tradeStock. So how do I access this collection in my datatable? And how do I set the parameters of the resulting dynamic query?

Upvotes: 1

Views: 2752

Answers (2)

Kirill Dubovikov
Kirill Dubovikov

Reputation: 1486

  • For parameterizing your query see this article.

  • A collection returned by getOrderReportByUserName method will contain Object[2] elements. The first element will be an instance of OrderStock object and the second will be an instance of TradeStock object. To persist recieved entities you should use persist or merge methods of EntityManager class.

  • If you are using JPA 2.0 you can use Criteria API to construct type-safe queries. It can be much better than hardcoding JPQL queries.

Upvotes: 1

Pranalee
Pranalee

Reputation: 3409

You haven't created any variable with name tradeStock in ur code, but i assume that variable 'c' in 3rd last line is your 'tradeStock'. So if you want to return 2 lists from your class, you should create a new class say Stock. Stock will have 2 member lists i.e. tradeStock and overStock and getter/setter for them. In this method create an instance of Stock.when you fetch resultList from query set it in Stock and return Stock from method.

Upvotes: 0

Related Questions