2011-01-21

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.

12 comments:

  1. I believe there is a typo in the first section of this blog. It looks like you mistakenly copied the Maven dependencies from http://blog.mysema.com/2010/07/querying-hibernate-with-querydsl.html

    There is no mention of querydsl-sql anywhere and I'm pretty sure we don't need to depend on querydsl-hql for SQL code.

    ReplyDelete
  2. Thanks for the correction. Fixed and published.

    ReplyDelete
  3. Hi, great post!
    I was wondering: is there any way of generating the Bean classes directly from maven as well, instead of java?

    ReplyDelete
  4. Miguel, just add the element true to the configuration. For more information see the reference documentation.

    ReplyDelete
  5. Is there a beginner's tutorial somewhere? I'm familiar with Java, but not so familiar with working with databases with Java.

    For example, what are my imports for

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


    I need to reverse map a database and I'm assuming this MetaDataExporter stuff is what I need to use, but I just need a little more info.

    Thanks

    ReplyDelete
  6. Never mind to the anonymous above. I figured it out:) I was just a little confused about which backend I had to use - JPA, JDO, Hibernate, etc..

    I ended up generating the code using code like the above and was able to do queries using SQLTemplates and SQLQuery.

    Great library. Thanks

    ReplyDelete
  7. There are various Querydsl tutorials on this blog and the reference docs : http://source.mysema.com/static/querydsl/2.2.0/reference/html/

    They are all meant for beginners and intermediate users.

    ReplyDelete
  8. hi
    can I make next select on querydsl? (in example H2 sintax)

    WITH RECURSIVE T(N) AS (
    SELECT 1
    UNION ALL
    SELECT N+1 FROM T WHERE N<10
    )
    SELECT * FROM T;

    -- returns the values 1 .. 10

    ReplyDelete
  9. Currently not, but you can a open a ticket for it on GitHub.

    ReplyDelete
  10. PROBLEM:

    The value of "packageName" is ignored and all classes, bean classes
    and query classes, are generated into "beanPackageName" package.


    My "pom.xml" file contains the following "querydsl-maven-plugin" configuration:




    ...

    com.mysema.querydsl
    querydsl-maven-plugin
    2.6.0



    export




    ...
    ${project.basedir}/src/main/java
    com.mycompany.querydsltest.sakila.metamodel
    true


    com.mycompany.querydsltest.sakila.model

    ...







    Running maven with "-e" flag outputs the following:

    ...
    [DEBUG] Configuring mojo 'com.mysema.querydsl:querydsl-maven-plugin:2.6.0:export' -->
    [DEBUG] (f) beanPackageName = com.mycompany.querydsltest.sakila.model
    [DEBUG] (f) columnAnnotations = false
    [DEBUG] (f) createScalaSources = false
    [DEBUG] (f) exportBeans = true
    [DEBUG] (f) innerClassesForKeys = false
    [DEBUG] (f) jdbcDriver = com.mysql.jdbc.Driver
    [DEBUG] (f) jdbcPassword = root
    [DEBUG] (f) jdbcUrl = jdbc:mysql://localhost:3306/sakila
    [DEBUG] (f) jdbcUser = root
    [DEBUG] (f) namePrefix = Q
    [DEBUG] (f) packageName = com.mycompany.querydsltest.sakila.metamodel
    [DEBUG] (f) project = MavenProject: com.mycompany:querydsltest:0.0.1-SNAPSHOT @ D:\myprojects\querydsltest\pom.xml
    [DEBUG] (f) schemaToPackage = false
    [DEBUG] (f) targetFolder = D:\myprojects\querydsltest/src/main/java
    [DEBUG] (f) validationAnnotations = false
    [DEBUG] -- end configuration --
    ...

    ReplyDelete
    Replies
    1. This has been fixed already although not yet released https://github.com/mysema/querydsl/issues/162

      Delete
  11. How do i query a max or count using beans?

    ReplyDelete