Using Querydsl with PostgreSQL

Querydsl is a domain model centric ORM that was created out of a need to add typesafety to HQL. As demonstrated in my blog on HQL, to write queries using HQL, at times long string concatenations need to be used. Such strings are not only hard to read, they are prone to runtime errors, avoiding which is the main driver for using ORMs.

Although it was initially primarily targeted at HQL, Querydsl is now also available for JPA, JDO, JDBC, Lucene, Hibernate Search, MongoDB, Collections and RDFBean.

Why Use Querydsl?

The biggest advantage Querydsl offers is typesafety. Rather than relying on strings, it provides you the ability of querying your database using functions. This takes care of syntactic correctness and ensures that you don’t get runtime errors.

With typesafety, comes the added benefit of auto code completion using your favorite IDE. Your ability to reference domain types and properties is greatly enhanced as is your ability to quickly adopt changes in the domain.

With proper object annotation, Querydsl automatically generates domain classes that you can use in your subsequent querying as demonstrated below.

Prominent Features

In order to demonstrate some of the features of Querydsl, let’s continue using the same ‘largecities’ example we have been using for the previous blogs in this series.

PreRequisites

In order to get Querydsl working, you first need to download the libraries from http://www.querydsl.com/static/querydsl. Also, you need to have a persistence.xml file present in your project’s META-INF folder. A sample format for the XML file is given below:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
    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">
    <persistence-unit name="QuerydslExample" transaction-type="RESOURCE_LOCAL">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <class>org.secondquadrant.javabook.querydsl.LargeCities</class>
        <properties>
            <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/postgres" />
            <property name="javax.persistence.jdbc.user" value="postgres" />
            <property name="javax.persistence.jdbc.password" value="" />
        </properties>
    </persistence-unit>
</persistence>

In case you are using an IDE, you will need to ensure that it understands how to translate the annotations. For Eclipse, as an example, besides ensuring that Querydsl libraries are on the build path, you need to specify annotation processing available under project Properties -> Java Compiler -> Annotation Processing. You should enable the project-specific settings and specify an output source directory for generated code along with adding a new key/value pair: defaultOverwrite/true. Under ‘Factory Path’ you should give the path to annotation processing jar file: querydsl-jpa-x.x.x-apt-hibernate-one-jar.jar.

If you don’t use an IDE, these settings will need to be specified in ant and maven settings XML.

Generating the Query Type

With all settings in place, the query type will automatically get generated when you make and save the domain type. For our case, the domain type is:

package org.secondquadrant.javabook.querydsl;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class LargeCities {

@Id 
private int rank;
private String name;
public int getRank() {
return rank;
}
public void setRank(int rank) {
this.rank = rank;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
} 
}

Note how the annotations have been used here.

When you save this domain type, the following code will automatically get generated as the query type by Querydsl:

package org.secondquadrant.javabook.querydsl;

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;

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

    private static final long serialVersionUID = -1795361894L;

    public static final QLargeCities largeCities = new QLargeCities("largeCities");

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

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

    public QLargeCities(String variable) {
        super(LargeCities.class, forVariable(variable));
    }

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

    public QLargeCities(PathMetadata metadata) {
        super(LargeCities.class, metadata);
    }
}

You are now all set to go!

Querying the Database

In order to query the database, you need to create an entitymanager, using which you can create the query factory. This query factory then uses the query type generated above to fetch data from the database. A simple select-all, therefore is programmed as:

        try  {
       
         EntityManager em = getEmf().createEntityManager();
         JPAQueryFactory factory = new JPAQueryFactory(em);

         QLargeCities cities = QLargeCities.largeCities; 
         List<LargeCities> list = factory.selectFrom(cities).fetch(); 
        
         for (LargeCities a : list) {
         System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
         }
        } 

        catch (Exception e) {
            e.printStackTrace();
        }

The output of this program (using the same data set as in the previous sections) is:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 10 Name: Sao Paulo

Specifying Extract Criteria

