Query With IN Clause in Spring Data Cassandra

1. Overview

In this tutorial, we’ll learn how to implement a query to fetch multiple records using Spring Data Cassandra.

We’ll implement the query using the IN clause to specify multiple values for a column. We’ll also see an unexpected error while testing this.

Finally, we’ll understand the root cause and fix the problem.

2. Implementing Query With IN Operator in Spring Data Cassandra

Let’s imagine we need to build a simple application that queries the Cassandra database to fetch one or more records.

We can use IN, an equality condition operator, in the WHERE clause to specify multiple possible values for a column.

2.1. Understanding Usage of IN Operator

Let’s understand the usage of this operator before building the application.

The IN condition is allowed on the last column of the partition key only if we query all preceding key columns for equality. Similarly, we can use it in any clustering key column following the same rule.

We’ll see this with an example on the product table:

CREATE TABLE mykeyspace.product (
    product_id uuid,
    product_name text,
    description text,
    price float,
    PRIMARY KEY (product_id, product_name)
)

Let’s imagine we try to find the products having the same set of product_id:

cqlsh:mykeyspace> select * from product where product_id in (2c11bbcd-4587-4d15-bb57-4b23a546bd7e, 2c11bbcd-4587-4d15-bb57-4b23a546bd22);

 product_id                           | product_name | description     | price
--------------------------------------+--------------+-----------------+-------
 2c11bbcd-4587-4d15-bb57-4b23a546bd22 |       banana |    banana |  6.05
 2c11bbcd-4587-4d15-bb57-4b23a546bd22 |    banana v2 | banana v2 |  8.05
 2c11bbcd-4587-4d15-bb57-4b23a546bd22 |    banana v3 | banana v3 |  6.25
 2c11bbcd-4587-4d15-bb57-4b23a546bd7e |    banana chips | banana chips | 10.05

In the above query, we applied the IN clause on the product_id column, and there are no other preceding primary keys to include.

Similarly, we find all products having the same product names:

cqlsh:mykeyspace> select * from product where product_id = 2c11bbcd-4587-4d15-bb57-4b23a546bd22 and product_name in ('banana', 'banana v2');

 product_id                           | product_name | description     | price
--------------------------------------+--------------+-----------------+-------
 2c11bbcd-4587-4d15-bb57-4b23a546bd22 |       banana |    banana |  6.05
 2c11bbcd-4587-4d15-bb57-4b23a546bd22 |    banana v2 | banana v2 |  8.05

In the above query, we applied the equality check on all preceding keys, i.e., product_id.

We should note that the where clause should contain the columns in the same order defined in the primary key clause.

Next, we’ll implement this query in the Spring data application.

2.2. Maven Dependencies

We’ll add the spring-boot-starter-data-cassandra dependency:

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

2.3. Implement the Spring Data Repository

Let’s implement the query by extending the CassandraRepository interface.

First, we’ll implement the above product table with a few properties:

@Table
public class Product {

    @PrimaryKeyColumn(name = "product_id", ordinal = 0, type = PrimaryKeyType.PARTITIONED)
    private UUID productId;

    @PrimaryKeyColumn(name = "product_name", ordinal = 1, type = PrimaryKeyType.CLUSTERED)
    private String productName;

    @Column("description")
    private String description;

    @Column("price")
    private double price;
}

In the above Product class, we’ve annotated productId as partitioned key and productName as the clustered key. Both these columns together form the primary key.

Now, let’s imagine we try to find all products matching a single productId and multiple productName.

We’ll the implement the ProductRepository interface with the IN query:

@Repository
public interface ProductRepository extends CassandraRepository<Product, UUID> {
    @Query("select * from product where product_id = :productId and product_name in :productNames")
    List<Product> findByProductIdAndNames(@Param("productId") UUID productId, @Param("productNames") String[] productNames);
}

In the above query, we’re passing productId as UUID and productNames as array type to fetch matching products.

Cassandra doesn’t allow queries for non-primary key columns when all the primary keys aren’t included. This is due to the performance unpredictability in executing such queries across multiple nodes.

Alternatively, we can use IN or any other condition on any column using the ALLOW FILTERING option:

cqlsh:mykeyspace> select * from product where product_name in ('banana', 'apple') and price=6.05 ALLOW FILTERING;

The ALLOW FILTERING option might have a potential performance impact, and we should use it with caution.

3. Implement the Test for ProductRepository

Let’s now implement a test case for ProductRepository by using a Cassandra container instance.

3.1. Setup the Test Container

To experiment, we’ll need a test container to run Cassandra. We’ll setup the container using the testcontainers library.

We should note that testcontainers library requires a running Docker environment to function.

Let’s add the testcontainers and testcontainers-cassandra dependencies:

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>testcontainers</artifactId>
    <version>1.19.0</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>cassandra</artifactId>
    <version>1.19.0</version>
    <scope>test</scope>
</dependency>

3.2. Start the Test Container

First, we’ll set the test class with the Testcontainers annotation:

@Testcontainers
@SpringBootTest
class ProductRepositoryIntegrationTest { }

Next, we’ll define the Cassandra container object and expose it on a specified port:

@Container
private static final CassandraContainer cassandra = new CassandraContainer("cassandra:3.11.2")
  .withExposedPorts(9042);

Finally, let’s configure a few connection-related properties and create the Keyspace:

@BeforeAll
static void setupCassandraConnectionProperties() {
    System.setProperty("spring.cassandra.keyspace-name", "mykeyspace");
    System.setProperty("spring.cassandra.contact-points", cassandra.getHost());
    System.setProperty("spring.cassandra.port", String.valueOf(cassandra.getMappedPort(9042)));
    createKeyspace(cassandra.getCluster());
}

static void createKeyspace(Cluster cluster) {
    try (Session session = cluster.connect()) {
       session.execute("CREATE KEYSPACE IF NOT EXISTS " + KEYSPACE_NAME + " WITH replication = \n" +
         "{'class':'SimpleStrategy','replication_factor':'1'};");
    }
}

3.3. Implement the Integration Test

To test, we’ll retrieve some existing products using the above ProductRepository query.

Now, let’s complete the test and verify the retrieval functionality:

UUID productId1 = UUIDs.timeBased();
Product product1 = new Product(productId1, "Apple", "Apple v1", 12.5);
Product product2 = new Product(productId1, "Apple v2", "Apple v2", 15.5);
UUID productId2 = UUIDs.timeBased();
Product product3 = new Product(productId2, "Banana", "Banana v1", 5.5);
Product product4 = new Product(productId2, "Banana v2", "Banana v2", 15.5);
productRepository.saveAll(List.of(product1, product2, product3, product4));

List<Product> existingProducts = productRepository.findByProductIdAndNames(productId1, new String[] {"Apple", "Apple v2"});
assertEquals(2, existingProducts.size());
assertTrue(existingProducts.contains(product1));
assertTrue(existingProducts.contains(product2));

It’s expected that the above test should pass. Instead, we’ll get an unexpected error from ProductRepository:

com.datastax.oss.driver.api.core.type.codec.CodecNotFoundException: Codec not found for requested operation: [List(TEXT, not frozen]
<-> [Ljava.lang.String;]
	at com.datastax.oss.driver.internal.core.type.codec.registry.CachingCodecRegistry.createCodec(CachingCodecRegistry.java:609)
	at com.datastax.oss.driver.internal.core.type.codec.registry.DefaultCodecRegistry$1.load(DefaultCodecRegistry.java:95)
	at com.datastax.oss.driver.internal.core.type.codec.registry.DefaultCodecRegistry$1.load(DefaultCodecRegistry.java:92)
	at com.datastax.oss.driver.shaded.guava.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3527)
	....
	at com.datastax.oss.driver.internal.core.data.ValuesHelper.encodePreparedValues(ValuesHelper.java:112)
	at com.datastax.oss.driver.internal.core.cql.DefaultPreparedStatement.boundStatementBuilder(DefaultPreparedStatement.java:187)
	at org.springframework.data.cassandra.core.PreparedStatementDelegate.bind(PreparedStatementDelegate.java:59)
	at org.springframework.data.cassandra.core.CassandraTemplate$PreparedStatementHandler.bindValues(CassandraTemplate.java:1117)
	at org.springframework.data.cassandra.core.cql.CqlTemplate.query(CqlTemplate.java:541)
	at org.springframework.data.cassandra.core.cql.CqlTemplate.query(CqlTemplate.java:571)...
	at com.sun.proxy.$Proxy90.findByProductIdAndNames(Unknown Source)
	at org.baeldung.inquery.ProductRepositoryIntegrationTest$ProductRepositoryLiveTest.givenExistingProducts_whenFindByProductIdAndNames_thenProductsIsFetched(ProductRepositoryNestedLiveTest.java:113)

Next, let’s investigate the error in detail.

3.4. Root Cause of the Error

The above log indicates that the test failed to fetch products with an internal CodecNotFoundException exception. The CodecNotFoundException exception indicates that the query parameter type isn’t found for the requested operation.

The exception class shows that the codec isn’t found for the cqlType and its corresponding javaType:

public CodecNotFoundException(@Nullable DataType cqlType, @Nullable GenericType<?> javaType) {
    this(String.format("Codec not found for requested operation: [%s <-> %s]", cqlType, javaType), (Throwable)null, cqlType, javaType);
}

The CQL data type includes all the usual primitive, collections and user-defined types, but the array isn’t allowed. In some earlier version of Spring Data Cassandra like 1.3.x, the List type was also not supported.

4. Fixing the Query

To fix the error, we’ll add a valid query parameter type in the ProductRepository interface.

We’ll change the request parameter type to List from array:

@Query("select * from product where product_id = :productId and product_name in :productNames")
List<Product> findByProductIdAndNames(@Param("productId") UUID productId, @Param("productNames") List<String> productNames);

Finally, we’ll re-run the test and validate if the query works:

givenExistingProducts_whenFindByIdAndNamesIsCalled_thenProductIsReturned: 1 total, 1 passed

5. Conclusion

In this article, we learned how to implement the IN query clause in Cassandra using Spring Data Cassandra. We also faced an unexpected error while testing and understood the root cause. We saw how to fix the problem using a valid Collection type in the method parameter.