Zühl, Kühle WS 08/09

JPQL
Java Persistence Query Language
Cornelius Zühl
Arend Kühle
Komponentenarchitektur WS 08/09
Java Persistence Query Language
Zühl, Kühle WS 08/09
Java Persistence API
JPA
import javax.persistence
• Persistence Entity
• Objektrelationale Metadaten
– Java-Annotationen oder XML-Datei
• Java Persistence Query Language (JPQL)
– ähneln syntaktisch SQL-Abfragen
– bezieht sich auf Entitäten
Java Persistence Query Language
Zühl, Kühle WS 08/09
Java Persistence API
JPA
persistance.xml
EntityManager
Hibernate
Objektoperationen
find merge persist update remove
JPQL
Query API
Oracle TopLink
Entity
Java Persistence Query Language
Zühl, Kühle WS 08/09
JPQL Syntax
Query
select u from User u
SQL Syntaxkriterien werden genutzt
and or < > = <= <> ( )
select u.name from User u where (u.age > 18 and u.age <= 65) or u.age = 83
Erweitert um Objektorientierte Befehle
is empty, is null, not …
Java Persistence Query Language
Zühl, Kühle WS 08/09
JPQL Statement
JPQL Statement
select update delete
Query q = em.createQuery („select u from User u");
List<User> userList = q.getResultList();
Query q = em.createQuery ("update User u set u.name = ‘Max‘ where u.name = ‘Moritz‘");
int updated = q.executeUpdate();
Query q = em.createQuery ("delete u from User u where u.name = ‘Busch‘ ");
int deleted = q.executeUpdate();
Java Persistence Query Language
Zühl, Kühle WS 08/09
Datenabfrage
Datenabfrage
EntityManager
Query API
Primary Key
EntityManager em;
em.find(...);
JPQL
Query q;
Query
q = em.createQuery(...)
NativeQuery
q = em.createNativeQuery(...)
NamedQuery
q = em.createNamedQuery(...)
Java Persistence Query Language
Zühl, Kühle WS 08/09
JPQL Funktionen
select u from User u where concat(u.name, 's') = 'Walters'
select u from User u where substring(u.name, 1, 1) = 'W'
select u from User u where trim(leading 'a' FROM u.name) = 'W'
select u from User u where lower(u.name) = 'walter'
select u from User u where upper(u.name) = 'WALTER'
select u from User u where length(u.name) = 6
select u from User u where locate('a', u.name) = 2
select u from User u where abs(u.age) >= 5.00
select u from User u where sqrt(u.age) >= 1000
select u from User u where mod(u.age, 10) = 0
Java Persistence Query Language
Zühl, Kühle WS 08/09
Aggregatsfunktionen
• MIN, MAX, AVG, COUNT und SUM
Query q = em.createQuery("select
min(u.age),
max(u.age),
avg(u.age),
sum(u.age)
from User u”);
Object[] stats = (Object[]) q.getSingleResult();
Java Persistence Query Language
Zühl, Kühle WS 08/09
Polimorphische Queries
Auto
Fiat
Opel
Renault
Query q = em.createQuery („select a from Auto a where a.price < 10000");
List<User> autoList = q.getResultList();
Java Persistence Query Language
Zühl, Kühle WS 08/09
Prepared Statements
em.createQuery("select u from User u where name = " +name+ " and age > " +age);
public List<User> findUsers(String name, int age) {
Query q = em.createQuery(" Select u from User u where name = :name and age > :age");
q.setParameter("name", name);
q.setParameter("age", age);
return List<User> users = q.getResultList();
}
public List<User> findUsers(String name, int age) {
Query q = em.createQuery(" select u from User u where name = ?1 and age > ?2");
q.setParameter(1, name);
q.setParameter(2, age);
return List<User> users = q.getResultList();
}
Entity Object
public User findUserByAddress(Address address) {
Query q = em.createQuery(" select u from User u where u.address = :address ");
q.setParameter(„address", adedress);
return q.getSingleResult();
}
Java Persistence Query Language
Zühl, Kühle WS 08/09
Begrenzen der Ergebnismenge
Query q = em.createQuery("select u from User u");
q.setFirstResult(30);
q.setMaxResults(10);
List<User> users = q.getResultList();
public List<User> findUsers(int firstRow, int maxResults) {
Query q = em.createQuery("select u from User u");
q.setFirstResult(firstRow);
q.setMaxResults(maxResults);
return List<User> users = q.getResultList();
}
Java Persistence Query Language
Zühl, Kühle WS 08/09
Konkrete Daten
statt komplette Objekte
Query q = em.createQuery("select u.id, u.name from User u");
List<Object[]> result = q.getResultList();
List<User> users = new ArrayList<User>(result.size());
for(Object[] objAr : result){
Long userId = objAr[0];
String userName = objAr[1];
User u = new User();
u.setUserId(userId);
u.setUserName(userName);
users.add(u);
}
public class User {
public User(String id, String name) {
this.id = id;
this.name = name;
}}
Query q = em.createQuery("select new User(u.id, u.name) from User u");
List<User> result = q.getResultList();
Java Persistence Query Language
Zühl, Kühle WS 08/09
Named Queries
@Entity()
@NamedQuery (name=„findAllUsers", query="select u from User u")
public class User {
// ...
}
@Entity()
@NamedQuerys ({
@NamedQuery (name=„findAllUsers", query="select u from User u"),
@NamedQuery (name=„findAdults", query="select u from User u where age > 17"),
@NamedQuery (name=„getAverageAge", query="select avg(u.age) from User u")
})
public class User {
// ...
}
public List<User> getUser() {
Query query = em.createNamedQuery("findAllUsers");
// Query query = em.createNamedQuery("User.findAllUsers");
List<User> userList = query.getResultList();
return userList;
}
Java Persistence Query Language
Zühl, Kühle WS 08/09
Exception Handling
getSingleResult()
javax.persistence.NoResultException
javax.persistence.NoneUniqueResultException
java.lang.IllegalStateException
getResultList()
java.lang.IllegalStateException
Java Persistence Query Language
Zühl, Kühle WS 08/09
Und nun zur Praxis
Vorführung
• Tools
• Queries
• Tipps
Quellen
• http://edocs.bea.com/kodo/docs41/full/html/ejb3_overview_query.html
• http://java.sun.com/mailers/techtips/enterprise/2006/TechTips_Oct06.html
• http://javathreads.de/2008/04/jpa-mit-hibernate-einfuehrung/
• http://www.kunkelgmbh.de/jpa/
Java Persistence Query Language
Zühl, Kühle WS 08/09