Java Persistence: Queries and EJB-QL

1
Java PersistenceQueries and EJB-QL
2
Goals

• Be able to execute queries against the database
  based on a Java O/R mapping.

3
Objectives

• Query types
• Query results
• Query parameters
• EJB-QL
• Bulk Operators
• Native Queries
• Named Queries

4
Class Model/DB Schema used for Examples
5
Data Used in Examples

• CLERK_ID FIRSTNAME HIREDATE LASTNAME TERMDATE
• -------- --------- ---------- -------- --------
• (A) Manny 1970-02-01 Pep (null)
• (B) Moe 1970-02-01 Pep (null)
• (C) Jack 1973-02-01 Pep (null)
• SALE_ID AMOUNT BUYERID DATE SALE_STORE
• ------- ------ ------- ---------- ----------
• (D) 100 (F) 1998-04-10 (I)
• (E) 150 (G) 1999-06-11 (I)
• SALE_ID CLERK_ID
• ------- --------
• (D) (A)
• (E) (A)
• (E) (B)
• CUSTOMER_ID FIRSTNAME LASTNAME
• ----------- --------- --------
• (F) cat inhat

(PKs) were auto generated and changed during
each test
6
EntityManager Query Methods

• public interface EntityManager
• Query createQuery(String ejbqlString)
• create a Query using the supplied EJB-QL String
• Query createNamedQuery(String name)
• create a Query using a named pre-configured query
• Query createNativeQuery(String sqlString)
• create a Query using the supplied native SQL
  String
• Query createNativeQuery(String sqlString, Class
  resultClass)
• create a Query that returns results within an
  instantiated class
• Query createNativeQuery(String sqlString, String
  resultSetMapping)
• create a Query with detailed control of how
  results are mapped

7
Query Interface

• public interface javax.persistence.Query
• List getResultList()
• execute a query and return results in a list
• Object getSingleResult()
• execute a query and return a single result
• int executeUpdate()
• execute an update and return number of rows
  affected
• Query setMaxResults(int)
• Query setFirstResult(int)
• Query setHint(java.lang.String, java.lang.Object)
• Query setParameter(java.lang.String,
  java.lang.Object)
• Query setParameter(java.lang.String,
  java.util.Date, javax.persistence.TemporalType)
• Query setParameter(java.lang.String,
  java.util.Calendar, javax.persistence.TemporalType
  )
• provide capability to pass parameters by name
• Query setParameter(int, java.lang.Object)
• Query setParameter(int, java.util.Date,
  javax.persistence.TemporalType)
• Query setParameter(int, java.util.Calendar,
  javax.persistence.TemporalType)

8
Query.getSingleResult()

• Looks for a single, unique object based on the
  query
