Querydsl overview 2014

Querydsl presentation

1. Querydsl

Most popular querying tool for Java
Timo Westkämper
@timowest
www.querydsl.com

2. What?
●Querydsl is an easy to use unified type-safe query
language
●Compile time query validation
●Instant feedback on query errors
●Compact and intuitive fluent syntax
●Syntactically close to SQL
●Great for dynamic query building
●Supports multiple back-ends and query languages
with consistent query API
●JPA/Hibernate, Mongodb, SQL, Lucene...

3. Why?
●Querydsl makes you more productive and your code
less errorprone
●Query syntax validation by execution is slow and
breaks the flow
●Each back-end has its own query language and API
●SQL-like for JPA and JDO, but not for MongoDB
and Lucene
●Verbose parameter binding by name or position to
parameter placeholders of a prepared statement
●Or risk injection attack if parameters are directly
concatenated to query

4. How?
QPerson person = QPerson.person;
JPAQuery query = new JPAQuery(entityManager);
List<Person> persons = query.from(person)
.where(
person.firstName.eq("John"),
person.lastName.eq("Doe"))
.list(person);

is translated into

select person from com.acme.Person person
where person.firstName eq = ?1 and person.lastName = ?2

5. Before Querydsl
●Queries as strings within code

TypedQuery<Person> query = em.createQuery(
"select person from Person person " +
"where person.firstName = ?1", Person.class);
query.setParameter(1, "Max");
List<Person> persons = query.getResultList();

●Must remember query syntax, domain classes,
properties and relationships
●Syntax reference always at hand
●Domain model/schema reference at hand
●High cognitive overhead
●Error-prone

6. Before Querydsl
●Dynamic query building by string concatenation
●Very hard with multiple joins, ordering and complex
conditionals depending on actual parameters

StringBuilder where = new StringBuilder();
if (firstName != null)
where.append("person.firstName = :firstName");
...
TypedQuery<Person> query = entityManager.createQuery(
"select person from Person person where " + where,
Person.class);
if (firstName != null) query.setParameter("firstName", firstName);
...
List<Person> persons = query.getResultList();

7. Before Querydsl
●Hibernate Criteria API as an alternative?
●Better for dynamic queries and has easier
parameter binding, but...
●Lacking expressivity, unintuitive, verbose,
cognitive overhead for schema if not for syntax,
not type-safe, slow validation...

●Hibernate with three query languages to
master with different focuses and expressivity

8. Querydsl to the rescue!
●Create your variables
QPerson.person // default variable
new QPerson("myPerson") // custom variable

●Create your query
JPAQuery, HibernateQuery, SQLQuery etc

●Populate your query
from, where, groupBy, having, orderBy

●Get the results
count, iterate, list, uniqueResult

9. Order
// Get persons ordered by last name and first name (desc)
query.from(person)
.orderBy(person.lastName.asc(), person.firstName.desc())
.list(person);

translated into

select person from Person person
order by person.lastname asc, person.firstName desc

10. Order
// Get persons ordered by women first
query.from(person)
.orderBy(person.gender
.when(Gender.FEMALE).then(0)
.otherwise(1).asc())
.list(person);

translated into

select person from Person person
order by case person.gender = Gender.FEMALE then 0 else 1 end asc

11. Grouping
// Get person counts grouped by last name
query.from(person)
.groupBy(person.lastName)
.list(person.lastName, person.count());

translated into

select person.lastName, count(person) from Person person
group by person.lastName

12. Subqueries
//Get persons with max child count
QPerson parent = new QPerson("parent");
query.from(person)
.where(person.children.size().eq(
new JPASubQuery().from(parent)
.uniqueResult(parent.children.size().max())
)).list(person);

translated into

select person from Person person
where person.children.size() = (
select max(parent.children.size()) from Person parent)

13. Constructor projection
// DTO class with @QueryProjection constructor annotation
public class PersonInfo {
long id;
String name;
@QueryProjection
public PersonInfo(long id, String name) {
this.id = id;
this.name = name;
}
}

