Find Array Elements in Array using JPA Query in Spring Boot-CodePudding

I am trying to find to array elements in array, but getting error

@Query(
   "SELECT i FROM User i WHERE (i.personalDetail.gender = :gender) and "  
       "(:country is null or i.contactDetail.country = :country) and "  
       "(COALESCE(:state, NULL) is null or i.contactDetail.state IN (:state)) and "  
       "(COALESCE(:motherLanguage, NULL) is null or i.personalDetail.motherLanguage IN (:motherLanguage))"
    )
    Page<User> findAllUser(
            @Param("gender") Optional<String> gender,
            @Param("country") Optional<String> country,
            @Param("state") Optional<List<String>> state,
            @Param("motherLanguage") Optional<List<String>> motherLanguage,
            Pageable pageable
    );

 @RequestParam("country") Optional<String> country,
 @RequestParam("state") Optional<String[]> state,
 @RequestParam("motherLanguage") Optional<String[]> motherLanguage,



Page<User> pageObj = userRepository.findAllUser(
                gender,
                country,
                Optional.of(Arrays.asList(state.orElse(new String[] { }))),                Optional.of(Arrays.asList(motherLanguage.orElse(new String[] { }))),
                pageable
        );

Model

    @Column(name = "state")
    private String state;

    @Type(type = "string-array")
    @Column(
            name = "mother_language",
            columnDefinition = "text[]"
    )
    private String[] motherLanguage;

I found that the error is due to this line in Query

"(COALESCE(:motherLanguage, NULL) is null or i.personalDetail.motherLanguage IN (:motherLanguage))"

My query properly properly works for state parameter because state is just a string, but motherLanguageis an array.

Basically i want to know how i can query in array using array. If found something on internet SELECT ARRAY[1,2] && ARRAY[1,3,4,7]; but when i tried to put && in JPA Query it is giving error

"(COALESCE(:motherLanguage, NULL) is null or i.personalDetail.motherLanguage && (:motherLanguage))"

This is the ERROR For Bellow Query

"(:motherLanguage is null or i.personalDetail.motherLanguage IN :motherLanguage) and "

There was an unexpected error (type=Internal Server Error, status=500).
Parameter value element [Hindi] did not match expected type [[Ljava.lang.String; (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value element [Hindi] did not match expected type [[Ljava.lang.String; (n/a)]
org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value element [Hindi] did not match expected type [[Ljava.lang.String; (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value element [Hindi] did not match expected type [[Ljava.lang.String; (n/a)]

CodePudding user response：

You should rewrite the JPQL query as this:

@Query(
    "SELECT i FROM User OR WHERE (i.personalDetail.gender = :gender) AND "  
    "(:country IS NULL OR i.contactDetail.country = :country) AND "  
    "(:state IS NULL OR i.contactDetail.state IN :state) AND "  
    "(:motherLanguage IS NULL OR i.personalDetail.motherLanguage IN :motherLanguage)"
)

JPA should be smart enough to compare a bound collection directly against null. There is no need to use COALESCE() as you were using it.

CodePudding user response：

Try with two parameters :

(:motherLanguageB IS NULL OR i.personalDetail.motherLanguage IN :motherLanguage)

Force to null if list is empty :

if(motherLanguages != null && motherLanguages.isEmpty()) motherLanguages = null;

And set the parameters (don't know how to this with Spring Data):

 setParameter("motherLanguageB", motherLanguages == null ? null : 1)
.setParameter("motherLanguage", motherLanguages);