martes, 24 de mayo de 2011

JPA IV - JPA QL Avanzado

Seguiremos tomando como ejemplo las siguientes tablas y registros como lo hicimos en el tutorial anterior Ver tutorial Tutorial JPA III - HQL y JPA QL y la configuracion de las entidades como se hizo en el primer tutorial de manejo de relaciones Ver tutorial Tutorial JPA I - Manejo de relaciones

Tomando como base estos dos tutoriales, vamos a seguir dando ejemplos y mostrando ejercicios mas avanzados sobre el lenguaje de consultas JPQL y que consultas SQL se ejecutan por detras, para que el desarrollador siempre sepa que es en realidad lo que se esta haciendo.

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:
  • Manejando colecciones (IS EMPTY, MEMBER OF)  
  • Usando constructores (NEW)
  • Subqueries (EXISTS, ALL, ANY)
  • Funciones (CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LENGTH, LOCATE, ABS, SIZE)
  • Query with parameters (positional or named parameters)
  • Manejando fechas y horas
  • Bulk Update and Delete

Ejemplo de tablas que vamos a necesitar para entender los ejemplos que se daran a la hora de explicar un tema.

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     DATEOPENED                 OVERDRAFTLIMIT    INTERESTRATE    CUSOMTER_ID   
 ---------------                -------------              ----------           ---------------------                   -----------------               ---------------               --------------
 C                              1                        10                 6/21/2011 12:00:00 AM       0                                (null)                        1             
 C                              2                        20                 (null)                                 0                                (null)                        1             
 S                              3                        100                6/21/2011 12:00:00 AM       (null)                          0                              1        

TABLA CUSTOMER_ADDRESS

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

TABLA ADDRESS
 
 ADDRESS_ID     ADDRESSLINE     COUNTRY     POSTCODE   
 -------------             --------------              ----------            -----------
 2                           addressLine            ARGENTINA  1428       
 0                           addressLine2          BRASIL            1629       
 

■ Manejando colecciones

a. IS EMPTY

Query query = em.createQuery("SELECT c FROM Customer c WHERE c.addresses IS EMPTY"); 
List customers =  query.getResultList(); 
for (Customer customer : customers) {
   log.debug("CUSTOMER ID " + customer.getId() + " "
             + customer.getFirstName() + " " 
             + customer.getLastName()); 
}

