Importance of Query Language
Object Query Language
- Developer want the query results to be converted to entities so that the query results are used directly by application logic.
- Domain model are abstraction of the physical model (tables) in ORM, so Object Query Language like JPA QL are abstraction over the native SQL
JPA QL – Features
•Single and multiple value result types
•Aggregate functions, with sorting and
grouping clauses
•A more natural join syntax, including
support for both inner and outer joins
•Conditional expressions involving subqueries
•Update and delete queries for bulk data
changes
Result projection into non-persistent
classesJPA QL
•Supports two methods for expressing
queries
–Query
Language
–Criteria
API
And
also, we can use native SQL queriesJPA QL in its simplest form
•SQL
–Select EMP_ID, EMP_NAME from EMPLOYEE
•JPA
QL
–from Employee
–Select e from Employee e(uses an alias
mostly useful in joins)
Select
e.empName from
Employee e Querying Employee Objects
@PersistenceContext
EntityManager em;
List<Employees> findAllEmployees()
{
Query query = newEm.createQuery("from Employee");
return
query.getResultList();
}
Note:
employees is a collection of Employee Entity ObjectsExpression in JPA QL
•from
Employee emp
where emp.empAge
between 25 and 30
•from
Employee emp
where emp.empAge
> 26
•from
Employee emp
where emp.deptName in (‘BIDW', ‘JAVA')
•from
Employee e where e.deptName like ‘J%‘
•from
Employee e where e.deptName not like ‘J%‘
•from
Employee e where ( e.empAge / 20) > 0
•from
Employee e
where
(e.empName
like 'K%' and e.empId = 109 ) or
e.empAge
in (25, 30 )Ordering Query Results
•from
User u order by u.username
•from
User u order by u.username desc
Distinct
Rows
•select distinct item.description from Item itemParameter Binding (Prepared Statements)
•Building
queries using String concatenation is buggy and difficult to maintain.
–"from
Item i where i.description like
'" + search + "'";
•A
cleaner approach to building queries is to use parameter binding.
•JPA
Supports 2 types
–Named
Parameter Binding – Preferred Approach
"from Item item where item.description like
:search";
Query q = em.createQuery(queryString)
.setParameter("search",
searchString);
–Positional
Parameter Binding – Not Recommended
"from Item item where item.description like
?1 and item.date >
?2";
Query q = em.createQuery(queryString)
.setParameter(1, searchString)
.setParameter(2, minDate, TemporalType.DATE);Pagination using JPA QL
•What
is Pagination?
•Approaches
–Fetch complete data from the table and
store in session and subsequently retrieve from session. (No query change)
–Fetch data as required from the database.
(Query needs to contain the info like number of records to fetch and from which
record number for each page).
•JPA
QL support
Query query =
newEm.createQuery("from
Employee");
query.setFirstResult(1);
query.setMaxResults(5);
List employees =
query.getResultList();Named Queries
•JPA
QL queries embedded within Java Code makes the code unreadable and reduces
maintainability
•Externalizing
queries as metadata using annotations helps resolve the above issues.
•Two
Steps
–Externalize
the query in your Entity class using @NamedQuery and give it a name
@NamedQueries({
@NamedQuery(
name
= "findEmployees",
query
= "from Employee")})
–Refer
to the query by its name from the Java code.
Query query =
em.createNamedQuery("
findEmployees")
Note:
Query names are global and hence must be unique.Aggregate Functions
•count(),
min(), max(), sum() and avg()
select
count(i) from Item I
•Using
group by
select
u.lastname,
count(u) from User u
group
by u.lastname
When aggregate functions are used the query.getResultList() method returns a list of Object arrays
with the values in order as specified in the select clauseCriteria API
•Constructing queries using programming
API.
•Simple Example
–SELECT e FROM Employee e WHERE e.name =
'John Smith'
–Equivalent query constructed using the
Criteria API
CriteriaBuilder cb = em.getCriteriaBuilder();CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
c.select(emp) .where(cb.equal(emp.get("name"), "John Smith"));
No comments:
Post a Comment