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

Question

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.

Does this answer your question? [Spring Data optional parameter in query method](https://stackoverflow.com/questions/32728843/spring-data-optional-parameter-in-query-method) — Chanandler Bong, Jun 20 '23 at 06:52

score 3 · Accepted Answer · answered Feb 21 '18 at 15:00

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

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

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

1 Answers1