martes, 14 de junio de 2011

Java Persistence Query Language

Para este ejemplo estoy utilizando Netbeans 6.9, Ubuntu 10.04, Glassfish 3.0 y Derby DB.
En este ejemplo voy a ejemplificar el funcionamiento de EJB en un Enterprise Project. Para esto vamos a crear un Enterprise Project llamado ConsultasEJBQL.





Luego de eso en el proyecto EJB vamos a crear una unidad de persistencia. La llamaremos puEJBQL, EclipseLink será el proveedor de persistencia y apuntará al datasource jdbc/sample de Derby y como estrategia de generación de las tablas, seleccionamos None.

Hecho esto, vamos a crear beans de entidad por medio de la opción: "Entity Classes from Database"
Seleccionamos todas las tablas, damos click sobre Next.




Luego colocamos un paquete, en este caso es: com.ejemplo.ejb.entity y damos click sobre Next.

En la caja de selección de Collection Type, seleccionamos java.util.List y damos click en Finish.



Una vez creados los Entity Beans vamos a crear un Session Bean del tipo Stateless, con interface remoto, llamado Utilitario en el paquete com.ejemplo.ejb.session

A continuación muesto ciertas consultas EJBQL, definidas en Utilitario.

package com.ejemplo.ejb.session;

import com.ejemplo.ejb.entity.Customer;
import com.ejemplo.ejb.entity.Manufacturer;
import com.ejemplo.ejb.entity.Product;
import com.ejemplo.ejb.entity.ProductCode;
import java.util.List;
import java.util.Vector;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

