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.mysema.querydsl</groupId>
  <artifactId>querydsl-sql</artifactId>
  <version>${querydsl.version}</version>
</dependency>    
        
<dependency>
  <groupId>com.mysema.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 or Ant.

2.3.2. Code generation via Maven

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

   
<plugin>
  <groupId>com.mysema.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>  

Use the goal test-export to add the targetFolder as a test compile source root instead of a compile source root.

Table 2.1. Parameters

NameDescription
jdbcDriverclass name for 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)
schemaPatternant style pattern to restrict code generation to certain schemas (default: null)
tableNamePatternant style pattern to restrict code generation to certain tables (default: null)
targetFoldertarget folder where source filder should be generated
namingStrategyClassclass name for NamingStrategy class (default: DefaultNamingStrategy)
beanSerializerClassclass name for BeanSerializer class (default: BeanSerializer)
serializerClassclass name for 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)

2.3.3. Code generation via ANT

The ANT task com.mysema.query.sql.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.

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. Querying

Querying with Querydsl SQL is as simple as this:

 
QCustomer customer = new QCustomer("c");

SQLTemplates dialect = new HSQLDBTemplates(); // SQL-dialect
SQLQuery query = new SQLQuery(connection, dialect); 
List<String> lastNames = query.from(customer)
    .where(customer.firstName.eq("Bob"))
    .list(customer.lastName);

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'

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

  • CUBRIDTemplates (tested with 8.4)

  • DerbyTemplates (tested with 10.8.2.2)

  • HSQLDBTemplates (tested with 2.2.4)

  • H2Templates (tested with 1.3.164)

  • MySQLTemplates (tested with MySQL 5.5)

  • OracleTemplates (test with Oracle XE 10.2.0.1.0)

  • PostgresTemplates (tested with 9.1)

  • SQLiteTemplates (tested with xerial JDBC 3.7.2)

  • SQLServerTemplates (tested with SQL Server 2008)

2.3.6. General usage

Use the the cascading methods of the SQLQuery class like this

from: Add the query sources here.

innerJoin, join, leftJoin, 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.7. Joins

Joins are constructed using the following syntax:

 
QCustomer customer = QCustomer.customer;
QCompany company = QCompany.company;
query.from(customer)
    .innerJoin(customer.company, company)
    .list(customer.firstName, customer.lastName, company.name);

and for a left join:

 
query.from(customer)
    .leftJoin(customer.company, company)
    .list(customer.firstName, customer.lastName, company.name);

Alternatively the join condition can also be written out:

 
query.from(customer)
    .leftJoin(company).on(customer.company.eq(company.id))
    .list(customer.firstName, customer.lastName, company.name);

2.3.8. Ordering

The syntax for declaring ordering is

 
query.from(customer)
    .orderBy(customer.lastName.asc(), customer.firstName.asc())
    .list(customer.firstName, customer.lastName);

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.9. Grouping

Grouping can be done in the following form

 
query.from(customer)
    .groupBy(customer.lastName)
    .list(customer.lastName);

which is equivalent to the following native SQL

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

2.3.10. Using Subqueries

To create a subquery you create a SQLSubQuery instance, define the query parameters via from, where etc and use unique or list to create a subquery, which is just a type-safe Querydsl expression for the query. unique is used for a unique (single) result and list for a list result.

 
QCustomer customer = QCustomer.customer;
QCustomer customer2 = new QCustomer("customer2");
query.from(customer).where(
  customer.status.eq(new SQLSubQuery().from(customer2).unique(customer2.status.max()))
  .list(customer.all())  

Another example

 
QStatus status = QStatus.status;
query.from(customer).where(  
  customer.status.in(new SQLSubQuery().from(status).where(status.level.lt(3)).list(status.id))  
  .list(customer.all())    

2.3.11. 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 extends AbstractSQLQuery<MySQLQuery> {
    
    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.mysema.query.QueryFlag.Position enum class.

2.3.12. Window functions

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

Usage example:

query.from(employee)
    .list(SQLExpressions.rowNumber()
        .over()
        .partitionBy(employee.name)
        .orderBy(employee.id));

2.3.13. Using Data manipulation commands

All the DMLClause implementation in the Querydsl SQL module take three parameters, the Connection, the SQLTemplates instance used in the queries and the main entity the DMLClause is bound to.

2.3.13.1. Insert

With columns

QSurvey survey = QSurvey.survey;
  
new SQLInsertClause(conn, dialect, survey)
    .columns(survey.id, survey.name)
    .values(3, "Hello").execute();

Without columns

new SQLInsertClause(conn, dialect, survey)
    .values(4, "Hello").execute();

With subquery

new SQLInsertClause(conn, dialect, survey)
    .columns(survey.id, survey.name)
    .select(new SQLSubQuery().from(survey2).list(survey2.id.add(1), survey2.name))
    .execute();

With subquery, without columns

new SQLInsertClause(conn, dialect, survey)
    .select(new SQLSubQuery().from(survey2).list(survey2.id.add(10), survey2.name))
    .execute();

2.3.13.2. Update

With where

QSurvey survey = QSurvey.survey;

new SQLUpdateClause(conn, dialect, survey)
    .where(survey.name.eq("XXX"))
    .set(survey.name, "S")
    .execute();

Without where

  
new SQLUpdateClause(conn, dialect, survey)
    .set(survey.name, "S")
    .execute()  

2.3.13.3. Delete

With where

QSurvey survey = QSurvey.survey;

new SQLDelecteClause(conn, dialect, survey)
    .where(survey.name.eq("XXX"))
    .execute();
    

Without where

  
new SQLDelecteClause(conn, dialect, survey)
    .execute()  

2.3.14. 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;

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

SQLUpdateClause update = 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:

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

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

Insert:

SQLInsertClause insert = 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.15. 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 = insert(e).populate(employee).executeWithKey(e.id);
employee.setId(id);

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

// Query
Employee smith = query().from(e).where(e.lastname.eq("Smith")).uniqueResult(e);
assertEquals("John", smith.getFirstname());

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

The factory methods used in the previous example are here:

      	
protected SQLUpdateClause update(RelationalPath<?> e){
    return new SQLUpdateClause(Connections.getConnection(), templates, e);
}

protected SQLInsertClause insert(RelationalPath<?> e){
    return new SQLInsertClause(Connections.getConnection(), templates, e);
}

protected SQLDeleteClause delete(RelationalPath<?> e){
    return new SQLDeleteClause(Connections.getConnection(), templates, e);
}

protected SQLMergeClause merge(RelationalPath<?> e){
    return new SQLMergeClause(Connections.getConnection(), templates, e);
}

protected SQLQuery query() {
    return new SQLQuery(Connections.getConnection(), templates);  	
}
  	

2.3.16. 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.mysema.query.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 maping 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.