Java
Raiting:
6

How to Use Querydsl in Java Application


querydsl

Technology: Querydsl is a java based framework which enables the construction of statically typed SQL-like queries. Instead of writing queries as inline strings or externalizing them into XML files they can be constructed via a fluent API like Querydsl.we can use Querydsl in java application for creating all kinds of sql statements.Querydsl has various plugins for JPA,mongoDb, SQL,lucene and also for java collections.

The benefits of using a fluent API in comparison to simple strings are:
    code completion in IDE
    almost none syntactically invalid queries allowed
    domain types and properties can be referenced safely
    adopts better to refactoring changes in domain types


Principles of Query DSL:

Type safety is the core principle of Querydsl. Queries are constructed based on generated query types that reflect the properties of domain types. Also function/method invocations are constructed in a fully type-safe manner.

Consistency is another important principle. The query paths and operations are the same in all implementations and also the Query interfaces have a common base interface.

Querying JPA:
Querydsl defines a general statically typed syntax for querying on top of persisted domain model data.Querydsl for JPA is an alternative to both JPQL and Criteria queries. It combines the dynamic nature of Criteria queries with the expressiveness of JPQL and all that in a fully type-safe manner.

Preparation:

Add the property in pom.xml :
<querydsl.version>4.1.3</querydsl.version>

And add the below dependencies in pom.xml:

<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.6.1</version>
</dependency>

And Query DSL Maven Plugin(APT Plugin):

<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>generated-sources</outputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>


The JPAAnnotationProcessor finds domain types in classpath which are annotated with the javax.persistence.Entity annotation and generates query types for them.

If we use Hibernate annotations instead of JPA annotation in domain types we need to use the APT processor com.querydsl.apt.hibernate.HibernateAnnotationProcessor instead of com.querydsl.apt.jpa.JPAAnnotationProcessor

Generating Query Types:
After adding Maven plugin, if we run the clean compile then classes will be generated in specified outputDirectory(generated-sources).

Adding generated sources to classpath:
If we run the run mvn eclipse:eclipse to update Eclipse project to include outputDirectory as a source folder.

Queries with QueryDSL:
Queries can be constructed based on generated query types in QueryDSL entities, and also function/method(s) are constructed using type-safe manner.
All the QueryDSL entities are extending EntityPathBase class in type-safer manner.

Creating Entity and QueryDsl type:

Lets define one simple entity and we will use the same entity is used in following examples.

Person.java

package org.sample.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@Column
private String firstname;

@Column
private String surname;

@Column
private int age;

public Person() {
}

public Person(final String firstname, final String surname) {
this.firstname = firstname;
this.surname = surname;
}

public Person(final String firstname, final String surname, final int age) {
this(firstname, surname);
this.age = age;
}
// setters and getters…
}


QueryDsl maven plugin will generate query type with QPerson with same package name,It contains a static field which will return Person type.

public static final QPerson person = new QPerson("person");

Generated QPerson.java

package org.sample.entity;

import static com.querydsl.core.types.PathMetadataFactory.*;

import com.querydsl.core.types.dsl.*;

import com.querydsl.core.types.PathMetadata;
import javax.annotation.Generated;
import com.querydsl.core.types.Path;

/**
* QPerson is a Querydsl query type for Person
*/
@Generated("com.querydsl.codegen.EntitySerializer")
public class QPerson extends EntityPathBase<Person> {

private static final long serialVersionUID = 1183946598L;

public static final QPerson person = new QPerson("person");

public final NumberPath<Integer> age = createNumber("age", Integer.class);

public final StringPath firstname = createString("firstname");

public final NumberPath<Long> id = createNumber("id", Long.class);

public final StringPath surname = createString("surname");

public QPerson(String variable) {
super(Person.class, forVariable(variable));
}

public QPerson(Path<? extends Person> path) {
super(path.getType(), path.getMetadata());
}

public QPerson(PathMetadata metadata) {
super(Person.class, metadata);
}

}


Building Queries with JPAQuery:

We need to create EntityManager using persistence.xml for retrieving the Database.we can create dataSource and using dataSource we can create a EntityManagerFactoryBean bean.

Persistence.xml:
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd" version="2.0">

<persistence-unit name="default" transaction-type="RESOURCE_LOCAL">
<properties>
<property name="hibernate.hbm2ddl.auto" value="update" />
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.transaction.flush_before_completion" value="true" />
<property name="hibernate.cache.provider_class" value="org.hibernate.cache.HashtableCacheProvider" />
</properties>
</persistence-unit>

</persistence>

