lunes, 25 de abril de 2011

JPA III - HQL y JPA QL

La idea en este tutorial es mostrar el lenguaje de queries JPA QL o JPQL (el lenguaje de queries de JPA). y como se ejecuta por detras el SQL. En primer lugar vamos a hacer un repaso del leguaje de queries de Hibernate HQL, siendo JPAQL un subset de HQL y luego vamos a profundizar en las queries de JPA.
Tanto las queries en HQL como  en JPQL son expresadas en objetos java en lugar de tablas y columnas.
El EntityManager de JPA usa la configuracion JDBC especificada en el persistence.xml (mas adelante se profundizara sobre estos temas) para convertir una consulta JPA en una consulta SQL nativa.

La idea principal de este tutorial es poder escribir una query en JPAQL y saber que es lo que esta haciendo por detras. Es decir, que consulta SQL esta ejecutando el framework

Al final de este tutorial, el lector encontrara un link para descargar los ejemplos vistos en todos los tutoriales JPA de este blog.

Temas a tratar:
  • HQL alternativas de creacion de queries con el objeto Session
  • HQL ejemplos basicos
  • JPAQL ejemplo basico
  • JPAQL consulta con proyeccion
  • JPAQL consulta con condicionales
  •  Comparativa entre operadores aritmeticos de HQL y JPAQL
  • JPAQL funciones de agregacion (AVG, MAX, SUM, COUNT)
  • GROUP BY
  • HAVING
  • JOINS, INNER JOINS, LEFT OUTER JOINS, INNER JOINS FETCH
  • JPAQL Identificadores reservados

1. HQL 

Existen 3 caminos distintos para expresar queries en Hibernate
Vamos a tomar como ejemplo, la entidad Account creada en el tutorial I de JPA (Ver tutorial JPA I Manejo de Relaciones)


■  3 Alternativas de armar queries en HQL

A.- Hibernate Query Language (HQL):

List list = session.createQuery("from Account a where a.balance=100").list();

SQL Trace:

    select
        account0_.ACCOUNT_ID as ACCOUNT2_3_,
        account0_.balance as balance3_,
        account0_.CUSOMTER_ID as CUSOMTER6_3_,
        account0_.overdraftLimit as overdraf4_3_,
        account0_.interestRate as interest5_3_,
        account0_.ACCOUNT_TYPE as ACCOUNT1_3_
    from
        Account account0_
    where
        account0_.balance=100


    select
        customer0_.CUSTOMER_ID as CUSTOMER1_4_2_,
        customer0_.FIRST_NAME as FIRST2_4_2_,
        customer0_.GENDER as GENDER4_2_,
        customer0_.LAST_NAME as LAST4_4_2_,
        customer0_.referee_id as referee7_4_2_,
        customer0_.name1 as name5_4_2_,
        customer0_.name2 as name6_4_2_,
        accounts1_.CUSOMTER_ID as CUSOMTER6_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts1_.balance as balance3_0_,
        accounts1_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts1_.overdraftLimit as overdraf4_3_0_,
        accounts1_.interestRate as interest5_3_0_,
        accounts1_.ACCOUNT_TYPE as ACCOUNT1_3_0_,
        referee2_.id as id2_1_,
        referee2_.comments as comments2_1_,
        referee2_.name as name2_1_
    from
        Customer customer0_
    left outer join
        Account accounts1_
            on customer0_.CUSTOMER_ID=accounts1_.CUSOMTER_ID
    left outer join
        Referee referee2_
            on customer0_.referee_id=referee2_.id
    where
        customer0_.CUSTOMER_ID=?



B.- Criteria API for query by criteria (QBC) and query by example (QBE):

List list = session.createCriteria(Account.class).add( Restrictions.eq("balance", 100d) ).list();

SQL Trace:

 select
        this_.ACCOUNT_ID as ACCOUNT2_3_2_,
        this_.balance as balance3_2_,
        this_.CUSOMTER_ID as CUSOMTER6_3_2_,
        this_.overdraftLimit as overdraf4_3_2_,
        this_.interestRate as interest5_3_2_,
        this_.ACCOUNT_TYPE as ACCOUNT1_3_2_,
        customer2_.CUSTOMER_ID as CUSTOMER1_4_0_,
        customer2_.FIRST_NAME as FIRST2_4_0_,
        customer2_.GENDER as GENDER4_0_,
        customer2_.LAST_NAME as LAST4_4_0_,
        customer2_.referee_id as referee7_4_0_,
        customer2_.name1 as name5_4_0_,
        customer2_.name2 as name6_4_0_,
        referee3_.id as id2_1_,
        referee3_.comments as comments2_1_,
        referee3_.name as name2_1_
    from
        Account this_
    left outer join
        Customer customer2_
            on this_.CUSOMTER_ID=customer2_.CUSTOMER_ID
    left outer join
        Referee referee3_
            on customer2_.referee_id=referee3_.id
    where
        this_.balance=?



    select
        accounts0_.CUSOMTER_ID as CUSOMTER6_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts0_.balance as balance3_0_,
        accounts0_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts0_.overdraftLimit as overdraf4_3_0_,
        accounts0_.interestRate as interest5_3_0_,
        accounts0_.ACCOUNT_TYPE as ACCOUNT1_3_0_
    from
        Account accounts0_
    where
        accounts0_.CUSOMTER_ID=?

