How to Optimise a JPA Query

Question

I have three entities as follows:

public class EntityA
{
    private Long id;
    //Getters and setters
}

public class EntityB
{
    private Long id;
    private EntityA entitya;
    //Getters and setters
}

public class EntityC
{
    private Long id;
    private BigDecimal amount;
    private EntityB entityb;
    //Getters and setters
}

Now, given an Instance of EntityA, i want to get a list of EntityC. I have two options available to me currently. I don't know which one is more optimized. The options are:

1.
select c from EntityC c where c.entityb in (select b from EntityB b where b.entitya = :entitya)

2. Add a new property to EntityB

private Set<EntityC> entityCCol;

@OneToMany(mappedBy="entityb")
public Set<EntityC> getEntityCCol()
{
   return entityCCol;
}

select b from EntityB a join fetch a.entityCCol b

Which of these two queries is easier and optimized?

Answer 1

It depends on the size of the collection. For small collections I would use the relationships in the object model. More from a design/usability perspective than performance, it is more object-oriented. I wouldn't join fetch it though, just access the model normally. You should probably also have a relationship from A to B to make your model more useful.

For the query in #1, your query is not very efficient using the sub-selects, just use a join,

select c from EntityC c where c.entityb.entitya = :entitya

Answer 2

i think all the queries with interpreted into SQL queries, these are only different styles, you do not have to think about it. all the queries will interpreted into theta style. I do not think, there is a performance difference, it is only a personal choice. here is a very fresh article about SQL query style MySQL joins: ON vs. USING vs. Theta-style , I hope this link helps you somehow.