And Spring Bean configuration:
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" name="EntityManagerFactory">
<property name="persistenceUnitName" value="default"></property>
<property name="dataSource" ref="dataSource"></property>
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<property name="showSql" value="true" />
<property name="generateDdl" value="true" />
<property name="databasePlatform" value="${db.dialect}" />
</bean>
</property>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${db.driver}" />
<property name="url" value="${db.url}" />
<property name="username" value="${db.username}" />
<property name="password" value="${db.password}" />
</bean>

We are externalizing the Database related properties in properties file:
Db.properties:
db.username=sa
db.password=
db.driver=org.hsqldb.jdbc.JDBCDriver
db.url=jdbc:hsqldb:mem:app-db
db.dialect=org.hibernate.dialect.HSQLDialect

We are injecting properties file using PropertyPlaceholderConfigurer bean.
<bean id="placeholderConfig" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:db.properties</value>
</list>
</property>
</bean>


We need to create JPAQuery using EntityManager.

JPAQuery<Person> query = new JPAQuery<>(em); where em is EntityManager instance.
We need to create Query Entity Object using static variable.
QPerson person = QPerson.person;

Using this query entity we can construct the sql query and we can invoke the and return the entities.
Eg: if we want to query person where person firstname is “Kent”.
query.from(person).where(person.firstname.eq(“Kent”)).fetch();
It will return collection of persons which has firstname is “Kent”.
The from call defines the query source and projection, the where part defines the filter and list tells Querydsl to return all matched elements.
public List<Person> findPersonsByFirstnameQueryDSL(final String firstname) {
final JPAQuery<Person> query = new JPAQuery<>(em);
final QPerson person = QPerson.person;

return query.from(person).where(person.firstname.eq(firstname)).fetch();
}
Like we can add multiple where conditions.
If we want to get all record with given firstname and surname then
query.from(person).where(person.firstname.eq("firstname").and(person.surname.eq("surname")))
.fetch();


Sorting query:
Query entity provides a method orderBy, and we can pass on which property we need sorting and in which order.
Eg: If we want the rows in descending order for surname property then
query.from(person).where(person.firstname.eq(firstname)).orderBy(person.surname.desc()).fetch();

Aggregation using Querydsl:
Query entity provides the select method to specify the aggregate methods, for integers fields it has max, min methods.

Eg: query.from(person).select(person.age.max()).fetchFirst();

Aggregation with GroupBy:
We can groupby elements for property using transform method.

Eg:If we want to groupby using firstname and age then
query.from(person).transform(GroupBy.groupBy(person.firstname).as(GroupBy.max(person.age)));

Testing With Querydsl:

We will create a new person object and we will store and we can search using firstname and firstname and surname both.

Main.java:

package org.sample;

import org.sample.dao.PersonDao;
import org.sample.entity.Person;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main {
public static void main(String[] args) {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("Spring-Context.xml");
PersonDao personDao = context.getBean(PersonDao.class);
personDao.save(new Person("Erich", "Gamma"));
Person person = new Person("Kent", "Beck");
personDao.save(person);
personDao.save(new Person("Ralph", "Johnson"));
Person personFromDb = personDao.findPersonsByFirstnameQueryDSL("Kent").get(0);
System.out.println(personFromDb.getFirstname());
System.out.println(personFromDb.getSurname());
context.close();
}
}

Conclusion:
In this Tutorial we explained about what is querydsl and how the entities generated using maven plugin and how we can crate the queries using querydsl.

For queries related to querydsl, you can anytime make comments below. Java development India based experts will answer your questions related to querydsl via comments.

We can download the full implementation of tutorial https://github.com/sravan4rmhyd/querydsl.git
JohnnyMorgan 8 september 2016, 9:08
Vote for this post
Bring it to the Main Page
 

Comments

Leave a Reply

B
I
U
S
Help
Avaible tags
  • <b>...</b>highlighting important text on the page in bold
  • <i>..</i>highlighting important text on the page in italic
  • <u>...</u>allocated with tag <u> text shownas underlined
  • <s>...</s>allocated with tag <s> text shown as strikethrough
  • <sup>...</sup>, <sub>...</sub>text in the tag <sup> appears as a superscript, <sub> - subscript
  • <blockquote>...</blockquote>For  highlight citation, use the tag <blockquote>
  • <code lang="lang">...</code>highlighting the program code (supported by bash, cpp, cs, css, xml, html, java, javascript, lisp, lua, php, perl, python, ruby, sql, scala, text)
  • <a href="http://...">...</a>link, specify the desired Internet address in the href attribute
  • <img src="http://..." alt="text" />specify the full path of image in the src attribute