@Query with 2 parameters in Java Spring-CodePudding

I'm looking for a solution for following problem - i want to create a @Query like this:

@Query("select s from Student s where s.name like %?1% and s.surname like %?1%")

because I need to be able to show student with given name and surname. I was able to make it sort of work, because when I change and with or, the query shows entries with either given name or given surname, but as soon as i change it back to and nothing shows.

interface StudentRepository extends JpaRepository<Student, Integer> {

@Query("select s from Student s where s.name like %?1% and s.surname like %?1%")
Page<Student> findByNameAndSurname( String name, String surname,  Pageable pageable);
}

@GetMapping
Page<Student> getAllStudents(@RequestParam Optional<String> name,
                             @RequestParam Optional<String> surname,
                             @RequestParam Optional<Integer> page,
                             @RequestParam Optional<String> sortBy) {
    return repository.findByNameAndSurname(name.orElse("_"),
                                            surname.orElse("_"),
                                            PageRequest.of(
                                                            page.orElse(0), 5,
                                                            Sort.Direction.ASC, sortBy.orElse("id")));

I also have second question, is it possible to remove this code that shows at the end of JSONs while using pageRequest - I would like only the Student entries to show without this if possible


{"content":[],"pageable":{"sort":{"empty":false,"sorted":true,"unsorted":false},"offset":0,"pageNumber":0,"pageSize":5,"unpaged":false,"paged":true},"last":true,"totalPages":0,"totalElements":0,"size":5,"number":0,"sort":{"empty":false,"sorted":true,"unsorted":false},"first":true,"numberOfElements":0,"empty":true}

I tried using native query in @Query annotation, I also tried modifying the query itself, using some concat tricks i found online, but nothing works;(

CodePudding user response：

JpaRepository supports the query method so if you want to search the items which contains the place holder values you can do it by just defining the method like below.

Page<Student> findByStartingWithFirstNameAndStartingWithSurname();

CodePudding user response：

Take a look at your query:

@Query("select s from Student s where s.name like %?1% and s.surname like %?1%")

You have defined two placeholders with ?1 what will result in both placeholders to have the same value. So you're literally searching for someone with the same first and last name, that's why an OR would work here.

I am not familiar with Spring, but reading the following tutorial tells me that you can write your query as follows:

@Query("select s from Student s where s.name like %:firstname% and s.surname like %:lastname%")

You need to bind the parameters with the @Param(var) annotation in your method's parameter list though.

For your last question: You probably shouldn't be returning a Page<Student> from your REST (?) service, but rather a List<Student>.