CODEWITHSUNDEEP
javasqlspringget-mapping
Bajter
Bajter

Reputation: 7

@Query with 2 parameters in Java Spring

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

Upvotes: 1

Views: 347

Answers (3)

Marc Bannout
Marc Bannout

Reputation: 451

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

Upvotes: 0

Kunal Varpe
Kunal Varpe

Reputation: 463

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

Upvotes: 0

maio290
maio290

Reputation: 6742

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

Upvotes: -1

Related Questions