11

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')

Bill H
  • 470
  • 1
  • 6
  • 12
  • 1
    did you try any Collection type? Set or List of strings instead of String array ? – Mustafa Genç Apr 02 '15 at 19:53
  • Both Lists and Sets return an error similar to this on startup `Caused by: java.lang.IllegalArgumentException: encountered unsupported query parameter type [interface java.util.Set]` – Bill H Apr 03 '15 at 20:40

3 Answers3

11

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);
Faraj Farook
  • 14,385
  • 16
  • 71
  • 97
  • I wish I could use named parameters, but since I'm actually passing in 7, I end up with `Invalid amount of bind variable` exception. I am able to get it working when passing in a single string to constrain on, but when passing in an array I see this on startup `Caused by: java.lang.IllegalArgumentException: encountered unsupported query parameter type [class [Ljava.lang.String;]` – Bill H Apr 03 '15 at 20:44
  • 1
    Then can you try the first one. your same query with bracets. Like `IN (?1)` – Faraj Farook Apr 03 '15 at 21:06
  • If I pass in a single name, it does work. If I pass multiple, it gets wrapped in quotes so the query ends up `IN ('Joe,Jim')`. Obviously, no one is named 'Joe,Jim'. I also tried building a request string, `'Joe','Jim'` but when it gets passed in the library escapes all the quotes and I end up with `'''Joe'',''Jim'''` – Bill H Apr 06 '15 at 19:05
  • I suppose these queries are in interfaces so you don't need public modifier – walv Nov 09 '17 at 16:33
  • 4
    With current spring Cassandra it works if you don't have braces.```@Query("SELECT * FROM people WHERE last_name=?0 AND first_name IN ?1") public List findByLastName(String lastName, List firstName); ``` – Nitish Bhagat Dec 20 '17 at 09:47
  • without brakets and using List collection it works for me – Jayyrus Sep 12 '18 at 09:43
  • @NitishBhagat - Bulls eye! Thanks. This should also be one of the valid Answers.This solved my issue in `2.0.13.Release` . I was doing IN on List of UUIDs. Now changed it like this - `@Query("SELECT * FROM users where id in ?0")` – Angshuman Agarwal Oct 08 '19 at 14:35
3

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);

davey
  • 1,666
  • 17
  • 24
2

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.

Visruth
  • 3,430
  • 35
  • 48