Why does my JpaRepository (spring-data-jpa) custom query return me an empty list?-CodePudding

i'm trying to develop a webapp where users post their ads, I'm currently working on the research an ad section, it's just a form that sends a search model (it has a string which is the text written on the search bar and some non-required filters) to the backend (spring boot), the backend gets the string and gives it to a JpaRepository method that should do a query on my oracle database and give me back the result set as a list of AdEntity, only problem is the list is always empty, this is the method:

@Query(value = "select distinct a.ann_id, a.nome, a.autore, a.descrizione, a.prodotto, a.prezzo "
          "from annunci a, prodotti p "
          "where a.prodotto = p.prod_id  "
          "and (a.nome like '%:main%' or p.nome like '%:main%')", nativeQuery = true)
public List<AnnuncioEntity> searchWithoutFilters(String main);

and this is where I call it:

@PostMapping("/")
public List<FullAnnuncio> searchAnnunci(@RequestBody SearchEntity search) {
    List<AnnuncioEntity> li = annuncioDAO.searchWithoutFilters(search.getMain());
    return null;
}

The li list is always empty (checked with debugger) and I have no idea how to fix it, I'm sure the query works and produces the expected resultSet as I tried it on sqlDeveloper first, and no the string I get from search isn't null

Help pls I'm new to these things as I'm not even a junior programmer lmao

CodePudding user response：

Try changing the query ":main" to "?1" in both occurrences

CodePudding user response：

Done, the Like clause on jpa repository kinda works like dodoo so I had to write it like this:

LIKE CONCAT(CONCAT('%', :main), '%')

(the sintax is correct only with oracle databases tho so be careful, for mySql and others write it like this:

LIKE CONCAT('%', :param, '%')

thanks @Arnaud