Download
eclipselink jpa black belt course n.
Skip this Video
Loading SlideShow in 5 Seconds..
EclipseLink JPA Black Belt Course PowerPoint Presentation
Download Presentation
EclipseLink JPA Black Belt Course

EclipseLink JPA Black Belt Course

199 Vues Download Presentation
Télécharger la présentation

EclipseLink JPA Black Belt Course

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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);