1. Danny van Bruggen
  2. queryprutser

Overview

What is it?

A library that defines a DSL to create SQL or HQL queries. It fills the gap between using string concatenation and using a fancy code generator with introspection and all, instead offering a pretty straight forward API. It's not the prettiest API, but it certainly is the easiest to get started with.

Use it when...

  • you've got complex string manipulation to put a query together, or
  • you've given up on the Hibernate Criteria API, or
  • you're tired of tracking where spaces and comma's go while building a query, or
  • you like a little java between your SQL/HQL, or
  • you like building dynamic queries with a DSL, but the existing DSL's are way overfeatured, or
  • you want a DSL that doesn't prevent you from using all features of HQL/SQL, or
  • you just want a little help, not a complete takeover of your data layer.

Usage

QueryPrutser is basically a class that keeps track of commas and spaces for you in HQL/SQL's query clauses: select, from, where, etc. It does this by basing all the clauses on the SeparatedStringBuilder class. Strings can be added to the SeparatedStringBuilder, and it knows how to prefix, postfix, and infix the resulting string. For example: the select clause has "SELECT " as the prefix, ", " as the infix, and a space as the postfix.

Basic usage of QueryPrutser is to instantiate a query or a statement, then call "add(string)" on its clauses to build it.

Advanced usage is to use one of the many shortcuts to make the code a bit prettier.

HQL

HQL's starting point is the HqlPrutser class.

SQL

Start with QueryBuilder, InsertBuilder, DeleteBuilder, or UpdateBuilder.

From a unit test:

    final QueryBuilder builder = new QueryBuilder();
    builder
            .select("OwnerName")
                .sum("AmountPaid", "Paid")
                .sum("AmountOwedComplete", "Owed")
                .sum("AmountOwedThisMonth", "OwedMonth")
                .sum("PaidForPast", "PaidPast")
                .sum("PaidForPresent", "PaidPresent")
                .sum("AmountPaid - PaidForPast - PaidForPresent", "PaidFuture")
                .sum("[Description]");

    final Subquery subquery1 = builder.from.subSelect("OwnerName", "AmountPaid", "AmountOwedComplete", "AmountOwedThisMonth", "PaidForPast", "[Description]");
    subquery1.select.subSelect("CASE WHEN (AmountPaid - PaidForPast) < ABS(AmountOwedThisMonth) THEN AmountPaid - PaidForPast ELSE ABS(AmountOwedThisMonth) END) AS PaidForPresent");

    final Subquery subSelect2 = subquery1.from.subSelect("OwnerName", "AmountPaid", "AmountOwedTotal - AmountPaid AS AmountOwedComplete", " AmountOwedThisMonth");
    subSelect2.select.subSelect("CASE WHEN (AmountPaid < ABS((AmountOwedTotal - AmountPaid)) + AmountOwedThisMonth) THEN AmountPaid ELSE ABS((AmountOwedTotal - AmountPaid)) + AmountOwedThisMonth END) AS PaidForPast");
    subSelect2.select("Description", "TransactionDate");

    System.out.println(builder.queryText());