@Stateless
public class Utilitario implements UtilitarioRemote {

@PersistenceContext
EntityManager em;

@Override
public void getAllCustomers() {
String consulta1 = "select obj from Customer obj";
System.err.println("____CONSULTA CUSTOMER____");
Query query = em.createQuery(consulta1);
for (Customer customer : (List) query.getResultList()) {
System.out.println(customer.getName());
}
}

@Override
public void getCustomerByName(String parametro) {
String consulta2 = "select obj from Customer obj where obj.city=:ciudad";
System.err.println("____CONSULTA CUSTOMER PARAMETRIZADA____");
Query query = em.createQuery(consulta2);
query.setParameter("ciudad", parametro);
for (Customer customer : (List) query.getResultList()) {
System.out.println(customer.getName());
}
}

@Override
public void consultaMaximoResultados() {
String consulta1 = "select obj from Customer obj";
System.err.println("____CONSULTA MAXIMA RESULTADOS____");
Query query = em.createQuery(consulta1);
query.setMaxResults(5);
for (Customer customer : (List) query.getResultList()) {
System.out.println(customer.getName());
}
}

@Override
public void consultaUnica() {
String resultadoUnico = "select obj from Customer obj where obj.customerId=:id";
System.err.println("____CONSULTA UNICA____");
Integer parametro = 1;
Query query = em.createQuery(resultadoUnico);
query.setParameter("id", parametro);
Customer customer = (Customer) query.getSingleResult();
System.out.println(customer.getName());
}

@Override
public void consultarNamedQuery() {
String consultaNamedQuery = "Product.findAll";
System.err.println("____CONSULTA NAMED QUERY____");
Query query = em.createNamedQuery(consultaNamedQuery);
for (Product product : (List) query.getResultList()) {
System.out.println(product.getDescription());
}
}

@Override
public void consultarNamedQueryAddress() {
String consultaNamedQueryDireccion = "Customer.findByAddressline1";
System.err.println("____CONSULTA NAMED QUERY DIRECCION____");
Query query = em.createNamedQuery(consultaNamedQueryDireccion);
query.setParameter("addressline1", "9754 Main Street");
for (Customer customer : (List) query.getResultList()) {
System.out.println(customer.getName());
}
}

@Override
public void consultarNativeQuery() {
String consultaNativeQuery = "Select * from Manufacturer";
System.err.println("____CONSULTA NAMED QUERY____");
Query query = em.createNativeQuery(consultaNativeQuery, Manufacturer.class);
for (Manufacturer fabricante : (List) query.getResultList()) {
System.out.println(fabricante.getName());
}
}

@Override
public void consultarNativeQueryLista() {
String consultaNativeQueryList = "Select name,state,city from Manufacturer";
System.err.println("____CONSULTA NATIVE QUERY____");
Query query = em.createNativeQuery(consultaNativeQueryList);
List resultados = (List) query.getResultList();
for (int i = 0; i < resultados.size(); i++) {
//System.out.println((Vector) resultados.get(i));//Usado para TopLink
System.out.println(java.util.Arrays.toString((Object[]) resultados.get(i)));//Usado para EclipseLink
}
}

@Override
public void consultarPosicional() {
String consultaPosicional = "select obj from Manufacturer obj where obj.city=?1";
System.err.println("____CONSULTAR POSICIONAL____");
String parametro1 = "San Jose";
Query query = em.createQuery(consultaPosicional);
query.setParameter(1, parametro1);
for (Manufacturer manufacturer : (List) query.getResultList()) {
System.out.println(manufacturer.getName());
}
}

@Override
public void consultaGrupo() {
String consultaGrupo = "select pc.prodCode from ProductCode pc GROUP BY pc.prodCode";
System.err.println("____CONSULTA GRUPO____");
Query query = em.createQuery(consultaGrupo);
List resultados = (List) query.getResultList();
for (int i = 0; i < resultados.size(); i++) {
System.out.println((String) resultados.get(i));
}
}

@Override
public void actualizar() {
String update = "UPDATE MicroMarket mm SET mm.areaLength=mm.areaWidth";
Query query = em.createQuery(update);
query.executeUpdate();
}

@Override
public void eliminar() {
Manufacturer m = new Manufacturer();
m.setManufacturerId(777);
m.setName("ejemplo");
m.setAddressline1("Amazonas");
m.setAddressline2("NNUU");
m.setCity("Quito");
m.setState("Pi");
m.setZip("123");
m.setPhone("123456789");
m.setFax("987654321");
m.setEmail("ejemplo@gmail.com");
m.setRep("cualquiera");
em.persist(m);
String delete = "DELETE FROM Manufacturer c WHERE c.name ='ejemplo'";
Query query = em.createQuery(delete);
query.executeUpdate();
}

@Override
public void consultaGroupBy() {
String consultaGroupBy = "SELECT p.productCode.prodCode,AVG(p.purchaseCost) FROM Product p GROUP BY p.productCode.prodCode ORDER BY p.productCode.prodCode ASC";
System.err.println("____CONSULTA GROUP BY____");
Query query = em.createQuery(consultaGroupBy);
List resultados = (List) query.getResultList();
for (int i = 0; i < resultados.size(); i++) {
System.out.println(((Object[]) resultados.get(i))[0] + "," + ((Object[]) resultados.get(i))[1]);
}
}

@Override
public void consultaPrecios() {
String consultarValoresEntre = "SELECT p FROM Product p WHERE p.purchaseCost<:maximo AND p.purchaseCost>:minimo";
System.err.println("____CONSULTA VALOR ENTRE____");
Query query = em.createQuery(consultarValoresEntre);
query.setParameter("maximo", new Double(40));
query.setParameter("minimo", new Double(10));
for (Product p : (List) query.getResultList()) {
System.out.println(p.getDescription());
}
}

@Override
public void consultaPrecios2() {
String consultarValoresEntre2 = "SELECT p FROM Product p WHERE p.purchaseCost BETWEEN :minimo AND :maximo";
System.err.println("____CONSULTA VALOR ENTRE 2____");
Query query = em.createQuery(consultarValoresEntre2);
query.setParameter("maximo", new Double(40));
query.setParameter("minimo", new Double(10));
for (Product p : (List) query.getResultList()) {
System.out.println(p.getDescription());
}
}

@Override
public void consultaLike() {
String consultarLike = "SELECT p FROM Product p WHERE p.description LIKE 'Lea%'";
System.err.println("____CONSULTA LIKE____");
Query query = em.createQuery(consultarLike);
for (Product p : (List) query.getResultList()) {
System.out.println(p.getDescription());
}
}

@Override
public void consultaJoin() {
String consultaJoin1 = "select pc FROM ProductCode pc join pc.productList p WHERE p.purchaseCost>10000";
System.err.println("____CONSULTA JOIN____");
Query query = em.createQuery(consultaJoin1);
for (ProductCode pc : (List) query.getResultList()) {
System.out.println(pc.getDescription());
}
}

@Override
public void consultarJoin2() {
String consultarJoin2 = "select p from Product p LEFT join p.productCode HW";
System.err.println("____CONSULTA JOIN____");
Query query = em.createQuery(consultarJoin2);
for (Product p : (List) query.getResultList()) {
System.out.println(p.getDescription());
}
}

@Override
public void consultarJoin3() {
String consultarJoin3 = "SELECT Object(pc) FROM ProductCode pc, IN (pc.productList) pl where pc.prodCode='SW'";
System.err.println("____CONSULTA JOIN____");
Query query = em.createQuery(consultarJoin3);
for (ProductCode pc : (List) query.getResultList()) {
System.out.println(pc.getDescription());
}
}

@Override
public void consultarJoin4() {
String consultarJoin4 = "select pc FROM ProductCode pc join pc.productList p WHERE p.quantityOnHand>50";
System.err.println("____CONSULTA JOIN____");
Query query = em.createQuery(consultarJoin4);
for (ProductCode pc : (List) query.getResultList()) {
System.out.println(pc.getDescription());
}
}

@Override
public void consultarJoin5() {
String consultarJoin5 = "SELECT DISTINCT pc FROM ProductCode pc, IN(pc.productList) l WHERE l.manufacturer.name = 'Acer'";
System.err.println("____CONSULTA JOIN____");
Query query = em.createQuery(consultarJoin5);
for (ProductCode pc : (List) query.getResultList()) {
System.out.println(pc.getDescription());
}
}

@Override
public void consultarJoin6() {
String consultarJoin6 = "SELECT pc from ProductCode pc LEFT JOIN pc.productList o WHERE pc.prodCode='SW'";
System.err.println("____CONSULTA JOIN____");
Query query = em.createQuery(consultarJoin6);
for (ProductCode pc : (List) query.getResultList()) {
System.out.println(pc.getDescription());
}
}

@Override
public void consultaFuncion() {
String consultaFuncionRaiz = "select SQRT(p.purchaseCost) from Product p";
System.err.println("____CONSULTA FUNCION SQRT____");
Query query = em.createQuery(consultaFuncionRaiz);
List resultados = (List) query.getResultList();
for (int i = 0; i < resultados.size(); i++) {
System.out.println((Double) resultados.get(i));
}
}
}




