Welcome‎ > ‎Java‎ > ‎

005 - JPA Projective Query in EL & Hibernate

I decided to continue playing around with Hibernate and EclipseLink. I would like to try projective query. This is the query in form SELECT NEW FlightNumber(f.number, f.airline) from Flight f WHERE f.departure.code = 'SYD'.

Here is the data:


 select * from FLIGHT


Here is the FlightNumber class:

public class FlightNumber {

  private String number;
  
  private String airline;


  public FlightNumber(String number, String airline) {
    super();
    this.number = number;
    this.airline = airline;
  }
  public FlightNumber(String number) {
    this.number = number;
  }

  public void setNumber(String number) {
    this.number = number;
  }

  public String getNumber() {
    return number;
  }

  public void setAirline(String airline) {
    this.airline = airline;
  }

  public String getAirline() {
    return airline;
  }

}

which is a view of a Flight class as follow:

public class Flight {

  private String number;
    
  private String airline;
  
  @ManyToOne
  @JoinColumn(name="DEPARTURE")
  private Airport departure;
  
  @ManyToOne
  @JoinColumn(name="ARRIVAL")
  private Airport arrival;
  
  @Id private int id;
  
  public String getNumber() {
    return number;
  }
  
  public void setNumber(String number) {
    this.number = number;
  }
  
  public int getId() {
    return id;
  }
  
  public void setId(int id) {
    this.id = id;
  }

  public void setDeparture(Airport departure) {
    this.departure = departure;
  }

  public Airport getDeparture() {
    return departure;
  }

  public void setArrival(Airport arrival) {
    this.arrival = arrival;
  }

  public Airport getArrival() {
    return arrival;
  }

  public void setAirline(String airline) {
    this.airline = airline;
  }

  public String getAirline() {
    return airline;
  }

  @Override
  public String toString() {
    return "Flight [number=" + number + ", airline=" + airline
        ", departure=" + departure + ", arrival=" + arrival + ", id="
        + id + "]";
  }
}

Start with the most basic one, Query embedded in the code:
     Query query = em.createQuery(
          "SELECT new com.arizal.flight.FlightNumber" +
          "(f.number, f.airline) " +
           " FROM Flight f WHERE f.departure.code=:code").
          setParameter("code""SYD");
      List storedFlights = query.getResultList();
      for (Iterator it = storedFlights.iterator(); it.hasNext();) {
        FlightNumber fNumber = (FlightNumber)it.next();
        System.out.println(fNumber.getAirline() " " + fNumber.getNumber());
      }

It works great. But of course named query would be better:
First, the definition (in Flight.java class):
@NamedQuery(name="Flight.findByDeparture"
        query="SELECT new com.arizal.flight.FlightNumber(f.number, f.airline) " +
            "FROM Flight f WHERE f.departure.code=:code")

Then the code:

      Query query = em.createNamedQuery(
          "Flight.findByDeparture").
          setParameter("code""SYD");
      List storedFlights = query.getResultList();
      for (Iterator it = storedFlights.iterator(); it.hasNext();) {
        FlightNumber fNumber = (FlightNumber)it.next();
        System.out.println(fNumber.getAirline() " " + fNumber.getNumber());
      }

It works perfectly. I continue to improve by introducing typed query instead:

      TypedQuery<FlightNumber>query = em.createNamedQuery(
          "Flight.findByDeparture",
          FlightNumber.class).
          setParameter("code""SYD");
      List<FlightNumber> storedFlights = query.getResultList();
      for (FlightNumber fn : storedFlights) {
        System.out.println(fn.getAirline() " " + fn.getNumber());
      }




Unfortunately, I got IllegalArgumentException: Cannot create TypedQuery for query with more than one return. 

Dommage. Changing from named query to embedded one didn't solve the problem.

Changed to EclipseLink 2.0.0 didn't solve the problem either. However, EclipseLink 2.1.0 did solve the problem.

After checking the JIRA Hibernate, the problem is already reported couple days ago:

July, 12, 2010.


Comments