Hibernate Community • View topic - Query related entities with EJB QL (SQL performance)

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Query related entities with EJB QL (SQL performance)

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

DanielYWoo

Post subject: Query related entities with EJB QL (SQL performance)

Posted: Tue Jan 13, 2009 8:18 am

Newbie

Joined: Tue May 06, 2008 4:19 am
Posts: 6

I am using JPA with Hibernate3.3.x, I have a simple OneToMany mapping, eg,

Code:

Order uses OneToMany annotation to define relationship with its OrderLineItems

Surely I can do a query to get an Order along with all its OrderLineItems shi way

Code:

    List<Order> orderList = em.createQuery(
            "select o from Order o where o.id=:id").
            setParameter("id", orderId).getResultList();

But how can I only want the OrderLineItem list? Can I use the following code?

Code:

    List<OrderLineItem> itemList = em.createQuery(
            "select i from OrderLineItem i inner join Order o where o.id=:id").
            setParameter("id", orderId).getResultList();

Last edited by DanielYWoo on Tue Jan 13, 2009 9:17 am, edited 1 time in total.

Top

DanielYWoo

Post subject:

Posted: Tue Jan 13, 2009 8:30 am

Newbie

Joined: Tue May 06, 2008 4:19 am
Posts: 6

I think I can make it by

Code:

    return em.createQuery(
   "select i from OrderLineItem i where i.order.id = :orderId").
   setParameter("orderId", orderId).getResultList();

But that causes Hibernate execute an unnecessary SQL as below (the first one is not necessary)

Code:

select *
    from orderlineitem where orderlineitem.orderid=?
        
select *
    from orderlineitem
    where orderlineitem.order_id=? order by ...

I can also make it by another query

Code:

    return em.createQuery(
   "select o.orderLineItemList from Order o where o.id = :orderId").
   setParameter("orderId", orderId).getResultList();

But this also gives an unnecessary SQL like this:

Code:

    select *
    from oder inner join orderlineitem on order.id=orderlineitem.orderid
    where order.id=?
 
    select *
    from orderlineitem
    where orderlineitem.orderid=? order by ...

How can I get only one query like

Code:

select * from orderlineitem where orderlineitem.orderid=?

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum