We are using the HyperSQL database version 2.2.8 for our Querydsl examples in this chapter. One nice feature of HyperSQL is that we can run the database in both server mode and in-memory. The in-memory option is great for integration tests since starting and stopping the database can be controlled by the application configuration using Spring’s EmbeddedDatabaseBuilder, or the <jdbc:embedded-database> tag when using the spring-jdbc XML namespace. The build scripts download the dependency and start the in-memory database automatically. To use the database in standalone server mode, we need to download the distribution and unzip it to a directory on our system. Once that is done, we can change to the hsqldb directory of the unzipped distribution and start the database using this command:

java -classpath lib/hsqldb.jar org.hsqldb.server.Server 
  --database.0 file:data/test --dbname.0 test

Running this command starts up the server, which generates some log output and a message that the server has started. We are also told we can use Ctrl-C to stop the server. We can now open another command window, and from the same hsqldb directory we can start up a database client so we can interact with the database (creating tables and running queries, etc.). For Windows, we need to execute only the runManagerSwing.bat batch file located in the bin directory. For OS X or Linux, we can run the following command:

java -classpath lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

This should bring up the login dialog shown in Figure 5-1. We need to change the Type to HSQL Database Engine Server and add “test” as the name of the database to the URL so it reads jdbc:hsqldb:hsql://localhost/test. The default user is “sa” with a blank password. Once connected, we have an active GUI database client.

Figure 5-1. HSQLDB client login dialog

The SQL module of Querydsl provides a type-safe option for the Java developer to work with relational databases. Instead of writing SQL queries and embedding them in strings in your Java program, Querydsl generates query types based on metadata from your database tables. You use these generated types to write your queries and perform CRUD operations against the database without having to resort to providing column or table names using strings.

The way you generate the query types is a bit different in the SQL module compared to other Querydsl modules. Instead of relying on annotations, the SQL module relies on the actual database tables and available JDBC metadata for generating the query types. This means that you need to have the tables created and access to a live database before you run the query class generation. For this reason, we recommend running this as a separate step of the build and saving the generated classes as part of the project in the source control system. We need to rerun this step only when we have made some changes to our table structures and before we check in our code. We expect the continuous integration system to run this code generation step as well, so any mismatch between the Java types and the database tables would be detected at build time.

We’ll take a look at what we need to generate the query types later, but first we need to understand what they contain and how we use them. They contain information that Querydsl can use to generate queries, but they also contain information you can use to compose queries; perform updates, inserts, and deletes; and map data to domain objects. Let’s take a quick look at an example of a table to hold address information. The address table has three VARCHAR columns: street, city, and country. Example 5-1 shows the SQL statement to create this table.

CREATE TABLE address (
  id BIGINT IDENTITY PRIMARY KEY,
  customer_id BIGINT CONSTRAINT address_customer_ref 
    FOREIGN KEY REFERENCES customer (id),
  street VARCHAR(255),
  city VARCHAR(255),
  country VARCHAR(255));

Example 5-2 demonstrates the generated query type based on this address table. It has some constructors, Querydsl path expressions for the columns, methods to create primary and foreign key types, and a static field that provides an instance of the QAddress class.

package com.oreilly.springdata.jdbc.domain;

import static com.mysema.query.types.PathMetadataFactory.*;

import com.mysema.query.types.*;
import com.mysema.query.types.path.*;

import javax.annotation.Generated;


/**
 * QAddress is a Querydsl query type for QAddress
 */
@Generated("com.mysema.query.sql.codegen.MetaDataSerializer")
public class QAddress extends com.mysema.query.sql.RelationalPathBase<QAddress> {

  private static final long serialVersionUID = 207732776;

  public static final QAddress address = new QAddress("ADDRESS");

  public final StringPath city = createString("CITY");
  public final StringPath country = createString("COUNTRY");
  public final NumberPath<Long> customerId = createNumber("CUSTOMER_ID", Long.class);
  public final NumberPath<Long> id = createNumber("ID", Long.class);
  public final StringPath street = createString("STREET");
  public final com.mysema.query.sql.PrimaryKey<QAddress> sysPk10055 = createPrimaryKey(id);
  public final com.mysema.query.sql.ForeignKey<QCustomer> addressCustomerRef = 
      createForeignKey(customerId, "ID");

  public QAddress(String variable) {
    super(QAddress.class, forVariable(variable), "PUBLIC", "ADDRESS");
  }

  public QAddress(Path<? extends QAddress> entity) {
    super(entity.getType(), entity.getMetadata(), "PUBLIC", "ADDRESS");
  }

  public QAddress(PathMetadata<?> metadata) {
    super(QAddress.class, metadata, "PUBLIC", "ADDRESS");
  }
}

By creating a reference like this:

QAddress qAddress = QAddress.address;

in our Java code, we can reference the table and the columns more easily using qAddress instead of resorting to using string literals.

In Example 5-3, we query for the street, city, and country for any address that has London as the city.

QAddress qAddress = QAddress.address;
SQLTemplates dialect = new HSQLDBTemplates();
SQLQuery query = new SQLQueryImpl(connection, dialect)
    .from(qAddress)
    .where(qAddress.city.eq("London"));
List<Address> results = query.list(
    new QBean<Address>(Address.class, qAddress.street,
      qAddress.city, qAddress.country));

First, we create a reference to the query type and an instance of the correct SQLTemplates for the database we are using, which in our case is HSQLDBTemplates. The SQLTemplates encapsulate the differences between databases and are similar to Hibernate’s Dialect. Next, we create an SQLQuery with the JDBC javax.sql.Connection and the SQLTemplates as the parameters. We specify the table we are querying using the from method, passing in the query type. Next, we provide the where clause or predicate via the where method, using the qAddress reference to specify the criteria that city should equal London.

Executing the SQLQuery, we use the list method, which will return a List of results. We also provide a mapping implementation using a QBean, parameterized with the domain type and a projection consisting of the columns street, city, and country.

The result we get back is a List of Addresses, populated by the QBean. The QBean is similar to Spring’s BeanPropertyRowMapper, and it requires that the domain type follows the JavaBean style. Alternatively, you can use a MappingProjection, which is similar to Spring’s familiar RowMapper in that you have more control over how the results are mapped to the domain object.

Based on this brief example, let’s summarize the components of Querydsl that we used for our SQL query:

So far, we haven’t integrated with any Spring features, but the rest of the chapter covers this integration. This first example is just intended to introduce the basics of the Querydsl SQL module.

The code for the Querydsl part of this chapter is located in the jdbc module of the sample GitHub project.

Before we can really start using Querydsl in our project, we need to configure our build system so that we can generate the query types. Querydsl provides both Maven and Ant integration, documented in the “Querying SQL” chapter of the Querydsl reference documentation.

In our Maven pom.xml file, we add the plug-in configuration shown in Example 5-4.

<plugin>
  <groupId>com.mysema.querydsl</groupId>
  <artifactId>querydsl-maven-plugin</artifactId>
  <version>${querydsl.version}</version>
  <configuration>
    <jdbcDriver>org.hsqldb.jdbc.JDBCDriver</jdbcDriver>
    <jdbcUrl>jdbc:hsqldb:hsql://localhost:9001/test</jdbcUrl>
    <jdbcUser>sa</jdbcUser>
    <schemaPattern>PUBLIC</schemaPattern>
    <packageName>com.oreilly.springdata.jdbc.domain</packageName>
    <targetFolder>${project.basedir}/src/generated/java</targetFolder>
  </configuration>
  <dependencies>
    <dependency>
      <groupId>org.hsqldb</groupId>
      <artifactId>hsqldb</artifactId>
      <version>2.2.8</version>
    </dependency>
    <dependency>
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-classic</artifactId>
      <version>${logback.version}</version>
    </dependency>
  </dependencies>
</plugin>

We will have to execute this plug-in explicitly using the following Maven command:

mvn com.mysema.querydsl:querydsl-maven-plugin:export

