JPQL Java Persistence Query Language

1. JPQLJava Persistence Query Language Cornelius Zühl Arend Kühle Komponentenarchitektur WS 08/09 Java Persistence Query Language

2. Java Persistence APIJPA 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

3. Java Persistence APIJPA persistance.xml EntityManager Hibernate Objektoperationen find merge persist update remove JPQL Query API Oracle TopLink Entity Java Persistence Query Language

4. JPQL Syntax Query select u from User u SQL Syntaxkriterien werden genutztand or < > = <= <> ( ) select u.name from User u where (u.age> 18 andu.age<= 65)oru.age= 83 Erweitert um Objektorientierte Befehleis empty, is null, not … Java Persistence Query Language

5. 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

6. Datenabfrage Datenabfrage EntityManager em;em.find(...); EntityManager Primary Key Query API JPQL Query q; Query q = em.createQuery(...) NativeQuery q = em.createNativeQuery(...) NamedQuery q = em.createNamedQuery(...) Java Persistence Query Language

7. 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

8. 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

9. 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

10. 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

11. 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(intfirstRow, intmaxResults) { 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

12. Konkrete Datenstatt 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); } publicclass User { publicUser(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

13. Named Queries @Entity() @NamedQuery (name=„findAllUsers", query="select u from User u") publicclass 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") }) publicclass 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

14. Exception Handling getSingleResult() javax.persistence.NoResultException javax.persistence.NoneUniqueResultException java.lang.IllegalStateException getResultList() java.lang.IllegalStateException Java Persistence Query Language

15. 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