Create the Queries Using Query DSL

  • by

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, mongo DB, SQL, lucene and also for java collections.

 

The benefits of using a fluent API in comparison to simple strings are:

  1. code completion in IDE
  2. almost none syntactically invalid queries allowed
  3. domain types and properties can be referenced safely
  4. 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:

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

Company .java

package org.sample.entity;




import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;




@Entity

public class Company {

       @Id

       @GeneratedValue(strategy = GenerationType.IDENTITY)

       private Long companyId;

       private String name;

       private String location;

       private String state;

       private String country;

       private boolean isMNCCompany;

       private String cmmiLevel;




       public Company()

       {




       }

       public Company(String name, String location, String state, String country, boolean isMNCCompany,

                        String cmmiLevel) {

            super();

            this.name = name;

            this.location = location;

            this.state = state;

            this.country = country;

            this.isMNCCompany = isMNCCompany;

            this.cmmiLevel = cmmiLevel;

       }

       // getters and setters

}

 

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

 public static final QCompany company = new QCompany("company");




Generated QCompany.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;







/**

 * QCompany is a Querydsl query type for Company

 */

@Generated("com.querydsl.codegen.EntitySerializer")

public class QCompany extends EntityPathBase<Company> {




    private static final long serialVersionUID = -323435892L;




    public static final QCompany company = new QCompany("company");




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




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




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




    public final BooleanPath isMNCCompany = createBoolean("isMNCCompany");




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




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




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




    public QCompany(String variable) {

        super(Company.class, forVariable(variable));

    }




    public QCompany(Path<? extends Company> path) {

        super(path.getType(), path.getMetadata());

    }




    public QCompany(PathMetadata metadata) {

        super(Company.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<Company> query = new JPAQuery<>(em); where em is EntityManager instance.

We need to create Query Entity Object using static variable.

QCompany company= QCompany.company;




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 company where company location is “Hyderabad”.




query.from(company).where(company.location.eq(location)).fetch();




It will return collection of companies which has location is “Hyderabad”.




The from call defines the query source and projection, the where part defines the filter and list tells Querydsl to return all matched elements.

 

@Override

    public List<Company> getCompaniesByLocation(String location) {

        final JPAQuery<Company> query = new JPAQuery<>(em);

        final QCompany company = QCompany.company;

        return query.from(company).where(company.location.eq(location)).fetch();

}




Like we can add multiple where conditions.




If we want to get all record with given location and state then

query.from(company).where(company.location.eq(location).and(company.state.eq(state))).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 location property then

query.from(company).where(company.location.eq(location)).orderBy(company.location.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(company).select(company.location.max()).fetchFirst();

 

Aggregation with GroupBy:

We can groupby elements for property using transform method.

 

Eg:If we want to groupby using location and state then

query.from(company).transform(GroupBy.groupBy(company.location).as(GroupBy.max(company.state)));

 

 

Testing With Querydsl:

 

We will create a new company object and we will store and we can search using any/all fields in company.

 

CompanyTest.java:




package org.sample;




import static org.junit.Assert.assertEquals;




import java.util.List;




import org.junit.AfterClass;

import org.junit.BeforeClass;

import org.junit.Test;

import org.sample.dao.CompanyDao;

import org.sample.entity.Company;

import org.springframework.context.support.ClassPathXmlApplicationContext;




public class CompanyTest {

            private static ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("Spring-Context.xml");

            private static CompanyDao companydao =null;

            @BeforeClass

            public static void prepareData()

            {

                        companydao = context.getBean(CompanyDao.class);

                        companydao.saveCompany(new Company("Google", "Hyderabad","Telangana","India",true,"LEVEL5"));

                        Company microsoft = new Company("IBM", "Chennai","Tamilnadu","India",true,"LEVEL5");

                        companydao.saveCompany(new Company("ABC", "Banglore","Karnataka","India",false,"LEVEL5"));

                        companydao.saveCompany(microsoft);

            }

            @Test

            public void testgetMNCCompanies()

            {

                        List<Company> companyFromDb = companydao.getMNCCompanies();

                        assertEquals(2, companyFromDb.size());

            }




            @Test

            public void testgetCompaniesByLocation()

            {

                        Company companyFromDb = companydao.getCompaniesByLocation("Hyderabad").get(0);

                        assertEquals("Telangana", companyFromDb.getState());

                        assertEquals("Google", companyFromDb.getName());

            }




            @Test

            public void testgetCompanyCountBylocation()

            {

                        assertEquals(1, companydao.getCompanyCountBylocation("Hyderabad"));

                        assertEquals(1, companydao.getCompanyCountBylocation("Chennai"));

                        assertEquals(1, companydao.getCompanyCountBylocation("Banglore"));

            }

            @AfterClass

            public static void close()

            {

                        context.close();

            }

}

 

 

Conclusion:

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

 

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

 

We can download the full implementation of tutorial https://github.com/sravan4rmhyd/querydsl.git

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *