IN clause with Spring Data and Cassandra @Query

IN clause with Spring Data and Cassandra @Query

spring data cassandra crudrepository
spring boot cassandra crud example
spring-boot-starter-data-cassandra
spring data cassandra multiple keyspaces
spring boot cassandra template example
spring data cassandra multiple contact points
spring data cassandra batch operations example
spring data cassandra schema-action

I'm trying to query a Cassandra table using the IN clause and the @Query annotation from Spring Data. I have a table with a partition key of last_name and a clustering key of first_name.

I have this query working

@Query("SELECT * FROM people WHERE last_name=?0")
public List<People> findByLastName(String lastName);

and I would like to do something like

@Query("SELECT * FROM people WHERE last_name=?0 AND first_name IN ?1")
public List<People> findByLastName(String lastName, String[] firstName);

I have it working using

CassandraOperations.select("SELECT * FROM people WHERE last_name=" + lastName + 
" AND first_name IN (" + concatinatedNameList + ")", People.class);

But for a number of reasons (code style, testing, I swear there are more) I would prefer to use @Query. Any ideas?

EDIT FOR MORE INFO!

Passing in an array, set, or list returns Caused by: java.lang.IllegalArgumentException: encountered unsupported query parameter type [class [Ljava.lang.String;] in method public abstract

Also tried:

String firstName = "Joe,Jim";
@Query("SELECT * FROM people WHERE last_name=?0 AND first_name IN (?1)")
public List<People> findByLastName(String lastName, String firstName);

Nothing found, library searches for a single person with a concatinated name ('Joe,Jim')

String firstName = "'Joe','Jim'";
@Query("SELECT * FROM people WHERE last_name=?0 AND first_name IN (?1)")
public List<People> findByLastName(String lastName, String firstName);

Nothing found, the request is escaped and ends up ('''Joe'',''Jim''')

String firstName = "Joe','Jim"; // Hoping the library would just add the outer quotes, getting desperate
@Query("SELECT * FROM people WHERE last_name=?0 AND first_name IN (?1)")
public List<People> findByLastName(String lastName, String firstName);

Nothing found, the request is escaped and ends up ('Joe'',''Jim')


Update

With current spring, it seems to be working without braces.


Old answer

You have to use bracers when you are using IN.

@Query("SELECT * FROM people WHERE last_name=?0 AND first_name IN (?1)")
public List<People> findByLastName(String lastName, String[] firstName);

But there are some other issues in your code. I changed them all to a good coding standards as below. Including my personal favorite of using named parameters.

@Query("SELECT p FROM People p WHERE p.lastName = :lastName AND p.firstName IN (:firstNames)")
public List<People> findByName(@Param("lastName") String lastName, @Param("firstNames") String[] firstNames);

Spring Data for Apache Cassandra, Cassandra Query Language (CQL) is the query language for the Cassandra database and CqlTemplate is the low-level data access template in  Above query will select all those employees whose id is 1 or 2 or 3. While working with spring data JPA we require List  or Collection  of those records that we require to pass in IN clause. Spring data JPA support IN queries using method name, @Query annotation, or native query. Here is a complete example of spring JPA with the database.


It seems to be not possible!

I checked the source code of spring-data-cassandra-1.3.2.RELEASE.jar.

The allowed data types of parameter in the query method are String.class, CharSequence.class, char.class, Character.class, char[].class, long.class, Long.class, boolean.class, Boolean.class, BigDecimal.class, BigInteger.class, double.class, Double.class, float.class, Float.class, InetAddress.class, Date.class, UUID.class, int.class are Integer.class.

They can be found in

org.springframework.data.cassandra.repository.query.CassandraQueryMethod.ALLOWED_PARAMETER_TYPES = Collections.unmodifiableList(Arrays
            .asList(new Class<?>[] { String.class, CharSequence.class, char.class, Character.class, char[].class, long.class,
                    Long.class, boolean.class, Boolean.class, BigDecimal.class, BigInteger.class, double.class, Double.class,
                    float.class, Float.class, InetAddress.class, Date.class, UUID.class, int.class, Integer.class }));

If we pass data types other than these, then org.springframework.data.cassandra.repository.query.CassandraQueryMethod.verify(Method method, RepositoryMetadata metadata) will throw IllegalArgumentException.

Using Spring Data CassandraTemplate, Testing the connection to Cassandra through cqlsh. Cassandra uses the Cassandra Query Language (CQL), which offers a model close to SQL. To interact with  If you are totally new with spring data JPA then here is Spring data JPA tutorial which will help you to learn more about Spring Data JPA. To apply filters on the data we can use LIKE or IN query inside WHERE clause here are articles about Spring JPA Like Query and Spring JPA In query.


Nitish Bhagat is right, with the current version spring-data-cassandra:2.0.6.RELEASE it does NOT work with the braces.

For me, it works ONLY without the braces like: @Query("SELECT * FROM people WHERE first_name IN ?0") public List<People> findByFirstName(List<String> firstName);

Spring Data for Cassandra: A Complete Example, how to use Spring Data functionalites to create, read, update and query cassandra… In Cassandra you can not use column in where clause except primary  The Spring Data for Apache Cassandra project applies core Spring concepts to the development of solutions using the Cassandra Columnar data store. A “template” is provided as a high-level abstraction for storing and querying documents. This project has noticeable similarities to the JDBC support in the core Spring Framework.


Introduction to Spring Data Cassandra - HexaScale, In Cassandra, a table represents a query, which might sound very wierd to you if you normally work with relational databases that store data and  When Spring Data discovers the unsafe Sort order for a method that uses the @Query annotation, then it just appends the sort clause to the query — it skips checking whether the property to sort by belongs to the domain model.


Getting Started With Spring Data Cassandra, Sample method annotated with {@link Query}. This method executes the CQL from the {@link Query} value. * * @param id * @return */ @Query("SELECT * from  The Spring Data for Apache Cassandra project applies core Spring concepts to the development of solutions using the Cassandra Columnar data store. A “template” is provided as a high-level abstraction for storing and querying documents. This project has noticeable similarities to the JDBC support in the core Spring Framework.


Java Code Examples org.springframework.data.cassandra , I'm trying to query a Cassandra table using the IN clause and the @Query annotation from Spring Data. I have a table with a partition key of  The reasons for these differences come mainly from the fact that Cassandra is dealing with distributed data and aims to prevent inefficient queries. One of the places where CQL differs a lot from SQL is the WHERE clause. The goal of this post is to describe what is supported by the CQL WHERE clause and the reasons why it differs from normal SQL.