HTTPS SSH

QueryDSL examples

Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. - Terry Pratchett

People (rightly) keep asking how to do certain SQL in QueryDSL. So this repo has been created to give examples of using it.

As a starting point you should checkout the examples in the QueryDSL official documentation albeit its a little thin and hence this repository.

If you work out how to do something beyond the basics in QueryDSL, extend this repo so that others can learn from your successes.

There is an Examples class in here that allows you to get the syntax compilable and to capture the SQL output.

The example SQL is given here in Postgres format. Of course one of the purposes of QueryDSL is to allow you to write platform independent SQL that is generated correctly at runtime. But for illustrative purposes Postgres was chosen as an output format.

Contributing to the repo

If you have learn how to do a construct in QueryDSL please consider adding it to this repo.

There is no need to do a full PR process around this. Just commit to master and any mistakes can be fixed up retroactively.

Unless of course you want to make substantive changes to the whole code base and hence a PR might be in order ;) Use your judgment, you are a highly educated professional.

A Simple Insert

    SQLInsertClause insertClause = new SQLInsertClause(connection, PostgreSQLTemplates.DEFAULT, POST);
    insertClause.set(POST.CONTENT, "content")
                .set(POST.TITLE, "title");
    insert into POST (CONTENT, TITLE)
    values (?, ?)

A Simple Insert returning the key of the inserted item

    SQLInsertClause insertClause = new SQLInsertClause(connection, PostgreSQLTemplates.DEFAULT, POST);
    insertClause.set(POST.CONTENT, "content")
                .set(POST.TITLE, "title");
                .executeWithKey(Integer.class);
    insert into POST (CONTENT, TITLE)
    values (?, ?)

A Simple Update

    SQLUpdateClause updateClause = new SQLUpdateClause(connection, PostgreSQLTemplates.DEFAULT, POST);
    updateClause.set(POST.CONTENT, "updated content")
                .set(POST.TITLE, "updated title")
                .where(POST.ID.eq(1l));
    update POST
    set CONTENT = ?, TITLE = ?
    where POST.ID = ?

A Simple Update (Increment) against a value in the database

    SQLUpdateClause updateClause = new SQLUpdateClause(connection, PostgreSQLTemplates.DEFAULT, POST);
    updateClause
               .set(POST.ID, POST.ID.add(5))
               .where(POST.ID.eq(1l));
    update POST
    set ID = ID + ?
    where POST.ID = ?

Joining tables

    query()
        .select(PRODUCT_ITEM_SKU.SKU_BARCODE, PRODUCT_ITEM.PRICE, PRODUCT.NAME)
        .from(PRODUCT)
        .join(PRODUCT_ITEM)
        .on(PRODUCT_ITEM.PRODUCT_ID.eq(PRODUCT.ID))
        .join(PRODUCT_ITEM_SKU)
        .on(PRODUCT_ITEM_SKU.ID.eq(PRODUCT_ITEM.SKU_ID))
        .orderBy(PRODUCT_ITEM.PRICE.asc());
    select PRODUCT_ITEM_SKU.SKU_BARCODE, PRODUCT_ITEM.PRICE, PRODUCT.NAME
    from PRODUCT PRODUCT
    join PRODUCT_ITEM PRODUCT_ITEM
    on PRODUCT_ITEM.PRODUCT_ID = PRODUCT.ID
    join PRODUCT_ITEM_SKU PRODUCT_ITEM_SKU
    on PRODUCT_ITEM_SKU.ID = PRODUCT_ITEM.SKU_ID
    order by PRODUCT_ITEM.PRICE asc

Left join on tables

    query()
        .select(PRODUCT_ITEM_SKU.SKU_BARCODE, PRODUCT_ITEM.PRICE, PRODUCT.NAME)
        .from(PRODUCT)
        .leftJoin(PRODUCT_ITEM)
        .on(PRODUCT_ITEM.PRODUCT_ID.eq(PRODUCT.ID))
        .orderBy(PRODUCT_ITEM.PRICE.asc())
    select PRODUCT_ITEM_SKU.SKU_BARCODE, PRODUCT_ITEM.PRICE, PRODUCT.NAME
    from PRODUCT PRODUCT
    left join PRODUCT_ITEM PRODUCT_ITEM
    on PRODUCT_ITEM.PRODUCT_ID = PRODUCT.ID
    order by PRODUCT_ITEM.PRICE asc