Para profundizar en este tema, ver http://docs.jboss.org/hibernate/core/3.5/reference/es-ES/html/querycriteria.html



C.- Direct SQL with or without automatic mapping of resultsets to objects:

List list = session.createSQLQuery("select {a.*} from account {a} where balance=100").addEntity("a", Account.class).list();

SQL Trace:

select
        a.ACCOUNT_ID as ACCOUNT2_3_0_,
        a.balance as balance3_0_,
        a.CUSOMTER_ID as CUSOMTER6_3_0_,
        a.overdraftLimit as overdraf4_3_0_,
        a.interestRate as interest5_3_0_,
        a.ACCOUNT_TYPE as ACCOUNT1_3_0_
    from
        account a
    where
        balance=100



    select
        customer0_.CUSTOMER_ID as CUSTOMER1_4_2_,
        customer0_.FIRST_NAME as FIRST2_4_2_,
        customer0_.GENDER as GENDER4_2_,
        customer0_.LAST_NAME as LAST4_4_2_,
        customer0_.referee_id as referee7_4_2_,
        customer0_.name1 as name5_4_2_,
        customer0_.name2 as name6_4_2_,
        accounts1_.CUSOMTER_ID as CUSOMTER6_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts1_.balance as balance3_0_,
        accounts1_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts1_.overdraftLimit as overdraf4_3_0_,
        accounts1_.interestRate as interest5_3_0_,
        accounts1_.ACCOUNT_TYPE as ACCOUNT1_3_0_,
        referee2_.id as id2_1_,
        referee2_.comments as comments2_1_,
        referee2_.name as name2_1_
    from
        Customer customer0_
    left outer join
        Account accounts1_
            on customer0_.CUSTOMER_ID=accounts1_.CUSOMTER_ID
    left outer join
        Referee referee2_
            on customer0_.referee_id=referee2_.id
    where
        customer0_.CUSTOMER_ID=?

Para profundizar en este tema, ver http://docs.jboss.org/hibernate/core/3.5/reference/es-ES/html/querysql.html


En los 3 casos, podemos observar que respetan a la perfeccion lo que se definio en el mapeo de la entidad Account (Ver tutorial JPA I Manejo de Relaciones). Donde la relacion N a 1 entre Account y Customer tiene por defecto una lectura temprana (ya que este tipo de relaciones, al no explicitar ningun valor, toman por defecto la lectura temprana), es decir, esta seteada por default a EAGER.

En caso que la relacion entre Account y Customer hubiere tenido lectura tardia (LAZY)

....
.....
@ManyToOne(fetch=FetchType.LAZY)
           @JoinColumn(name="CUSOMTER_ID",
                       referencedColumnName="CUSTOMER_ID"
          )
private Customer customer;
....
....

Al ejecutar las consultas anteriores, el sql trace hubiese sido el siguiente:

select
        account0_.ACCOUNT_ID as ACCOUNT2_3_,
        account0_.balance as balance3_,
        account0_.CUSOMTER_ID as CUSOMTER6_3_,
        account0_.overdraftLimit as overdraf4_3_,
        account0_.interestRate as interest5_3_,
        account0_.ACCOUNT_TYPE as ACCOUNT1_3_
    from
        Account account0_
    where
        account0_.balance=100


■ Bindeando parametros

a.- Usando named parameters

 String queryString ="from Customer c where c.firstName = :name";
 Query q = session.createQuery(queryString).setString("name", "First name 1"); 
 List list = q.list();

b.- Usando poositional parameters

 String queryString2 ="from Customer c where c.firstName = ?"; 
 Query q2 = session.createQuery(queryString2).setString(0, "First name 1"); 
 List list22 = q2.list();


■ Query Hints (modificar la configueracion de los objetos de persistencia antes de ejecutar la query)

Query q = session.createQuery(queryString2).setFlushMode(FlushMode.COMMIT);
Query q = session.createQuery(queryString2).setTimeout(60);


■ Unico resultado

Account a = (Account) session.createQuery("from Account ").setMaxResults(1).uniqueResult();

Account ac = (Account) session.createCriteria(Account.class).add( Restrictions.eq("id", 2)).uniqueResult();


■ Expresiones de comparacion

List acccounts = session.createQuery("from Account a where a.id between 1 and 3").list();

List acccounts2 = session.createQuery("from Account a where a.id in (1,3)").list();

List ids = new ArrayList(); 
ids.add(1); 
ids.add(3); 
List acccounts3 = session.createQuery("from Account a where a.id in (:ids)").setParameterList("ids", ids).list();


Para profundizar en HQL, ver http://docs.jboss.org/hibernate/core/3.5/reference/es-ES/html/queryhql.html



2. JPA QL  

Antes de empezar, vamos a tomar como ejemplo las siguientes tablas y sus registros sobre las cuales vamos a realizar los ejemplos, sobre todo, para que se entiendan las queries en SQL que se lanzan a la hora de ejecutar las consultas en JPQL

