2.3. Querying SQL

This chapter describes the query type generation and querying functionality of the SQL module.

2.3.1. Maven integration

Add the following dependencies to your Maven project:

<dependency>
  <groupId>com.querydsl</groupId>
  <artifactId>querydsl-sql</artifactId>
  <version>${querydsl.version}</version>
</dependency>

<dependency>
  <groupId>com.querydsl</groupId>
  <artifactId>querydsl-sql-codegen</artifactId>
  <version>${querydsl.version}</version>
  <scope>provided</scope>
</dependency>

<dependency>
  <groupId>org.slf4j</groupId>
  <artifactId>slf4j-log4j12</artifactId>
  <version>1.6.1</version>
</dependency>

The querydsl-sql-codegen dependency can be skipped, if code generation happens via Maven.

2.3.2. Code generation via Maven

This functionality should be primarily used via the Maven plugin. Here is an example:

<project>
  <build>
    <plugins>
      ...
      <plugin>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-maven-plugin</artifactId>
        <version>${querydsl.version}</version>
        <executions>
          <execution>
            <goals>
              <goal>export</goal>
            </goals>
          </execution>
        </executions>
        <configuration>
          <jdbcDriver>org.apache.derby.jdbc.EmbeddedDriver</jdbcDriver>
          <jdbcUrl>jdbc:derby:target/demoDB;create=true</jdbcUrl>
          <packageName>com.myproject.domain</packageName>
          <targetFolder>${project.basedir}/target/generated-sources/java</targetFolder>
        </configuration>
        <dependencies>
          <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derby</artifactId>
            <version>${derby.version}</version>
          </dependency>
        </dependencies>
      </plugin>
      ...
    </plugins>
  </build>
</project>

Use the goal test-export to treat the target folder as a test source folder for use with test code.

Table 2.1. Parameters

NameDescription
jdbcDriverclass name of the JDBC driver
jdbcUrlJDBC url
jdbcUserJDBC user
jdbcPasswordJDBC password
namePrefixname prefix for generated query classes (default: Q)
nameSuffixname suffix for generated query classes (default: )
beanPrefixname prefix for generated bean classes
beanSuffixname suffix for generated bean classes
packageNamepackage name where source files should be generated
beanPackageNamepackage name where bean files should be generated, (default: packageName)
beanInterfacesarray of interface classnames to add to the bean classes (default: empty)
beanAddToStringset to true to create a default toString() implementation (default: false)
beanAddFullConstructorset to true to create a full constructor in addition to public empty (default: false)
beanPrintSupertypeset to true to print the supertype as well (default: false)
schemaPatterna schema name pattern in LIKE pattern form; must match the schema name as it is stored in the database, multiple can be separated by comma (default: null)
tableNamePatterna table name pattern in LIKE pattern form; must match the table name as it is stored in the database, multiple can be separated by comma (default: null)
targetFoldertarget folder where sources should be generated
beansTargetFoldertarget folder where bean sources should be generated, defaults to the same value as targetFolder
namingStrategyClassclass name of the NamingStrategy class (default: DefaultNamingStrategy)
beanSerializerClassclass name of the BeanSerializer class (default: BeanSerializer)
serializerClassclass name of the Serializer class (default: MetaDataSerializer)
exportBeansset to true to generate beans as well, see section 2.14.13 (default: false)
innerClassesForKeysset to true to generate inner classes for keys (default: false)
validationAnnotationsset to true to enable serialization of validation annotations (default: false)
columnAnnotationsexport column annotations (default: false)
createScalaSourceswhether to export Scala sources instead of Java sources, (default: false)
schemaToPackageappend schema name to package (default: false)
lowerCaselower case transformation of names (default: false)
exportTablesexport tables (default: true)
exportViewsexport views (default: true)
exportPrimaryKeysexport primary keys (default: true)
tableTypesToExportComma-separated list of table types to export (allowable values will depend on JDBC driver). Allows for arbitrary set of types to be exported, e.g.: "TABLE, MATERIALIZED VIEW". The exportTables and exportViews parameters will be ignored if this parameter is set. (default: none)
exportForeignKeysexport foreign keys (default: true)
customTypesCustom user types (default: none)
typeMappingsMappings of table.column to Java type (default: none)
numericMappingsMappings of size/digits to Java type (default: none)
importsArray of java imports added to generated query classes: com.bar for package (without .* notation), com.bar.Foo for class (default: empty)

Custom types can be used to register additional Type implementations:

<customTypes>
  <customType>com.querydsl.sql.types.InputStreamType</customType>
</customTypes>

Type mappings can be used to register table.column specific java types:

<typeMappings>
  <typeMapping>
    <table>IMAGE</table>
    <column>CONTENTS</column>
    <type>java.io.InputStream</type>
  </typeMapping>
</typeMappings>

The defaults for the numeric mappings are

Table 2.2. Numeric mappings

Total digitsDecimal digitsType
> 180BigInteger
> 90Long
> 40Integer
> 20Short
> 00Byte
> 0> 0BigDecimal

They can be customized for specific total/decimal digits combinations like this:

<numericMappings>
  <numericMapping>
    <total>1</total>
    <decimal>0</decimal>
    <javaType>java.lang.Byte</javaType>
  </numericMapping>
</numericMappings>

Imports can be used to add cross-schema foreign keys support.

Schemas, tables and columns can also be renamed using the plugin. Here are some examples:

Renaming a schema:

<renameMappings>
  <renameMapping>
    <fromSchema>PROD</fromSchema>
    <toSchema>TEST</toSchema>
  </renameMapping>
</renameMappings>

Renaming a table:

<renameMappings>
  <renameMapping>
    <fromSchema>PROD</fromSchema>
    <fromTable>CUSTOMER</fromTable>
    <toTable>CSTMR</toTable>
  </renameMapping>
</renameMappings>

Renaming a column:

<renameMappings>
  <renameMapping>
    <fromSchema>PROD</fromSchema>
    <fromTable>CUSTOMER</fromTable>
    <fromColumn>ID</fromColumn>
    <toColumn>IDX</toTable>
  </renameMapping>
</renameMappings>

Note: fromSchema can be omitted when renaming tables and columns.

Compared to APT based code generation certain functionality is not available such as QueryDelegate annotation handling.

2.3.3. Code generation via ANT

The ANT task com.querydsl.sql.codegen.ant.AntMetaDataExporter of the querydsl-sql module provides the same functionality as an ANT task. The configuration parameters of the task are the same as for the Maven plugin, except for the composite types.

The composite types are used without the wrapper element like in this example.

<project name="testproject" default="codegen" basedir=".">

  <taskdef name="codegen" classname="com.querydsl.sql.codegen.ant.AntMetaDataExporter"/>

  <target name="codegen">
    <codegen
      jdbcDriver="org.h2.Driver"
      jdbcUser="sa"
      jdbcUrl="jdbc:h2:/dbs/db1"
      packageName="test"
      targetFolder="target/generated-sources/java">
      <renameMapping fromSchema="PUBLIC" toSchema="PUB"/>
    </codegen>
  </target>
</project>

2.3.4. Creating the query types

To get started export your schema into Querydsl query types like this:

java.sql.Connection conn = ...;
MetaDataExporter exporter = new MetaDataExporter();
exporter.setPackageName("com.myproject.mydomain");
exporter.setTargetFolder(new File("target/generated-sources/java"));
exporter.export(conn.getMetaData());

This declares that the database schema is to be mirrored into the com.myproject.domain package in the target/generated-sources/java folder.

The generated types have the table name transformed to mixed case as the class name and a similar mixed case transformation applied to the columns which are available as property paths in the query type.

In addition to this primary key and foreign key constraints are provided as fields which can be used for compact join declarations.

2.3.5. Configuration

The configuration is done via the com.querydsl.sql.Configuration class which takes the Querydsl SQL dialect as an argument. For H2 you would create it like this

SQLTemplates templates = new H2Templates();
Configuration configuration = new Configuration(templates);

Querydsl uses SQL dialects to customize the SQL serialization needed for different relational databases. The available dialects are:

  • CUBRIDTemplates (tested with CUBRID 8.4)

  • DB2Templates (tested with DB2 10.1.2)

  • DerbyTemplates (tested with Derby 10.8.2.2)

  • FirebirdTemplates (tested with Firebird 2.5)

  • HSQLDBTemplates (tested with HSQLDB 2.2.4)

  • H2Templates (tested with H2 1.3.164)

  • MySQLTemplates (tested with MySQL 5.5)

  • OracleTemplates (test with Oracle 10 and 11)

  • PostgreSQLTemplates (tested with PostgreSQL 9.1)

  • SQLiteTemplates (tested with xerial JDBC 3.7.2)

  • SQLServerTemplates (tested with SQL Server)

  • SQLServer2005Templates (for SQL Server 2005)

  • SQLServer2008Templates (for SQL Server 2008)

  • SQLServer2012Templates (for SQL Server 2012 and later)

  • TeradataTemplates (tested with Teradata 14)

For customized SQLTemplates instances you can use the builder pattern like this

  H2Templates.builder()
     .printSchema() // to include the schema in the output
     .quote()       // to quote names
     .newLineToSingleSpace() // to replace new lines with single space in the output
     .escape(ch)    // to set the escape char
     .build();      // to get the customized SQLTemplates instance

The methods of the Configuration class can be used to enable direct serialization of literals via setUseLiterals(true), override schema and tables and register custom types. For full details look at the javadocs of Configuration.

2.3.6. Querying

For the following examples we will be using the SQLQueryFactory class for query creation. Using it results in more concise code compared to constructor based query creation.

SQLQueryFactory queryFactory = new SQLQueryFactory(configuration, dataSource);

Querying with Querydsl SQL is as simple as this:

QCustomer customer = new QCustomer("c");

List<String> lastNames = queryFactory.select(customer.lastName).from(customer)
    .where(customer.firstName.eq("Bob"))
    .fetch();

which is transformed into the following sql query, assuming that the related table name is customer and the columns first_name and last_name:

SELECT c.last_name
FROM customer c
WHERE c.first_name = 'Bob'

2.3.7. General usage

Use the the cascading methods of the SQLQuery class like this

select: Set the projection of the query. (Not necessary if created via query factory)

from: Add the query sources here.

innerJoin, join, leftJoin, rightJoin, fullJoin, on: Add join elements using these constructs. For the join methods the first argument is the join source and the second the target (alias).

where: Add query filters, either in varargs form separated via commas or cascaded via the and-operator.

groupBy: Add group by arguments in varargs form.

having: Add having filter of the "group by" grouping as an varags array of Predicate expressions.

orderBy: Add ordering of the result as an varargs array of order expressions. Use asc() and desc() on numeric, string and other comparable expression to access the OrderSpecifier instances.

limit, offset, restrict: Set the paging of the result. Limit for max results, offset for skipping rows and restrict for defining both in one call.

2.3.8. Joins

Joins are constructed using the following syntax:

QCustomer customer = QCustomer.customer;
QCompany company = QCompany.company;
queryFactory.select(customer.firstName, customer.lastName, company.name)
    .from(customer)
    .innerJoin(customer.company, company)
    .fetch();

and for a left join:

queryFactory.select(customer.firstName, customer.lastName, company.name)
    .from(customer)
    .leftJoin(customer.company, company)
    .fetch();

Alternatively the join condition can also be written out:

queryFactory.select(customer.firstName, customer.lastName, company.name)
    .from(customer)
    .leftJoin(company).on(customer.company.eq(company.id))
    .fetch();

2.3.9. Ordering

The syntax for declaring ordering is

