Social Media

Category Archives for JEE

JPQL vs Criteria API

Ok this is ongoing and I’ll fill in the blanks as I get time

Simple Queries

SELECT ag FROM Agreement ag

[sourcecode lang=”java”] CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Agreement> cq = cb.createQuery(Agreement.class);
Root<Agreement> agreement = cq.from(Agreement.class);
cq.select(agreement);
TypedQuery<Agreement> q = em.createQuery(cq);
List<Agreement> allAgreements = q.getResultList();
[/sourcecode]

SELECT DISTINCT ag.agreementYear FROM Agreement ag

[sourcecode lang=”java”] CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Agreement> cq = cb.createQuery(Agreement.class);
Root<Agreement> agreement = cq.from(Agreement.class);
cq.select(agreement).distinct(true); // distinct == true
TypedQuery<Agreement> q = em.createQuery(cq);
List<Agreement> allAgreements = q.getResultList();
[/sourcecode]

SELECT ag FROM Agreement ag WHERE ag.agreementYear = ‘2014’

[sourcecode lang=”java”] // Without MetaModel
cq = cb.createQuery(Agreement.class);
Root<Agreement> agreement = cq.from(Agreement.class);
cq.select(agreement).where(
cb.equal(agreement.get("agreementYear"), "2014")
);

// Without MetaModel – typesafety thru metamodel
cq = cb.createQuery(Agreement.class);
Root<Agreement> agreement = cq.from(Agreement.class);
cq.select(agreement).where(cb.equal(
agreement.get(Agreement_.agreementYear), 2014)
);
[/sourcecode]

The rest of the examples will use the metamodel.

SELECT ag FROM Agreement ag WHERE ag.agreementName LIKE ‘%test%’

[sourcecode lang=”java”] cq = cb.createQuery(Agreement.class);
Root<Agreement> agreement = cq.from(Agreement.class);
cq.select(agreement).where( cb.like(
agreement.get(Agreement_.agreementName), "%test%" )
)
);
[/sourcecode]

SELECT ag FROM Agreement ag WHERE ag.agreementName IS NULL

[sourcecode lang=”java”] cq = cb.createQuery(Agreement.class);
Root<Agreement> agreement = cq.from(Agreement.class);
cq.select(agreement).where(
agreement.get(Agreement_.agreementName).isNull()
);
[/sourcecode]

SELECT ag FROM Agreement ag WHERE ag.claims IS EMPTY

[sourcecode lang=”java”] cq = cb.createQuery(Agreement.class);
Root<Agreement> agreement = cq.from(Agreement.class);
cq.select(agreement).where(
cb.isEmpty(agreement.<Collection>get("claims"))
);
[/sourcecode]

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

[sourcecode lang=”java”] CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Agreement> query = cb.createQuery(Agreement.class);
Root<Agreement> agreement = query.from(Agreement.class);
Join<Claim, Agreement> claims = agreement.join("claims");
query.select(claims);
List<Claim> claims = em.createQuery(query).getResultList();
[/sourcecode]

SELECT ag FROM Agreement ag JOIN ag.claims cl WHERE cl.amount = 1000

[sourcecode lang=”java”] CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Agreement> query = cb.createQuery(Agreement.class);
Root<Agreement> agreement = query.from(Agreement.class);
Join<Claim, Agreement> claims = agreement.join("claims");
query.select(claims).where(cb.equal(agreement .get("amount"), 1000));
List<Claim> claims = em.createQuery(query).getResultList();
[/sourcecode]

LEFT JOIN == LEFT OUTER JOIN

SELECT ag FROM Agreement ag LEFT JOIN ag.claims cl WHERE cl.amount > 1000

[sourcecode lang=”java”] CriteriaQuery cq = cb.createQuery(Agreement.class);
Root<Agreement> = cq.from(Agreement.class);
SetJoin claims = agreement.join(Agreement_.claims, JoinType.LEFT);
cq.select(agreement).where( cb.equal(claims.get(Claim_.amount), 1000) );
List result = em.createQuery(cq).getResultList();
[/sourcecode]

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

[sourcecode lang=”java”] CriteriaQuery cq = cb.createQuery(Agreement.class);
Root<Agreement> agreement = cq.from(Agreement.class);
SetJoin<Agreement, Claims> claims = agreement.join(Agreement_.claims, JoinType.LEFT);
cq.select(agreement).where( cb.equal(claims.get(Claim_.amount), 1000) );
agreement.fetch("claims");
List result = em.createQuery(cq).getResultList();
[/sourcecode]

References

http://en.wikibooks.org/wiki/Java_Persistence/Criteria
http://docs.oracle.com/javaee/6/tutorial/doc/gjrij.html