TABLA CUSTOMER
 
 CUSTOMER_ID     FIRST_NAME     GENDER     LAST_NAME     NAME1     NAME2     REFEREE_ID    
 --------------  -------------  ---------  ------------  --------  --------  -------------
 1               First name 1   FEMALE     Last name 1   Damian    (null)    1             
 2               First name 2   FEMALE     Last name 2   (null)    (null)    2             
 3               First name 3   FEMALE     Last name 3   (null)    (null)    1             
 4               First name 4   MALE       Last name 4   (null)    (null)    (null)       

TABLA REFEREE

 ID     COMMENTS     NAME     
 -----  -----------  ---------
 1      comments 1   Referee 1
 2      comments 2   Referee 2 

TABLA ACCOUNTS


 ACCOUNT_TYPE     ACCOUNT_ID     BALANCE     OVERDRAFTLIMIT     INTERESTRATE     CUSOMTER_ID   
 ---------------  -------------  ----------  -----------------  ---------------  --------------
 C                1              10          0                  (null)           1             
 C                2              20          0                  (null)           1             
 S                3              100         (null)             0                1             

TABLA CUSTOMER_ADDRESS

 CUST_ID     ADD_ID   
 ----------         --------
 1                     2        
 1                     0     

■ Simple Query

Query query = em.createQuery("SELECT c from Customer c");
List customers = query.getResultList();

Hibernate:
    select
        customer0_.CUSTOMER_ID as CUSTOMER1_4_,
        customer0_.FIRST_NAME as FIRST2_4_,
        customer0_.GENDER as GENDER4_,
        customer0_.LAST_NAME as LAST4_4_,
        customer0_.referee_id as referee7_4_,
        customer0_.name1 as name5_4_,
        customer0_.name2 as name6_4_
    from
        Customer customer0_
Hibernate:
    select
        referee0_.id as id2_0_,
        referee0_.comments as comments2_0_,
        referee0_.name as name2_0_
    from
        Referee referee0_
    where
        referee0_.id=?
Hibernate:
    select
        referee0_.id as id2_0_,
        referee0_.comments as comments2_0_,
        referee0_.name as name2_0_
    from
        Referee referee0_
    where
        referee0_.id=?
Hibernate:
    select
        accounts0_.CUSOMTER_ID as CUSOMTER6_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts0_.balance as balance3_0_,
        accounts0_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts0_.overdraftLimit as overdraf4_3_0_,
        accounts0_.interestRate as interest5_3_0_,
        accounts0_.ACCOUNT_TYPE as ACCOUNT1_3_0_
    from
        Account accounts0_
    where
        accounts0_.CUSOMTER_ID=?
Hibernate:
    select
        accounts0_.CUSOMTER_ID as CUSOMTER6_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts0_.balance as balance3_0_,
        accounts0_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts0_.overdraftLimit as overdraf4_3_0_,
        accounts0_.interestRate as interest5_3_0_,
        accounts0_.ACCOUNT_TYPE as ACCOUNT1_3_0_
    from
        Account accounts0_
    where
        accounts0_.CUSOMTER_ID=?
Hibernate:
    select
        accounts0_.CUSOMTER_ID as CUSOMTER6_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts0_.balance as balance3_0_,
        accounts0_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts0_.overdraftLimit as overdraf4_3_0_,
        accounts0_.interestRate as interest5_3_0_,
        accounts0_.ACCOUNT_TYPE as ACCOUNT1_3_0_
    from
        Account accounts0_
    where
        accounts0_.CUSOMTER_ID=?
Hibernate:
    select
        accounts0_.CUSOMTER_ID as CUSOMTER6_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts0_.balance as balance3_0_,
        accounts0_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts0_.overdraftLimit as overdraf4_3_0_,
        accounts0_.interestRate as interest5_3_0_,
        accounts0_.ACCOUNT_TYPE as ACCOUNT1_3_0_
    from
        Account accounts0_
    where
        accounts0_.CUSOMTER_ID=?


Podemos observar como la query contra la tabla ACCOUNTS se repite 4 veces. Esto se debe a que al hacer el select contra la tabla CUSTOMER, se obtienen 4 resultados (Customers) y por cada uno, se hara un select contra la tabla ACCOUNTS (debido a como se configuro la relacion entre estas dos entidades - fetch=FetchType.EAGER - lectura temprana).
Por otra parte, tambien podemos observar que se hicieron 2 consultas sobre la tabla Referee, ya que los 4 customers, solo tienen como referencia a 2 referees (estas queries se ejecutan ya que como podemos ver, cuando se configuro la relacion uno a uno entre customer y referee, no se indico como debia ser dicha relacion (Ver tutorial JPA I Manejo de Relaciones), por ende, JPA por defecto, a las relaciones uno a uno las hace EAGER - de lectura temprana)


■ Projection Query

Query query = em.createQuery("SELECT c.firstName, c.lastName from Customer c");
List customers = query.getResultList();
for (Object[] customer : customers) {
        log.debug("CUSTOMER " + customer[0] + " " + customer[1]);
}


Hibernate:
    select
        customer0_.FIRST_NAME as col_0_0_,
        customer0_.LAST_NAME as col_1_0_
    from
        Customer customer0_


Como podemos ver en este caso, solo se hara una query SQL simple sin ir a buscar todas las referencias, como si lo hacia la consulta anterior donde tenia que traer todas las columnas de la tabla customer.



■ Conditional Query

