EclipseLink JPA Black Belt Course

1. EclipseLink JPABlack Belt Course Section 5: Querying <PRESENTER>

2. EclipseLink JPA - Querying • EclipseLink JPA Query Hints • Native Querying

3. Advanced Queries

4. SQL TopLink JDBC Stored Procedure EJB QL OO (API/MW) Query Framework Query By Example J2EE App Mappings Cache Hit? Cache Object Builder Cache Result? ORM Query Execution SQL Query Database Rows Objects

5. Primary Query Types and Options • Primary Query Types • ReadAllQuery • ReadObjectQuery • ReportQuery Primary Query Features • Ordering • Optimizations – Join, Batch, and Bind • Cache: Usage (hits) and Refreshing

6. Query Extensions • Oracle specific operators (DECODE, …) • Outer join support • HINT support • Hierarchical Querying • Flashback querying • Pessimistic locking SELECT … FOR UPDATE [NOWAIT]

7. Defining Named Queries in Java

8. Example: Registering Query to a Descriptor in an After-Load Method public static void sfterLoadAccount(ClassDescriptor desc) { String queryName = “findByOwner”; String param1 = “anOwner”; ReadAllQuery q = new ReadAllQuery(Account.class); ExpressionBuilder bldr = new ExpressionBuilder(); Expression exp = bldr.get(“owner”). like(bldr.getParameter(param1)); q.addArgument(param1); q.setSelectionCriteria(exp); desc.getQueryManager().addQuery(queryName, q); }

9. Query Properties Overview • Ordering (ReadAllQuery): • addAscendingOrdering(), addDescendingOrdering(), addOrdering() • Refresh Policy: • refreshIdentityMapResult() • Always refreshes the object’s attributes with current values in the database • Decreases the chances of optimistic lock failure • Partial Attribute Reading: • addPartialAttribute() • Only populates attributes that are defined by user • Results are read-only and not maintained in the identity map.

10. Query Properties Overview • Cache Maintenance: • dontMaintainCache() will not register objects that are created from this query with the identity map. • Performance optimization • Cache Usage: • Provides options for in-memory querying functionality • conformResultsInUnitOfWork(), checkCacheOnly(), and so on

11. Query Properties Overview • Joined/Batch reading (ReadAllQuery): • addJoinedAttribute(), addBatchReadAttribute() • Attempts to preread related objects by using 0–1 additional SQLs • Pessimistic Locking • Acquiring locks on queried objects

12. Executing Named Queries at Run Time • Use executeQuery() session method: Vector arguments = new Vector(); arguments.add(“Smith”); DatabaseSession session = getDatabaseSession(); Person person = (Person) session.executeQuery(“findByLastName”, Person.class, arguments);

13. An A1 A2 Batch Reading: Motivation • Queries in TopLink are object based: • One query is required to read in object orobjects of the same class • One query for each object relationship(assuming no cache hits) • Example: • “Reading n persons and their addresses”requires n+1 queries P1 SQL SQL SQL P2 Pn SQL

14. An A1 A2 Batch Reading Features • An option on ReadAllQuery objects • Can be set for relationships off the reference class (of query) • Attempts to “batch-read” a specified relationship for all objects that are returned from the query • Using one single query instead of n queries P1 SQL P2 SQL Pn

15. Setting Up Batch Reading • Set on ReadAllQuery: • Specify an attribute name of reference class to batch read. • Nested batch reading: • “Read all sales reps, batch reading their accounts, and the customer for each of those accounts” Expression exp = bldr.get(“age”).greaterThan(40); ReadAllQuery q = new ReadAllQuery(Person.class, exp); q.addBatchReadAttribute(“address”); q.addBatchReadAttribute(“relatives”); ReadAllQuery q = new ReadAllQuery(SalesRep.class, exp); q.addBatchReadAttribute( q.getExpressionBuilder().get(“accounts”). get(“customer”));

16. Joined Reading • Special performance enhancement • One-to-one (1:1) relationships can be read in alongside the objects being queried for. • Available for ReadObject and ReadAll queries • Can propagate down the tree across 1:1 relationships • Not possible to join to the same table • Example: Employee->manager – use batch reading instead • Queries for entire set of joined attributes in one SQL Expression exp = bldr.get(“age”).greaterThan(40); ReadAllQuery q = new ReadAllQuery(Person.class, exp); q.addJoinedAttribute(“address”);

17. Query by Example • Motivation: • Handle dynamic querying based on model • Query by Example (QBE): • The user creates an “example” object, and populates the attributes. • The example object must be of the same class type as the objects being queried. • The example object is passed into query. • The query expression is generated based on the populated attributes. • By default, the AND condition and equality (=) are used to form the expression.

18. QBE Across Relationships • Can use QBE to query across 1:1 relationships: • “Find all John Smiths who live in Springfield, Illinois” // Example object with 1:1 relationship filled Person examplePerson = new Person(); Address a = new Address(); a.setCity(“Springfield”); a.setState(“Illinois”); examplePerson.setAddress(a); examplePerson.setFirstName(“John”); examplePerson.setLastName(“Smith”); ReadAllQuery q = new ReadAllQuery(Person.class); q.setExampleObject(examplePerson);

19. Controlling QBE Expressions • Default: • AND is used to link all subexpressions. • Equality is used for each subexpression. • Attributes that are set to null and primitives that are set to their default values are ignored. • Providing query with a QueryByExamplePolicy allows for further customization: // QBE policy: optional to the query QueryByExamplePolicy policy = new QueryByExamplePolicy(); // Customize the policy policy.excludeValue(new Integer(-1)); ReadAllQuery q = new ReadAllQuery(Person.class); q.setExampleObject(examplePerson); q.setQueryByExamplePolicy(policy);

20. QBE Policy Options • addSpecialOperation(Class,String): • alwaysIncludeAttribute: • excludeValue: • includeAllValues: // All string attributes will use like comparator qbePolicy.addSpecialOperation(String.class, “like”); // Compare value set in this attribute qbePolicy.alwaysIncludeAttribute(Integer.class, “age”); // Do not compare any attributes with this value qbePolicy.excludeValue(Integer.class, new Integer(-1)); // Compare all attributes qbePolicy.includeAllValues();

21. Report Query - Projections • Some queries do not require objects returned. • Example: “Get the maximum salary for all employees in Ottawa” • If objects are not required, then not building them reduces overhead. • Report query provides a way to query data that is associated with objects, not objects themselves. • No custom SQL to write; expression framework is used to define the selection criteria. • Provides the ability to collect statistical data from a set of objects and their related objects • Supports database reporting functions such as SUM, MIN, MAX, AVG, and COUNT • Supports GROUP BY, and ORDER BY

22. Creating a Report Query • A report query is similar to an object query: • Specify the reference class (starting point of reference). • Specify the selection criteria that define the result set to report on. • However, instead of returning objects, the user defines specific data to be returned from the result set. • The report query must be given an expression builder on creation. • The expression builder will be used in building the report. // Create report query with reference class and // expression builder ExpressionBuilder bldr = new ExpressionBuilder(); ReportQuery q = new ReportQuery(Person.class, bldr);

23. Report Query Selection Criteria • Define the selection criteria by using regular TopLink expression framework. • But the report query’s expression builder must be used. • You can now specify object attributes from the result set to report on. • Or a database report function to apply to the result set // Create report of all persons who do not live in USA ExpressionBuilder reportBldr = new ExpressionBuilder(); ReportQuery q = new ReportQuery(Person.class, reportBldr); Expression exp = reportBldr.get(“address”).get(“country”). notEqual(“USA”); q.setSelectionCriteria(exp);

24. Building the Report • You can report on specific attributes of the reference class. • Example: “For people who live outside the USA, provide a list of their ages and marital statuses” • Executing this query returns a collection of ages and marital statuses in the report. • Each element of collection represents data for a particular row in the result set. // Add “age” and “marital status” as attributes to report // for previous report query q.addAttribute(“age”); q.addAttribute(“maritalStatus”);

25. Report Query Results • Executing report query returns a collection of ReportQueryResult objects. • Similar to a DatabaseRow or Hashtable • Can get a reported value by using get(String key) • The user specifies the key when building the report: // Here the key has the same name as the attribute... q.addAttribute(“age”); q.addAttribute(“maritalStatus”); collection “age”  25 “maritalStatus”  “S” “age”  36 “maritalStatus”  “W” “age”  15 “maritalStatus”  “M” ReportQueryResult

26. Report Query Results • In general, you can specify any unique key for a report item: // Here the key has the same name as the attribute... q.addAttribute(“NR-Age”, reportBldr.get(“age”)); q.addAttribute(“NR-MS”,reportBldr.get(“maritalStatus”)); Define attribute to the report by using report query’s expression builder collection “NR-Age”  25 “NR-MS”  “S” “NR-Age”  36 “NR-MS”  “W” “NR-Age”  15 “NR-MS”  “M” ReportQueryResult

