2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Umair's PlanetPostgreSQL3 / Using Querydsl with PostgreSQL
Umair Shahid

Using Querydsl with PostgreSQL

June 30, 2016/4 Comments/in Umair's PlanetPostgreSQL /by Umair Shahid

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.

Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
4 replies
  1. Adrian Cox
    Adrian Cox says:
    June 30, 2016 at 9:01 pm

    I think you’re a little harsh on QueryDSL here: you’ve chosen to use QueryDSL JPA rather than QueryDSL SQL, but all the drawbacks you then list are a consequence of your initial decision.

    There’s no requirement to use JPA with QueryDSL, and you don’t mention that QueryDSL can read metadata from an existing database to generate query classes for direct SQL querying. It’s quite possible to introduce QueryDSL into an application with an existing relational model by generating the query classes and writing new queries in QueryDSL.

    Reply
    • Umair Shahid
      Umair Shahid says:
      July 1, 2016 at 7:45 am

      Thank you for your feedback Adrian. Because this is a series of blogs, the intent is to show different ways that Java applications can talk to PostgreSQL along with possible pros and cons of each approach. As you have correctly pointed out, the cons will have workarounds and my intent is to stay as neutral as possible when comparing different technologies.

      Reply
  2. Grey Panther
    Grey Panther says:
    July 1, 2016 at 7:05 am

    Have you looked at jOOQ (http://www.jooq.org/)? It seems more advanced / better maintained.

    Reply
    • Umair Shahid
      Umair Shahid says:
      July 1, 2016 at 7:55 am

      Thank you Grey. I actually wrote about jOOQ in a previous blog: https://blog.2ndquadrant.com/using-java-object-oriented-querying-jooq-with-postgresql/

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

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

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Letter from Japan Evolution of Fault Tolerance in PostgreSQL
Scroll to top
×