You can set the plug-in to execute as part of the generate-sources life cycle phase by specifying an execution goal. We actually do this in the example project, and we also use a predefined HSQL database just to avoid forcing you to start up a live database when you build the example project. For real work, though, you do need to have a database where you can modify the schema and rerun the Querydsl code generation.

Now that we have the build configured, we can generate the query classes, but let’s first review the database schema that we will be using for this section. We already saw the address table, and we are now adding a customer table that has a one-to-many relationship with the address table. We define the schema for our HSQLDB database as shown in Example 5-5.

CREATE TABLE customer (
  id BIGINT IDENTITY PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  email_address VARCHAR(255));

CREATE UNIQUE INDEX ix_customer_email ON CUSTOMER (email_address ASC);

CREATE TABLE address (
  id BIGINT IDENTITY PRIMARY KEY,
  customer_id BIGINT CONSTRAINT address_customer_ref FOREIGN KEY REFERENCES customer (id),
  street VARCHAR(255),
  city VARCHAR(255),
  country VARCHAR(255));

The two tables, customer and address, are linked by a foreign key reference from address to customer. We also define a unique index on the email_address column of the address table.

This gives us the domain model implementation shown in Figure 5-2.

Figure 5-2. Domain model implementation used with Querydsl SQL

We have already seen the schema for the database, and now we will take a look at the corresponding Java domain classes we will be using for our examples. We need a Customer class plus an Address class to hold the data from our database tables. Both of these classes extend an AbstractEntity class that, in addition to equals(…) and hashCode(), has setters and getters for the id, which is a Long:

public class AbstractEntity {

  private Long id;

  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  @Override
  public boolean equals(Object obj) { … }

  @Override
  public int hashCode() { … }
}

The Customer class has name and email information along with a set of addresses. This implementation is a traditional JavaBean with getters and setters for all properties:

public class Customer extends AbstractEntity {

  private String firstName;
  private String lastName;
  private EmailAddress emailAddress;
  private Set<Address> addresses = new HashSet<Address>();

  public String getFirstName() {
    return firstName;
  }

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }

  public String getLastName() {
    return lastName;
  }

  public void setLastName(String lastName) {
    this.lastName = lastName;
  }

  public EmailAddress getEmailAddress() {
    return emailAddress;
  }

  public void setEmailAddress(EmailAddress emailAddress) {
    this.emailAddress = emailAddress;
  }

  public Set<Address> getAddresses() {
    return Collections.unmodifiableSet(addresses);
  }

  public void addAddress(Address address) {
    this.addresses.add(address);
  }

  public void clearAddresses() {
    this.addresses.clear();
  }
}

The email address is stored as a VARCHAR column in the database, but in the Java class we use an EmailAddress value object type that also provides validation of the email address using a regular expression. This is the same class that we have seen in the other chapters:

public class EmailAddress {

  private static final String EMAIL_REGEX = …;
  private static final Pattern PATTERN = Pattern.compile(EMAIL_REGEX);

  private String value;

  protected EmailAddress() {
  }

  public EmailAddress(String emailAddress) {
    Assert.isTrue(isValid(emailAddress), "Invalid email address!");
    this.value = emailAddress;
  }

  public static boolean isValid(String source) {
    return PATTERN.matcher(source).matches();
  }
}

The last domain class is the Address class, again a traditional JavaBean with setters and getters for the address properties. In addition to the no-argument constructor, we have a constructor that takes all address properties:

public class Address extends AbstractEntity {
  private String street, city, country;

  public Address() {
  }

  public Address(String street, String city, String country) {
    this.street = street;
    this.city = city;
    this.country = country;
  }

  public String getCountry() {
    return country;
  }

  public void setCountry(String country) {
    this.country = country;
  }

  public String getStreet() {
    return street;
  }

  public void setStreet(String street) {
    this.street = street;
  }

  public String getCity() {
    return city;
  }

  public void setCity(String city) {
    this.city = city;
  }
}

The preceding three classes make up our domain model and reside in the com.oreilly.springdata.jdbc.domain package of the JDBC example project. Now we are ready to look at the interface definition of our CustomerRepository:

