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