I have spring jpa repository code something like below, and the fields id, std, email are coming from the frontend based on the filter selected by user.
@Query(value = "select * from dbo.student s where s.id = :id and s.std = :std and s.email = :email ", nativeQuery = true)
List<Student> filterStudents(String id, Integer std, String email);
If Frontend is not providing id then, I want to have query as below to be executed to get correct result
select * from dbo.student s where s.id = s.id and s.std = 12 and s.email = 's1@gmail.com'
is it possible to to add the columnName in value parameter. or is there any other way to achieve this in native query.
I am aware that this could be achieved via CriteriaBuilder but as my actual query is CTE query I am not inclined to use criteriaQuery here.
You can try modifying your query with COALESCE when front end send it as null. like this:
@Query(value = "select * from dbo.student s where (COALESCE(:id, s.id) = s.id) and (COALESCE(:std, s.std) = s.std) and (COALESCE(:email, s.email) = s.email) ", nativeQuery = true)
List<Student> filterStudents(String id, Integer std, String email);