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

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

CodePudding user response：

Try this JPA query "select a from Animal a left join User u where a.name like %:keyword% or u.firstName like %:keyword%"

CodePudding user response：

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)

CodePudding user response：

This query will give List based on animals name and owner name

 @Query(value = "select * from animals where name = ?1 union
 (select * from animals where user_id in (select id from users where
 firstName = ?1 or lastName = ?1 ))", nativeQuery = true)
    List<Animal> findByKeyword(@Param("keyword") String keyword);