27. Processing the Results • Example: Build separate collections for age and marital status. Vector report = (Vector)session.executeQuery(reportQuery); Vector ages = new Vector(); Vector ms = new Vector(); Iterator i = report.iterator(); while (i.hasNext()) { ReportQueryResult result = (ReportQueryResult)i.next(); ages.add(result.get(“NR-Age”); ms.add(result.get(“NR-MS”); }

28. Reporting on Relationships • You can report on attributes of related objects. • Example: “For people who do not live in the USA, collect all the cities that they are from” • Assume that “city” is an attribute of the Address class. • You can report on 1:M relationships by using anyOf(). ExpressionBuilder reportBldr = new ExpressionBuilder(); ReportQuery q = new ReportQuery(Person.class, reportBldr); Expression exp = reportBldr.get(“address”).get(“country”). notEqual(“USA”); q.setSelectionCriteria(exp); q.addAttribute(“city Name”, reportBldr.get(“address”). get(“city”));

29. Database Reporting Functions • Supports database aggregate functions: • AVERAGE, COUNT, MAX, MIN, SUM • Example: “Return the average age of people who live in North Carolina” ExpressionBuilder reportBldr = new ExpressionBuilder(); ReportQuery q = new ReportQuery(Person.class, reportBldr); Expression exp = reportBldr.get(“address”).get(“state”). equal(“NC”); q.setSelectionCriteria(exp); q.addAverage(“avgAge”, reportBldr.get(“age”));

30. Reporting on Groups of Data • The database allows data to be grouped by using GROUP BY. • Reporting functions can be invoked on grouped data • Creates “subtotal” summaries • Example: ExpressionBuilder reportBldr = new ExpressionBuilder(); ReportQuery q = new ReportQuery(Person.class, reportBldr); Expression exp = reportBldr.get(“address”).get(“country”). equal(“USA”); q.setSelectionCriteria(exp); q.addAttribute(“state”, reportBldr.get(“address”). get(“state”)); q.addGrouping(reportBldr.get(“address”).get(“state”)); q.addAverage(“avgAge”, reportBldr.get(“age”));

31. In-Memory Querying • You can perform queries solely against contents in the identity map cache. • Without going to the database • Not all query expressions are supported • In-memory querying is set on a per query basis. ReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employee.class); query.checkCacheOnly(); // Returns only those employees in cache; do not go // to database Vector employees = (Vector) getSession().executeQuery(query);

32. In-Memory Querying Options • checkCacheByPrimaryKey() • Default: Return object in memory if the expression is based only on primary keys. • checkCacheThenDatabase() • Query the cache; if none found, then go to the database • checkCacheOnly() • Do not even think about going to the database. • conformResultsInUnitOfWork() • Applies only to queries that are executed via UnitOfWork • Queries will not return objects that are new, deleted, or changed (before a Unit of Work commits). • Performance loss, bad design

33. Subqueries • Defining a query within a query • Results of a subquery are used to determine the results of the outer query. • Examples: • “Get all people whose age is greater than the average age” • “Get all employees who are not working on a project” • “Get job applicants who live in a city with a sales office” • The outer query returns objects. • The subquery is a report query that generally returns information that is used in the outer query.

34. Simple Subquery Example • “Get all people whose age is greater than the average age” • Translation: Get all people where their age is greater than (find the average age for all people) • Subquery: • Outer query expression: ExpressionBuilder reportBldr = new ExpressionBuilder(); ReportQuery subQuery = new ReportQuery(Person.class, reportBldr); subQuery.addAverage(“age”); Expression exp = bldr.get(“age”).greaterThan(subQuery);

35. Complex Subquery Example • “Get all employees who are not working on a project” • Translation: Get all employees where there does not exist (any projects that has that employee as staff member) ExpressionBuilder outerBldr = new ExpressionBuilder(); ExpressionBuilder reportBldr = new ExpressionBuilder(); ReportQuery subQuery = new ReportQuery(Project.class, reportBldr); Expression exp = reportBldr.anyOf(“staff”). equal(outerBldr); Expression exp2 = outerBldr.notExists(subQuery); ReadAllQuery q = new ReadAllQuery(Employee.class, exp);

36. TopLink Support for Query Expressions • Expressions are used to “select” the results of a query based on a condition: • TopLink expressions • EJB Query Language (EJB QL) • SQL ExpressionBuilder bldr = new ExpressionBuilder(); Expression exp = bldr.get(“lastName”).equal(“Smith”); query.setSelectionCriteria(exp); query.setEJBQLString( “select Object(Employee) from Employee e where ” + “e.lastName = ‘Smith’”); query.setSQLString( “SELECT * FROM EMPLOYEE e WHERE e.L_NAME = ‘Smith’”);

37. Building Query Objects • Using Session API for querying is very limited: • You cannot use available query options. • Another approach: • Build your own query object. • This allows query options to be used. Query type:Choose the appropriate subclass of DatabaseQuery ReadAllQuery q = new ReadAllQuery(); q.setReferenceClass(Employee.class) q.setSelectionCriteria(anExpression); Reference class:Class being queried Selection criteria:Can be defined by using TopLink Expression Framework, EJB QL, or SQL

38. Query Object Example Building and executing a query: // This type of query returns a Vector of objects ReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employee.class); Expression exp = getTopLinkExpression(); // Set query expression or query logic query.setSelectionCriteria(exp); // Customize query by adding ordering to results query.addAscendingOrdering(“lastName”); // Execute query Vector employees = (Vector)getSession().executeQuery(query);

39. TopLink Expression Framework • Defines the query logic from an “object model” perspective: • Defined in terms of objects, and their attributes and relationships • Not in terms of tables and fields • Isolates database implementation from the application • Applications do not require any custom SQL to be defined: • Supports basic SQL functionality as well as complex functionality: • - Functions, outer join support • - Nested subselects • Allows querying across object relationships via simple navigation of object attributes

40. TopLink Expressions Example • Find all customers who ordered a “Widget.” • Syntax for Java developers, a simple object attribute traversal: custExpression.anyOf(“orders”).anyOf(“orderItems”).get(“product”).get("productName").equal(“Widget”) • Equivalent SQL: SELECT t3.ID, t3.FIRST_NAME, t3.LAST_NAME, t3.STREET, t3.CITY, t3.STATE, t3.ZIP FROM CUSTOMER t3, ORDER t2, ORDER_ITEMS t1, PRODUCT t0 WHERE ((t0.PRODUCT_NAME = 'Widget') AND ((t2.CUST_ID = t3.ID) AND ((t1.ORDER_ID = t2.ID) AND (t0.ID = t1.PRODUCT_ID)))) Customer Order Product OrderItem 1 orderDate cost orderItems productName cost weight quantity discount product firstName lastName orders 1 1 * 1 *

41. TopLink Expression Framework Classes • You can construct expressions by using two classes: • oracle.toplink.expressions.ExpressionBuilder • “Factory” for creating new expressions • oracle.toplink.expressions.Expression • “Building blocks” for defining a query’s selection criteria … Expression Expression ExpressionBuilder Expression … Expression Expression

42. QUERY ExpressionBuilder • An expression “factory” creates expressions and begins the building of a selection criterion. ExpressionBuilder bldr = new ExpressionBuilder(); Expression exp1 = bldr.get(“city”).equal(“Boston”); Object attribute name “operator” a value ExpressionBuilder get(“city”) Expression equal(“Boston”) Expression setSelectionCriteria(exp)

43. Expressions • Expressions have a root context that is defined by the reference class of the query. • You can define path expressions by using get() to obtain attributes relating to the root context object. ReadAllQuery q = new ReadAllQuery(); q.setReferenceClass(Employee.class); Expression exp = bldr.get(“lastName”).equal(“Smith”); Conditional expression operator “Smith”

44. TopLink Expression Examples • Employees whose last name starts with “S”: • Projects whose budget is greater than $100,000: • Addresses for these large cities: ExpressionBuilder bldr = new ExpressionBuilder(); Expression exp = bldr.get(“lastName”).like(“S%”); ExpressionBuilder bldr = new ExpressionBuilder(); Expression exp = bldr.get(“budget”).greaterThan(100000); Vector cities = getLargeCityNames(); ExpressionBuilder bldr = new ExpressionBuilder(); Expression exp = bldr.get(“city”).in(cities);

45. Expression Examples • Compound expressions: Employees whose last name starts with “S,” and whose marital status is “single.” ExpressionBuilder bldr = new ExpressionBuilder(); Expression exp1 = bldr.get(“lastName”).like(“S%”); Expression exp2 = bldr.get(“maritalStatus”).equal(‘S’); Expression finalExpr = exp1.and(exp2); Each branch of a compound expression must be created by using the same expression builder.

46. Complete Example Find all employees who earn more than $25,000. ReadAllQuery raq = new ReadAllQuery(); raq.setReferenceClass(Employee.class); ExpressionBuilder bldr = new ExpressionBuilder(); Expression exp = bldr.get(“salary”).greaterThan(25000); raq.setSelectionCriteria(exp); Vector results = null; results = (Vector)session.executeQuery(raq);