Query query = em.createQuery("SELECT c FROM Customer c WHERE c.lastName = 'Last name 4' ");

Hibernate:
    select
        customer0_.CUSTOMER_ID as CUSTOMER1_4_,
        customer0_.FIRST_NAME as FIRST2_4_,
        customer0_.GENDER as GENDER4_,
        customer0_.LAST_NAME as LAST4_4_,
        customer0_.referee_id as referee7_4_,
        customer0_.name1 as name5_4_,
        customer0_.name2 as name6_4_
    from
        Customer customer0_
    where
        customer0_.LAST_NAME='Last name 4'
Hibernate:
    select
        accounts0_.CUSOMTER_ID as CUSOMTER6_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts0_.balance as balance3_0_,
        accounts0_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts0_.overdraftLimit as overdraf4_3_0_,
        accounts0_.interestRate as interest5_3_0_,
        accounts0_.ACCOUNT_TYPE as ACCOUNT1_3_0_
    from
        Account accounts0_
    where
        accounts0_.CUSOMTER_ID=?

En este caso, podemos ver como se hace la consulta para obtener un customer y a su vez un accounts, pero no se obtiene el referee ya que justo este customer, como vemos en las tablas del comienzo de este apartado, no tiene referee asociado.

 CUSTOMER_ID     FIRST_NAME     GENDER     LAST_NAME     NAME1     NAME2     REFEREE_ID    
 --------------  -------------  ---------  ------------  --------  --------  -------------           
 4               First name 4   MALE       Last name 4   (null)    (null)    (null)      


Query query1 = em.createQuery("SELECT c FROM Customer c WHERE c.lastName = 'Last name 4' and c.firstName = 'First name 4' ");

Ademas del "=", tambien tenemos los siguienets comparadores en JPAQL:

>, >=, <, <=, <> (not equal)

Query query2 = em.createQuery("SELECT c FROM Customer c WHERE c.referee is null");

Query query3 = em.createQuery("SELECT c FROM Customer c WHERE c.referee is not null");

Query query4 = em.createQuery("SELECT a FROM Account a WHERE a.balance BETWEEN 20 and 100 ");

Hibernate:
    select
        account0_.ACCOUNT_ID as ACCOUNT2_3_,
        account0_.balance as balance3_,
        account0_.CUSOMTER_ID as CUSOMTER6_3_,
        account0_.overdraftLimit as overdraf4_3_,
        account0_.interestRate as interest5_3_,
        account0_.ACCOUNT_TYPE as ACCOUNT1_3_
    from
        Account account0_
    where
        account0_.balance between 20 and 100
Hibernate:
    select
        customer0_.CUSTOMER_ID as CUSTOMER1_4_2_,
        customer0_.FIRST_NAME as FIRST2_4_2_,
        customer0_.GENDER as GENDER4_2_,
        customer0_.LAST_NAME as LAST4_4_2_,
        customer0_.referee_id as referee7_4_2_,
        customer0_.name1 as name5_4_2_,
        customer0_.name2 as name6_4_2_,
        accounts1_.CUSOMTER_ID as CUSOMTER6_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts1_.balance as balance3_0_,
        accounts1_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts1_.overdraftLimit as overdraf4_3_0_,
        accounts1_.interestRate as interest5_3_0_,
        accounts1_.ACCOUNT_TYPE as ACCOUNT1_3_0_,
        referee2_.id as id2_1_,
        referee2_.comments as comments2_1_,
        referee2_.name as name2_1_
    from
        Customer customer0_
    left outer join
        Account accounts1_
            on customer0_.CUSTOMER_ID=accounts1_.CUSOMTER_ID
    left outer join
        Referee referee2_
            on customer0_.referee_id=referee2_.id
    where
        customer0_.CUSTOMER_ID=?

En el trace del SQL que se ejecuta al lanzar este consulta JPA, podemos apreciar que tambien se ejecuta la consulta para obtener el customer del account (ver que en la relaciones uno-a-uno y muchos-a-uno la lectura de las relaciones es temprana (eager) por defecto si en el mapeo/configuracion de las entidades no indicamos lo contrario - Ver tutorial JPA I Manejo de Relaciones).
Por otra parte, cabe destacar que el between es inclusive, es decir, que es identico a realizar las operacoines >= and <=. El resultado de esta consulta es lo siguiente:

TABLA ACCOUNTS

 ACCOUNT_TYPE     ACCOUNT_ID     BALANCE     OVERDRAFTLIMIT     INTERESTRATE     CUSOMTER_ID   
 ---------------  -------------  ----------  -----------------  ---------------  --------------             
 C                2              20          0                  (null)           1             
 S                3              100         (null)             0                1             

Query query5 = em.createQuery("SELECT a FROM Account a WHERE a.id IN (1,2)");    

El equivalente a esta consulta seria a.id=1 OR a.id=2 

Query query6 = em.createQuery("SELECT c FROM Customer c WHERE c.firstName LIKE '_i%a%'");

En este caso esta consulta nos traera todos los registros de la tabla Customer (los 4 registros segun el ejemplo que estamos siguiendo) ya que indica que nos traiga los customers cuyo first name tenga una "i" como segundo caracter y una "a" en algun otro lugar del string

Query query7 = em.createQuery("SELECT c FROM Customer c ORDER BY c.firstName DESC");