Esta es la interface remota de Utilitario:

package com.ejemplo.ejb.session;

import javax.ejb.Remote;

@Remote
public interface UtilitarioRemote {
public void getAllCustomers();

public void getCustomerByName(String ciudad);

public void consultaMaximoResultados();

public void consultaUnica();

public void consultarNamedQuery();

public void consultarNamedQueryAddress();

public void consultarNativeQuery();

public void consultarNativeQueryLista();

public void consultarPosicional();

public void consultaJoin();

public void consultaGrupo();

public void consultaFuncion();

public void actualizar();

public void eliminar();

public void consultarJoin2();

public void consultarJoin3();

public void consultaGroupBy();

public void consultaPrecios();

public void consultaPrecios2();

public void consultaLike();

public void consultarJoin4();

public void consultarJoin5();

public void consultarJoin6();

}


y este es el código de la clase Main:

package consultaejbql;

import com.ejemplo.ejb.session.UtilitarioRemote;
import javax.ejb.EJB;

public class Main {

@EJB private static UtilitarioRemote utilitario;
public static void main(String[] args) {
utilitario.getAllCustomers();
utilitario.getCustomerByName("New York");
utilitario.consultaMaximoResultados();
utilitario.consultaUnica();
utilitario.consultarNamedQuery();
utilitario.consultarNamedQueryAddress();
utilitario.consultarNativeQuery();
utilitario.consultarNativeQueryLista();
utilitario.consultarPosicional();
utilitario.consultaJoin();
utilitario.consultaFuncion();
utilitario.consultaGrupo();
utilitario.actualizar();
utilitario.eliminar();
utilitario.consultarJoin3();
utilitario.consultaPrecios();
utilitario.consultaPrecios2();
utilitario.consultaLike();
utilitario.consultaGroupBy();
}
}

