jeudi 30 janvier 2020

How to Query passing in a parameter to a crud repository?

I have a web application that connects to an SQL database and has several entities.

The main tables are;

  1. Employees
  2. EmployeesxSkills (mapping the id of a certain employee to the id of a certian skill)
  3. Skill
  4. SkillxRole (mapping id of skill to id of a role)
  5. Role

I am trying to create a method that queries the roles based on a name input by the user, gets the role id and then queries the SkillxRole table and returns a string array of all the skills associated with that role id.

How do I query in a crud repository using a parameter specified by the user. This will be input using html input box using ajax and then I assume a valid endpoint.

Any help would be much appreciated. Below is my searchController code that I wish to query my repositories within.

@Controller
public class SearchController {
      public EmployeeRepository employeeRepository;
      public RoleRepository roleRepository; 
      public SkillRepository skillRepository;   
      public roleSkillRatingsRepository roleSkillRatingsRepository;
      @Autowired
        public void  employeeRepository(EmployeeRepository employeeRepository){
            this.employeeRepository = employeeRepository;
        }
      @Autowired
        public void  roleRepository(RoleRepository roleRepository){
            this.roleRepository = roleRepository;
        }
      @Autowired
        public void  skillRepository(SkillRepository skillRepository){
            this.skillRepository = skillRepository;
        }
      @Autowired
        public void  roleSkillRatingsRepository(roleSkillRatingsRepository roleSkillRatingsRepository){
            this.roleSkillRatingsRepository = roleSkillRatingsRepository;
        }


      @ResponseBody
        public List<Employee> getAllemployees() {
            System.out.println(employeeRepository.findAll());
            return employeeRepository.findAll();
        }

      @ResponseBody
        public Map<String,Integer> getRoleSkills() {

            return null;
        }

Role Repository

@Repository
public interface RoleRepository extends CrudRepository<Role, Integer> {

    List<Role> findAll();

    //something I found online but unsure how to apply this to my situation 
    @Query("select p from Person AS p"
               + " ,Name AS n"  
               + " where p.forename = n.forename "
               + " and p.surname = n.surname"
               + " and n = :name")
               String [] findByName(@Param("name") Name name);

    }




Aucun commentaire:

Enregistrer un commentaire