Query query8 = em.createQuery("SELECT a FROM Account a ORDER BY a.id ASC, a.balance DESC");


Imagen extraida del libro Java Persistence with Hibernate - Revised Edition fo Hiernate In Action - Christian Bauer and Gavin King - 2007


■ Aggregate Function (Funciones de agregacion)

Query query = em.createQuery("SELECT AVG(a.balance) FROM Account a ");
Double avg = (Double) query.getSingleResult();
System.out.println("AVG Balance " + avg);

El valor de esta funcion de agregacion sera un valor numerico, mas especificamente un double. 
Segun el ejemplo que estamos siguiendo, esta consulta nos dara un unico resultado que sera 
AVG Balance 43.333333333333336 


Query query1 = em.createQuery("SELECT MAX(a.balance) FROM Account a ");
Double max = (Double) query1.getSingleResult();
System.out.println("MAX Balance " + max);

Segun el ejemplo que estamos siguiendo, esta consulta nos dara un unico resultado que sera 
MAX Balance 100.0

El trace SQL que sera lanzado es el siguiente:

Hibernate:
    select
        *
    from
        ( select
            MAX(account0_.balance) as col_0_0_
        from
            Account account0_ )
    where
        rownum <= ?


Lo mismo sera para la funcion MIN
Por otra parte, tanto la funcion de MAX como la de MIN, seran aplicables tanto a valores numericos, como strings, como caracteres o bien como dates.


Query query2 = em.createQuery("SELECT SUM(a.balance) FROM Account a ");    
Double sum = (Double) query2.getSingleResult();
System.out.println("SUM Balance " + sum);

En este caso, la funcion SUM sumara solamente valores numericos. Esta funcion retornara valores del tipo Long, Double, BigInteger, or BigDecimal

El resultado sera el siguiente:

SUM Balance 130.0


Query query4 = em.createQuery("SELECT COUNT(a) FROM Account a ");
Long count = (Long) query4.getSingleResult();
System.out.println("COUNT " + count);

En este caso, la funcion COUNT podra ser aplicada a cualquier propiedad o entidad misma. Esta funcion retornara un valor del tipo Long
El resultado sera el siguiente:
COUNT 3


Query query5 = em.createQuery("SELECT DISTINCT COUNT(c.firstName) FROM Customer c");
Long count1 = (Long) query5.getSingleResult();
System.out.println("COUNT " + count1); 

Esta funcion, nos permitira elimiar los valores duplicados en funcion a la propiedad firstName (no se contaran customers mas de una vez si tienen el mismo firstName)
El resultado sera el siguiente:
COUNT 4

NOTA: al aplicar cualquier función de agregacion a cualquier campo con un valor nulo se eliminará independientemente de si la palabra clave distinct es especificada.


■ Group By

Query query = em.createQuery("SELECT a.id, AVG(a.balance) FROM Account a GROUP BY a.id");
List accounts =  query.getResultList();
for (Object[] acc : accounts) { 
    log.debug("Account ID " + acc[0]+ " " + acc[1]);
}

Tipicamente cuando usamos funciones de agregacion sobre alguno de las propiedades de la entiedad, necesitamos agrupar el resto de las propiedades bajo algun criterio
El traza SQL de esta query es la siguiente

Hibernate:
    select
        account0_.ACCOUNT_ID as col_0_0_,
        AVG(account0_.balance) as col_1_0_
    from
        Account account0_
    group by
        account0_.ACCOUNT_ID

Por otra parte, el resultado es el siguiente (luego de iterar mediante un FOR lo que devuelve la consulta)

Account ID 1 10.0 
Account ID 2 20.0 
Account ID 3 100.0


■ Having

Esta funcion permite agregar condiciones a los grupos que se establezcan mediante la funcion Group By
En primer lugar, hacemos la query sin el Having para que se entienda bien como funciona:

Query query = em.createQuery("SELECT count(c), c.gender FROM Customer c GROUP BY c.gender");
List customers =  query.getResultList();
for (Object[] customer : customers) {
     log.debug("customer " + customer[0] + " " + customer[1]);       
}

Lo que mostrara esta consulta es lo siguiente (siempre teniendo en cuenta el ejemplo)

customer 3 FEMALE
customer 1 MALE

Ahora agregamos la funcion Having a la query realizada anteriormente y vemos que nos devuelve:

Query query1 = em.createQuery("SELECT count(c), c.gender FROM Customer c GROUP BY c.gender HAVING COUNT(c.gender) > 1"); 
List customers1 =  query1.getResultList(); 
for (Object[] customer : customers1) { 
       log.debug("customer " + customer[0] + " " + customer[1]);
}

La traza SQL que ejectua esta consulta es la siguiente:

Hibernate:
    select
        count(customer0_.CUSTOMER_ID) as col_0_0_,
        customer0_.GENDER as col_1_0_
    from
        Customer customer0_
    group by
        customer0_.GENDER
    having
        count(customer0_.GENDER)>1

Lo que nos devolvera esta consulta es lo siguiente:

customer 3 FEMALE 

Aqui podemos ver que a diferencia de la consulta anterior (donde obtuvimos dos resultados) aqui obtenemos un resultado.
Esto es gracias a la funcion having que nos condiciono el resultado diciendo que solamente devolvera aquellos grupos de generos donde al menos tengan mas de un registro. Y como podemos ver en la tabla Customer, vemos que el genero femenino es el que cumple esta condicion.


