@Query with 2 parameters in Java Spring

Question

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;(

You are passing `_` when the name isn't present, which I doubt is a name. If parameters are optional you are better of using the criteria API through the use of the `Predicate` s of Spring Data. — M. Deinum, Nov 21 '22 at 18:28
Regarding the return type, you are returing a `Page` what else would you expect. Also ifyou only would return the content, how would you know how manu pages there are? Beats a bit the purpose of using a page in the first place. — M. Deinum, Nov 21 '22 at 19:05

score 0 · Answer 1 · answered Nov 21 '22 at 19:31

0

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();

answered Nov 21 '22 at 19:31

Kunal Varpe

419
1
5
28

the question is whether this accepts optionals? This assignment forces me to code it with only one getmapping at /students and possibility to pass one, two, three or four independent arguments - name, name and surname, surname, only pagination etc. – Bajter Nov 22 '22 at 10:40

Marc Bannout · Answer 2 · 2022-11-24T18:07:45.917

?1 is for the first parameter name so you must replace your second ?1 with ?2 to use the parameter surname.

For your second question, you can map your page into a list:

repository.findByNameAndSurname(name.orElse("_"),
                                            surname.orElse("_"),
                                            PageRequest.of(
                                                            page.orElse(0), 5,
                                                            Sort.Direction.ASC, sortBy.orElse("id"))).stream().toList();

score -1 · Answer 3 · answered Nov 21 '22 at 17:50

-1

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>.

answered Nov 21 '22 at 17:50

maio290

6,440
1
21
38

That won't work and will result in an errornous query as the `%` will be outside of the escaped value for `?1` or `:firstname`. The `%` needs to be added as a value to the parameter passed in. – M. Deinum Nov 21 '22 at 19:04
Because it is an `OR` and not an `AND`.. .`lastname="_" OR firstName = "Somevalue"` instead of `lastname="_" AND firstName = "Somevalue"`... I doubt there is someone with the lastname of `_`. Nonetheless that wasn't what my comment was about, that was about a wrong solution to the problem as it simply won't work and will lead to an illegal query. But if you don't want to learn feel free to dismiss that. Basically your query leads to a SQL of `SELECT s.* FROM student s WHERE s.name=%'input'% ... ` The `%'input'%` makes the query invalid. – M. Deinum Nov 22 '22 at 07:05
I don't understand why he got downvoted, the solution he gave me works as intended. Is it bad coding practice? Also, the "_" was part of solution I found and based this piece of code on, without any input ("") the query was making the endpoint /students throw me a 500HTTP error, now that I changed the @Query values to ``` @Query("select s from Student s where s.name like %:name% and s.surname like %:surname%") ``` everything runs just fine – Bajter Nov 22 '22 at 10:44

@Query with 2 parameters in Java Spring

3 Answers3