Social Media

JPQL

“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

About the Author Martin Farrell

My name is Martin Farrell. I have almost 20 years Java experience. I specialize inthe Spring Framework and JEE. I’ve consulted to a range of businesses, and have provide Java and Spring mentoring and training. You can learn more at About or on my consultancy website Glendevon Software

follow me on:

Leave a Comment: