Abstract schema - entities, state, relationships
Abstract schema type - type to which the persistent property in the abstract schema
BNF - language syntax
Navigation - traversal of relationships
Path expression - navigates to entity state or relationship field
State field - persistent field of entity
Relationship field - type is abstract schema type of related entity
Dynamic query: EntityManager.createQuery()
Static Query:
@javax.persistence.NamedQuery( name="xxx", query="SELECT c FROM Customer c...")
EntityManager.createNamedQuery("xxx")
Named parameters:
".... name LIKE :custName"
Query.setParameter("custName",someName)
Positional Parameters (not recommended)
".... LIKE ?1"
Query.setParameter(1, something)
Select statements
SELECT: types of the objects or values
DISTINCT
AVG COUNT MAX MIN SUM
entity: Player p
fields selection: c.name, c.country.name - returns a list of Object[]
constructor: SELECT NEW full.qualified.ClassName(c.field1, c.field2)
allow return list of instances instead of Object[]
FROM:
one or more id variables, which is
abstract schema name (FROM Player p)
element of a collection relationship
many player <-> many team
in Player.java - @ManyToMany(mappedBy="players") public Collection<Team> getTeams() {}
query: From Player p, IN(p.teams) t
element of a single-valued relationship
one team one league
query: From Team t JOIN t.league l
member of a collection that is the multiple side of a one-to-many relationship
WHERE: condition
cannot navigate beyond relationship fields that are collections (illegal p.teams.city for teams is collection)
t.league = :league (give league an object of League type parameter)
t.league.sport = :sport (because no collection is involved, navigation is possible)
LIKE: _ (single char), % (any)
IS NULL
IS EMPTY - for collection, zero member
BETWEEN :lower AND :higher
IN: o.country IN ('UK','US')
MEMBER OF: where :lineItem MEMBER OF o.lineItems
sub queries: where (SELECT COUNT(o) FROM c.orders o) > 10
EXISTS (NOT EXISTS): where EXISTS (SELECT....)
ALL / ANY: where emp.salary > ALL(SELECT m.salary....)
functions:
CONCAT
LENGTH(string)
LOCATE(string, string [, start])
SUBSTRING(string, start, length)
TRIM([[LEADING|TRAILING|BOTH] char) FROM] (string)
LOWER(string) UPPER(string)
ABS MOD SQRT
SIZE(Collection)
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
CASE xxx WHEN literal THEN value WHEN...THEN...ELSE value END
GROUP BY:
HAVING: used by GROUP BY to further restrict
ORDER BY
Update statements (bulk operation)
UPDATE (example: Player p)
SET (example: p.status = 'xxx')
WHERE
Delete statements (bulk operation)
DELETE
FROM Player p
WHERE
Literals
'string'
number: 65, -233
Boolean: true / false / TRUE / FALSE
Enum: full qualified class name (com.xyz.EmployeeStatus.FULL_TIME)