Sub-queries via SQlExpression.select

    query()
        .select(PRODUCT_ITEM_SKU.SKU_BARCODE, PRODUCT_ITEM.PRICE, PRODUCT.NAME)
        .from(PRODUCT)
        .join(PRODUCT_ITEM)
        .on(PRODUCT_ITEM.PRODUCT_ID.eq(PRODUCT.ID))
        .join(PRODUCT_ITEM_SKU)
        .on(PRODUCT_ITEM_SKU.ID.eq(PRODUCT_ITEM.SKU_ID))
        .where(
                PRODUCT.NAME.like("drone%")
                        .and(PRODUCT.ID.in(
                                SQLExpressions.select(PRODUCT.ID)
                                        .from(PRODUCT)
                                        .join(PRODUCT_AVAILIBILITY)
                                        .on(PRODUCT_AVAILIBILITY.PRODUCT_ID.eq(PRODUCT.ID))
                                        .where(PRODUCT_AVAILIBILITY.AVAILABLE.eq(true))
                        )))
        .orderBy(PRODUCT_ITEM.PRICE.asc());
    select PRODUCT_ITEM_SKU.SKU_BARCODE, PRODUCT_ITEM.PRICE, PRODUCT.NAME
    from PRODUCT PRODUCT
    join PRODUCT_ITEM PRODUCT_ITEM
    on PRODUCT_ITEM.PRODUCT_ID = PRODUCT.ID
    join PRODUCT_ITEM_SKU PRODUCT_ITEM_SKU
    on PRODUCT_ITEM_SKU.ID = PRODUCT_ITEM.SKU_ID
    where PRODUCT.NAME like ? and PRODUCT.ID in (select PRODUCT.ID
    from PRODUCT PRODUCT
    join PRODUCT_AVAILABILITY PRODUCT_AVAILABILITY
    on PRODUCT_AVAILABILITY.PRODUCT_ID = PRODUCT.ID
    where PRODUCT_AVAILABILITY.AVAILABLE = ?)
    order by PRODUCT_ITEM.PRICE asc

Aliasing tables

    // note that the Q entities are instantiated with aliases in this case
    QEmployee WORKER = new QEmployee("worker");
    QEmployee MANAGER = new QEmployee("manager");

    query()
        .select(WORKER.NAME.as("worker_name"), MANAGER.NAME.as("manager_name"))
        .from(WORKER)
        .join(MANAGER)
        .on(MANAGER.ID.eq(WORKER.MANAGER_ID));
    select worker.NAME as worker_name, manager.NAME as manager_name
    from EMPLOYEE worker
    join EMPLOYEE manager
    on manager.ID = worker.MANAGER_ID

Select for update on a table

Select for update locks the table so you can make a subsequent modification such as an update.

See Postgres documentation for more details

    query()
        .select(PRODUCT.all())
        .forUpdate()
        .from(PRODUCT)
        .orderBy(PRODUCT.NAME.asc())
    select PRODUCT.ID, PRODUCT.NAME, PRODUCT.LAUNCH_DATE
    from PRODUCT PRODUCT
    order by PRODUCT.NAME asc
    for update

Current Date and Time as expressions

    query()
            .select(PRODUCT.LAUNCH_DATE, Expressions.currentTime(), Expressions.currentTimestamp())
            .from(PRODUCT)
            .where(PRODUCT.LAUNCH_DATE.before(Expressions.currentDate()))
    select PRODUCT.LAUNCH_DATE, current_time, current_timestamp
    from PRODUCT PRODUCT
    where PRODUCT.LAUNCH_DATE < current_date

Maths expression - max

There are plenty of other Maths expressions such as min(),max(),avg(),round() and so on...

    query()
            .select(PRODUCT_ITEM.PRICE.max())
            .from(PRODUCT_ITEM)
    select max(PRODUCT_ITEM.PRICE)
    from PRODUCT_ITEM PRODUCT_ITEM

Contructor Projections

QueryDSL has an ability to map raw Tuples (analogous to a ResultSet in JDBC) to specific Java classes.

