Just to remember a simple example of JPA 2 Criteria API usage...

jpa2-criteria-api-sample.md

• to perform a simple SELECT * FROM PLANE;

		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<Plane> criteria = cb.createQuery(Plane.class);
		Root<Plane> plane = criteria.from(Plane.class);
		criteria.select(plane);
		List<Plane> planes = em.createQuery(criteria).getResultList();

• to join 3 tables

		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<Flight> flightCriteria = cb.createQuery(Flight.class);
		Root<Flight> flight = flightCriteria.from(Flight.class);

		Join<Flight, Route> flightRoutes = flight.join("route");
		Join<Route, Airport> originAirport = flightRoutes.join("airport1"); 
		Join<Route, Airport> destinationAirport = flightRoutes.join("airport2");
		
		ParameterExpression<String> origin = cb.parameter(String.class, "originCode");
		ParameterExpression<String> destination = cb.parameter(String.class, "destinationCode");
		
		flightCriteria.select(flight)
			.where(cb.and(
					cb.equal(originAirport.get("airportcode"), origin),
					cb.equal(destinationAirport.get("airportcode"), destination)))
			.orderBy(cb.desc(flight.get("departuredate")));
		
		TypedQuery<Flight> flightsQuery = em.createQuery(flightCriteria);
		flightsQuery.setParameter("originCode", "SFO");
		flightsQuery.setParameter("destinationCode", "JFK");
		
		List<Flight> flights = flightsQuery.getResultList();

generates the following native SQL:

Hibernate: 
    select
        flight0_.flightid as flightid1_1_,
        flight0_.arrivaldate as arrivald2_1_,
        flight0_.departuredate as departur3_1_,
        flight0_.firstclassprice as firstcla4_1_,
        flight0_.flightnumber as flightnu5_1_,
        flight0_.planeid as planeid8_1_,
        flight0_.price as price6_1_,
        flight0_.routeid as routeid9_1_,
        flight0_.seatstaken as seatstak7_1_ 
    from
        JBTRAVEL.Flight flight0_ 
    inner join
        JBTRAVEL.Route route1_ 
            on flight0_.routeid=route1_.routeid 
    inner join
        JBTRAVEL.Airport airport2_ 
            on route1_.originairportid=airport2_.airportid 
    inner join
        JBTRAVEL.Airport airport3_ 
            on route1_.destinationairportid=airport3_.airportid 
    where
        airport2_.airportcode=? 
        and airport3_.airportcode=? 
    order by
        flight0_.departuredate desc
Hibernate: 
    select
        plane0_.planeid as planeid1_2_0_,
        plane0_.planemodel as planemod2_2_0_,
        plane0_.tailnumber as tailnumb3_2_0_,
        plane0_.totalnumberofseats as totalnum4_2_0_ 
    from
        JBTRAVEL.Plane plane0_ 
    where
        plane0_.planeid=?
Hibernate: 
    select
        route0_.routeid as routeid1_4_2_,
        route0_.originairportid as originai3_4_2_,
        route0_.destinationairportid as destinat4_4_2_,
        route0_.distance as distance2_4_2_,
        airport1_.airportid as airporti1_0_0_,
        airport1_.airportcode as airportc2_0_0_,
        airport1_.airportname as airportn3_0_0_,
        airport1_.city as city4_0_0_,
        airport1_.country as country5_0_0_,
        airport1_.latitude as latitude6_0_0_,
        airport1_.longitude as longitud7_0_0_,
        airport1_.state_prov as state_pr8_0_0_,
        airport2_.airportid as airporti1_0_1_,
        airport2_.airportcode as airportc2_0_1_,
        airport2_.airportname as airportn3_0_1_,
        airport2_.city as city4_0_1_,
        airport2_.country as country5_0_1_,
        airport2_.latitude as latitude6_0_1_,
        airport2_.longitude as longitud7_0_1_,
        airport2_.state_prov as state_pr8_0_1_ 
    from
        JBTRAVEL.Route route0_ 
    left outer join
        JBTRAVEL.Airport airport1_ 
            on route0_.originairportid=airport1_.airportid 
    left outer join
        JBTRAVEL.Airport airport2_ 
            on route0_.destinationairportid=airport2_.airportid 
    where
        route0_.routeid=?