public interface CustomerRepository {

  Customer findById(Long id);

  List<Customer> findAll();

  void save(Customer customer);

  void delete(Customer customer);

  Customer findByEmailAddress(EmailAddress emailAddress);
}

We have a couple of finder methods and save and delete methods. We don’t have any repository methods to save and delete the Address objects since they are always owned by the Customer instances. We will have to persist any addresses provided when the Customer instance is saved.

The implementation will be autowired with a DataSource; in that setter, we will create a QueryDslJdbcTemplate and a projection for the table columns used by all queries when retrieving data needed for the Customer instances. (See Example 5-6.)

@Repository
@Transactional
public class QueryDslCustomerRepository implements CustomerRepository {

  private final QCustomer qCustomer = QCustomer.customer;
  private final QAddress qAddress = QAddress.address;

  private final QueryDslJdbcTemplate template;
  private final Path<?>[] customerAddressProjection;

  @Autowired
  public QueryDslCustomerRepository(DataSource dataSource) {
    
    Assert.notNull(dataSource);

    this.template = new QueryDslJdbcTemplate(dataSource);
    this.customerAddressProjection = new Path<?>[] { qCustomer.id, qCustomer.firstName, 
      qCustomer.lastName, qCustomer.emailAddress, qAddress.id, qAddress.customerId, 
      qAddress.street, qAddress.city, qAddress.country };
  }

  @Override
  @Transactional(readOnly = true)
  public Customer findById(Long id) { … }

  @Override
  @Transactional(readOnly = true)
  public Customer findByEmailAddress(EmailAddress emailAddress) { … }

  @Override
  public void save(final Customer customer) { … }

  @Override
  public void delete(final Customer customer) { … }
}

We are writing a repository, so we start off with an @Repository annotation. This is a standard Spring stereotype annotation, and it will make your component discoverable during classpath scanning. In addition, for repositories that use ORM-style data access technologies, it will also make your repository a candidate for exception translation between the ORM-specific exceptions and Spring’s DataAccessException hierarchy. In our case, we are using a template-based approach, and the template itself will provide this exception translation.

Next is the @Transactional annotation. This is also a standard Spring annotation that indicates that any call to a method in this class should be wrapped in a database transaction. As long as we provide a transaction manager implementation as part of our configuration, we don’t need to worry about starting and completing these transactions in our repository code.

We also define two references to the two query types that we have generated, QCustomer and QAddress. The array customerAddressProjection will hold the Querydsl Path entries for our queries, one Path for each column we are retrieving.

The constructor is annotated with @Autowired, which means that when the repository implementation is configured, the Spring container will inject the DataSource that has been defined in the application context. The rest of the class comprises the methods from the CustomerRepository that we need to provide implementations for, so let’s get started.

First, we will implement the findById method (Example 5-7). The ID we are looking for is passed in as the only argument. Since this is a read-only method, we can add a @Transactional(readOnly = true) annotation to provide a hint that some JDBC drivers will use to improve transaction handling. It never hurts to provide this optional attribute for read-only methods even if some JDBC drivers won’t make use of it.

@Transactional(readOnly = true)
public Customer findById(Long id) {

  SQLQuery findByIdQuery = template.newSqlQuery()
    .from(qCustomer)
    .leftJoin(qCustomer._addressCustomerRef, qAddress)
    .where(qCustomer.id.eq(id));

  return template.queryForObject(
    findByIdQuery,
    new CustomerExtractor(),
    customerAddressProjection);
}

We start by creating an SQLQuery instance. We have already mentioned that when we are using the QueryDslJdbcTemplate, we need to let the template manage the SQLQuery instances. That’s why we use the factory method newSqlQuery() to obtain an instance. The SQLQuery class provides a fluent interface where the methods return the instance of the SQLQuery. This makes it possible to string a number of methods together, which in turn makes it easier to read the code. We specify the main table we are querying (the customer table) with the from method. Then we add a left outer join against the address table using the leftJoin(…) method. This will include any address rows that match the foreign key reference between address and customer. If there are none, the address columns will be null in the returned results. If there is more than one address, we will get multiple rows for each customer, one for each address row. This is something we will have to handle in our mapping to the Java objects later on. The last part of the SQLQuery is specifying the predicate using the where method and providing the criteria that the id column should equal the id parameter.

