'How can i use many to one sql query with Java Spring?

I am trying to make a search bar .This search bar must find animals with own name or owner name. Animal table have user_id as foreign key . I have to find animals with their owner name for that i think joining users table to animals .I use MySQL.

AnimalRepository.java

public interface AnimalRepository extends JpaRepository<Animal,Integer > {
    public Long countById(Integer id);
    
    @Query(value = "select * from animal join users  where name like %:keyword% or where users.name like %:keyword%", nativeQuery = true)
    List<Animal> findByKeyword(@Param("keyword") String keyword);

}

SearchPageController.java

    @Autowired
    private AnimalService animalService;

    @RequestMapping(path = {"/","/search"})
    public String home(Animal shop, Model model, String keyword) {
        if(keyword!=null) {
            List<Animal> list = animalService.getByKeyword(keyword);
            model.addAttribute("list", list);
        }else {
            List<Animal> list = animalService.getAllAnimals();
            model.addAttribute("list", list);}
        return "index";
    }

AnimalService.java

    public List<Animal> getAllAnimals(){
        List<Animal> list =  (List<Animal>)repo.findAll();
        return list;
    }

    public List<Animal> getByKeyword(String keyword){
        return repo.findByKeyword(keyword);
    }

animal table users table

All code here: https://github.com/Furkan-Ahmet-Ozdemir/Spring-vet

** SOLVED: "select a.* from animals a inner join users u on u.id=a.user_id where a.name like %:keyword% or u.first_name like %:keyword%" **

javamysqlsqlspringmany-to-one


Solution 1:[1]

Please try this one

@Query(value = "select a.* from animal a inner join users u on u.id=a.user_id where a.name like %:keyword% or u.name like %:keyword%", nativeQuery = true)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 hakima maarouf

Related Questions