CODEWITHSUNDEEP
spring-data-jpajpql
Mr.DevEng
Mr.DevEng

Reputation: 2421

How to avoiding AND condition if parameter is null in Spring Data JPA query

I am trying to get the result of one query using Spring Data JPA. Here I am sending some parameter and receiving result according to that.

My repository query is,

@Query("select u.username,p.pname from Users u join u.priviJoin p where u.username = :uname AND p.pname = :pname")
List<Users> findByUsername(@Param("uname") String uname , @Param("pname") String pname );

And calling from controller like the following,

@RequestMapping(value = "/joinResult", method = RequestMethod.GET)
public List<Users> joinResultShow()
    {
        return (List<Users>) userRepo.findByUsername("test_user","testRole");

    }

Here we can see that if I am passing some value then only checking according to that parameter. Here I need to modify my query like if parameter is null, then not need to use AND condition in query.

How can I modify this query for avoiding AND condition if parameter is null? I am new to Spring Data JPA world.

Upvotes: 3

Views: 5974

Answers (1)

pvpkiran
pvpkiran

Reputation: 27048

Here are some possible options for you
1. Create multiple methods in your repository like 

  @Query("select u.username,p.pname from Users u join u.priviJoin p where u.username = :uname AND p.pname = :pname")
    List<Users> findByusernamewithRole(@Param("uname") String uname , @Param("pname") String pname );

  @Query("select u.username,p.pname from Users u join u.priviJoin p where u.username = :uname")
    List<Users> findByUsernameWithoutRole(@Param("uname") String uname);

  1. Write a custom respository and use EntityManager. With this you can create a dynamic queries based on your input using CriteriaBuilder and use this criteria in querying.

  2. Last and the most preferred option in case of dynamic inputs(like you have) is Querydsl. Some articles about querydsl
    http://www.baeldung.com/querydsl-with-jpa-tutorial
    http://www.querydsl.com/static/querydsl/latest/reference/html/ch02.html

Upvotes: 3

Related Questions