After we have the SQLQuery created, we execute our query by calling the queryForObject method of the QueryDslJdbcTemplate, passing in the SQLQuery and a combination of a mapper and a projection. In our case, that is a ResultSetExtractor and the customerAddressProjection that we created earlier. Remember that we mentioned earlier that since our query contained a leftJoin, we needed to handle potential multiple rows per Customer.

Example 5-8 is the implementation of this CustomerExtractor.

private static class CustomerExtractor implements ResultSetExtractor<Customer> {

  CustomerListExtractor customerListExtractor =
    new CustomerListExtractor(OneToManyResultSetExtractor.ExpectedResults.ONE_OR_NONE);

  @Override
  public Customer extractData(ResultSet rs) throws SQLException, DataAccessException {

    List<Customer> list = customerListExtractor.extractData(rs);
    return list.size() > 0 ? list.get(0) : null;
  }
}

As you can see, we use a CustomerListExtractor that extracts a List of Customer objects, and we return the first object in the List if there is one, or null if the List is empty. We know that there could not be more than one result since we set the parameter expectedResults to OneToManyResultSetExtractor.ExpectedResults.ONE_OR_NONE in the constructor of the CustomerListExtractor.

Before we look at the CustomerListExtractor, let’s look at the base class, which is a special implementation named OneToManyResultSetExtractor that is provided by the Spring Data JDBC Extension project. Example 5-9 gives an outline of what the OneToManyResultSetExtractor provides.

public abstract class OneToManyResultSetExtractor<R, C, K> 
    implements ResultSetExtractor<List<R>> {

  public enum ExpectedResults {
    ANY,
    ONE_AND_ONLY_ONE,
    ONE_OR_NONE,
    AT_LEAST_ONE
  }

  protected final ExpectedResults expectedResults;
  protected final RowMapper<R> rootMapper;
  protected final RowMapper<C> childMapper;

  protected List<R> results;

  public OneToManyResultSetExtractor(RowMapper<R> rootMapper, RowMapper<C> childMapper) {
    this(rootMapper, childMapper, null);
  }

  public OneToManyResultSetExtractor(RowMapper<R> rootMapper, RowMapper<C> childMapper, 
      ExpectedResults expectedResults) {

    Assert.notNull(rootMapper);
    Assert.notNull(childMapper);

    this.rootMapper = rootMapper;
    this.childMapper = childMapper;
    this.expectedResults = expectedResults == null ? ExpectedResults.ANY : expectedResults;
  }

  public List<R> extractData(ResultSet rs) throws SQLException, DataAccessException { … }

  /**
   * Map the primary key value to the required type.
   * This method must be implemented by subclasses.
   * This method should not call {@link ResultSet#next()} 
   * It is only supposed to map values of the current row.
   *
   * @param rs the ResultSet
   * @return the primary key value
   * @throws SQLException
   */
  protected abstract K mapPrimaryKey(ResultSet rs) throws SQLException;

  /**
   * Map the foreign key value to the required type.
   * This method must be implemented by subclasses.
   * This method should not call {@link ResultSet#next()}.
   * It is only supposed to map values of the current row.
   *
   * @param rs the ResultSet
   * @return the foreign key value
   * @throws SQLException
   */
  protected abstract K mapForeignKey(ResultSet rs) throws SQLException;

  /**
   * Add the child object to the root object
   * This method must be implemented by subclasses.
   *
   * @param root the Root object
   * @param child the Child object
   */
  protected abstract void addChild(R root, C child);
}

This OneToManyResultSetExtractor extends the ResultSetExtractor, parameterized with List<T> as the return type. The method extractData is responsible for iterating over the ResultSet and extracting row data. The OneToManyResultSetExtractor has three abstract methods that must be implemented by subclasses mapPrimaryKey, mapForeignKey, and addChild. These methods are used when iterating over the result set to identify both the primary key and the foreign key so we can determine when there is a new root, and to help add the mapped child objects to the root object.

The OneToManyResultSetExtractor class also needs RowMapper implementations to provide the mapping required for the root and child objects.

Now, let’s move on and look at the actual implementation of the CustomerListExtractor responsible for extracting the results of our customer and address results. See Example 5-10.

private static class CustomerListExtractor 
  extends OneToManyResultSetExtractor<Customer, Address, Integer> {

  private static final QCustomer qCustomer = QCustomer.customer;

  private final QAddress qAddress = QAddress.address;

  public CustomerListExtractor() {
    super(new CustomerMapper(), new AddressMapper());
  }

  public CustomerListExtractor(ExpectedResults expectedResults) {
    super(new CustomerMapper(), new AddressMapper(), expectedResults);
  }

  @Override
  protected Integer mapPrimaryKey(ResultSet rs) throws SQLException {
    return rs.getInt(qCustomer.id.toString());
  }

  @Override
  protected Integer mapForeignKey(ResultSet rs) throws SQLException {
    String columnName = qAddress.addressCustomerRef.getLocalColumns().get(0).toString();
    if (rs.getObject(columnName) != null) {
      return rs.getInt(columnName);
    } else {
      return null;
    }
  }

  @Override
  protected void addChild(Customer root, Address child) {
    root.addAddress(child);
  }
}

The CustomerListExtractor extends this OneToManyResultSetExtractor, calling the superconstructor passing in the needed mappers for the Customer class, CustomerMapper (which is the root of the one-to-many relationship), and the mapper for the Address class, AddressMapper (which is the child of the same one-to-many relationship).

In addition to these two mappers, we need to provide implementations for the mapPrimaryKey, mapForeignKey, and addChild methods of the abstract OneToManyResultSetExtractor class.

Next, we will take a look at the RowMapper implementations we are using.

The RowMapper implementations we are using are just what you would use with the regular JdbcTemplate. They implement a method named mapRow with a ResultSet and the row number as parameters. The only difference with using a QueryDslJdbcTemplate is that instead of accessing the columns with string literals, you use the query types to reference the column labels. In the CustomerRepository, we provide a static method for extracting this label via the toString method of the Path:

private static String columnLabel(Path<?> path) {
  return path.toString();
}

Since we implement the RowMappers as static inner classes, they have access to this private static method.

First, let’s look at the mapper for the Customer object. As you can see in Example 5-11, we reference columns specified in the qCustomer reference to the QCustomer query type.

private static class CustomerMapper implements RowMapper<Customer> {

  private static final QCustomer qCustomer = QCustomer.customer;

  @Override
  public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
    
    Customer c = new Customer();
    
    c.setId(rs.getLong(columnLabel(qCustomer.id)));
    c.setFirstName(rs.getString(columnLabel(qCustomer.firstName)));
    c.setLastName(rs.getString(columnLabel(qCustomer.lastName)));

    if (rs.getString(columnLabel(qCustomer.emailAddress)) != null) {
      c.setEmailAddress(
          new EmailAddress(rs.getString(columnLabel(qCustomer.emailAddress))));
    }

    return c;
  }
}

Next, we look at the mapper for the Address objects, using a qAddress reference to the QAddress query type (Example 5-12).

private static class AddressMapper implements RowMapper<Address> {

  private final QAddress qAddress = QAddress.address;

  @Override
  public Address mapRow(ResultSet rs, int rowNum) throws SQLException {

    String street = rs.getString(columnLabel(qAddress.street));
    String city = rs.getString(columnLabel(qAddress.city));
    String country = rs.getString(columnLabel(qAddress.country));

    Address a = new Address(street, city, country);
    a.setId(rs.getLong(columnLabel(qAddress.id)));

    return a;
  }
}

Since the Address class has setters for all properties, we could have used a standard Spring BeanPropertyRowMapper instead of providing a custom implementation.

When it comes to querying for a list of objects, the process is exactly the same as for querying for a single object except that you now can use the CustomerListExtractor directly without having to wrap it and get the first object of the List. See Example 5-13.

@Transactional(readOnly = true)
public List<Customer> findAll() {

  SQLQuery allCustomersQuery = template.newSqlQuery()
      .from(qCustomer)
      .leftJoin(qCustomer._addressCustomerRef, qAddress);

  return template.query(
      allCustomersQuery,
      new CustomerListExtractor(),
      customerAddressProjection);
}

We create an SQLQuery using the from(…) and leftJoin(…) methods, but this time we don’t provide a predicate since we want all customers returned. When we execute this query, we use the CustomerListExtractor directly and the same customerAddressProjection that we used earlier.

When you want to insert some data into the database, Querydsl provides the SQLInsertClause class. Depending on whether your tables autogenerate the key or you provide the key explicitly, there are two different execute(…) methods. For the case where the keys are autogenerated, you would use the executeWithKey(…) method. This method will return the generated key so you can set that on your domain object. When you provide the key, you instead use the execute method, which returns the number of affected rows. The QueryDslJdbcTemplate has two corresponding methods: insertWithKey(…) and insert(…).

We are using autogenerated keys, so we will be using the insertWithKey(…) method for our inserts, as shown in Example 5-14. The insertWithKey(…) method takes a reference to the query type and a callback of type SqlInsertWithKeyCallback parameterized with the type of the generated key. The SqlInsertWithKeyCallback callback interface has a single method named doInSqlInsertWithKeyClause(…). This method has the SQLInsertClause as its parameter. We need to set the values using this SQLInsertClause and then call executeWithKey(…). The key that gets returned from this call is the return value of the doInSqlInsertWithKeyClause.

Long generatedKey = qdslTemplate.insertWithKey(qCustomer, 
  new SqlInsertWithKeyCallback<Long>() {

    @Override
    public Long doInSqlInsertWithKeyClause(SQLInsertClause insert) throws SQLException {

      EmailAddress emailAddress = customer.getEmailAddress();
      String emailAddressString = emailAddress == null ? null : emailAddress.toString(); 

      return insert.columns(
              qCustomer.firstName, qCustomer.lastName, qCustomer.emailAddress)
          .values(customer.getFirstName(), customer.getLastName(), emailAddress);
          .executeWithKey(qCustomer.id);
    }
  });

customer.setId(generatedKey);

Performing an update operation is very similar to the insert except that we don’t have to worry about generated keys. The method on the QueryDslJdbcTemplate is called update, and it expects a reference to the query type and a callback of type SqlUpdateCallback. The SqlUpdateCallback has the single method doInSqlUpdateClause(…) with the SQLUpdateClause as the only parameter. After setting the values for the update and specifying the where clause, we call execute on the SQLUpdateClause, which returns an update count. This update count is also the value we need to return from this callback. See Example 5-15.

qdslTemplate.update(qCustomer, new SqlUpdateCallback() {

  @Override
  public long doInSqlUpdateClause(SQLUpdateClause update) {

    EmailAddress emailAddress = customer.getEmailAddress();
    String emailAddressString = emailAddress == null ? null : emailAddress.toString(); 

    return update.where(qCustomer.id.eq(customer.getId()))
        .set(qCustomer.firstName, customer.getFirstName())
        .set(qCustomer.lastName, customer.getLastName())
        .set(qCustomer.emailAddress, emailAddressString)
        .execute();
  }
});

Deleting is even simpler than updating. The QueryDslJdbcTemplate method you use is called delete, and it expects a reference to the query type and a callback of type SqlDeleteCallback. The SqlDeleteCallback has the single method doInSqlDeleteClause with the SQLDeleteClause as the only parameter. There’s no need to set any values here—just provide the where clause and call execute. See Example 5-16.

qdslTemplate.delete(qCustomer, new SqlDeleteCallback() {

  @Override
  public long doInSqlDeleteClause(SQLDeleteClause delete) {
    return delete.where(qCustomer.id.eq(customer.getId())).execute();
  }
});