// List PersonInfo DTOs
List<PersonInfo> infos = query.from(person)
.list(new QPersonInfo(person.id,
person.lastName.concat(", ”).concat(person.firstName)));

14. Tuple projection
// List ages of persons
List<Tuple> tuples = query.from(person)
.list(new QTuple(
person.lastName,
person.firstName,
person.yearOfBirth));

for (Tuple tuple : tuples){
// Typed access to mapped query results!
String name = tuple.get(person.firstName) +
" " + tuple.get(person.lastName);
int age = tuple.get(person.yearOfBirth)
- getCurrentYear();
System.out.println(name + " is " + age + " years");
}

15. BooleanBuilder
●Helper for building complex Boolean expressions
dynamically

BooleanBuilder nameDisjunction = new BooleanBuilder();
for (String name : names) {
nameDisjunction.or(person.firstName.like(name));
nameDisjunction.or(person.lastName.like(name));
}
query.where(nameDisjunction);

16. Update
// Set firstName of all Does to John
long updatedRowCount =
new JPAUpdateClause(getEntityManager(), person)
.set(person.firstName, "John")
.where(person.lastName.eq("Doe"))
.execute();

translated into

update Person person
set person.firstName = ?1
where person.lastName = ?2

17. Delete
// Delete all John Does
long updatedRowCount =
new JPADeleteClause(getEntityManager(), person)
.where(person.lastName.eq("Doe"),
person.firstName.eq("John"))
.execute();

translated into

delete Person person
where person.lastName = ?1 and person.firstName = ?2

18. Querydsl extensions
●Customize the code generation
●@QueryType(PropertyType.NONE)
●Non searchable
●@QueryType(PropertyType.SIMPLE)
●Equality comparisons only (eq, ne, in)
●Custom query classes
●Extend abstract super classes and preserve fluent
API
●Custom expressions
●Static delegate methods with @QueryDelegate
●Template expressions for e.g. custom SQL
functions

19. Querydsl extensions
●Query serialization can be customized
●Works for JPA, JDO and SQL
●SQL dialects
●Overriding default templates (e.g.
String#startsWith with like or regexp or...)
●Expression DSL can be replaced
●E.g. Querydsl for Scala
●Custom back-ends
●Lucene (10 classes) + Mongodb (6 classes)

20. Delegate methods
public class MyQueryExtensions {
@QueryDelegate(Date.class)
public static NumberExpression<Integer> yearAndMonth(DateTimePath<Date> date) {
return date.year().multiply(100).add(date.month());
}
}

causes code generation of

package ext.java.util;
...
public class QDate extends DateTimePath<java.util.Date> {
...
public NumberExpression<Integer> yearAndMonth() {
return MyQueryExtensions.yearAndMonth(this);
}
}

21. Template expressions
// ilike
query.from(person)
.where(BooleanTemplate.create("{0} ilike {1}”,
person.lastName, ConstantImpl.create("P%")))
.list(person);

translated into

select person from Person person
where person.lastName ilike ?1

22. Custom query classes
public class PersonQuery extends AbstractJPAQuery<PersonQuery> {
final QPerson person = QPerson.person;
public PersonQuery(EntityManager em) {
super(em);
from(person);
}
public PersonQuery nameMatches(String name) {
return where(person.firstName.like(name)
.or(person.lastName.like(name)));
}
}

23. JPA 2.0 Criteria vs Querydsl
●JPA 2 Criteria is the standard for type-safe
queries in JPA, but Querydsl is in our opinion
superior in many ways
●Easier and less verbose syntax
●Customizable
●Supports multiple back-ends – not just JPA
●JPA has a difficult to use static query-model
●Verbose property paths
●Operations via builder object
●Inverse order: “equals property value” vs.
“property equals value”
●Broken flow

24. Criteria example
// All possible pairs of single males and females
CriteriaQuery<Person> query = builder.createQuery(Person.class);
Root<Person> men = query.from( Person.class );
Root<Person> women = query.from( Person.class );
Predicate menRestriction = builder.and(
builder.equal( men.get( Person_.gender ), Gender.MALE ),
builder.equal( men.get( Person_.relationshipStatus ),
RelationshipStatus.SINGLE )
);
Predicate womenRestriction = builder.and(
builder.equal( women.get( Person_.gender ), Gender.FEMALE ),
builder.equal( women.get( Person_.relationshipStatus ),
RelationshipStatus.SINGLE )
);
query.where( builder.and( menRestriction, womenRestriction ) );

25. Querydsl example
// All possible pairs of single males and females
JPAQuery query = new JPAQuery(entityManager);
QPerson men = new QPerson("men");
QPerson women = new QPerson("women");
query.from(men, women).where(
men.gender.eq(Gender.MALE),
men.relationshipStatus.eq(RelationshipStatus.SINGLE),
women.gender.eq(Gender.FEMALE),
women.relationshipStatus.eq(RelationshipStatus.SINGLE));

26. SQL
●Pretty similar to JPA/Hibernate
●No deep paths over relations though
●No implicit joins

SQLTemplates templates = new MySQLTemplates();
...
SQLQuery query = new SQLQuery(connection, templates);
query.from(person);
query.innerJoin(parent).on(parent.id.eq(person.parent.id));

●Shortcut for joins with foreign keys

query.innerJoin(person.parentFK, parent);

27. SQL
●Maven plugin for generating query model
●Support for special SQL constructs and extensions

●Databases supported include
●MySQL
●PostgreSQL
●Oracle
●MS SQL Server
●H2
●HSQLDB
●Derby
●SQLite
●CUBRID
●Teradata

28. SQL extensions
●Sub class of AbstractSQLQuery
●e.g. OracleQuery with connectByPrior
●Template expressions
●Direct addition of “flags”

SQLInsertClause insert =
new SQLInsertClause(connection, templates, person);
insert.addFlag(Position.START_OVERRIDE , "replace into ");

29. Collections
●Provides querying functionality over collections of
beans with joins, filtering and sorting
●The same metamodel types can be used like for e.g.
JPA and Mongodb

List<User> users = CollQueryFactory.from(user, users)
.where(user.firstName.eq(“Bob”))
.list(user);

30. JPA/Hibernate Maven
Integration
<build><plugins><plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.0.9</version>
<executions>
<execution>
<goals><goal>process</goal></goals>
<configuration>
<outputDirectory>target/generated-sources/java</outputDirectory>
<processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin></plugins></build>

31. SQL Maven Integration
<build><plugins><plugin>
<groupId>com.mysema.querydsl</groupId>
<artifactId>querydsl-maven-plugin</artifactId>
<version>${querydsl.version}</version>
<executions><execution>
<goals><goal>export</goal></goals>
</execution></executions>
<configuration>
<jdbcDriver>org.apache.derby.jdbc.EmbeddedDriver</jdbcDriver>
<jdbcUrl>jdbc:derby:target/demoDB;create=true</jdbcUrl>
<!—- optional elements : namePrefix, jdbcUser, jdbcPassword, schemaPattern, tableNamePattern -->
<packageName>com.myproject.domain</packageName>
<targetFolder>${project.basedir}/target/generated-sources/java</targetFolder>
</configuration>
<dependencies><dependency>
<!—- jdbc driver dependency -->
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>${derby.version}</version>
</dependency></dependencies>
</plugin></plugins></build>

32. What services does Mysema
offer for Querydsl?
●Free public support
●GitHub Issues
●Querydsl Google Group
●Mysema Blog
●Consulting services
●User support
●Custom extensions and integration
●Training

33. Questions?

34. Thanks!
Timo Westkämper
@timowest
www.querydsl.com
www.mysema.com