• query em.createQuery(
• "select s from Store s where s.name'Big
  A1''s'")
• store (Store)query.getSingleResult()
• throws NoResultException if nothing found
• try
• query em.createQuery(
• "select s from Store s where s.name'A1
  Sales'")
• query.getSingleResult()
• catch (NoResultException ex) ...
• throws NonUniqueResultException if too many found
• try
• query em.createQuery(
• "select c from Clerk c where
  lastName'Pep'")
• query.getSingleResult()

9
Query.getResultList()

• Returns a List of objects based on the query
• Query query em.createQuery(
• "select c from Clerk c where
  lastName'Pep'")
• ListltClerkgt clerks query.getResultList()
• assertTrue("unexpected number of clerks"
  clerks.size(),
• clerks.size() gt 1)
• for(Clerk c clerks)
• log.info("found clerk" c)
• -found clerkid4, firstNameManny,
  lastNamePep, hireDateSun Feb 01 000034 EST
  1970, termDatenull, sales(2)3, 4,
• -found clerkid5, firstNameMoe, lastNamePep,
  hireDateSun Feb 01 000034 EST 1970,
  termDatenull, sales(1)4,
• -found clerkid6, firstNameJack, lastNamePep,
  hireDateThu Feb 01 000034 EST 1973,
  termDatenull, sales(0)

10
Query Parameters

• named parameters
• Query query em.createQuery(
• "select c from Customer c "
• "where c.firstNamefirstName and
  c.lastNamelastName")
• query.setParameter("firstName", "cat")
• query.setParameter("lastName", "inhat")
• Customer customer (Customer)query.getSingleResul
  t()
• assertNotNull(customer)
• log.info("found customer for param names"
  customer)
• -found customer for param namesid7,
  firstNamecat, lastNameinhat

11
Query Parameters (cont.)

• positional parameters
• query em.createQuery(
• "select c from Customer c "
• "where c.firstName?1 and c.lastName like
  ?2")
• query.setParameter(1, "thing")
• query.setParameter(2, "")
• ListltCustomergt customers query.getResultList()
• assertTrue("unexpected number of customers"
  customers.size(),
• customers.size() 2)
• for(Customer c customers)
• log.info("found customer for param ordinals"
  c)
• -found customer for param positionid8,
  firstNamething, lastNameone
• -found customer for param positionid9,
  firstNamething, lastNametwo

12
Date Parameters

• Date parameters have TemporalType modifiers
• Calendar hireDate Calendar.getInstance()
• hireDate.set(Calendar.YEAR, 1972)
• Query query em.createQuery(
• "select c from Clerk c "
• "where c.hireDate gt date")
• query.setParameter("date", hireDate,
  TemporalType.DATE)
• Clerk clerk (Clerk)query.getSingleResult()
• log.info("found clerk by date(" hireDate ")"
  clerk)
• -found clerk by date(Mon Oct 09 001650 EDT
  1972)id12, firstNameJack, lastNamePep,
  hireDateThu Feb 01 000050 EST 1973,
  termDatenull, sales(0)

13
Paging Results

• Control index and amount of results returned
• Query query em.createQuery(
• "select s from Sale s")
• for(int i0 ilt2 i)
• ListltSalegt sales query.setMaxResults(10)
• .setFirstResult(i)
• .getResultList()
• assertTrue("unexpected sale count"
  sales.size(),
• sales.size() gt 1)
• for(Sale s sales)
• log.info("found sale in page(" i ")"
  s)
• em.clear() //keep from caching all pages
• -found sale in page(0)id9, dateFri Apr 10
  000036 EDT 1998, amount100, buyer13,
  clerks(1)13,
• -found sale in page(0)id10, dateFri Jun 11
  000036 EDT 1999, amount150, buyer14,
  clerks(2)13, 14,
• -found sale in page(1)id10, dateFri Jun 11
  000036 EDT 1999, amount150, buyer14,
  clerks(2)13, 14,

first iteration i0
second iteration i1
14
Query Modifiers

• Hints
• permit specification of vendor hints to improve
  behavior
• Query query ...
• query.setHint(vendor.property.x, value)
• FlushMode
• modify default flush mode
• default is to flush before a query
• Query query ...
• query.setFlushMode(FlushModeType.COMMIT)

15
EJB-QL

• SQL-like
• Query based on abstract schema represented by
  class metadata
• entity name
• _at_Entity(name...)
• properties
• get/set...
• relationships
• _at_...To...

16
Simple Queries

• select object(c) from Customer as c
• from
• determines scope of select
• as c
• assigns the entity type to a logical name
• as is optional and can be dropped
• identifier
• can be any length
• is not case sensitive
• must be careful not to overload abstract schema
  name
• wrong Customer as customer
• cannot be any EJB-QL reserved word (e.g., JOIN,
  NEW)
• object() operator
• optional and can be dropped
• left-over from EJB2.1
• no where clause
• requests all objects for scope

17
Selecting Entity Properties

• Use abstract schema to select properties
• Query query em.createQuery(
• "select c.firstName, c.hireDate from
  Clerk c")
• List results query.getResultList()
• assertTrue("no results", results.size() gt 0)
• for(Iterator itrresults.iterator()
  itr.hasNext() )
• Object result (Object)itr.next()
• assertNotNull("no result array", result)
• assertEquals("unexpected result length"
  result.length,
• 2, result.length)
• String firstName (String) result0
• Date hireDate (Date) result1
• log.info("firstName" firstName
• " hireDate" hireDate)
• -firstNameManny hireDate1970-02-01
• -firstNameMoe hireDate1970-02-01
• -firstNameJack hireDate1973-02-01

18
Selecting Entity Relationships

• Use abstract schema to select relationships
• Query query em.createQuery(
• "select s.id, s.store.name from Sale s")
• List results query.getResultList()
• assertTrue("no results", results.size() gt 0)
• for(Iterator itrresults.iterator()
  itr.hasNext() )
• Object result (Object)itr.next()
• assertNotNull("no result array", result)
• assertEquals("unexpected result length"
  result.length,
• 2, result.length)
• Long id (Long) result0
• String name (String) result1
• log.info("sale.id" id ",
  sale.store.name" name)
• -sale.id15, sale.store.nameBig Al's
• -sale.id16, sale.store.nameBig Al's

19
Constructor Expressions

• Useful in creating Data Transfer Objects DTOs
• Query query em.createQuery(
• "select new ejava.examples.orm.ejbql.Receipt
  ("
• "s.id,s.buyerId,s.date, s.amount) "
• "from Sale s")
• List results query.getResultList()
• assertTrue("no results", results.size() gt 0)
• for(Iterator itrresults.iterator()
  itr.hasNext() )
• Receipt receipt (Receipt)itr.next()
• assertNotNull("no receipt", receipt)
• log.info("receipt" receipt)
• -receiptsale25, customer17, date1998-04-10,
  amount100.0
• -receiptsale26, customer18, date1999-06-11,
  amount150.0

20
Constructor Expressions Example DTO

• public class Receipt implements Serializable
• private long saleId
• private long customerId
• private Date date
• private double amount
• public Receipt(
• long saleId, long customerId, Date date,
  BigDecimal amount)
• this(customerId, saleId, date,
  amount.doubleValue())
• public Receipt(long saleId, long customerId,
  Date date, double amount)
• this.customerId customerId
• ...
• public String toString()
• StringBuilder text new StringBuilder()
• text.append("sale" saleId)
• text.append(", customer" customerId)
• text.append(", date" date)
• text.append(", amount" amount)

21
IN Operator

• Used to navigate to or through collections
• Illegal
• select s.sale from Store s
• Legal with use if IN Operator
• select sale from Store s, IN(s.sales) sale
• select sale.date from Store s, IN(s.sales) sale
• -found resultid19, dateFri Apr 10 000045 EDT
  1998, amount100, buyer28, clerks(1)28,
• -found resultid20, dateFri Jun 11 000045 EDT
  1999, amount150, buyer29, clerks(2)28, 29,
• -found result1998-04-10
• -found result1999-06-11

22
INNER JOIN

• Alternate form for IN operator
• Retrieves only objects where there is a match
• select sale from Store s INNER JOIN s.sales sale
• select sale.date from Store s INNER JOIN s.sales
  sale
• -found resultid21, dateFri Apr 10 000015
  EDT 1998, amount100, buyer31, clerks(1)31,
• -found resultid22, dateFri Jun 11 000015
  EDT 1999, amount150, buyer32, clerks(2)31,
  32,
• -found result1998-04-10
• -found result1999-06-11

23
LEFT (OUTER) JOIN

• Retrieves matching and non-matching
• select c.id, c.firstName, sale.amount
• from Clerk c
• LEFT JOIN c.sales sale
• -clerk.id34, clerk.firstNameManny, amount100
• -clerk.id34, clerk.firstNameManny, amount150
• -clerk.id35, clerk.firstNameMoe, amount150
• -clerk.id36, clerk.firstNameJack, amountnull

Jack is picked up even though he does not have
any sales
24
Fetch JOINS

• Preloads LAZY relationships
• select s from Store s LEFT JOIN FETCH s.sales

25
WHERE Clause

• WHERE Clause used to limit scope of objects
  selected
• select ...
• where ...
• Literals
• LIKE
• Arithmetic Operators
• Comparisons
• Logical Operators
• Equality
• BETWEEN
• IN
• IS NULL
• IS EMPTY
• MEMBER OF

26
WHERE Clause and Literals

• Used to test for specific values
• select c from Customer c
• where c.firstName'cat'
• -executing queryselect c from Customer c where
  c.firstName'cat'
• -found resultid43, firstNamecat,
  lastNameinhat

27
WHERE Clause and Special Characters

• Escape single quote (') with a single quote(')
• i.e. two single quotes ('')
• select s from Store s
• where s.name'Big Al''s'
• -executing queryselect s from Store s where
  s.name'Big Al''s'
• -found resultid16, nameBig Al's,
  sales(2)31, 32,

28
WHERE Clause and LIKE

• Use percent () character to locate any string
• select c from Clerk c where c.firstName like 'M'
• -found resultid49, firstNameManny,
  lastNamePep, hireDateSun Feb 01 000049 EST
  1970, termDatenull, sales(2)33, 34,
• -found resultid50, firstNameMoe,
  lastNamePep, hireDateSun Feb 01 000049 EST
  1970, termDatenull, sales(1)34,
• Can pass expressions in parameters
• em.createQuery(select c from Clerk c
• where c.firstName like
  firstName)
• .setParameter(firstName,M) ...
• Can build expression through concat()
• em.createQuery(select c from Clerk c
• where c.firstName like
  concat(firstName,'')
• .setParamter(firstName, M) ...

29
WHERE Clause and LIKE

• Use underscore (_) character to locate any
  character
• select c from Clerk c where c.firstName like
  '_anny'
• -found resultid49, firstNameManny,
  lastNamePep, hireDateSun Feb 01 000040 EST
  1970, termDatenull, sales(2)33, 34,
• Use back slash (\) character to escape and _
  characters appearing within expression

30
WHERE Arithmetic Operators and Comparisons

• Used to perform calculations during evaluation
• select s from Sale s
• where (s.amount tax) gt amount
• em.createQuery(ejbqlQueryString)
• .setParameter(tax, new BigDecimal(0.07))
• .setParameter(amount, new
  BigDecimal(10.00))
• .getResultList()
• -executing queryselect s from Sale s where
  (s.amount tax) gt amount
• - with paramsamount10,tax0.0700000000000000
  06661338147750939242541790008544921875,
• -found resultid32, dateFri Jun 11 000030
  EDT 1999, amount150, buyer47, clerks(2)46,
  47,

31
WHERE Logical Operators

• AND, OR
• select c from Customer c
• where (c.firstName'cat' AND c.lastName'inhat')
  OR
• c.firstName'thing'
• -found resultid46, firstNamecat,
  lastNameinhat
• -found resultid47, firstNamething,
  lastNameone
• -found resultid48, firstNamething,
  lastNametwo
• NOT
• select c from Customer c
• where NOT (c.firstName'cat' AND
  c.lastName'inhat')
• OR c.firstName'thing'
• -found resultid47, firstNamething,
  lastNameone
• -found resultid48, firstNamething,
  lastNametwo

32
WHERE Clause Equality

• Must compare values
• of same type
• of legal promotion type
• can compare int123 to long123
• cannot compare int123 to string123
• May compare entities
• //find a clerk by their name
• Clerk clerk (Clerk)em.createQuery(
• "select c from Clerk c where c.firstName
  'Manny'")
• .getSingleResult()
• //find all sales that involve this clerk using
  the entity
• em.createQuery("select s "
• "from Sale s, IN (s.clerks) c "
• "where c clerk")
• .setParameter(clerk, clerk)
• .getResultList()
• -executing queryselect s from Sale s, IN
  (s.clerks) c where c clerk

33
WHERE Clause BETWEEN

• Tests for a range of values
• em.createQuery("select s from Sale s "
• "where s.amount BETWEEN low AND
  high")
• .setParameter("low", new BigDecimal(90.00))
• .setParameter("high", new BigDecimal(110.00))
  
• .getResultList()
• -executing queryselect s from Sale s where
  s.amount BETWEEN low AND high
• - with paramshigh110,low90,
• -found resultid37, dateFri Apr 10 000019
  EDT 1998, amount100, buyer55, clerks(1)55,
• Using NOT Operator
• NOT BETWEEN low AND high
• same as
• s.amount lt low OR s.amount gt high

34
WHERE Clause Testing IS NULL

• Used to determine unassigned or assigned One
  relationship
• select s from Sale s where s.store IS NULL
• -executing queryselect s from Sale s where
  s.store IS NULL
• select s from Sale s where s.store IS NOT NULL
• -executing queryselect s from Sale s where
  s.store IS NOT NULL
• -found resultid39, dateFri Apr 10 000029
  EDT 1998, amount100, buyer58, clerks(1)58,
• -found resultid40, dateFri Jun 11 000029
  EDT 1999, amount150, buyer59, clerks(2)58,
  59,

35
WHERE Clause Testing IS EMPTY

• Used to determine empty or populated Many
  relationship
• select c from Clerk c where c.sales IS EMPTY
• -found resultid63, firstNameJack,
  lastNamePep, hireDateThu Feb 01 000005 EST
  1973, termDatenull, sales(0)
• select c from Clerk c where c.sales IS NOT EMPTY
• -found resultid61, firstNameManny,
  lastNamePep, hireDateSun Feb 01 000005 EST
  1970, termDatenull, sales(2)41, 42,
• -found resultid62, firstNameMoe,
  lastNamePep, hireDateSun Feb 01 000005 EST
  1970, termDatenull, sales(1)42,

36
WHERE Clause MEMBER OF

• Checks if entity is member of the Many side of a
  relationship
• can be a tool to traverse from ManyToOne from a
  uni-directional OneToMany relationship
• //find a clerk by their name
• Clerk clerk (Clerk)em.createQuery(
• "select c from Clerk c where c.firstName
  'Manny'")
• .getSingleResult()
• //find all sales that involve this clerk using
  the entity
• em.createQuery("select s "
• "from Sale s "
• "where clerk MEMBER OF s.clerks")
• .setParameter(clerk, clerk)
• .getResultList()
• -executing queryselect s from Sale s where
  clerk MEMBER OF s.clerks
• - with paramsclerkid64, firstNameManny,
  lastNamePep, hireDateSun Feb 01 000000 EST
  1970, termDatenull, sales(2)43, 44, ,
• -found resultid43, dateFri Apr 10 000000
  EDT 1998, amount100, buyer64, clerks(1)64,
• -found resultid44, dateFri Jun 11 000000
  EDT 1999, amount150, buyer65, clerks(2)64,
  65,

37
Functional Expressions

• String Functions
• Math Functions
• Date Functions
• Aggregate Functions

38
String Functions

• UPPER, LOWER, and TRIM
• select c from Customer c where c.firstName'CAT'
• (no rows)
• select c from Customer c where
  c.firstNameLOWER('CAT')
• found resultid73, firstNamecat, lastNameinhat
• select UPPER(c.firstName) from Customer c where
  c.firstNameLOWER('CAT')
• found resultCAT
• select TRIM(LEADING 'c' FROM c.firstName) from
  Customer c where c.firstName'cat')
• found resultat

39
String Functions (cont)

• CONCAT, LENGTH, LOCATE
• select c from Customer c where
  CONCAT(CONCAT(c.firstName,' '),c.lastName) 'cat
  inhat')
• found resultid73, firstNamecat, lastNameinhat
• select c from Customer c where
  LENGTH(c.firstName) 3
• found resultid73, firstNamecat, lastNameinhat
• select c from Customer c where
  LOCATE('cat',c.firstName,2) gt 1
• (no results)
• select c from Customer c where
  LOCATE('at',c.firstName,2) gt 1
• found resultid73, firstNamecat,
  lastNameinhat
• Note container support for start position
  within LOCATE is not mandatory

40
String Functions (cont)

• SUBSTRING
• select SUBSTRING(c.firstName,2,2) from Customer
  c where c.firstName 'cat'
• found resultat
• select c from Customer c where
  SUBSTRING(c.firstName,2,2) 'at'
• found resultid73, firstNamecat, lastNameinhat

41
Date Functions

• CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
• select s from Sale s where s.date lt CURRENT_DATE
• -found resultid51, dateFri Apr 10 000046
  EDT 1998, amount100, buyer76, clerks(1)76,
• -found resultid52, dateFri Jun 11 000046
  EDT 1999, amount150, buyer77, clerks(2)76,
  77,
• select s from Sale s where s.date CURRENT_DATE
• (no results)
• update Sale s set s.date CURRENT_TIMESTAMP
• 2 rows updated
• select s from Sale s where s.date CURRENT_DATE
• -found resultid51, date2008-10-04,
  amount100, buyer76, clerks(1)76,
• -found resultid52, date2008-10-04,
  amount150, buyer77, clerks(2)76, 77,

42
Numeric Select Functions

• Must operate on a path that ends with a numeric
  value
• COUNT, MAX, MIN, SUM, AVG
• select COUNT(s) from Sale s
• -found result2
• select MAX(s.amount) from Sale s
• -found result150.0
• select MIN(s.amount) from Sale s
• -found result100.0
• select SUM(s.amount) from Sale s
• -found result250.0
• select AVG(s.amount) from Sale s
• -found result125.0

43
DISTINCT Select Function

• Ensures that query does not return duplicates
• select DISTINCT c.lastName from Customer c
• select DISTINCT c.firstName from Customer c
• -executing queryselect DISTINCT c.lastName from
  Customer c
• -found resultinhat
• -found resultone
• -found resulttwo
• -executing queryselect DISTINCT c.firstName
  from Customer c
• -found resultcat
• -found resultthing

44
ORDER BY Clause

• must order by a field within what is selected
• default is always ASC
• select s from Sale s ORDER BY s.amount ASC
• -found resultid59, dateFri Apr 10 000029 EDT
  1998, amount100, buyer88, clerks(1)88,
• -found resultid60, dateFri Jun 11 000029 EDT
  1999, amount150, buyer89, clerks(2)88, 89,
• select s from Sale s ORDER BY s.amount DESC
• -found resultid60, dateFri Jun 11 000029 EDT
  1999, amount150, buyer89, clerks(2)88, 89,
• -found resultid59, dateFri Apr 10 000029 EDT
  1998, amount100, buyer88, clerks(1)88,
• can specificy multiple ORDER BYs
• ORDER BY c.firstName ASC, c.lastName DESC

45
Subqueries

• Useful when information not available in
  relationship model
• note example Customer and Sale have not
  directly defined relationship to one another
• select c from Customer c where c.id IN
  (select s.buyerId from Sale s where
  s.amount gt 100)
• -found resultid92, firstNamething,
  lastNameone

46
ALL and ANY Subqueries

• ALL
• all values meet a criteria
• no values must fail criteria
• select c from Clerk c where 125 lt ALL (select
  s.amount from c.sales s)
• Not all Manny's sales (2) are above 125 -
  excluded
• All Moe's sales (1) are gt 125 - included
• Jack has no sales (0) to evaluate - included
• -found resultid95, firstNameMoe,
  lastNamePep, hireDateSun Feb 01 000047 EST
  1970, termDatenull, sales(1)64,
• -found resultid96, firstNameJack,
  lastNamePep, hireDateThu Feb 01 000047 EST
  1973, termDatenull, sales(0)
• select c from Clerk c where 125 gt ALL (select
  s.amount from c.sales s)
• Not all Manny's sales (2) are below 125
  excluded
• All Moe's sales (1) are gt 125 excluded
• Jack has no sales (0) to evaluate - included
• -found resultid96, firstNameJack,
  lastNamePep, hireDateThu Feb 01 000047 EST
  1973, termDatenull, sales(0)

47
ALL and ANY Subqueries (cont.)

• ANY
• any one value must meet criteria
• select c from Clerk c where 125 lt ANY (select
  s.amount from c.sales s)
• Manny has at least one sale gt 125 - included
• Moe has at least one sale are gt 125 - included
• Jack has no sales (0) - excluded
• -found resultid94, firstNameManny,
  lastNamePep, hireDateSun Feb 01 000047 EST
  1970, termDatenull, sales(2)63, 64,
• -found resultid95, firstNameMoe,
  lastNamePep, hireDateSun Feb 01 000047 EST
  1970, termDatenull, sales(1)64,
• select c from Clerk c where 125 gt ANY (select
  s.amount from c.sales s)
• Manny has at least one sale lt 125 included
• Moe has no sales lt 125 excluded
• Jack has no sales lt 125 - excluded
• -found resultid94, firstNameManny,
  lastNamePep, hireDateSun Feb 01 000047 EST
  1970, termDatenull, sales(2)63, 64,

48
Bulk Update Operator

• Provides the ability to make bulk updates
  directly to database
• Query update em.createQuery(
• "update Clerk c set c.lastNamenewlast
• where c.lastNamelast")
• update.setParameter("last", "Pep")
• update.setParameter("newlast", "Peppy")
• int rows update.executeUpdate()
• assertEquals("unexpected rows updated" rows,
  3, rows)
• Entity cache will be out of date with database
  after completion
• em.clear()

49
Bulk Delete Operator

• Provides the ability to bulk delete objects from
  database
• Query update em.createQuery(
• "delete from Customer c "
• "where c.firstName like first AND
• c.lastName like last")
• int rows update.setParameter("first", "thing")
• .setParameter("last", "")
• .executeUpdate()
• assertTrue("no rows updated", rows gt 0)
• Entity cache will be out of date with database
  after completion
• em.clear()

50
Scalar Native Query

• Executes a raw sql query and returns a scalar
  array of results
• Query query em.createNativeQuery(
• "select from ORMQL_CUSTOMER "
• "where ORMQL_CUSTOMER.firstName
  first")
• List results query.setParameter("first",
  "thing")
• .getResultList()
• assertTrue("no customers found", results.size() gt
  0)
• for(Object o results)
• StringBuilder text new StringBuilder()
• Object objects (Object)o
• for(Object obj objects)
• text.append(obj.toString() ",")
• log.info("results" text)
• -results56,thing,one,
• -results57,thing,two,

51
Scalar Entity Query

• Executes a raw sql query and returns instantiated
  entities
• must query for all parameters of entity
• Query query em.createNativeQuery(
• "select from ORMQL_CUSTOMER "
• "where ORMQL_CUSTOMER.firstName
  first",
• Customer.class)
• ListltCustomergt results query.setParameter("first
  ", "thing")
• .getResultList()
• assertTrue("no customers found", results.size() gt
  0)
• for(Customer c results)
• log.info("customer found" c)
• -customer foundid59, firstNamething,
  lastNameone
• -customer foundid60, firstNamething,
  lastNametwo

52
Names Query Example Java Mapping

• Permits a query to be separately defined and
  referenced
• _at_Entity _at_Table(name"ORMQL_CUSTOMER")
• _at_NamedQueries(
• _at_NamedQuery(name"getCustomersByName",
• query"select c from Customer c "
• "where c.firstName like first
  AND
• c.lastName like last"),
• _at_NamedQuery(name"getCustomerPurchases",
• query"select s from Sale s "
• "where s.buyerIdcustId")
• )
• public class Customer

53
Names Query Example Usage

• Customer customer (Customer)
• em.createNamedQuery("getCustomersByName")
• .setParameter("first", "cat")
• .setParameter("last", "inhat")
• .getResultList()
• .get(0)
• assertNotNull("no customer found", customer)
• log.info("found customer" customer)
• Query query em.createNamedQuery("getCustomerPurc
  hases")
• ListltSalegt sales
• query.setParameter("custId",
  customer.getId())
• .getResultList()
• assertTrue("no sales found", sales.size() gt 0)
• for (Sale s sales)
• log.info("found sale" s)
• -found customerid61, firstNamecat,
  lastNameinhat

54
Summary

• Query types
• Query results
• Query parameters
• EJB-QL
• Bulk Operators
• Native Queries
• Named Queries

55
References

• Enterprise JavaBeans 3.0, 5th Edition Burke
  Monsen-Haefel ISBN 0-596-00978-X O'Reilly