@Query Annotation doesn't fill and execute parameters

Question

I am using the Hibernate JPA with Spring Boot, and I am having a trouble returning results from a CRUDRepository where I have written a custom SQL query using the @Query tag. When I access the DAO and execute the method, it fires fine, but nothing is returned from the query.

public interface ContactRepository extends CrudRepository<Contact, BigInteger> {

    @Query(value ="SELECT * FROM all_contacts ac WHERE ac.userid= :userId " +
          "AND LOWER(ac.city) LIKE CONCAT('%', LOWER(:city), '%')",
          nativeQuery=true)
    List<Contact> findAllUserContactsByCityPartialMatch(@Param("city") String city, @Param("userId") BigInteger userId);

}

and when I fire off the code in a Model or Controller somewhere:

@Autowired
ContactRepository contactDAO

List<Contact> results = contactDAO.findAllUserContactsByCityPartialMatch("Chicago", 12345);

results will always be empty

My Logger tells me it's firing off this query:

SELECT * FROM all_contacts ac 
WHERE ac.userid= ? AND LOWER(ac.city) LIKE CONCAT('%', LOWER(?), '%')

I know the query is fine, because when I run the query and fill the fields manually in MySQL terminal, I get a result back.

SELECT * FROM all_contacts ac
WHERE ac.userid= 12345 AND LOWER(ac.city) LIKE CONCAT('%', LOWER("Chicago"), '%')`

Also, I know the DB is configured properly, because if I run say

Iterable<Contact> contactItr = contactDAO.findAll();

in the same model which was mentioned earlier, I get several persisted results back from the DB.

My thought is that it's not filling the parameters or executing them with the parameters in the CRUDRepository class, but I'm not sure. Any thoughts? Thanks!

veljkost · Accepted Answer · 2017-10-10 21:50:53Z

1

Named parameters are not supported in a native query in JPA. You can use positional parameters.

Also you could make use of JPA query language without @Query at all, something like:

public interface ContactRepository extends CrudRepository<Contact, BigInteger> {

    List<Contact> findAllByCityContainingIgnoreCaseAndUserId(String city, BigInteger userId);

}

answered Oct 10, 2017 at 21:50

veljkost

1,94023 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NateH06 Over a year ago

Aha, that almost works, except that it generates an SQL query using a Like, but does not add any Wildcards, thus not returning any results. Shouldn't it be tossing in "%" 's? The end of a query like the one you wrote ends up being: from all_contacts contact0_ where (upper(contact0_.city) like upper(?)) and contact0_.userid=?

NateH06 Over a year ago

Ah, I was putting "'s around the search parameters from the client side where they were coming, so it wasn't adding the %'s properly. However, how would I search for partially completed numbers? Even just a "starts with" ? It seems to add the % in that case

Collectives™ on Stack Overflow

@Query Annotation doesn't fill and execute parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related