1. Overview
1.概述
In this tutorial, we’ll learn how to implement a query to fetch multiple records using Spring Data Cassandra.
在本教程中，我们将学习如何使用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.
我们将使用 IN 子句实现查询，为一列指定多个值。在测试过程中，我们还将看到一个意外错误。
Finally, we’ll understand the root cause and fix the problem.
最后，我们将了解根本原因并解决问题。
2. Implementing Query With IN Operator in Spring Data Cassandra
2.在 Spring Data Cassandra 中使用 IN 操作符实现查询
Let’s imagine we need to build a simple application that queries the Cassandra database to fetch one or more records.
假设我们需要构建一个简单的应用程序，查询 Cassandra 数据库以获取一条或多条记录。
We can use IN, an equality condition operator, in the WHERE clause to specify multiple possible values for a column.
我们可以在WHERE条款中使用 IN（相等条件操作符），为列指定多个可能的值。
2.1. Understanding Usage of IN Operator
2.1.了解IN操作符的用法
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.
IN 条件只允许在分区关键字的最后一列中使用 如果我们查询前面所有关键字列是否相等。同样，我们也可以按照相同的规则在任何聚类关键字列中使用它。
We’ll see this with an example on the product table:
我们将以 product 表为例说明这一点：
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:
假设我们要查找具有相同 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.05In the above query, we applied the IN clause on the product_id column, and there are no other preceding primary keys to include.
在上述查询中，我们在 product_id 列上应用了IN子句，前面没有包含其他主键。
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.05In the above query, we applied the equality check on all preceding keys, i.e., product_id.
在上述查询中，我们对所有前面的键，即 product_id 进行了相等检查。
We should note that the where clause should contain the columns in the same order defined in the primary key clause.
我们应该注意，where子句应按照 primary key 子句中定义的相同顺序包含列。
Next, we’ll implement this query in the Spring data application.
接下来，我们将在 Spring 数据应用程序中实现这一查询。
2.2. Maven Dependencies
2.2.Maven 依赖项
We’ll add the spring-boot-starter-data-cassandra dependency:
我们将添加 spring-boot-starter-data-cassandra 依赖关系：
<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
2.3.实现 Spring 数据存储库
Let’s implement the query by extending the CassandraRepository interface.
让我们通过扩展 CassandraRepository 接口来实现查询。
First, we’ll implement the above product table with a few properties:
首先，我们将通过几个属性来实现上述 product 表：
@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.
在上述 Product 类中，我们将 productId 注释为分区键，将 productName 注释为聚类键。这两列共同构成主键。
Now, let’s imagine we try to find all products matching a single productId and multiple productName.
现在，让我们设想一下，我们试图找到与单个 productId 和多个 productName 匹配的所有产品。
We’ll the implement the ProductRepository interface with the IN query:
我们将使用 IN查询来实现 ProductRepository 接口：
@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.
在上述查询中，我们传递 productId 作为 UUID 和 productNames 作为数组类型，以获取匹配的产品。
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.
Cassandra 不允许在不包含所有主键的情况下查询非主键列。这是因为在多个节点上执行此类查询时性能不可预测。
Alternatively, we can use IN or any other condition on any column using the ALLOW FILTERING option:
或者，我们可以使用 IN 或其他条件，使用ALLOW FILTERING选项对任何列进行过滤：
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.
ALLOW FILTERING（允许过滤）选项可能会影响性能，我们应谨慎使用。
3. Implement the Test for ProductRepository
3.为 ProductRepository 实施测试
Let’s now implement a test case for ProductRepository by using a Cassandra container instance.
现在，让我们使用 Cassandra 容器实例来实现 ProductRepository 的测试用例。
3.1. Setup the Test Container
3.1.设置测试容器
To experiment, we’ll need a test container to run Cassandra. We’ll setup the container using the testcontainers library.
要进行实验，我们需要一个测试容器来运行 Cassandra。我们将使用 testcontainers 库设置容器。
We should note that testcontainers library requires a running Docker environment to function.
我们应该注意，testcontainers库需要运行 Docker 环境才能运行。
Let’s add the testcontainers and testcontainers-cassandra dependencies:
让我们添加 testcontainers 和 testcontainers-cassandra 依赖项：
<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
3.2.启动测试容器
First, we’ll set the test class with the Testcontainers annotation:
首先，我们将使用 Testcontainers 注解设置测试类：
@Testcontainers
@SpringBootTest
class ProductRepositoryIntegrationTest { }Next, we’ll define the Cassandra container object and expose it on a specified port:
接下来，我们将定义 Cassandra 容器对象，并在指定端口上公开它：
@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:
最后，让我们配置一些与连接相关的属性，并创建 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
3.3.实施集成测试
To test, we’ll retrieve some existing products using the above ProductRepository query.
为了进行测试，我们将使用上述 ProductRepository 查询检索一些现有产品。
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:
预计上述测试应该会通过。相反，我们会从 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
3.4.错误根源
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.
上述日志显示，测试因内部 CodecNotFoundException 异常而无法获取产品。CodecNotFoundException异常表明查询参数类型未找到所请求的操作。
The exception class shows that the codec isn’t found for the cqlType and its corresponding javaType:
异常类显示没有为 cqlType 及其对应的 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.
CQL 数据类型包括所有常用的基元、集合和用户定义类型，但不允许使用数组。在 Spring Data Cassandra 的某些早期版本（如 1.3.x）中，也不支持 List 类型。
4. Fixing the Query
4.修复查询
To fix the error, we’ll add a valid query parameter type in the ProductRepository interface.
为修复错误，我们将在 ProductRepository 接口中添加 有效查询参数类型.。
We’ll change the request parameter type to List from array:
我们将把请求参数类型改为 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 passed5. Conclusion
5.结论
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.
在本文中，我们学习了如何使用 Spring Data Cassandra 在 Cassandra 中实现 IN 查询子句。在测试过程中，我们还遇到了一个意想不到的错误，并了解了其根本原因。我们了解了如何在方法参数中使用有效的 Collection 类型来解决问题。
As always, the example code can be found over on GitHub.
和往常一样，您可以在 GitHub 上找到示例代码。