queryFactory.select(customer.firstName, customer.lastName)
    .from(customer)
    .orderBy(customer.lastName.asc(), customer.firstName.asc())
    .fetch();

which is equivalent to the following native SQL

SELECT c.first_name, c.last_name
FROM customer c
ORDER BY c.last_name ASC, c.first_name ASC

2.3.10. Grouping

Grouping can be done in the following form

queryFactory.select(customer.lastName)
    .from(customer)
    .groupBy(customer.lastName)
    .fetch();

which is equivalent to the following native SQL

SELECT c.last_name
FROM customer c
GROUP BY c.last_name

2.3.11. Using Subqueries

To create a subquery you can use one of the factory methods of SQLExpressions and add the query parameters via from, where etc.

QCustomer customer = QCustomer.customer;
QCustomer customer2 = new QCustomer("customer2");
queryFactory.select(customer.all())
    .from(customer)
    .where(customer.status.eq(
        SQLExpressions.select(customer2.status.max()).from(customer2)))
    .fetch()

Another example

QStatus status = QStatus.status;
queryFactory.select(customer.all())
    .from(customer)
    .where(customer.status.in(
        SQLExpressions.select(status.id).from(status).where(status.level.lt(3))))
    .fetch();

2.3.12. Selecting literals

To select literals you need to create constant instances for them like this:

queryFactory.select(Expressions.constant(1),
                    Expressions.constant("abc"));

The class com.querydsl.core.types.dsl.Expressions offers also other useful static methods for projections, operation and template creation.

2.3.13. Query extension support

Custom query extensions to support engine specific syntax can be created by subclassing AbstractSQLQuery and adding flagging methods like in the given MySQLQuery example:

public class MySQLQuery<T> extends AbstractSQLQuery<T, MySQLQuery<T>> {

    public MySQLQuery(Connection conn) {
        this(conn, new MySQLTemplates(), new DefaultQueryMetadata());
    }

    public MySQLQuery(Connection conn, SQLTemplates templates) {
        this(conn, templates, new DefaultQueryMetadata());
    }

    protected MySQLQuery(Connection conn, SQLTemplates templates, QueryMetadata metadata) {
        super(conn, new Configuration(templates), metadata);
    }

    public MySQLQuery bigResult() {
        return addFlag(Position.AFTER_SELECT, "SQL_BIG_RESULT ");
    }

    public MySQLQuery bufferResult() {
        return addFlag(Position.AFTER_SELECT, "SQL_BUFFER_RESULT ");
    }


    // ...
}

The flags are custom SQL snippets that can be inserted at specific points in the serialization. The supported positions are the enums of the com.querydsl.core.QueryFlag.Position enum class.

2.3.14. Window functions

Window functions are supported in Querydsl via the methods in the SQLExpressions class.

Usage example:

queryFactory.select(SQLExpressions.rowNumber()
        .over()
        .partitionBy(employee.name)
        .orderBy(employee.id))
     .from(employee)

2.3.15. Common table expressions

Common table expressions are supported in Querydsl SQL via two syntax variants

QEmployee employee = QEmployee.employee;
queryFactory.with(employee, SQLExpressions.select(employee.all)
                                          .from(employee)
                                          .where(employee.name.startsWith("A")))
            .from(...)

And using a column listing

QEmployee employee = QEmployee.employee;
queryFactory.with(employee, employee.id, employee.name)
            .as(SQLExpressions.select(employee.id, employee.name)
                              .from(employee)
                              .where(employee.name.startsWith("A")))
            .from(...)

If the columns of the common table expression are a subset of an existing table or view it is advisable to use a generated path type for it, e.g. QEmployee in this case, but if the columns don't fit any existing table PathBuilder can be used instead.

Below is an example for such a case

QEmployee employee = QEmployee.employee;
QDepartment department = QDepartment.department;
PathBuilder<Tuple> emp = new PathBuilder<Tuple>(Tuple.class, "emp");
queryFactory.with(emp, SQLExpressions.select(employee.id, employee.name, employee.departmentId,
                                          department.name.as("departmentName"))
                                      .from(employee)
                                      .innerJoin(department).on(employee.departmentId.eq(department.id))))
            .from(...)