■ Queries con relaciones

Query query = em.createQuery("SELECT c.lastName, c.referee.name FROM Customer c ");
List customers =  query.getResultList();
for (Object[] customer : customers) {
     log.debug("customer lastName " + customer[0] + " referee name " + customer[1]);
}

La consulta SQL que se ejecuta es la siguiente:

Hibernate:
    select
        customer0_.LAST_NAME as col_0_0_,
        referee1_.name as col_1_0_
    from
        Customer customer0_,
        Referee referee1_
    where
        customer0_.referee_id=referee1_.id


El resultado de esta consulta es el siguiente:

customer lastName Last name 1 referee name Referee 1
customer lastName Last name 2 referee name Referee 2
customer lastName Last name 3 referee name Referee 1

Otra forma de obtener lo mismo es la siguiente query

Query query1 = em.createQuery("SELECT c.lastName, c.referee FROM Customer c ");
List customers1 =  query1.getResultList();
for (Object[] customer : customers1) {
    log.debug("customer lastName " + customer[0] + " referee name " + ((Referee)customer[1]).getName());
}

Aqui podemos ver que en lugar de obtener el nombre del referee en la clausula del select, obtenemos directamente el obtejo Referee y luego casteamos a Referee y obtenemos el nombre.
Por otra parte, lo que cambia tambien es la consulta SQL que se ejecuta:

Hibernate:
 select
        customer0_.LAST_NAME as col_0_0_,
        customer0_.referee_id as col_1_0_,
        referee1_.id as id2_,
        referee1_.comments as comments2_,
        referee1_.name as name2_
    from
        Customer customer0_
    inner join
        Referee referee1_
            on customer0_.referee_id=referee1_.id

Vemos claramente que se obtienen todas las propiedades de la entidad Referee, mientras que en la consulta anterior solo se obtenia el nombre del Referee.

A. Joins

Al navegar por dos o mas entities, se esta efectuando una operacion de JOIN implicitamente, como vimos anteriormente (ejemplo SELECT c.lastName, c.referee.name FROM Customer c) donde vemos que la notacion por puntos nos simplifica la notacion evitando hacer lo siguiente SELECT c.lastName, r.name FROM Customer c, Referee r WHERE c.referee.id =r.id
Los JOINS implicitos (sea mediante la notacion por puntos o no, como vimos los dos ejemplos anteriores) funcionan perfectamente para las relaciones uno a uno o muchos a uno, ahora bien, para las relaciones uno a muchos o muchos a muchos, podremos navegar por estas relaciones usando el operador JOIN de forma explicita.



B. Inner Joins

Los operadores JOIN y INNER JOIN son sinonimos en JPQL (es decir, la palabra INNER es opcional)

Query query = em.createQuery("SELECT c.lastName, a.balance FROM Customer c INNER JOIN c.accounts a");
List customers =  query.getResultList();
for (Object[] customer : customers) {
   log.debug("customer lastName " + customer[0] + " customer balance " + customer[1]);
}

La traza SQL es la siguiente: 

Hibernate:
    select
        customer0_.LAST_NAME as col_0_0_,
        accounts1_.balance as col_1_0_
    from
        Customer customer0_
    inner join
        Account accounts1_
            on customer0_.CUSTOMER_ID=accounts1_.CUSOMTER_ID


El resultado es el siguiente

customer lastName Last name 1 customer balance 100.0
customer lastName Last name 1 customer balance 20.0
customer lastName Last name 1 customer balance 10.0
 
IMPORTANTE: En este tipo de consultas, en los casos en donde un customer no tiene una relacion con un account, no va a traer ningun registro. Ya que el JOIN o INNER JOIN funciona de esta manera. En el ejemplo anterior, como podemos ver, solo trajo 3 registros que corresponden a uno de la la tabla Customer que se relaciona con 3 registros de la tabla Accounts. Esto se debe,  como bien lo venimos explicando, que un solo registro de la tabla Customer, tiene referencia con la tabla Account  mientras que el resto no.  - Dicho en otros terminos, existen 3 customers que no tienen ninguna cuenta asociada -
Esta operacion nos devuelve el producto cartesiano entre Customer y Account

Otra forma de escribir la misma query es de la siguiente manera:

Query query1 = em.createQuery("SELECT c.lastName, a.balance FROM Customer c JOIN c.accounts a");


C. Outer Joins

Este es usado cuando no es necesario que el macheo en la condicion de join este presente. Es decir, cuando querramos que nos traigan los Customers independientemente de si tenga o no una Account.
La palabra reservada en JPQL es LEFT OUTER JOIN

Query query2 = em.createQuery("SELECT c.lastName, a.balance FROM Customer c LEFT OUTER JOIN c.accounts a"); 
List customers2 =  query2.getResultList(); 
for (Object[] customer : customers2) { 
    log.debug("customer lastName " + customer[0] + " customer balance " + customer[1]); 
}

La traza SQL es la siguiente:

Hibernate:
    select
        customer0_.LAST_NAME as col_0_0_,
        accounts1_.balance as col_1_0_
    from
        Customer customer0_
    left outer join
        Account accounts1_
            on customer0_.CUSTOMER_ID=accounts1_.CUSOMTER_ID

