IS907 Java EE JPA Queries Java Persistence Query

IS-907 Java EE JPA Queries

Java Persistence Query Language (JPQL) • designed to resemble SQL • • but selects entity objects rather than rows from tables A very simple query to select all persistent employee objects: SELECT e FROM Employee as e IS-202 - Course Info 2

JPQL – simple SELECT clauses • Because we are dealing with objects we can use the dot (. ) notation to select properties from objects. This is called a path expression: SELECT e. name FROM Employee e • It is also possible to select related entities: SELECT e. department FROM Employee e • and even attributes of related entities: SELECT e. department. name FROM Employee e • All these queries return lists of objects (List<String>, List<Department>, and List<String>). IS-202 - Course Info 3

JPQL – Where clauses • We can use WHERE clauses to restrict the number of entities found: SELECT e FROM Employee e WHERE e. department. name = ‘STAB’ • The condition can contain path expressions, constants and operators • Comparison: =, >, <, >=, <>, LIKE, BETWEEN • Logical: AND, OR, NOT IS-202 - Course Info 4

JPQL - JOINs • Entities can be joined using the WHERE clause: SELECT e. name FROM Employee e, Department d WHERE e. department = d AND d. name LIKE ‘S%’ • or using the JOIN operator: SELECT e. name FROM Department d JOIN d. employees e WHERE d. name LIKE ‘S%’ • Using the JOIN operator is the preferred way. IS-202 - Course Info 5

Using queries • The Entity. Manager can create query objects: Typed. Query<Department> q = em. create. Query("select d from Department as d“, Department. class); List<Department> deps = q. get. Result. List(); • The parameter is a query in the Java Persistence Query Language (JPQL) • JPQL resembles SQL but java types and expressions are used rather than the tables and columns in SQL. • get. Result. List() returns a list of the objects found by the query. Query objects have other methods that can be used in special situations (e. g. get. Single. Result() if the query returns only a single object) IS-202 - Course Info 6

JPA – Parametrized JPQL queries • Just as in JDBC we can have parameters in queries • We can use parameter names rather than numbers: Entity. Manager em; Typed. Query<Employee> q = em. create. Query(“SELECT e FROM Employee e “ +”WHERE e. name LIKE : pattern”, Employee. class); q. set. Parameter(“pattern”, “%en”); List<Employee> result = q. get. Result. List(); • The same parameter can occur several times in the query if the value is needed in more than one place (e. g. in a complex WHERE clause) IS-202 - Course Info 7

JPA – Defining Named Queries • Queries are faster and safer if they can be precompiled. • @Named. Query annotations can be used to define queries (usually with parameters) that will be compiled when the application is deployed. @Entity @Named. Queries({ @Named. Query(name=“Employee. find. By. Name” query=“SELECT e FROM Employee e WHERE name like : p”), . . . }) public class Employee {. . } IS-202 - Course Info 8

JPA – Using Named Queries • Named Queries are used in a similar way to dynamic queries: Entity. Manager em; Typed. Query<Employee> q = em. create. Named. Query(“Employee. find. Name”, Employee. class); q. set. Parameter(“p”, “%en”); List<Employee> result = q. get. Result. List(); • The difference is faster execution: • The SQL code has been created in advance • and it can be precompiled (like a JDBC Prepared. Statement) IS-202 - Course Info 9

JPQL – Selecting multiple values • It is possible to SELECT more than one expression, e. g. : SELECT e. name, d. name FROM Department d JOIN d. employees e ORDER BY e. name • This query creates an alphabetical list of names and their department. • The return type is a list of Object[], which could be printed like this: List<Object[]> result = q. get. Result. List(); for (Object[] row : result) { System. out. print(row[0]+”t”); // the employee name and a tabulator System. out. println(row[1]); // the department name } IS-202 - Course Info 10

JPQL – Aggregate Queries • Query results can be aggregated using the functions AVG, COUNT, MAX, MIN, and SUM • Find the average salary: SELECT AVG(e. salary) FROM Employee e • Find the number of employees and max salary in each department: SELECT d. name, COUNT(e), MAX(e. salary) FROM Department d JOIN d. employees e GROUP BY d. name IS-202 - Course Info 11