2. Querydsl Support

The Querydsl project provides a framework that let's you write type-safe queries in Java rather than constructing them using strings. This has several advantages like code completion in your IDE, domain types and properties can be accessed in a type-safe manner reducing the probability of query syntax errors during run-time. Querydsl has modules that support JPA, JDO, SQL, MongoDB and more. It is the SQL support that is used for the JDBC Extensions project. You can read more about Querydsl at their website http://www.querydsl.com.

2.1 Introduction to Querydsl

Before you can use the Spring support for Querydsl you need to configure your application to use the Querydsl SQL support. See the instruction in the Mysema blog post on how this is done. Once you have generated your Querydsl query types then you can start using the Spring support as outlined bellow.

2.2 QueryDslJdbcTemplate

The central class in the Querydsl support is the QueryDslJdbcTemplate. Just like the NamedParameterJdbcTemplate it wraps a regular JdbcTemplate that you can get access to by calling the getJdbcOperations method. One thing to note is that when you use the QueryDslJdbcTemplate, there is no need to specify the SQL dialect to be used since the template will auto-detect this when it is created.

You can create a QueryDslJdbcTemplate by passing in a JdbcTemplate or a DataSource in the constructor. Here is some example code showing this:

private QueryDslJdbcTemplate template;

@Autowired
public void setDataSource(DataSource dataSource) {
    this.template = new QueryDslJdbcTemplate(dataSource);
}

At this point the template is ready to be used and we give examples for various uses below.

2.3 Queries

For queries you need to have a reference to the query type. For the examples in this document we define the query type as follows:

private final QProduct qProduct = QProduct.product;

Now we are ready to create the first query. Instead of directly creating an instance of SQLQueryImpl we ask the template for a managed instance.

SQLQuery sqlQuery = template.newSqlQuery()

The managed part here refers to the managing of the connection and the SQL dialect. The QueryDslJdbcTemplate will provide both of these. The dialect is set when the SQLQuery is created and the connection is provided when the SQLQuery is executed using the clone feature of the SQLQuery implementation class.

We continue to build this query providing from and where clauses:

SQLQuery sqlQuery = template.newSqlQuery().from(qProduct)
        .where(qProduct.category.eq(categoryId));

Here categoryId is a parameter that is passed in to the method.

The final step is to execute the query. Depending on how you want to map the results, there are two flavors of the query methods. You can

  • use the method taking a regular Spring RowMapper together with a projection in the form of a Querydsl Expression.

or

  • use the method that takes a Querydsl class implementing Expression like an extension of the handy MappingProjection, a QBean implementation or a Querydsl query type combined with a Querydsl bean type to specify the mapping.

Here is an example using the query created above together with a MappingProjection for mapping the query results:

public List<Product> getProductListByCategory(final String categoryId)
        throws DataAccessException {

    SQLQuery sqlQuery = template.newSqlQuery().from(qProduct)
            .where(qProduct.category.eq(categoryId));

    return template.query(sqlQuery, new MappingProductProjection(qProduct);
}

private static class MappingProductProjection extends MappingProjection<Product> {

    public MappingProductProjection(QProduct qProduct) {
        super(Product.class, qProduct.productid,
            qProduct.name, qProduct.descn, qProduct.category));
    }

    @Override
    protected Product map(Tuple tuple) {
        Product product = new Product();

        product.setProductId(tuple.get(qProduct.productid));
        product.setName(tuple.get(qProduct.name));
        product.setDescription(tuple.get(qProduct.descn));
        product.setCategoryId(tuple.get(qProduct.category));

        return product;
    }
}

2.4 Inserts

For inserts we need to call the template's insert method and implement an SqlInsertCallback to handle the mapping of data from the domain object values to the insert. Here is an example:

public void insertProduct(final Product product) throws DataAccessException {
    template.insert(qProduct, new SqlInsertCallback() {
        public long doInSqlInsertClause(SQLInsertClause sqlInsertClause) {
            return sqlInsertClause.columns(qProduct.productid, qProduct.name, qProduct.descn, qProduct.category)
                    .values(product.getProductId(), product.getName(), product.getDescription(), product.getCategoryId())
                    .execute();
        }
    });
}

2.5 Updates

Updates are similar to the inerts but we of course call the update method and implement an SqlUpdateCallback to provide the where clause and handle the mapping of update parameter values.

public void updateProduct(final Product product) throws DataAccessException {
    template.update(qProduct, new SqlUpdateCallback() {

        public long doInSqlUpdateClause(SQLUpdateClause sqlUpdateClause) {
            return sqlUpdateClause.where(qProduct.productid.eq(product.getProductId()))
                    .set(qProduct.name, product.getName())
                    .set(qProduct.descn, product.getDescription())
                    .set(qProduct.category, product.getCategoryId())
                    .execute();
        }
    });
}

2.6 Deletes

Deletes are also very similar except we don't need to do any value mapping. We simply call the delete method and implement an SqlDeleteCallback with a where clause.

public void deleteProduct(final Product product) {
    template.delete(qProduct, new SqlDeleteCallback() {

        public long doInSqlDeleteClause(SQLDeleteClause sqlDeleteClause) {
            return sqlDeleteClause.where(qProduct.productid.eq(product.getProductId()))
                    .execute();
        }
    });
}