jpa avoid query

i have the next class

@Entity    
@Table(name = "table_order")    
@IdClass(OrderPK.class)    
public class Order {    
   /** Unique identifier of the currency code in which the transaction was negociated. *开发者_如何学Python/

@Column(name = "TRADECURRE", nullable = false, length = 5)    
  private String tradeCurrencyCode;

/** Currency Entity for Trade. */    
  @ManyToOne(optional = true, fetch = FetchType.LAZY)    
  @JoinColumns({     
     @JoinColumn(name = "TRADECURRE", referencedColumnName = "codigo", updatable = false, insertable = false) })    
  private Currency currencyEntity;

.. here get and sets    
}

then execute the next query:

StringBuilder jpaQuery = new StringBuilder();

StringBuilder whereClause = new StringBuilder();

jpaQuery.append("SELECT o, o.currencyEntity");

List orders = query.getResultList();

in this point the log of jpa show 2 querys executed, one to order table and other to Currency table.

bellow i write the next code (in the same class and method of the previous code)

for (Object orderElement : orders) {

  int indexArray = 0;
  Object[] orderArray = (Object[]) orderElement;
  Order orderEntity = (Order) orderArray [indexArray++];
  orderEntity.setCurrencyEntity((Currency) orderArray [indexArray++]);

}

When the line

orderEntity.setCurrencyEntity((Currency) orderArray [indexArray++]);

is executed, the query over the table currency is executed once again at database. I need avoid this query to fix some performance problems, i have all the data in the orderArray.

i'm using eclipselink 1.1

thanks in advance

This is happening because you haven't told JPA to pre-fetch the currentEntity in the initial select (although I think that's what you were trying to do with SELECT o, o.currencyEntity). As a result, JPA has to fetch the currentEntity each time round the loop, and it's a real performance killer.

The way to do this with JPA is with a fetch join (documented here). You'd write your query like this:

SELECT o from Order o LEFT JOIN FETCH o.currencyEntity

This also makes it easier to navigate the result set than with SELECT o, o.currencyEntity, since you'll only have a single entity returned, with its currencyEntity property intact:

List<Order> orders = query.getResultList();
for (Order order : orders) {
    // fully-populated, without requiring another database query
    Currency ccy = order.getCurrentEntity(); 
}