y la salida es:
SEVERE: ____CONSULTA CUSTOMER____
INFO: Instantiated an instance of org.hibernate.validator.engine.resolver.JPATraversableResolver.
INFO: EclipseLink, version: Eclipse Persistence Services - 2.0.1.v20100213-r6600
INFO: file:/home/santiago/NetBeansProjects/ConsultaEJBQL/dist/gfdeploy/ConsultaEJBQL/ConsultaEJBQL-ejb_jar/_puEJBQL login successful
INFO: JumboCom
INFO: Livermore Enterprises
INFO: Oak Computers
INFO: Nano Apple
INFO: HostProCom
INFO: CentralComp
INFO: Golden Valley Computers
INFO: ejemplo
INFO: West Valley Inc.
INFO: Ford Motor Co
INFO: Big Car Parts
INFO: New Media Productions
INFO: Yankee Computer Repair
SEVERE: ____CONSULTA CUSTOMER PARAMETRIZADA____
INFO: New Media Productions
INFO: Yankee Computer Repair
SEVERE: ____CONSULTA MAXIMA RESULTADOS____
INFO: JumboCom
INFO: Livermore Enterprises
INFO: Oak Computers
INFO: Nano Apple
INFO: HostProCom
SEVERE: ____CONSULTA UNICA____
INFO: JumboCom
SEVERE: ____CONSULTA NAMED QUERY____
INFO: Identity Server
INFO: Accounting Application
INFO: 1Ghz Sun Blade Computer
INFO: 10Gb Ram
INFO: Sound Card
INFO: Printer Cable
INFO: 24 inch Digital Monitor
INFO: 104-Key Keyboard
INFO: Directory Server
INFO: 512X IDE DVD-ROM
INFO: Learn Solaris 10
INFO: Network Cable
INFO: 300Mhz Pentium Computer
INFO: Learn NetBeans
INFO: Corporate Expense Survey
INFO: Sun Studio C++
INFO: Client Server Testing
INFO: Learn Java in 1/2 hour
INFO: Writing Web Service Applications
INFO: Jax WS Application Development Environment
INFO: Java EE 6 Application Development Environment
INFO: Java Application Development Environment
INFO: NetBeans Development Environment
INFO: Solaris x86 Computer
INFO: Ultra Spacr 999Mhz Computer
INFO: 686 7Ghz Computer
INFO: A1 900 watts Speakers
INFO: Mini Computer Speakers
INFO: Computer Tool Kit
INFO: Flat screen Monitor
SEVERE: ____CONSULTA NAMED QUERY DIRECCION____
INFO: Livermore Enterprises
SEVERE: ____CONSULTA NAMED QUERY____
INFO: Google
INFO: Sun MicroSystems
INFO: Acer
INFO: Matrox
INFO: 3Com
INFO: CBX Cables
INFO: Sony
INFO: Getaway
INFO: SoftClip
INFO: Toshiba
INFO: Sams Publishing
INFO: Computer Cables & More
INFO: BMC
INFO: Rico Enterprises
INFO: Dobs Computer Products
INFO: Zetsoft
INFO: Hitachi
INFO: World Savings
INFO: Wells Fargo
INFO: Bank Of America
INFO: Google
INFO: Google
INFO: Google
INFO: Sun MicroSystems
INFO: Sun MicroSystems
INFO: Sun MicroSystems
INFO: Sun MicroSystems
INFO: Sun MicroSystems
INFO: Sun MicroSystems
INFO: Sun MicroSystems
INFO: fabricante1
INFO: fabricante1
INFO: fabricante1
INFO: fabricante1
INFO: Ejemplo Clase
INFO: fabricante1
SEVERE: ____CONSULTA NATIVE QUERY____
INFO: [Google, CA, Mountain View]
INFO: [Sun MicroSystems, CA, Santa Clara]
INFO: [Acer, CA, San Jose]
INFO: [Matrox, CA, Novato]
INFO: [3Com, CA, El Cerrito]
INFO: [CBX Cables, IN, Indianapolis]
INFO: [Sony, CA, San Francisco]
INFO: [Getaway, CA, Santa Clara]
INFO: [SoftClip, MA, Boston]
INFO: [Toshiba, CA, Van Nuys]
INFO: [Sams Publishing, MA, Reading]
INFO: [Computer Cables & More, MI, Dearborn]
INFO: [BMC, CA, Pleasanton]
INFO: [Rico Enterprises, NY, New York]
INFO: [Dobs Computer Products, NM, Albuqerque]
INFO: [Zetsoft, AZ, Tombstone]
INFO: [Hitachi, CA, San Mateo]
INFO: [World Savings, CA, Oakland]
INFO: [Wells Fargo, CA, San Francisco]
INFO: [Bank Of America, CA, San Francisco]
INFO: [Google, CA, Mountain View]
INFO: [Google, CA, Mountain View]
INFO: [Google, CA, Mountain View]
INFO: [Sun MicroSystems, CA, Santa Clara]
INFO: [Sun MicroSystems, CA, Santa Clara]
INFO: [Sun MicroSystems, CA, Santa Clara]
INFO: [Sun MicroSystems, CA, Santa Clara]
INFO: [Sun MicroSystems, CA, Santa Clara]
INFO: [Sun MicroSystems, CA, Santa Clara]
INFO: [Sun MicroSystems, CA, Santa Clara]
INFO: [fabricante1, null, null]
INFO: [fabricante1, null, null]
INFO: [fabricante1, null, null]
INFO: [fabricante1, null, null]
INFO: [Ejemplo Clase, null, null]
INFO: [fabricante1, null, null]
SEVERE: ____CONSULTAR POSICIONAL____
INFO: Acer
SEVERE: ____CONSULTA JOIN____
INFO: Software
SEVERE: ____CONSULTA FUNCION SQRT____
INFO: 33.090784215548595
INFO: 107.24266874709897
INFO: 45.78198335590104
INFO: 7.742738533619742
INFO: 6.320601237224193
INFO: 3.997499218261337
INFO: 24.392621835300936
INFO: 3.3090784215548594
INFO: 7.067531393633848
INFO: 8.363611660042569
INFO: 5.475399528801528
INFO: 5.094114250780011
INFO: 44.73197961190629
INFO: 5.472659317004851
INFO: 8.660254037844387
INFO: 24.412087170088512
INFO: 4.466542286825459
INFO: 4.3531597719357835
INFO: 4.998999899979995
INFO: 14.489651479590528
INFO: 16.122964987867462
INFO: 16.430155203162265
INFO: 14.830711378757258
INFO: 37.429266623859995
INFO: 28.284094470214175
INFO: 24.412087170088512
INFO: 8.36540495134575
INFO: 7.067531393633848
INFO: 6.078651166171653
INFO: 14.140367746278736
SEVERE: ____CONSULTA GRUPO____
INFO: BK
INFO: CB
INFO: FW
INFO: HW
INFO: MS
INFO: SW
SEVERE: ____CONSULTA JOIN____
INFO: Software
INFO: Software
INFO: Software
INFO: Software
INFO: Software
INFO: Software
INFO: Software
INFO: Software
SEVERE: ____CONSULTA VALOR ENTRE____
INFO: Sound Card
INFO: Printer Cable
INFO: 104-Key Keyboard
INFO: Learn Solaris 10
INFO: Network Cable
INFO: Learn NetBeans
INFO: Client Server Testing
INFO: Learn Java in 1/2 hour
INFO: Writing Web Service Applications
INFO: Computer Tool Kit
SEVERE: ____CONSULTA VALOR ENTRE 2____
INFO: Sound Card
INFO: Printer Cable
INFO: 104-Key Keyboard
INFO: Learn Solaris 10
INFO: Network Cable
INFO: Learn NetBeans
INFO: Client Server Testing
INFO: Learn Java in 1/2 hour
INFO: Writing Web Service Applications
INFO: Computer Tool Kit
SEVERE: ____CONSULTA LIKE____
INFO: Learn Solaris 10
INFO: Learn NetBeans
INFO: Learn Java in 1/2 hour
SEVERE: ____CONSULTA GROUP BY____
INFO: BK,24.764
INFO: CB,20.965
INFO: FW,49.95
INFO: HW,717.2372
INFO: MS,55.975
INFO: SW,1775.2112

No hay comentarios:

Publicar un comentario