For example assume you have a model class like the following

    public class ProductDTO {
        private final Long id;
        private final String name;
        private final Date launchDate;

        public ProductDTO(Long id, String name, Date launchDate) {
            this.id = id;
            this.name = name;
            this.launchDate = launchDate;
        }

        public Long getId() {
            return id;
        }

        public Date getLaunchDate() {
            return launchDate;
        }

        public String getName() {
            return name;
        }
    }

You can then select from the database and have QueryDSL "map" via the class constructor to the columns that are returned in the query. In this case it will invoke the constructor of ProductDTO that takes 3 parameters.

    query()
            .select(Projections.constructor(
                    ProductDTO.class, PRODUCT.ID, PRODUCT.NAME, PRODUCT.LAUNCH_DATE)
            )
            .from(PRODUCT)
    select PRODUCT.ID, PRODUCT.NAME, PRODUCT.LAUNCH_DATE
    from PRODUCT PRODUCT

Bean projection

Should you be using mutable JavaBeans, then you can have QueryDSL project results onto that bean.

    public class ProductBean {
        private Long id;
        private String name;
        private Date launchDate;

        public ProductBean(Long id, String name, Date launchDate) {
            this.id = id;
            this.name = name;
            this.launchDate = launchDate;
        }

        public Long getId() {
            return id;
        }

        public void setId(Long id) {
            this.id = id;
        }

        public Date getLaunchDate() {
            return launchDate;
        }

        public void setLaunchDate(Date launchDate) {
            this.launchDate = launchDate;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }
    }

You can then declare your QEntity as being a relational path that produces that bean type. This is a clue to system as to what Java types this entity can be bound to.

Note also that this examples is using Pocketknife QueryDSL EnhancedRelationalPathBase. The RelationPathBase from native QueryDSL does the same thing.

     public class QProduct extends EnhancedRelationalPathBase<ProductBean> {

         public NumberPath<Long> ID = createLongCol("ID").asPrimaryKey().build();
         public StringPath NAME = createStringCol("NAME").build();
         public DatePath<Date> LAUNCH_DATE = createDateCol("LAUNCH_DATE", Date.class).build();

         public QProduct() {
             super(ProductBean.class, "PRODUCT");
         }
     }

When this runs it will instantiate a new instance of the "type" backing the PRODUCT entity which as seen above is class ProductBean.

The reason this approach is handy is that it means you get results in the Java shape you want.

    List<ProductDTO> result = query()
            .select(Projections.constructor(
                    ProductDTO.class, PRODUCT.ID, PRODUCT.NAME, PRODUCT.LAUNCH_DATE)
            )
            .from(PRODUCT)
            .fetch();
    select PRODUCT.ID, PRODUCT.NAME, PRODUCT.LAUNCH_DATE
    from PRODUCT PRODUCT

There is another variant that sets bean fields directly (via reflection) rather than requiring the class to contain setters.

    query()
            .select(Projections.fields(PRODUCT,
                    PRODUCT.ID, PRODUCT.NAME, PRODUCT.LAUNCH_DATE)
            )
            .from(PRODUCT)

Group by transformation

This allows you to perform an 'in memory group by' of Java objects. That's the key to remember here, the group by happens in memory not in the database. But its useful to be able to load a hierarchical set of data into one instance

            Map<Long, PostDTO> post = query()
                    .from(POST)
                    .join(COMMENT).on(POST.ID.eq(COMMENT.POST_ID))
                    .transform(GroupBy.groupBy(POST.ID).as(
                            Projections.bean(PostDTO.class, POST.ID, POST.TITLE, POST.CONTENT,
                                    GroupBy.set(COMMENT).as("comments"))
                            )
                    );

`Another way might be to group the POST ids to the list of comments for that POST. You can use GroupBy.list() for this

            Map<Long, List<CommentDTO>> result = query()
                    .from(POST)
                    .join(COMMENT).on(POST.ID.eq(COMMENT.POST_ID))
                    .transform(GroupBy.groupBy(POST.ID).as(
                            GroupBy.list(COMMENT))
                    );
    select POST.ID, POST.TITLE, POST.CONTENT
    from POST POST
    join COMMENT COMMENT
    on POST.ID = COMMENT.POST_ID

Add your example here