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

the query you write is handled by the database, which has no clue what[0] and [1] mean, or what a java.util.Array is that is passed in as the single ?1 parameter. If you have multiple parameters to pass in to a query, you need to break them up as multiple parameters for your method as well ie: getAllUsersByCode1AndCode2(String code1, String code2) method. — Chris
– Chris, Commented May 4, 2022 at 16:41
does code1 & code2 are different columns in users table? if yes, then syntax is wrong & you might need to use 2 arguments in your method. — Ashish Patil
– Ashish Patil, Commented May 4, 2022 at 16:41
I used used code1 and code2 as example, in fact i have 24 : code1, code2, code3, ... code24, this is why it's not practical to use parameters ... also i can add codes (code25, 26, ...) — Pro Grammer
– Pro Grammer, Commented May 4, 2022 at 16:47
If you have different columns, then using list as method argument won't be correct. Also I am wondering if you really need to input 24 columns as where clause of your query. — Ashish Patil
– Ashish Patil, Commented May 5, 2022 at 9:04
@AshishPatil, i have a constraint to use those values as columns. — Pro Grammer
– Pro Grammer, Commented May 5, 2022 at 10:17

Eugene Maksymets · Accepted Answer · 2022-05-12 11:36:08Z

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

Collectives™ on Stack Overflow

Read Array at index in jpa Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related