Lanky Dan Blog

Cached Prepared Statements with Spring Data Cassandra

October 23, 2018

springspring dataspring bootspring data cassandracassandrajava
GITHUB REPO FOR POST

Today I have a short post on using Prepared Statements in Spring Data Cassandra. Spring provides you with some utilities to make using Prepared Statements easier rather than relying on registering queries manually yourself with the Datastax Java Driver. The Spring code provides a cache to store prepared statements that are frequently used. Allowing you to execute your queries via the cache which either retrieves the prepared query from the cache or adds a new one before executing it.

To keep this short we should probably start looking at some code.

Dependencies

<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>2.0.5.RELEASE</version>
</parent>

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-cassandra</artifactId>
  </dependency>
</dependencies>

Using Spring Boot 2.0.5.RELEASE will pull in 2.0.10.RELEASE of Spring Data Cassandra.

Using Prepared Statements

Let’s go straight in:

@Repository
public class PersonRepository extends SimpleCassandraRepository<Person, PersonKey> {

  private final Session session;
  private final CassandraOperations cassandraTemplate;
  private final PreparedStatementCache cache = PreparedStatementCache.create();

  public PersonRepository(
      Session session,
      CassandraEntityInformation entityInformation,
      CassandraOperations cassandraTemplate) {
    super(entityInformation, cassandraTemplate);
    this.session = session;
    this.cassandraTemplate = cassandraTemplate;
  }

  // using ORM
  public List<Person> findByFirstNameAndDateOfBirth(String firstName, LocalDate dateOfBirth) {
    return cassandraTemplate
        .getCqlOperations()
        .query(
            findByFirstNameAndDateOfBirthQuery(firstName, dateOfBirth),
            (row, rowNum) -> cassandraTemplate.getConverter().read(Person.class, row));
  }

  private BoundStatement findByFirstNameAndDateOfBirthQuery(
      String firstName, LocalDate dateOfBirth) {
    return CachedPreparedStatementCreator.of(
            cache,
            select()
                .all()
                .from("people_by_first_name")
                .where(eq("first_name", bindMarker("first_name")))
                .and(eq("date_of_birth", bindMarker("date_of_birth"))))
        .createPreparedStatement(session)
        .bind()
        .setString("first_name", firstName)
        .setDate("date_of_birth", toCqlDate(dateOfBirth));
  }

  private com.datastax.driver.core.LocalDate toCqlDate(LocalDate date) {
    return com.datastax.driver.core.LocalDate.fromYearMonthDay(
        date.getYear(), date.getMonth().getValue(), date.getDayOfMonth());
  }

  // without ORM
  public List<Person> findByFirstNameAndDateOfBirthWithoutORM(
      String firstName, LocalDate dateOfBirth) {
    return cassandraTemplate
        .getCqlOperations()
        .query(
            findByFirstNameAndDateOfBirthQuery(firstName, dateOfBirth),
            (row, rowNum) -> convert(row));
  }

  private Person convert(Row row) {
    return new Person(
        new PersonKey(
            row.getString("first_name"),
            toLocalDate(row.getDate("date_of_birth")),
            row.getUUID("person_id")),
        row.getString("last_name"),
        row.getDouble("salary"));
  }

  private LocalDate toLocalDate(com.datastax.driver.core.LocalDate date) {
    return LocalDate.of(date.getYear(), date.getMonth(), date.getDay());
  }
}

There is a reasonable amount of boilerplate code here so we can gain access to Spring Data’s ORM. I have also provided code to demonstrate how to achieve the same goal without using the ORM (well mapping straight from query to an object manually anyway).

Let’s look at one of the methods more closely:

public List<Person> findByFirstNameAndDateOfBirth(String firstName, LocalDate dateOfBirth) {
  return cassandraTemplate
      .getCqlOperations()
      .query(
          findByFirstNameAndDateOfBirthQuery(firstName, dateOfBirth),
          (row, rowNum) -> cassandraTemplate.getConverter().read(Person.class, row));
}

private BoundStatement findByFirstNameAndDateOfBirthQuery(
    String firstName, LocalDate dateOfBirth) {
  return CachedPreparedStatementCreator.of(
          cache,
          select()
              .all()
              .from("people_by_first_name")
              .where(eq("first_name", bindMarker("first_name")))
              .and(eq("date_of_birth", bindMarker("date_of_birth"))))
      .createPreparedStatement(session)
      .bind()
      .setString("first_name", firstName)
      .setDate("date_of_birth", toCqlDate(dateOfBirth));
}

CachedPreparedStatementCreator does exactly what it says… It creates cached Prepared Statements. The of method takes in the cache defined when the bean is instantiated and creates a new query as defined by the second parameter. If the query is one that has already been registered recently, i.e it is in the cache already. Then the query is pulled from there rather than going through the whole process of registering a new statement.

The query passed in is a RegularStatement which is converted to a PreparedStatement by calling createPreparedStatement (duh I guess). We are now able to bind values to query so it actually does something useful.

In terms of caching Prepared Statements, that is all you have to do. There are other ways to do it, for example, you could use the PreparedStatementCache manually or define your own cache implementation. Whatever floats your boat.

You have now reached the end of this short post, hopefully, it actually contained enough information to be useful…

In this post, we covered how to use the CachedPreparedStatementCreator to create and put Prepared Statements into a cache for faster execution at a later time. Using the classes Spring Data provides us, we can reduce the amount of code that we need to write.

The code used in this post can be found on my GitHub.

If you found this post helpful, you can follow me on Twitter at @LankyDanDev to keep up with my new posts.


Dan Newton

Saving transactions where only a subset of parties are signers

July 05, 2019
cordakotlindltdistributed ledger technologyblockchain

It took a while for me to think of a title that could summarise the contents of this post without becoming a full sentence itself. I think I…

Kotlin primitive and object arrays

June 21, 2019
kotlinjavabeginner

I initially set out to write this post because I was playing around with some reflection code and thought I found something interesting…

Preventing invalid spending of broadcasted states

June 12, 2019
cordakotlindltdistributed ledger technologyblockchain

Corda is super flexible and will allow you to put together the code needed to write many complex workflows. This flexibility does come with…

Broadcasting a transaction to external organisations

May 31, 2019
cordakotlindltdistributed ledger technologyblockchain

There is a misconception that Corda cannot broadcast data across a network. This is simply wrong. In fact, Corda can send anything between…

Running a Kotlin class as a subprocess

May 25, 2019
kotlin

Last week I wrote a post on running a Java class as a subprocess . That post was triggered by my need to run a class from within a test…