La traza SQL es la siguiente:

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
        not (exists (select
            address2_.ADDRESS_ID
        from
            CUSTOMER_ADDRESS addresses1_,
            Address address2_
        where
            customer0_.CUSTOMER_ID=addresses1_.CUST_ID
            and 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
        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=?

El resultado es el siguiente:

CUSTOMER ID 2 First name 2 Last name 2
CUSTOMER ID 3 First name 3 Last name 3
CUSTOMER ID 4 First name 4 Last name 4

Como podemos ver, siguiendo el ejemplo de las tablas y registros, esta query obtiene solo los customers que no tienen address asociados, y para este caso, son todos menos el 1er customer.


b. MEMBER OF

Query query1 = em.createQuery("SELECT DISTINCT c FROM Customer c, Address a WHERE a MEMBER OF c.addresses"); 
List customers1 =  query1.getResultList(); 
for (Customer customer : customers1) { 
   log.debug("CUSTOMER ID " + customer.getId() + " " 
              + customer.getFirstName() + " " 
              + customer.getLastName()); 
}

La traza SQL que lanza esta consulta por detras es la siguiente:

Hibernate:
    select
        distinct 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_,
        Address address1_
    where
        address1_.ADDRESS_ID in (
            select
                address3_.ADDRESS_ID
            from
                CUSTOMER_ADDRESS addresses2_,
                Address address3_
            where
                customer0_.CUSTOMER_ID=addresses2_.CUST_ID
                and addresses2_.ADD_ID=address3_.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=?

El resultado de la ejecucion de esta query JPA es lo siguiente:

CUSTOMER ID 1 First name 1 Last name 1

Como podemos ver, esta consulta solo traera aquellos customers que al menos tengan alguna address. En otras palabras, solo traera aquellos customers que tengan al menos una direccion.


■ Usando constructores

Usando el constructor de un objeto en la clausula del SELECT, se puede inicializar el objeto con el resultado de la query

Antes tendremos que crear una clase que utilizaremos para guardar los datos de la query. A esta clase la llamaremos CustomerDef

package module1;
import java.io.Serializable;

public class CustomerDef implements Serializable{
   
    private String firstName;
    private String lastName;
    private String refereeName;
   
    public CustomerDef() {
    }

    public CustomerDef(String firstName, String lastName, String refereeName) {
        super();
        this.firstName = firstName;
        this.lastName = lastName;
        this.refereeName = refereeName;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getRefereeName() {
        return refereeName;
    }

    public void setRefereeName(String refereeName) {
        this.refereeName = refereeName;
    }
}


Query query = em.createQuery("SELECT NEW module1.CustomerDef(c.firstName, c.lastName, c.referee.name) FROM Customer c"); 
List customers =  query.getResultList(); 
for (CustomerDef customer : customers) { 
   log.debug("CUSTOMERDEF name: " + customer.getFirstName()
                       + " lastName: " + customer.getLastName()
                       + " referee "+customer.getRefereeName()); 
}

La traza SQL es la siguiente:

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

El resultado de la query es la siguiente:

CUSTOMERDEF name: First name 1 lastName: Last name 1 referee Referee 1
CUSTOMERDEF name: First name 2 lastName: Last name 2 referee Referee 2
CUSTOMERDEF name: First name 3 lastName: Last name 3 referee Referee 1

Observamos que el resultado de la query se mapeo automaticamente con las propiedades de la clase CustomerDef. Por otra parte, vemos que solo nos trajo los 3 customers que tienen referee, dejando excluido el customer cuyo ID es 4 ya que no tiene referee asociado.



■ Subqueries

Las subconsultas pueden ser utilizadas en las clausulas WHERE o HAVING. La subconsulta es una sentencia SELECT que va entre parentesis y que forma parte de una expresion condicional

Query query = em.createQuery("SELECT a FROM Account a WHERE a.balance > " +
        "(SELECT MIN(aux.balance) from Account aux)"); 
List accounts =  query.getResultList(); 
for (Account acc : accounts) { 
    log.debug("ACCOUNT ID: " + acc.getId()
              + " balance: " + acc.getBalance()); 
}

La traza SQL es la siguiente:

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>(
            select
                MIN(account1_.balance)
            from
                Account account1_
        )
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=?

El resultado de la query JPA es la siguiente:

ACCOUNT ID: 2 balance: 20.0
ACCOUNT ID: 3 balance: 100.0


Como podemos ver, el resultado son dos registros de los tres que tiene la tabla Account. Eso es correcto porque solo uno de los tres tiene el campo balance <= 10 mientras que los otros dos, cumplen la condicion que el campo balance sea > 10


a. EXISTS

Para este tipo de operador, siempre necesitamos incluir en la subconsulta el ID o como en este caso, directamente el customer que viene desde la consulta padre. Lo que nos indicaria esta tipo de subconsultas es que voy a mostrar aquellos customers donde el customer tenga asociado una account (es decir, que el customer de la consulta padre sea igual al customer de la subconsulta)

Query query1 = em.createQuery("SELECT c FROM Customer c WHERE EXISTS " 
           + " (SELECT a FROM Account a WHERE a.customer = c)");
List customers = query1.getResultList(); 
for (Customer customer : customers) { 
    log.debug("CUSTOMER " + customer.getFirstName() + " " 
               + customer.getLastName());
}

La traza SQL es la siguiente:
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
        exists (
            select
                account1_.ACCOUNT_ID
            from
                Account account1_
            where
                account1_.CUSOMTER_ID=customer0_.CUSTOMER_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=?



El resultado de la query es la siguiente:

CUSTOMER First name 1 Last name 1

Podemos observar que existe solo un customer que tiene 3 accounts segun el ejemplo que venimos manejando.


b. ALL

Query query2 = em.createQuery("SELECT c FROM Customer c WHERE c.referee.id >=
        ALL (SELECT r.id FROM Referee r WHERE r.id = 1 or r.id = 2)");
List customers2 = query2.getResultList(); 
for (Customer customer : customers2) { 
  log.debug("CUSTOMER " + customer.getFirstName() + " " + customer.getLastName());            
}

La traza SQL es la siguiente:

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_.referee_id>=all (
            select
                referee1_.id
            from
                Referee referee1_
            where
                referee1_.id=1
                or referee1_.id=2
        )
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=?

El resultado de la ejecucion de la query es la siguiente:

CUSTOMER First name 2 Last name 2

Como podemos ver, solo se mostraran los customers cuyo referee ID sea mayor o igual a 1 y mayor o igual a 2, y el unico que cumple con esa condicion es el customer cuyo ID es 2, ya que el resto de los customers o no tiene asociado un referee y o solo estan asociados con el referee cuyo ID es 1 (y este ultimo no cumple la condicion que el ID del referee sea mayor o igual a 1 y mayor o igual a 2) ya que este operador para que de true, necesita que se cumpla la condicion de >= para todos los valores de la subconsulta..


c. ANY

Query query3 = em.createQuery("SELECT c FROM Customer c WHERE c.referee.id >= 
            ANY (SELECT r.id FROM Referee r WHERE r.id = 1 or r.id = 2)"); 
List customers3 = query3.getResultList(); 
for (Customer customer : customers3) { 
 log.debug("CUSTOMER " + customer.getFirstName() + " " + customer.getLastName());
}

La traza SQL es la siguiente:

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_.referee_id>=any (
            select
                referee1_.id
            from
                Referee referee1_
            where
                referee1_.id=1
                or referee1_.id=2
        )
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=?

El resultado de la consulta es la siguiente:

CUSTOMER First name 1 Last name 1
CUSTOMER First name 2 Last name 2
CUSTOMER First name 3 Last name 3

Como podemos observar, existen 3 customers que cumplen con alguno de los dos posibles valores que nos devuleve la subconsulta (los valores que retorna la subconsulta son 1 y 2). Solo un customer no cumple con alguno de estos dos valores ya que no tiene asociado un referee. El operador ANY sera verdadero si al menos se cumple la condicion con alguno de los valores de la subconsulta.


■ Funciones

JPAQL provee una seria de funciones para ser usadas en la clausula WHERE o HAVING

a. CONCAT

Query query = em.createQuery("SELECT a FROM Address a WHERE CONCAT(a.country, a.postCode) LIKE 'A%1428'");
List addresses =  query.getResultList();
for (Address acc : addresses) {
    log.debug("ADDRESS ID: " + acc.getId() + " country: " + acc.getCountry());
}       

La traza SQL es la siguiente:

Hibernate:
    select
        address0_.ADDRESS_ID as ADDRESS1_5_,
        address0_.addressLine as addressL2_5_,
        address0_.country as country5_,
        address0_.postCode as postCode5_
    from
        Address address0_
    where
        address0_.country||address0_.postCode like 'A%1428'

El resultado de la ejecucion de la consulta es la siguiente:

ADDRESS ID: 2 country: ARGENTINA


b. SUBSTRING

La 1er posicion del string que querramos obtener sera denotada como 1

Query query1 = em.createQuery("SELECT a FROM Address a WHERE SUBSTRING(a.postCode, 1, 3) = '142'"); 
List addresses1 =  query1.getResultList();
for (Address acc : addresses1) { 
   log.debug("ADDRESS ID: " + acc.getId() + " country: " + acc.getCountry()); 
}

La traza SQL es la siguiente:

Hibernate:
    select
        address0_.ADDRESS_ID as ADDRESS1_5_,
        address0_.addressLine as addressL2_5_,
        address0_.country as country5_,
        address0_.postCode as postCode5_
    from
        Address address0_
    where
        substr(address0_.postCode, 1, 3)='142' 

El resultado de la ejecucion de la consulta es la siguiente:
  
ADDRESS ID: 2 country: ARGENTINA


c. TRIM

Query query2 = em.createQuery("SELECT a FROM Address a WHERE TRIM(a.country) = 'ARGENTINA'");
List addresses2 =  query2.getResultList(); 
for (Address acc : addresses2) { 
   log.debug("ADDRESS ID: " + acc.getId() + " country: " + acc.getCountry()); 
}      
   
La traza SQL es la siguiente:

Hibernate:
    select
        address0_.ADDRESS_ID as ADDRESS1_5_,
        address0_.addressLine as addressL2_5_,
        address0_.country as country5_,
        address0_.postCode as postCode5_
    from
        Address address0_
    where
        trim(address0_.country)='ARGENTINA'



El resultado de la consulta segun el ejemplo que venimos siguiendo es el siguiente: 

ADDRESS ID: 2 country: ARGENTINA


d. LOWER y UPPER

Query query3 = em.createQuery("SELECT a FROM Address a WHERE LOWER(a.country) = 'ARGENTINA'"); 
List addresses3 =  query3.getResultList(); List
for (Address acc : addresses3) { 
   log.debug("ADDRESS ID: " + acc.getId() + " country: " + acc.getCountry()); 
}

La traza SQL es la siguiente:

Hibernate:
    select
        address0_.ADDRESS_ID as ADDRESS1_5_,
        address0_.addressLine as addressL2_5_,
        address0_.country as country5_,
        address0_.postCode as postCode5_
    from
        Address address0_
    where
        lower(address0_.country)='ARGENTINA'

El resultado de la query es:

No hay resultado


Query query4 = em.createQuery("SELECT a FROM Address a WHERE UPPER(a.country) = 'ARGENTINA'"); 
List addresses4 =  query4.getResultList(); List
for (Address acc : addresses4) { 
   log.debug("ADDRESS ID: " + acc.getId() + " country: " + acc.getCountry()); 
}

La traza SQL es la siguiente:

  select
        address0_.ADDRESS_ID as ADDRESS1_5_,
        address0_.addressLine as addressL2_5_,
        address0_.country as country5_,
        address0_.postCode as postCode5_
    from
        Address address0_
    where
        upper(address0_.country)='ARGENTINA'

El resultado de la query es:

ADDRESS ID: 2 country: ARGENTINA

Como podemos ver, en la tabla address, el registro que tiene el country "argentina " esta en minusculas, por ende, la query que tiene el lower es la unica que puede obtener un resultado ya que la comparacion se esta realizando con un valor en mayusculas ("ARGENTINA")


e. LENGTH

Esta funcion nos devuelve el tamaño de un string como un entero

Query query5 = em.createQuery("SELECT a FROM Address a WHERE LENGTH(a.country) = 6"); 
List addresses5 =  query5.getResultList(); List
for (Address acc : addresses5) { 
   log.debug("ADDRESS ID: " + acc.getId() + " country: " + acc.getCountry()); 
}

La traza SQL es la siguiente:

Hibernate:
    select
        address0_.ADDRESS_ID as ADDRESS1_5_,
        address0_.addressLine as addressL2_5_,
        address0_.country as country5_,
        address0_.postCode as postCode5_
    from
        Address address0_
    where
        length(address0_.country)=6

El resultado de la query es lo siguiente:

ADDRESS ID: 0 country: BRASIL


f. LOCATE

Funcion que nos devuelve la primera posicion de una cadena de string (target_string) dada una posicion determinada (start_position). La primer posicion de un caracter se empieza  a contar a partir del 1
LOCATE(search_string, target_string [, start_position] )

Query query6 = em.createQuery("SELECT a FROM Address a WHERE LOCATE('2', a.postCode) = 3");
List addresses6 =  query6.getResultList(); List
for (Address acc : addresses6) { 
  log.debug("ADDRESS ID: " + acc.getId() + " country: " + acc.getCountry()); 
}

La traza SQL es la siguiente

Hibernate:
    select
        address0_.ADDRESS_ID as ADDRESS1_5_,
        address0_.addressLine as addressL2_5_,
        address0_.country as country5_,
        address0_.postCode as postCode5_
    from
        Address address0_
    where
        instr(address0_.postCode,'2')=3

El resultado de la query es lo siguiente:

ADDRESS ID: 2 country: ARGENTINA
ADDRESS ID: 0 country: BRASIL

Como podemos ver en esta query, vemos que esta funcion nos devuleve la posicion 3 ya que el valor "2" de la columna postcode esta en dicha posicion, Por otra parte, los dos registros cumplen con esta condicion ya que 1428 y 1629 (valores de los dos registros de la tabla address, columna postcode) el valor 2 esta en la posicion 3.


g. ABS

Función que nos retorna el valor absoluto de un campo dado. Los valores que puede recibir son del tipo double, integer o float.

Query query7 = em.createQuery("SELECT a FROM Account a WHERE ABS(a.balance) > 50");
List accounts7 =  query7.getResultList();
for (Account acc : accounts7) {
  log.debug("ACCOUNT ID: " + acc.getId() + " balance: " + acc.getBalance());
}

La traza SQL es la siguiente

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
        abs(account0_.balance)>50
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=?

El resultado de la query es lo siguiente:

ACCOUNT ID: 3 balance: 100.0


h. SIZE

Query query8 = em.createQuery("SELECT c FROM Customer c WHERE SIZE(c.accounts) = 3"); 
List customers8 = query8.getResultList(); 
for (Customer customer : customers8) { 
  log.debug("CUSTOMER ID: " + customer.getId() + " " + customer.getFirstName());
}

La traza SQL es la siguiente:

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
        (
            select
                count(accounts1_.CUSOMTER_ID)
            from
                Account accounts1_
            where
                customer0_.CUSTOMER_ID=accounts1_.CUSOMTER_ID
        )=3
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=?


El resultado de la query es el siguiente:

CUSTOMER ID: 1 First name 1


■ Query with parameters

a. Positional Parameters

Query query = em.createQuery("SELECT c FROM Customer c WHERE c.firstName LIKE ?1 and c.lastName LIKE ?2") 
.setParameter(1, "First name 1") 
.setParameter(2, "Last name 1"); 
List customers = query.getResultList(); 

b. Named Parameters

Query query1 = em.createQuery("SELECT c FROM Customer c WHERE c.firstName LIKE :firstName and c.lastName LIKE :lastName") 
.setParameter("firstName", "First name 1") 
.setParameter("lastName", "Last name 1"); 
List customers1 = query1.getResultList(); 

En ambos casos la traza SQL es la siguiente:


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_.FIRST_NAME like ?
        )
        and (
            customer0_.LAST_NAME like ?
        )
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=?



■ Manejando fechas y horas

A la hora de persistir un campo de fecha (java.util.Date)  como el campo dateOpened de la tabla Account, este debera ser mapeado a un tipo de dato java.sql.Date de base de datos. Para esto utilizaremos la anotacion @Temporal(TemporalType.DATE)
Existen otras posibilidades de mapeos:
  • @Temporal(TemporalType.TIME) para mapear a java.sql.Time
  • @Temporal(TemporalType.TIMESTAMP) para mapear a java.sql.Timestamp

Calendar c = Calendar.getInstance();
c.set(Calendar.MONTH, 5);
c.set(Calendar.DAY_OF_MONTH, 21);
c.set(Calendar.YEAR, 2011);

Query query = em.createQuery("SELECT a FROM Account a WHERE a.dateOpened = ?1").setParameter(1, c, TemporalType.DATE);
List accounts = query.getResultList();

for (Account account : accounts) {
  log.debug("ACCOUNT ID " + account.getId() + " " + account.getDateOpened());
}

La traza SQL es la siguiente:

Hibernate:
    select
        account0_.ACCOUNT_ID as ACCOUNT2_1_,
        account0_.balance as balance1_,
        account0_.CUSOMTER_ID as CUSOMTER7_1_,
        account0_.dateOpened as dateOpened1_,
        account0_.overdraftLimit as overdraf5_1_,
        account0_.interestRate as interest6_1_,
        account0_.ACCOUNT_TYPE as ACCOUNT1_1_
    from
        Account account0_
    where
        account0_.dateOpened=?
Hibernate:
    select
        customer0_.CUSTOMER_ID as CUSTOMER1_2_2_,
        customer0_.FIRST_NAME as FIRST2_2_2_,
        customer0_.GENDER as GENDER2_2_,
        customer0_.LAST_NAME as LAST4_2_2_,
        customer0_.referee_id as referee7_2_2_,
        customer0_.name1 as name5_2_2_,
        customer0_.name2 as name6_2_2_,
        accounts1_.CUSOMTER_ID as CUSOMTER7_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_1_0_,
        accounts1_.balance as balance1_0_,
        accounts1_.CUSOMTER_ID as CUSOMTER7_1_0_,
        accounts1_.dateOpened as dateOpened1_0_,
        accounts1_.overdraftLimit as overdraf5_1_0_,
        accounts1_.interestRate as interest6_1_0_,
        accounts1_.ACCOUNT_TYPE as ACCOUNT1_1_0_,
        referee2_.id as id0_1_,
        referee2_.comments as comments0_1_,
        referee2_.name as name0_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=?

El resultado de la consulta es la siguiente:

ACCOUNT ID 1 2011-06-21
ACCOUNT ID 3 2011-06-21

Para los positional parameters:

setParameter(int position, Date value, TemporalType temporalType)
setParameter(int position, Calendar value, TemporalType temporalType)

Para los named parameters:

setParameter(String name, Date value, TemporalType temporalType)
setParameter(String name, Calendar value, TemporalType temporalType)

Funciones

JPQL provee las siguientes funciones:

  • CURRENT_DATE retorna el valor de la fecha actual de la BD.
  • CURRENT_TIME retorna el valor de la hora actual de la BD.
  • CURRENT_TIMESTAMP retorna el valor de la fecha y hora actual de la BD

Query query1 = em.createQuery("SELECT a FROM Account a WHERE a.dateOpened = CURRENT_DATE"); 
List accounts1 = query1.getResultList(); 
for (Account account : accounts1) { 
  log.debug("ACCOUNT ID " + account.getId() + " " + account.getDateOpened()); 
}

La traza SQL es la siguiente:

Hibernate:
    select
        account0_.ACCOUNT_ID as ACCOUNT2_1_,
        account0_.balance as balance1_,
        account0_.CUSOMTER_ID as CUSOMTER7_1_,
        account0_.dateOpened as dateOpened1_,
        account0_.overdraftLimit as overdraf5_1_,
        account0_.interestRate as interest6_1_,
        account0_.ACCOUNT_TYPE as ACCOUNT1_1_
    from
        Account account0_
    where
        account0_.dateOpened=CURRENT_DATE


■ Bulk Update and Delete

JPA permite borrar o actualizar un varias instancias de entidades de una clase incluyendo tambien subclases de entidades.
Aqui vemos, en un primer lugar como obtenemos todos los accounts y luego vemos de borrar solo aquellos accounts que no tengan una fecha de apertura. Luego volvemos a obtener todos los accounts y vemos que la eliminacion se efectuo correctamente.

Query query = em.createQuery("SELECT a FROM Account a");
        List accounts = query.getResultList(); 
for (Account account : accounts) { 
  log.debug("ACCOUNT ID " + account.getId() + " " + account.getDateOpened());
}

em.clear();
em.getTransaction().begin();
em.createQuery("DELETE FROM Account a WHERE a.dateOpened IS NULL").executeUpdate();
em.getTransaction().commit();

em.clear();
Query query2 = em.createQuery("SELECT a FROM Account a");
List accounts2 = query2.getResultList();
for (Account account : accounts2) {
 log.debug("ACCOUNT ID " + account.getId() + " " + account.getDateOpened()); 
}       

La traza SQL con el resultado de las queries es lo siguiente:

Hibernate:
    select
        account0_.ACCOUNT_ID as ACCOUNT2_1_,
        account0_.balance as balance1_,
        account0_.CUSOMTER_ID as CUSOMTER7_1_,
        account0_.dateOpened as dateOpened1_,
        account0_.overdraftLimit as overdraf5_1_,
        account0_.interestRate as interest6_1_,
        account0_.ACCOUNT_TYPE as ACCOUNT1_1_
    from
        Account account0_
Hibernate:
    select
        customer0_.CUSTOMER_ID as CUSTOMER1_2_2_,
        customer0_.FIRST_NAME as FIRST2_2_2_,
        customer0_.GENDER as GENDER2_2_,
        customer0_.LAST_NAME as LAST4_2_2_,
        customer0_.referee_id as referee7_2_2_,
        customer0_.name1 as name5_2_2_,
        customer0_.name2 as name6_2_2_,
        accounts1_.CUSOMTER_ID as CUSOMTER7_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_1_0_,
        accounts1_.balance as balance1_0_,
        accounts1_.CUSOMTER_ID as CUSOMTER7_1_0_,
        accounts1_.dateOpened as dateOpened1_0_,
        accounts1_.overdraftLimit as overdraf5_1_0_,
        accounts1_.interestRate as interest6_1_0_,
        accounts1_.ACCOUNT_TYPE as ACCOUNT1_1_0_,
        referee2_.id as id0_1_,
        referee2_.comments as comments0_1_,
        referee2_.name as name0_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=?

ACCOUNT ID 1 2011-06-23
ACCOUNT ID 2 null
ACCOUNT ID 3 2011-06-23
Hibernate:
    delete
    from
        Account
    where
        dateOpened is null
Hibernate:
    select
        account0_.ACCOUNT_ID as ACCOUNT2_1_,
        account0_.balance as balance1_,
        account0_.CUSOMTER_ID as CUSOMTER7_1_,
        account0_.dateOpened as dateOpened1_,
        account0_.overdraftLimit as overdraf5_1_,
        account0_.interestRate as interest6_1_,
        account0_.ACCOUNT_TYPE as ACCOUNT1_1_
    from
        Account account0_
Hibernate:
    select
        customer0_.CUSTOMER_ID as CUSTOMER1_2_2_,
        customer0_.FIRST_NAME as FIRST2_2_2_,
        customer0_.GENDER as GENDER2_2_,
        customer0_.LAST_NAME as LAST4_2_2_,
        customer0_.referee_id as referee7_2_2_,
        customer0_.name1 as name5_2_2_,
        customer0_.name2 as name6_2_2_,
        accounts1_.CUSOMTER_ID as CUSOMTER7_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_4_,
        accounts1_.ACCOUNT_ID as ACCOUNT2_1_0_,
        accounts1_.balance as balance1_0_,
        accounts1_.CUSOMTER_ID as CUSOMTER7_1_0_,
        accounts1_.dateOpened as dateOpened1_0_,
        accounts1_.overdraftLimit as overdraf5_1_0_,
        accounts1_.interestRate as interest6_1_0_,
        accounts1_.ACCOUNT_TYPE as ACCOUNT1_1_0_,
        referee2_.id as id0_1_,
        referee2_.comments as comments0_1_,
        referee2_.name as name0_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=?

ACCOUNT ID 1 2011-06-23
ACCOUNT ID 3 2011-06-23



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 19:29
Etiquetas:

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)