Java Persistence Queries

Effektive DB-Abfragen mit Features aus dem Standard

und darüber hinaus

Expertenkreis Java, 18.06.2015, GEDOPLAN

Dirk Weil, GEDOPLAN GmbH

Java Persistence Queries

Dirk Weil

GEDOPLAN GmbH, BielefeldJava EE seit 1998Konzeption undRealisierungSeminareVorträgeVeröffentlichungen

2

Java Persistence Queries

Java Persistence

Mapping OO RDBMSPOJOsDetachment macht DTOs verzichtbar

API zum Speichern, Laden, Löschen, Finden von DB-EinträgenEclipselink, Hibernate, OpenJPA, …

Seit Java EE 5 im StandardAuch in SE nutzbar

Aktuelle Version 2.13

Java Persistence Queries

Demo-Entities

4

@Entitypublic class Publisher{ @Id private Integer id; private String name;

@ManyToOne private Country country;

@OneToMany(mappedBy = "publisher") private List<Book> books;

@Entitypublic class Book{ @Id private Integer id; private String name; private String isbn; private int pages;

@ManyToOne private Publisher publisher;

1

n

@Entitypublic class Country{ @Id @Column(name = "ISO_CODE") private String isoCode; private String name;n

1

Java Persistence Queries

JPQL

Java Persistence Query LanguageSQL-ähnlich, jedoch objektorientiertEntityManager.createQuery liefert TypedQuery<T>Ausführung mit getSingleResult bzw. getResultList

5

Publisher publisher = entityManager .createQuery("select p from Publisher p where p.name=?1", Publisher.class) .setParameter(1, "O'Melly Publishing") .getSingleResult();

List<Book> books = entityManager .createQuery("select b from Book b where b.pages>=500", Book.class) .getResultList();

Java Persistence Queries

JPQL

Navigation durch Relationen mit '.' und join

6

List<Book> books = entityManager .createQuery("select b from Book b where b.publisher.country=?1", Book.class) .setParameter(1, countryDE) .getResultList();

List<Publisher> publishers = entityManager .createQuery("select distinct p from Publisher p " + "join p.books b where b.pages>=500", Publisher.class) .getResultList();

Java Persistence Queries

JPQL

JPQL SQL "leichtgewichtig"

7

List<Publisher> publishers = entityManager .createQuery("select distinct p from Publisher p " + "join p.books b where b.pages>=500", Publisher.class) .getResultList();

SELECT DISTINCT t1.ID, t1.NAME, t1.COUNTRY_ISO_CODE FROM JPA_BOOK t0, JPA_PUBLISHER t1 WHERE ((t0.PAGES >= ?) AND (t0.PUBLISHER_ID = t1.ID))

select distinct publisher0_.ID as ID1_35_, publisher0_.COUNTRY_ISO_CODE as COUNTRY_3_35_, publisher0_.name as name2_35_ from JPA_PUBLISHER publisher0_ inner join JPA_BOOK books1_ on publisher0_.ID=books1_.publisher_ID where books1_.pages>=500

Eclipselink

Hibernate

Java Persistence Queries

Extended Queries

Selektion von Einzelattributen etc.

8

List<Object[]> resultList = entityManager .createQuery("select p.name, p.country.name from Publisher p", Object[].class) .getResultList();

List<Object[]> resultList = entityManager .createQuery("select p, count(b) from Publisher p " + "left join p.books b " + "group by p", Object[].class) .getResultList();

Java Persistence Queries

Extended Queries

Selektion von Einzelattributen etc.

9

List<NameAndCount> resultList = entityManager .createQuery("select new somepkg.NameAndCount(p.name, sum(b.pages)) " + "from Publisher p " + "join p.books b " + "where b.name like '%Java%' " + "group by p", NameAndCount.class) .getResultList();

public class NameAndCount{ private String name; private Number count;

public NameAndCount(String name, Number count) {

Java Persistence Queries

Native Queries

bei schon vorhandenen SQL-Queriesfür "besondere Fälle" (proprietäres SQL, spezielles Tuning, …)

"Verlust" des O/R-Mappingsauch Komplettobjekte als Ergebnis möglich

10

@SuppressWarnings("unchecked")List<Object[]> resultList = entityManager .createNativeQuery("SELECT DISTINCT p.ID, p.NAME, p.COUNTRY_ISO_CODE " + "FROM JPA_PUBLISHER p, JPA_BOOK b " + "WHERE b.PUBLISHER_ID = p.ID AND b.PAGES >= ?") .setParameter(1, 500) .getResultList();

Java Persistence Queries

Stored Procedure Queries

für "noch besonderere Fälle"IN-, OUT- oder IN/OUT-Parameter

11

CREATE PROCEDURE GET_COCKTAIL_COUNT(IN ZUTAT_NAME VARCHAR(255)) BEGIN select count(*) from JPA_COCKTAIL C where exists (…); END

Number count = (Number) entityManager .createStoredProcedureQuery("GET_COCKTAIL_COUNT") .registerStoredProcedureParameter("ZUTAT_NAME", String.class, ParameterMode.IN) .setParameter("ZUTAT_NAME", "Sekt") .getSingleResult();

Java Persistence Queries

Criteria Query API

Textbasierte Queries lassen sich zur Compile- oder Deploymentzeit nicht prüfen

SyntaxNamenTypen

12

select p fron Publisher p

select p from Publisher

select p from Publisher p where p.nam=:name

List<Publisher> books = entityManager .createQuery("select b from Book b where b.pages>=500", Publisher.class) .getResultList();

Java Persistence Queries

Criteria Query API

Query wird mittels API kombiniertCriteriaQuery<T> mittels CriteriaBuilder erstellen

from, where, select …

Ausführung als "normale" TypedQuery<T>

13

CriteriaBuilder cb = entityManager.getCriteriaBuilder();CriteriaQuery<Book> cq = cb.createQuery(Book.class);

List<Book> books = this.entityManager .createQuery(cq) .getResultList();

Java Persistence Queries

Criteria Query API

14

// select b from Book b where b.pages>=500Root<Book> b = cq.from(Book.class);cq.select(b) .where(cb.greaterThanOrEqualTo(b.get(Book_.pages), 500));

// select b from Book b where b.publisher.country=:countryRoot<Book> b = cq.from(Book.class);cq.select(b) .where(cb.equal(b.get(Book_.publisher).get(Publisher_.country), cb.parameter(Country.class, "country")));

List<Book> books = this.entityManager .createQuery(cq) .setParameter("country", CountryTest.testCountryDE) .getResultList();

Java Persistence Queries

Criteria Query API

nutzt statisches Metamodell E_ zu persistenter Klasse E

15

@StaticMetamodel(Publisher.class)public abstract class Publisher_ { public static volatile SingularAttribute<GeneratedIntegerIdEntity, Integer> id; public static volatile SingularAttribute<Publisher, String> name; public static volatile SingularAttribute<Publisher, Country> country; public static volatile ListAttribute<Publisher, Book> books;

@Entitypublic class Publisher{ @Id private Integer id; private String name;

@ManyToOne private Country country;

@OneToMany(mappedBy = "publisher") private List<Book> books;

Java Persistence Queries

Criteria Query API

Metamodell wird durch Annotation Processor generiertmuss im Compile Classpath liegen (z. B. als Maven Dependency)

wird vom Compiler aufgerufen (Java 6+)

16

<dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-jpamodelgen</artifactId> <version>4.3.10.Final</version> <scope>provided</scope> <optional>true</optional></dependency>

Java Persistence Queries

Criteria Query API

Annotation Processing in Eclipsekann in Luna entsprechend Maven konfiguriert werden

sonst: Im Projekt Annotation Processing manuell konfigurierenJava Compiler Annotation Processing aktivieren, Zielordner wählen Factory Path Compile Classpath einstellen

17

Java Persistence Queries

Root<Publisher> p = cq.from(Publisher.class);ListJoin<Publisher, Book> b = p.join(Publisher_.books);cq.select(cb.construct(NameAndCount.class, p.get(Publisher_.name), cb.sum(b.get(Book_.pages)))) .where(cb.like(b.get(Book_.name), "%Java%")) .groupBy(p);

Criteria Query API

Problem: Lesbarkeit

18

// select new NameAndCount(p.name, sum(b.pages)) // from Publisher p // join p.books b // where b.name like '%Java%' // group by p

Java Persistence Queries

Root<Publisher> p = cq.from(Publisher.class);ListJoin<Publisher, Book> b = p.join(Publisher_.books);cq.select(p.get(Publisher_.name)) .distinct(true) .where(cb.and(cb.equal(p.get(Publisher_.country), cb.parameter(Country.class, "country")), cb.like(b.get(Book_.name), "%Java%")));

Criteria Query API

Problem: API durch CriteriaBuilder (u. a.) nicht "fluent"

19

p ~ Publisher;b ~ p.books;c ~ Parameter(Country);select(p.name) .distinct() .from(p) .where(p.country.equal(c).and(b.name.like("%Java%")))

Wunsch

Ist

Java Persistence Queries

QueryDSL

Open Source (http://www.querydsl.com)Sponsored by Mysema (http://www.mysema.com)Query wird mittels API kombiniert

Query Roots und JPAQuery für EntityManager erzeugen

Methoden from, join, where, orderBy, distinct etc.

Query ausführen mittels singleResult, list

20

QPublisher p = QPublisher.publisher;JPAQuery jpaQuery = new JPAQuery(this.entityManager)

List<Publisher> result = jpaQuery.list(p);

Java Persistence Queries

QueryDSL

benötigt generierte Klassenähnlich JPA Metamodell

21

@Generated("com.mysema.query.codegen.EntitySerializer")public class QPublisher extends EntityPathBase<Publisher> { public static final QPublisher publisher = new QPublisher("publisher"); public final StringPath name = createString("name"); public final ListPath<Book, QBook> books = this.<Book, QBook>createList("books", … public final de.gedoplan.seminar.jpa.demo.basics.entity.QCountry country;

@Entitypublic class Publisher{ @Id private Integer id; private String name;

@ManyToOne private Country country;

@OneToMany(mappedBy = "publisher") private List<Book> books;

Java Persistence Queries

QueryDSL

Metamodell wird durch Annotation Processor generiertz. B. mit Maven Plugin

22

<plugin> <groupId>com.mysema.maven</groupId> <artifactId>apt-maven-plugin</artifactId> <version>1.1.3</version> <executions> <execution> <goals><goal>process</goal></goals> <configuration> <outputDirectory>target/generated-sources/annotations</outputDirectory> <processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor> </configuration> </execution> </executions></plugin>

Java Persistence Queries

QueryDSL

23

QPublisher p = QPublisher.publisher;QBook b = QBook.book;Param<Country> countryParam = new Param<>(Country.class);

List<String> names = new JPAQuery(this.entityManager) .from(p) .innerJoin(p.books, b) .where(p.country.eq(countryParam).and(b.name.like("%Java%"))) .distinct() .set(countryParam, CountryTest.testCountryDE) .list(p.name);

Root<Publisher> p = cq.from(Publisher.class);ListJoin<Publisher, Book> b = p.join(Publisher_.books);cq.select(p.get(Publisher_.name)) .distinct(true) .where(cb.and(cb.equal(p.get(Publisher_.country), cb.parameter(Country.class, "country")), cb.like(b.get(Book_.name), "%Java%")));

QueryDSL

Criteria

Query API

Java Persistence Queries

QueryDSL

Bisherige ErfahrungenIntuitives API (meist …)gute Lesbarkeit

API hat noch Schwächenz. B. Umwandlung von JPAQuery nur in Query möglich, nicht in TypedQuery

Noch nicht ganz ausgereifteinige Bugs (z. B. Constructor Result akzeptiert keine Aggregat-Ausdrücke)Dokumentation lückenhaft und fehlerhaftUmstellung com.mysema.querydsl com.querydsl buggy

24

Java Persistence Queries

More

http://www.gedoplan-it-training.deSeminare in Berlin, Bielefeld, Inhousehttp://www.gedoplan-it-consulting.deReviews, Coaching, …

http://javaeeblog.wordpress.com/http://expertenkreisjava.blogspot.de/

dirk.weil@gedoplan.de@dirkweil

25