Java Persistence Query Language (JPQL) - Query Language ad oggetti

Il Java Persistence Query Language (JPQL) è un query language utilizzato per effettuare query su entità memorizzate su database relazionali. La sintassi è simile a quella di SQL, ma opera su oggetti entità piuttosto che direttamente sulle tabelle del database. JPQL permette di scrivere query portabili, indipendentemente dal sottostante data store.

Sintassi completa: JPQL BNF

Esempi

- SELECT

SELECT employee FROM Employee employee JOIN employee.address address WHERE address.city = :city AND employee.firstName LIKE :name ORDER BY employee.firstName

- FROM

FROM Employee employee JOIN FETCH employee.address LEFT OUTER JOIN FETCH employee.phones JOIN employee.manager manager, Employee ceo

- WHERE

WHERE employee.firstName = :name AND employee.address.city LIKE 'Ott%' ESCAPE '/' OR employee.id IN (1, 2, 3) AND (employee.salary * 2) > 40000

- GROUP BY

GROUP BY employee.address.country, employee.address.city HAVING COUNT(employee.id) > 500

- ORDER BY

ORDER BY employee.address.country, employee.address.city DESC

- Subquery

WHERE employee.salary = (SELECT MAX(wellPaid.salary) FROM Employee wellPaid)

- UPDATE

UPDATE Employee SET salary = salary * 2 WHERE address.city = :city

- DELETE

DELETE FROM Employee WHERE address.city = :city

Esempio in Java

Query query = entityManager.createQuery("SELECT a FROM Address a WHERE a.customerNo = :customerNo");
query.setParameter("customerNo",customerNo);

con operatore:

Query query = entityManager.createQuery("SELECT a FROM Address a WHERE a.customerNo = ?1 OR a.customerNo = ?2");

query.setParameter(1,customerNo1).setParameter(2,customerNo2);