java : JPA rappels

Parfois en JPA, il peut être difficile de gérer certains cas, comme on aurait pu le faire en SQL classique. Pour cela, il existe les "natives query", qui vous donnent accès à l'ensemble des fonctionnalités du SQL.

un très bon article d'Oracle ...

Supposez, vous souhaitez faire la somme des commandes de "Tortuga Trading":

A noter, un exemple de jointure JPQL :


...
String sup_name ="Tortuga Trading";
BigDecimal sum = (List)em.createNativeQuery("SELECT SUM(p.price*l.quantity)
FROM orders o JOIN orderlineitems l ON o.pono=l.pono
JOIN products p ON l.prod_id=p.prod_id
JOIN suppliers s ON p.sup_id=s.sup_id WHERE sup_name =?1")
.setParameter(1, sup_name)
.getSingleResult();
out.println("The total cost of the ordered products supplied by Tortuga Trading: " + sum +"
");
...


Un second exemple :

...
Double max = (Double) em.createQuery("SELECT MAX(p.price) FROM PurchaseOrder
o JOIN o.orderLineItems l JOIN l.product p JOIN p.supplier s WHERE s.sup_name = 'Tortuga Trading'")
.getSingleResult();
out.println("The highest price for an ordered product supplied by Tortuga Trading: "+ max + "
");
...


Obtenir tous les résultats ou un seul résultat :


...
@PersistenceUnit
private EntityManagerFactory emf;
public void doGet(
...
EntityManager em = emf.createEntityManager();
PrintWriter out = response.getWriter();
List arr_cust = (List)em.createQuery("SELECT c FROM Customer c")
.getResultList();
out.println("List of all customers: "+"
");
Iterator i = arr_cust.iterator();
Customer cust;
while (i.hasNext()) {
cust = (Customer) i.next();
out.println(cust.getCust_id()+"
");
out.println(cust.getCust_name()+"
");
out.println(cust.getEmail()+"
");
out.println(cust.getPhone()+"
");
out.println("----------------" + "
");
}
...


...
Integer cust_id =2;
Customer cust = (Customer)em.createQuery("SELECT c FROM Customer c WHERE c.cust_id=:cust_id")
.setParameter("cust_id", cust_id)
.getSingleResult();
out.println("Customer with id "+cust.getCust_id()+" is: "+ cust.getCust_name()+"
");
...