2.3.16. Other SQL expressions

Other SQL expressions are also available from the SQLExpressions class as static methods.

2.3.17. Using Data manipulation commands

2.3.17.1. Insert

With columns

QSurvey survey = QSurvey.survey;

queryFactory.insert(survey)
    .columns(survey.id, survey.name)
    .values(3, "Hello").execute();

Without columns

queryFactory.insert(survey)
    .values(4, "Hello").execute();

With subquery

queryFactory.insert(survey)
    .columns(survey.id, survey.name)
    .select(SQLExpressions.select(survey2.id.add(1), survey2.name).from(survey2))
    .execute();

With subquery, without columns

queryFactory.insert(survey)
    .select(SQLExpressions.select(survey2.id.add(10), survey2.name).from(survey2))
    .execute();

As an alternative to the columns/values usage, Querydsl provides also a set method which can be used like this

QSurvey survey = QSurvey.survey;

queryFactory.insert(survey)
    .set(survey.id, 3)
    .set(survey.name, "Hello").execute();

which is equivalent to the first example. Usage of the set method always expands internally to columns and values.

Beware that

columns(...).select(...)

maps the result set of the given query to be inserted whereas

To get the created keys out instead of modified rows count use one of the executeWithKey/s method.

set(...)

maps single columns and nulls are used for empty subquery results.

To populate a clause instance based on the contents of a bean you can use

queryFactory.insert(survey)
    .populate(surveyBean).execute();

This will exclude null bindings, if you need also null bindings use

queryFactory.insert(survey)
    .populate(surveyBean, DefaultMapper.WITH_NULL_BINDINGS).execute();

2.3.17.2. Update

With where

QSurvey survey = QSurvey.survey;

queryFactory.update(survey)
    .where(survey.name.eq("XXX"))
    .set(survey.name, "S")
    .execute();

Without where

queryFactory.update(survey)
    .set(survey.name, "S")
    .execute();

Using bean population

queryFactory.update(survey)
    .populate(surveyBean)
    .execute();

2.3.17.3. Delete

With where

QSurvey survey = QSurvey.survey;

queryFactory.delete(survey)
    .where(survey.name.eq("XXX"))
    .execute();

Without where

queryFactory.delete(survey)
    .execute()

2.3.18. Batch support in DML clauses

Querydsl SQL supports usage of JDBC batch updates through the DML APIs. If you have consecutive DML calls with a similar structure, you can bundle the the calls via addBatch() usage into one DMLClause. See the examples how it works for UPDATE, DELETE and INSERT.

Update:

QSurvey survey = QSurvey.survey;

queryFactory.insert(survey).values(2, "A").execute();
queryFactory.insert(survey).values(3, "B").execute();

SQLUpdateClause update = queryFactory.update(survey);
update.set(survey.name, "AA").where(survey.name.eq("A")).addBatch();
update.set(survey.name, "BB").where(survey.name.eq("B")).addBatch();

Delete:

queryFactory.insert(survey).values(2, "A").execute();
queryFactory.insert(survey).values(3, "B").execute();

SQLDeleteClause delete = queryFactory.delete(survey);
delete.where(survey.name.eq("A")).addBatch();
delete.where(survey.name.eq("B")).addBatch();
assertEquals(2, delete.execute());

Insert:

SQLInsertClause insert = queryFactory.insert(survey);
insert.set(survey.id, 5).set(survey.name, "5").addBatch();
insert.set(survey.id, 6).set(survey.name, "6").addBatch();
assertEquals(2, insert.execute());

2.3.19. Bean class generation

To create JavaBean DTO types for the tables of your schema use the MetaDataExporter like this:

java.sql.Connection conn = ...;
MetaDataExporter exporter = new MetaDataExporter();
exporter.setPackageName("com.myproject.mydomain");
exporter.setTargetFolder(new File("src/main/java"));
exporter.setBeanSerializer(new BeanSerializer());
exporter.export(conn.getMetaData());

Now you can use the bean types as arguments to the populate method in DML clauses and you can project directly to bean types in queries. Here is a simple example in JUnit form:

QEmployee e = new QEmployee("e");

// Insert
Employee employee = new Employee();
employee.setFirstname("John");
Integer id = queryFactory.insert(e).populate(employee).executeWithKey(e.id);
employee.setId(id);

// Update
employee.setLastname("Smith");
assertEquals(1l, queryFactory.update(e).populate(employee).where(e.id.eq(employee.getId())).execute());

// Query
Employee smith = queryFactory.selectFrom(e).where(e.lastname.eq("Smith")).fetchOne();
assertEquals("John", smith.getFirstname());

// Delete
assertEquals(1l, queryFactory.delete(e).where(e.id.eq(employee.getId())).execute());

2.3.20. Extracting the SQL query and bindings

The SQL query and bindings can be extracted via the getSQL method:

SQLBindings bindings = query.getSQL();
System.out.println(bindings.getSQL());

If you need also all literals in the SQL string you can enable literal serialization on the query or configuration level via setUseLiterals(true).

2.3.21. Custom types

Querydsl SQL provides the possibility to declare custom type mappings for ResultSet/Statement interaction. The custom type mappings can be declared in com.querydsl.sql.Configuration instances, which are supplied as constructor arguments to the actual queries:

Configuration configuration = new Configuration(new H2Templates());
// overrides the mapping for Types.DATE
configuration.register(new UtilDateType());

And for a table column

Configuration configuration = new Configuration(new H2Templates());
// declares a mapping for the gender column in the person table
configuration.register("person", "gender",  new EnumByNameType<Gender>(Gender.class));

To customize a numeric mapping you can use the registerNumeric method like this

configuration.registerNumeric(5,2,Float.class);

This will map the Float type to the NUMERIC(5,2) type.

2.3.22. Listening to queries and clauses

SQLListener is a listener interface that can be used to listen to queries and DML clause. SQLListener instances can be registered either on the configuration and on the query/clause level via the addListener method.

Use cases for listeners are data synchronization, logging, caching and validation.

2.3.23. Spring integration

Querydsl SQL integrates with Spring through the querydsl-sql-spring module:

<dependency>
  <groupId>com.querydsl</groupId>
  <artifactId>querydsl-sql-spring</artifactId>
  <version>${querydsl.version}</version>
</dependency>

It provides Spring exception translation and a Spring connection provider for usage of Querydsl SQL with Spring transaction managers. Below is a configuration example:

package com.querydsl.example.config;

import com.querydsl.sql.H2Templates;
import com.querydsl.sql.SQLQueryFactory;
import com.querydsl.sql.SQLTemplates;
import com.querydsl.sql.spring.SpringConnectionProvider;
import com.querydsl.sql.spring.SpringExceptionTranslator;
import com.querydsl.sql.types.DateTimeType;
import com.querydsl.sql.types.LocalDateType;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.inject.Inject;
import javax.inject.Provider;
import javax.sql.DataSource;
import java.sql.Connection;

@Configuration
public class JdbcConfiguration {

    @Bean
    public DataSource dataSource() {
        // implementation omitted
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean
    public com.querydsl.sql.Configuration querydslConfiguration() {
        SQLTemplates templates = H2Templates.builder().build(); //change to your Templates
        com.querydsl.sql.Configuration configuration = new com.querydsl.sql.Configuration(templates);
        configuration.setExceptionTranslator(new SpringExceptionTranslator());
        return configuration;
    }

    @Bean
    public SQLQueryFactory queryFactory() {
        Provider<Connection> provider = new SpringConnectionProvider(dataSource());
        return new SQLQueryFactory(querydslConfiguration(), provider);
    }

}