JPQL

JPQL エンティティとエンティティのコレクション

SQL 行と列のテーブル構造

SELECT b

FROM Book b

WHERE b.title = 'xxx' AND b.price = yyy

SELECT DISTINCT b

FROM Book b

WHERE b.title = ?1 AND b.price = ?2 位置指定パラメータ

WHERE b.title = :title AND b.price = :price 名前付き指定パラメータ

SELECT

CASE b.editor

WHEN "xxx"

THEN b.price * 0.5

ELSE b.price * 0.8

END

FROM Book b

SELECT c.address

FROM Customer c

ORDER BY c.age DESC, c.address.country ASC

SELECT NEW com.xxx.MyCustomerDTO(c.aaa, c.bbb)

FROM Customer c

SELECT c

FROM Customer c

WHERE c.age = (SELECT MIN(c2.age) FROM Customer c2)

SELECT c.address.country, COUNT(c)

FROM Customer c

GROUP BY c.address.country

HAVING c.address.country <> 'xxx'

DELETE FROM Customer c

WHERE c.age < 20

UPDATE Customer c

SET c.name = 'xxx'

WHERE c.age < 20

※AND, OR

※=, >, <, <=, >=, <>, (NOT) BETWEEN, (NOT) LIKE, (NOT) IN, IS (NOT) NULL, IS (NOT) EMPTY, NOT (MEMBER) OF

※集計：AVG, COUNT, MAX, MIN, SUMなど

※数値：ABS, SORT, MOD, SIZE, INDEX

※文字列：CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LENGTH, LOCATE

※日時：CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

副問い合わせ

SELECT emp

FROM Employee emp

WHERE emp.salary > [ALL, ANY, SOME] (

SELECT m.salary

FROM Manager m

WHERE m.department = emp.department

)

ALL すべてに合致

ANY,SOME 一つ以上に合致

SELECT DISTINCT emp

FROM Employee emp

WHERE [NOT] EXISTS (

...

)

SELECT c

FROM Customer c

WHERE

(SELECT AVG(o.price) FROM c.orders o) > 100

結合

SELECT o

FROM Order o

[[LEFT] OUTER, INNER] JOIN o.lineItems l

JOIN l.product p

Inner Join

Implicit

SELECT employee.id, phone.id

FROM Employee employee, employee.phones phone

Explicit

SELECT employee.id, phone.id

FROM Employee employee JOIN employee.phones phone

Left Outer Join

SELECT DISTINCT employee.id

FROM Employee employee LEFT JOIN employee.phones phone

※トランザクション境界外の問題を解決するため、LAZYフェッチ⇒EAGERフェッチ

※FETCHの目的： Avoid lazy fetching / force eager fetching

SELECT c FROM class c WHERE id = :id class.getStudents() Lazy

SELECT c FROM class c JOIN FETCH c.students class.getStudents() Eager

★Literals

SELECT e FROM Employee e WHERE e.name = 'Andy' String

SELECT e FROM Employee e WHERE e.id = 1234 Integer

SELECT e FROM Employee e WHERE e.id = 1234L Long

SELECT s FROM Stat s WHERE s.ratio > 3.14F Float

SELECT s FROM Stat s WHERE s.ratio > 3.14e32D Double

SELECT e FROM Employee e WHERE e.active = TRUE Boolean

UPDATE Employee e SET e.manager = NULL WHERE e.manager = :manager NULL

-- Date {d'yyyy-mm-dd'}

SELECT e FROM Employee e WHERE e.startDate = {d'2014-04-11'}

-- Time {t'hh:mm:ss'}

SELECT e FROM Employee e WHERE e.startTime = {t'09:00:00'}

-- Timestamp {ts'yyy-mm-dd hh:mm:ss.nnnnnnnnn'}

SELECT e FROM Employee e WHERE e.version = {ts'2014-04-11 09:00:00.000000001'}

-- Enum package.class.enum

SELECT e FROM Employee e WHERE e.gender = xxx.Gender.MALE

★Add/Substract a time period to a date

public List<Item> getRecentItems(int numberOfDays) {

// JDK approach

Calendar calendar = new GregorianCalendar();

calendar.setTimeZone(TimeZone.getTimeZone("UTC+9"));

calendar.setTime(new Date());

calendar.add(Calendar.DATE, -numberOfDays);

Date dateToLookBack = calendar.getTime();

// Joda-time approach

DateTime dateToLookBack = new DateTime(new Date());

dateToLookBack = dateToLookBack.minusDays(numberOfDays);

dateToLookBack.toDate();

String sqlString =

"SELECT i FROM Item i where i.insertionDate > :dateToLookBack";

TypedQuery<Item> query = entityManager.createQuery(sqlString, Item.class);

query.setParameter("dateToLookBack", dateToLookBack, TemporalType.DATE);

return query.getResultList();

}