There are different ways of how you can specify an extract criteria. Simple WHERE clause usage is demonstrated below in a program that fetches the top 5 cities in the table.

        try  {
        
         EntityManager em = getEmf().createEntityManager();
         JPAQueryFactory factory = new JPAQueryFactory(em);

         QLargeCities cities = QLargeCities.largeCities; 
         List<LargeCities> list = factory.selectFrom(cities).where(cities.rank.lt(6)).fetch(); 
        
         for (LargeCities a : list) {
         System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
         }
        } 

        catch (Exception e) {
            e.printStackTrace();
        }

Notice the usage of function ‘lt’ which is used for ‘less than’. Output of this program is:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi

Data Ordering

Specifying order of the retrieved data is similarly handled using functions.

        try  {
        
         EntityManager em = getEmf().createEntityManager();
         JPAQueryFactory factory = new JPAQueryFactory(em);

         QLargeCities cities = QLargeCities.largeCities; 
         List<LargeCities> list = factory.selectFrom(cities).orderBy(cities.rank.desc()).fetch(); 
        
         for (LargeCities a : list) {
         System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
         }
        } 

        catch (Exception e) {
            e.printStackTrace();
        }

Output of this program is:

Rank: 10 Name: Sao Paulo
Rank: 9 Name: Lagos
Rank: 8 Name: Beijing
Rank: 7 Name: Mexico City
Rank: 6 Name: Delhi
Rank: 5 Name: Karachi
Rank: 4 Name: Guangzhou
Rank: 3 Name: Shanghai
Rank: 2 Name: Seoul
Rank: 1 Name: Tokyo

Updating Data

Much like in JPA, for any data changes, you need to start and then commit a transaction. The example below updates the database replacing the 10th entry with an 11th one, i.e Mumbai.

        try  {
        
         EntityManager em = getEmf().createEntityManager();
         JPAQueryFactory factory = new JPAQueryFactory(em);

         QLargeCities cities = QLargeCities.largeCities; 
        
         em.getTransaction().begin();
         factory.update(cities).where(cities.rank.eq(10)).set(cities.rank, 11).set(cities.name, "Mumbai").execute();
         em.getTransaction().commit();
        
         List<LargeCities> list = factory.selectFrom(cities).fetch(); 
        
         for (LargeCities a : list) {
         System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
         }
        } 

        catch (Exception e) {
            e.printStackTrace();
        }

Output of this program is:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos
Rank: 11 Name: Mumbai

As you can see, the 10th entry has now been updated to reflect Mumbai as the 11th largest city.

Deleting Data

Deleting data follows a pattern very similar to the update shown above.

        try  {
        
         EntityManager em = getEmf().createEntityManager();
         JPAQueryFactory factory = new JPAQueryFactory(em);

         QLargeCities cities = QLargeCities.largeCities; 
        
         em.getTransaction().begin();
         factory.delete(cities).where(cities.rank.eq(11)).execute();
         em.getTransaction().commit();
        
         List<LargeCities> list = factory.selectFrom(cities).fetch(); 
        
         for (LargeCities a : list) {
         System.out.println("Rank: " + a.getRank() + " Name: " + a.getName());
         }
        } 

        catch (Exception e) {
            e.printStackTrace();
        }

Output of this code is:

Rank: 1 Name: Tokyo
Rank: 2 Name: Seoul
Rank: 3 Name: Shanghai
Rank: 4 Name: Guangzhou
Rank: 5 Name: Karachi
Rank: 6 Name: Delhi
Rank: 7 Name: Mexico City
Rank: 8 Name: Beijing
Rank: 9 Name: Lagos

As you can see, the last entry that we had updated to ‘Mumbai’ has now been deleted.

Drawbacks of Using Querydsl

While Querydsl can provide you excellent typesafety and the ability for rapid development, it is not a tool that would fit all development scenarios. As an example, because of the way it is designed, Querydsl takes a domain centric approach. If your application is going to be primarily driven by your relational model, Querydsl is likely not the best tool suited for the job.

Building on this point, Querydsl is also not well suited in an environment where an existing application needs to be enhanced (the scenario is more common than you would imagine!). An existing application typically has an existing relational model. If you are forced to use Querydsl in such a scenario, you will need to write your object classes that map exactly to the existing data model, raising the probability of runtime errors besides being a fairly tedious task.