CODEWITHSUNDEEP
javajpaspring-data
Channa
Channa

Reputation: 5233

Spring Data JPA Repository Queries - How to map @Param value to multiple parameter types

I have implemented following JPA Repository query for some common search functionality. But with that query, due to age is a Integer value and mapping Parameter is String value I got some exception as follow. Is there any mechanism to implicitly casting parameter to relevant data type instead of we do that. Thanks.

Query with common parameter 

  @Query("select u from User u where u.firstname = :searchText or u.age = :searchText")
  List<User> findBySearchText(@Param("searchText") String searchText);

Exception 

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying 
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Upvotes: 1

Views: 1901

Answers (1)

Oliver Drotbohm
Oliver Drotbohm

Reputation: 83061

The issue you see has nothing to do with the binding itself. Spring Data basically binds the value you give to the named parameter searchText.

It looks like what happens next is that your persistence provider builds some SQL from it where there's a type mismatch apparently. Age doesn't seem to be of type String, is it? That said, I think trying to bind an arbitrary String to an integer (which it is I guess) is a very weird approach in the first place.

SQL is not really built to support arbitrary text search features and schema is helping you to detect invalid criterias (which it does in this case). Have you thought about adding a full-text search store (Elasticsearch, Solr or the like) and do the text searches in those?

Upvotes: 3

Related Questions