El resultado es el siguiente:

customer lastName Last name 1 customer balance 10.0
customer lastName Last name 1 customer balance 20.0
customer lastName Last name 1 customer balance 100.0
customer lastName Last name 4 customer balance null
customer lastName Last name 3 customer balance null
customer lastName Last name 2 customer balance null

Aqui podemos ver claramente una diferencia en los resultados que se obtienen respecto a las 2 consultas anteriores. Lo unico que se hizo fue agregar LEFT OUTER a la condicion del JOIN. Esto permite traer todos los customers mas alla de si tienen o no una, ninguna o varias accounts.


D. Fetch Joins

Esto permite realizar una carga de los datos relacionados de forma temprana (EAGER loading) incluso si se ha especificado una estrategia de lectura tardia (LAZY loading) al definir o configurar las relaciones - Ver tutorial JPA I Manejo de Relaciones

En primer lugar vamos a tirar una simple consulta con INNER JOIN para entender bien la diferencia entre esto y INNER JOIN FETCH

Query query = em.createQuery("SELECT c FROM Customer c INNER JOIN c.addresses"); 
List customers =  query.getResultList(); 
for (Customer customer : customers) { 
  log.debug("CUSTOMER ID " + customer.getId() + " " + customer.getFirstName() + " " + customer.getLastName()); 
  List addresses = customer.getAddresses();         
         for (Address address : addresses) {
              log.debug("ADDRESS ID " + address.getId());
         }
}
Y aca esta la traza SQL

Hibernate:
    select
        customer0_.CUSTOMER_ID as CUSTOMER1_4_,
        customer0_.FIRST_NAME as FIRST2_4_,
        customer0_.GENDER as GENDER4_,
        customer0_.LAST_NAME as LAST4_4_,
        customer0_.referee_id as referee7_4_,
        customer0_.name1 as name5_4_,
        customer0_.name2 as name6_4_
    from
        Customer customer0_
    inner join
        CUSTOMER_ADDRESS addresses1_
            on customer0_.CUSTOMER_ID=addresses1_.CUST_ID
    inner join
        Address address2_
            on addresses1_.ADD_ID=address2_.ADDRESS_ID
Hibernate:
    select
        referee0_.id as id2_0_,
        referee0_.comments as comments2_0_,
        referee0_.name as name2_0_
    from
        Referee referee0_
    where
        referee0_.id=?
Hibernate:
    select
        accounts0_.CUSOMTER_ID as CUSOMTER6_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts0_.balance as balance3_0_,
        accounts0_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts0_.overdraftLimit as overdraf4_3_0_,
        accounts0_.interestRate as interest5_3_0_,
        accounts0_.ACCOUNT_TYPE as ACCOUNT1_3_0_
    from
        Account accounts0_
    where
        accounts0_.CUSOMTER_ID=?
CUSTOMER ID 1 First name 1 Last name 1

Hibernate:
    select
        addresses0_.CUST_ID as CUST1_1_,
        addresses0_.ADD_ID as ADD2_1_,
        address1_.ADDRESS_ID as ADDRESS1_5_0_,
        address1_.addressLine as addressL2_5_0_,
        address1_.country as country5_0_,
        address1_.postCode as postCode5_0_
    from
        CUSTOMER_ADDRESS addresses0_
    left outer join
        Address address1_
            on addresses0_.ADD_ID=address1_.ADDRESS_ID
    where
        addresses0_.CUST_ID=?
ADDRESS ID 2
ADDRESS ID 0
CUSTOMER ID 1 First name 1 Last name 1
ADDRESS ID 2
ADDRESS ID 0

Como podemos ver, al obtener datos del 1er registro de customer y luego intentar recorrer y loguear los addreess, JPA por detras va a tirar la consulta SQL para obtener dichos valores de la tabla address ya que con la query original no los obtuvimos.
Es decir, podemos ver que cuando despues de loguear el primer registro de customer y luego al intentar acceder a los registros del address, vemos que ahi es cuando tira otra consulta SQL por detras, para obtener dichas addresss. Luego, no tira mas consultas sql porque las cachea para no ir nuevamente a la bd para obtener los mismos regustros.
Por otra parte, vemos que nos repite el customer y eso es correcto ya que la consulta hace el producto cartesiano entre customer y address y como un customer tiene dos address la consulta nos trae dos registros (customer id 1 address id 1 y customer id 1 address id 2) y por cada registro estamos logueando los datos (un customer con dos address)

En resumen, el resultado de la query que estamos logueando es la siguiente

CUSTOMER ID 1 First name 1 Last name 1
ADDRESS ID 2
ADDRESS ID 0
CUSTOMER ID 1 First name 1 Last name 1
ADDRESS ID 2
ADDRESS ID 0


Es decir, como vemos en la tabla CUSTOMER_ADDRESS, existen solo dos resultados donde tenemos que el customer cuyo ID 1 tiene dos addresses

TABLA CUSTOMER_ADDRESS

 CUST_ID     ADD_ID   
 ----------         --------
 1                     2        
 1                     0     


Ahora vamos a cambiar la query anterior y agregarle la notacion  INNER JOIN FETCH y siguiendo el mismo ejemplo (un customer tiene asociado dos address como bien lo indica la tabla CUSTOMER_ADDRESS)

