CODEWITHSUNDEEP
javamysqldatabasespringspring-data-jpa
kcsurapaneni
kcsurapaneni

Reputation: 814

Get all the records of column in Spring Data JPA, even we specify it as parameter

There is a table called zipcode with columns as id, zip, city, state.

In our case, we want to get the records based on either id or zip or city or state.

The General SQL query looks like

SELECT * FROM zipcode WHERE id=id AND zip=zip AND city=city AND state=state;

If we want to get a specific state then, we will replace the state value with the given name.

SELECT * FROM zipcode WHERE id=id AND zip=zip AND city=city AND state='California';

If we want a specific city, then we can write

SELECT * FROM zipcode WHERE id=id AND zip=zip AND city='Los Angles' AND state=state;

The problem with Spring Data JPA is, if we write a method

@Query(value = "SELECT * FROM zipcode WHERE id=?1 AND zip=?2 AND city=?3 AND state=?4", nativeQuery = true)
List<Zipcode> getZipCodes(Integer id, Integer zip, String city, String state)

I want to get all the cities but a specific state only, then I can't use this method, like how I can use the above SQL query, where we just need to replace the column value.

How can we achieve this in Spring Data JPA, using native query ?

Upvotes: 1

Views: 3347

Answers (3)

Vineeth Varada
Vineeth Varada

Reputation: 36

Use spring data jpa query params

@Query(value = "SELECT * FROM zipcode WHERE id=:id AND (zip=:zip or :zip is null) AND  (city=:city or :city is null)  AND (state=:state or :state is null) ", nativeQuery = true)
List<Zipcode> getZipCodes(@Param("id") Integer id, @Param("zip") Integer zip, @Praram("city) String city, @Param("state") String state)

Upvotes: 2

Himeshgiri gosvami
Himeshgiri gosvami

Reputation: 2884

Try this, 

@Query(value = "SELECT * FROM zipcode WHERE  (id=?1 or ?1 is null) AND (zip=?2 or ?2 is null) AND  (city=?3 or ?3 is null)  AND (state=?4 or ?4 is null) ", nativeQuery = true)
List<Zipcode> getZipCodes(Integer id, Integer zip, String city, String state)

Upvotes: 0

gbandres
gbandres

Reputation: 911

I don't know if Spring Data has a way of doing this in a single method, but you could try using an Example:

public List<Zipcode> getZipcodes(Integer id, Integer zip, String city, String state){
  Zipcode zipcode = new Zipcode();
  if (id != null)
    zipcode.setId(id);
  if (zip != null)
    zipcode.setZip(zip);
  ...
  return zipcodeRepository.findAll(Example.of(zipcode));
}

Upvotes: 0

Related Questions