Read Array at index in jpa Query

Question

I have a native Query that return a list of objects, i need to pass an array as parameter to the function getAllUsers.

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    @Query(nativeQuery = true, value = "SELECT id FROM users WHERE code1=(?1)[0] AND code2=(?1)[1]")
    public List<Object[]> getAllUsers(List<String> list);
}

The problem was that i can't get values of the parameter list in the query : code1=(?1)[0] AND code2=(?1)[1].

I tried to use Types :

public List<Object[]> getAllUsers(String[] list);

public List<Object[]> getAllUsers(String ...list);

But always without result

Many thanks For any help

Answer 1

I'm not sure that @Query is the right way to do the dynamic query. You should consider the CriteriaAPI usage. You could try to use the Specification or QueryDsl https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl

Example of the solution your problem via Specification:

@Repository
public interface UserRepository 
    extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User> { 

}

@Service
@RequiredAllArgConstuctor
public class UserService {
    
    private final UserRepository userRepository;
    
    public List<User> getAllUsers(List<String> codeArguments) {
        var specification = (Specification<T>) (root, query, criteriaBuilder) -> {
            var predicates = new Predicate[codeArguments.size()];
            for (int i = 0; i < codeArguments.size(); i++) {
                var predicate = criteriaBuilder.equal(root.get("code" + (i + 1)), codeArguments.get(i));
                predicates[i] = (predicate);
            }
            return criteriaBuilder.and(predicates);
        };
       
        return userRepository.findAll(specification);
    }
}