Query query = em.createQuery("SELECT c FROM Customer c INNER JOIN FETCH c.addresses"); 
List customers =  query.getResultList(); 
for (Customer customer : customers) { 
  log.debug("CUSTOMER ID " + customer.getId() + " " + customer.getFirstName() + " " + customer.getLastName()); 
List addresses = customer.getAddresses();
                   for (Address address : addresses) {
                        log.debug("ADDRESS ID " + address.getId());
       }
}
La traza SQL es la siguiente:
Hibernate:
    select
        customer0_.CUSTOMER_ID as CUSTOMER1_4_0_,
        address2_.ADDRESS_ID as ADDRESS1_5_1_,
        customer0_.FIRST_NAME as FIRST2_4_0_,
        customer0_.GENDER as GENDER4_0_,
        customer0_.LAST_NAME as LAST4_4_0_,
        customer0_.referee_id as referee7_4_0_,
        customer0_.name1 as name5_4_0_,
        customer0_.name2 as name6_4_0_,
        address2_.addressLine as addressL2_5_1_,
        address2_.country as country5_1_,
        address2_.postCode as postCode5_1_,
        addresses1_.CUST_ID as CUST1_0__,
        addresses1_.ADD_ID as ADD2_0__    
    from
        Customer customer0_
    inner join
        CUSTOMER_ADDRESS addresses1_
            on customer0_.CUSTOMER_ID=addresses1_.CUST_ID
    inner join
        Address address2_
            on addresses1_.ADD_ID=address2_.ADDRESS_ID
Hibernate:
    select
        referee0_.id as id2_0_,
        referee0_.comments as comments2_0_,
        referee0_.name as name2_0_
    from
        Referee referee0_
    where
        referee0_.id=?
Hibernate:
    select
        accounts0_.CUSOMTER_ID as CUSOMTER6_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_1_,
        accounts0_.ACCOUNT_ID as ACCOUNT2_3_0_,
        accounts0_.balance as balance3_0_,
        accounts0_.CUSOMTER_ID as CUSOMTER6_3_0_,
        accounts0_.overdraftLimit as overdraf4_3_0_,
        accounts0_.interestRate as interest5_3_0_,
        accounts0_.ACCOUNT_TYPE as ACCOUNT1_3_0_
    from
        Account accounts0_
    where
        accounts0_.CUSOMTER_ID=?
 
Aqui podemos apreciar que no se lanzan las consultas SQL para obtener los address ya que fueron recuperados por la 1er consulta SQL que se lanzo (ver traza sql de esta query de JPA)
Ademas, por cada customer que se obtiene, se obtienen tambien los address y aqui podemos ver una clara diferencia ya que esta consulta no lanza SQLs intermedios como si lo hacia la otra opcion. 
Es decir, una vez que se obtuvo todos los customers, luego el recuperar cada address de cada customer, no significara que por detras se lanze una consulta SQL ya que la query JPA resolvio esto de una vez. En cambio, sino usamos la notacion FETCH, al tratar de obtener un address de un customer en particular, por detras lanzara la aplicacion una consutla SQL obteniendo todos los datos referentes al address.

El resultado es el siguiente (igual al ejemplo anterior)

CUSTOMER ID 1 First name 1 Last name 1
ADDRESS ID 2
ADDRESS ID 0
CUSTOMER ID 1 First name 1 Last name 1
ADDRESS ID 2
ADDRESS ID 0

En conclusion, lo que cambia con la notacion FETCH no es el resultado final de los datos que se van a obtener sino la forma (por detras) en que se obtienen los datos. Si a traves de una query obtenemos todo o bien si vamos a ir lanzando a medida que necesitemos las distintas queries para obtener los datos relacionados.

■ Identificadores reservados para el lenguaje de queries JPQL

  • SELECT
  • FROM
  • WHERE
  • UPDATE
  • DELETE
  • JOIN
  • OUTER
  • INNER
  • LEFT
  • GROUP
  • BY
  • HAVING
  • FETCH
  • DISTINCT
  • OBJECT
  • NULL
  • TRUE
  • FALSE
  • NOT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • AS
  • UNKNOWN
  • EMPTY
  • MEMBER
  • OF
  • IS
  • AVG
  • MAX
  • MIN
  • SUM
  • COUNT
  • ORDER
  • BY
  • ASC
  • DESC
  • MOD
  • UPPER
  • LOWER
  • TRIM
  • POSITION
  • CHARACTER_LENGTH
  • CHAR_LENGTH
  • BIT_LENGTH
  • CURRENT_TIME
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • NEW
  • EXISTS
  • ALL
  • ANY
  • SOME
Descargas de ejemplos de mapeos JPA y consultas JPQL

Por favor, haga clic en: Descargar ejemplos JPA


Bibliografia



* Java Persistence with Hibernate - Revised Edition fo Hiernate In Action - Christian Bauer and Gavin King - 2007

* EJB 3 Developer Guide - A Practical Guide for developers and architects to the Enterprise Java Beans
   Standard - Michael Sikora - 2008
Publicado por Damian Ciocca en 13:10
Etiquetas:

1 comentario:

Suscribirse a: Enviar comentarios (Atom)