“JPQL is not SQL” was one of the first things I read about JPQL – it was strange because they look very similar, and serve a similar purpose. But basically JPQL is to objects, what SQL is to tables
The following examples will work off this object model, note there is a 1:1 mapping between these Objects and the underlying tables (Diagram TBD) –
Agreement -< Claims -< Claim_Lines — Instalments
[sourcecode lang=”java”]
class Agreement {
private String agreementName;
private Integer agreementYear;
private List claims;
private User createdBy;
}
class Claim{
private double amount;
private List claimLines;
}
class ClaimLine {
private Instalment instalment;
}
class Instalment {
private Integer instalmentNo;
}
class User {
}
[/sourcecode]
Simple Queries
SELECT ag FROM Agreement ag – This will return all the data underlying he Agreement object
SELECT DISTINCT ag.agreementYear FROM Agreement ag – Return Distinct Agreement Years
SELECT ag FROM Agreement ag WHERE ag.agreementYear = ‘2014’ – All agreements from this year (2014)
SELECT cl1 FROM claim cl1, claim cl2 WHERE cl1.amount > cl2.amount – All claims where claim amount 1 > claim amount 2
SELECT ag FROM Agreement ag WHERE ag.agreementName LIKE ‘%test%’
SELECT ag FROM Agreement ag WHERE ag.agreementName IS NULL
SELECT ag FROM Agreement ag WHERE ag.claims IS EMPTY
Joins
The above queries are nice – but the real power of a query language is JOIN’s
JOIN == INNER JOIN
SELECT ag FROM Agreement ag JOIN ag.claims cl WHERE cl.amount > 1000 – Return all agreements where claim amount > 1000
Equals –
SELECT ag FROM Agreement ag INNER JOIN ag.claims cl WHERE cl.amount > 1000 – Return all agreements where claim amount > 1000
LEFT JOIN == LEFT OUTER JOIN
SELECT ag FROM Agreement ag LEFT JOIN ag.claims cl WHERE cl.amount > 1000 – Return all agreements, and claim amount > 1000
Equals –
SELECT ag FROM Agreement ag LEFT OUTER JOIN ag.claims cl WHERE cl.amount > 1000 – Return all agreements, and claim amount > 1000
FETCH JOIN
A FETCH JOIN enables the fetching of an association as a side effect of the execution of a query
The effect of the FETCH is to not return the associated claims
SELECT ag FROM agreement ag LEFT JOIN FETCH ag.claims cl WHERE cl.amount = 1000
IN
You can use the IN object instead of a JOIN –
SELECT DISTINCT cl FROM claim cl,
IN(cl.claimLines) cll
WHERE cll.claimLineNo = 1
EXISTS
SELECT DISTINCT usr FROM User usr WHERE EXISTS (SELECT ag FROM Agreement agr WHERE agr.createdBy = usr)
Bulk Update and Delete
DELETE FROM Agreement agr;
DELETE FROM Agreement agr WHERE agr.claims IS EMPTY;
UPDATE Agreement agr SET agr.agreementYear = ‘2014’ WHERE agr.agreementYear IS NULL;
References
http://docs.oracle.com/cd/E17904_01/apirefs.1111/e13946/ejb3_langref.html
Like this:
Like Loading...