Querying in SQL with Querydsl

Querydsl provides a typesafe querying layer on top of JPA, JDO, JDBC and other backends. This blog post presents a simple tutorial on how to get started with querying in SQL using Querydsl.

Querydsl for SQL provides a typesafe view of the relational schema and makes expressing database queries in Java as intuitive as possible.

Getting started

To get started with Querydsl for SQL using a Maven 2 based build environment, follow these steps.

Add the following dependencies to your Maven project and make sure that the Maven 2 repo of Mysema Source is accessible from your POM :

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

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

The snippet to include the Mysema Source Maven repository is :

<repository>
  <id>msource</id>
  <url>http://source.mysema.com/maven2/releases/</url>
  <releases>
    <enabled>true</enabled>
  </releases>
  <snapshots>
    <enabled>false</enabled>
  </snapshots>
</repository>

Set the variable querydsl.version to the latest Querydsl version available.

If you are not using Maven you can download the latest builds from here

Creating the Querydsl query types

To get started with creating Querydsl SQL queries you need to create Java classes for your relational schema. This can be done either in Java, via Maven or via Ant.

In Java

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("src/main/java"));
exporter.export(conn.getMetaData());  

This declares that the database schema is to be mirrored into the com.myproject.domain package in the src/main/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 Maven

This functionality is also available as a Maven plugin. The presented example can be declared like this in the POM :

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

In 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 jdbcDriverClass, dbUrl, dbUserName, dbPassword, namePrefix, targetPackage, targetSourceFolder, schemaPattern, tableNamePattern, exportBeans and innerClassesForKeys.

Using query types

To create queries with Querydsl you need to instantiate variables and Query implementations. We will start with the variables.

For each table in your mirrored schema a query type is created. For example for the table customer Querydsl will generate a query type with the simple name QCustomer into the folder and package declared in the code generation setup. QCustomer can be used as a statically typed variable in Querydsl queries as a representative for the customer table.

QCustomer has a default instance variable which can be accessed as a static field :

QCustomer customer = QCustomer.customer;

Alternatively you can define your own QCustomer instances like this :

QCustomer customer = new QCustomer("c");

Querying

Querying with Querydsl SQL is as simple as this :

QCustomer customer = new QCustomer("c"); // alias for the CUSTOMER table

SQLTemplates dialect = new HSQLDBTemplates(); // SQL-dialect
SQLQuery query = new SQLQueryImpl(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'

Internally Querydsl SQL uses prepared statements.

General usage

Use the the cascading methods of the com.mysema.query.sql.SQLQuery interface like this

from : Define the query sources here.

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

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

groupBy : Define the group by arguments in varargs form.

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

orderBy : Define the 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 : Define the paging of the result. Limit for max results, offset for skipping rows and restrict for defining both in one call.

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

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

Subqueries

To create a subquery you create a com.mysema.query.sql.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.

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

Another example

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

Query extension support

Custom query extensions to support engine specific syntax can be created by subclassing com.mysema.query.sql.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.

Using DDL commands

CREATE TABLE commands can be used in fluent form via the com.mysema.query.sql.ddl.CreateTableClause class. Here are some examples :

new CreateTableClause(conn, templates, "language")
 .column("id", Integer.class).notNull()
 .column("text", String.class).size(256).notNull()
 .primaryKey("PK_LANGUAGE","id")
 .execute();

new CreateTableClause(conn, templates, "symbol")
 .column("id", Long.class).notNull()
 .column("lexical", String.class).size(1024).notNull()
 .column("datatype", Long.class)
 .column("lang", Integer.class)
 .column("intval",Long.class)
 .column("floatval",Double.class)
 .column("datetimeval",Timestamp.class)
 .primaryKey("PK_SYMBOL","id")
 .foreignKey("FK_LANG","lang").references("language","id")
 .execute();

new CreateTableClause(conn, templates, "statement")
 .column("model", Long.class)
 .column("subject", Long.class).notNull()
 .column("predicate", Long.class).notNull()
 .column("object", Long.class).notNull()
 .foreignKey("FK_MODEL","model").references("symbol","id")
 .foreignKey("FK_SUBJECT","subject").references("symbol","id")
 .foreignKey("FK_PREDICATE","predicate").references("symbol","id")
 .foreignKey("FK_OBJECT","object").references("symbol","id")
 .execute();    

The constructor of CreateTableClause takes the connection, the templates and the table name. The rest is declared via column, primaryKey and foreignKey invocations.

Here are the corresponding CREATE TABLE clauses as they are executed.

CREATE TABLE language (
  id INTEGER NOT NULL,
  text VARCHAR(256) NOT NULL,
  CONSTRAINT PK_LANGUAGE PRIMARY KEY(id)
)

 CREATE TABLE symbol (
  id BIGINT NOT NULL,
  lexical VARCHAR(1024) NOT NULL,
  datatype BIGINT,
  lang INTEGER,
  intval BIGINT,
  floatval DOUBLE,
  datetimeval TIMESTAMP,
  CONSTRAINT PK_SYMBOL PRIMARY KEY(id),
  CONSTRAINT FK_LANG FOREIGN KEY(lang) REFERENCES language(id)
)

 CREATE TABLE statement (
  model BIGINT,
  subject BIGINT NOT NULL,
  predicate BIGINT NOT NULL,
  object BIGINT NOT NULL,
  CONSTRAINT FK_MODEL FOREIGN KEY(model) REFERENCES symbol(id),
  CONSTRAINT FK_SUBJECT FOREIGN KEY(subject) REFERENCES symbol(id),
  CONSTRAINT FK_PREDICATE FOREIGN KEY(predicate) REFERENCES symbol(id),
  CONSTRAINT FK_OBJECT FOREIGN KEY(object) REFERENCES symbol(id)
)  

Using DML commands

All the com.mysema.query.dml.DMLClause implementations in the Querydsl SQL module take three parameters, the java.sql.Connection instance, the com.mysema.query.sql.SQLTemplates instance used in the queries and the main entity the DMLClause is bound to.

Insert examples :

// with columns
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();

Update examples :

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

Delete examples :

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

Bean class generation

To create JavaBean DTO types for the tables of your schema use the com.mysema.query.sql.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 :

@Test
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());

Where to go next

See the related section of the latest Reference docs for more information.