spring boot search with mysql native query with LIKE return empty

0
votes

I have table with city and country together. I try select city and country name by some first letters with LIKE but everytime it returns empty list. In MySql Workbench this query return result. I try many version but nothing work.

@Query(value = "SELECT city, country FROM test8.cities WHERE city LIKE ?'%'", nativeQuery = true)
List<City> findCity(String str);

I try and this methods also return empty list.

   List<City> findByCityStartsWith(String str);
   List<City> findTop20DistinctByCityIsStartingWith(String str);

@Query(value = "SELECT city, country FROM test8.cities WHERE city LIKE ?1", nativeQuery = true) List findByCityIsStartingWith(String str);

@Query(value = "select city, country FROM test8.cities WHERE city = :str", nativeQuery = true) List findByCity2(@Param("str")String str);

1
mysqlsqlspringspring-bootjpa
Your param str should be something like %someCity% Can you share value of string parameter @Param("str")String str? also, you can use Containing, Contains, IsContaining – Lemmy
@GetMapping("/cities") public @ResponseBody List<City> getCitiesCountry(@RequestParam("searchChars") String searchChars){ List<States> resultList = countryRepo.findCity(searchChars); return resultList; } – Togrul Sadigov
What is the value of searchChars? You should wrap it with %. Your query looks like: select city, country FROM test8.cities WHERE city like ‘test’ instead of ‘%test%’ – Lemmy
I select city with first letters every press on input type should get new List of cities so I add +'%' after ? wildcard. – Togrul Sadigov

1 Answers

0
votes

Problem was in entity that was changed.