How to avoid null parameter in And condition using Entity Manager in spring data JPA

Question

I am trying to implement search functionality on grid using And condition. If am giving two parameter its giving Result. But if I want to search using only first field and second is null. Its giving empty array according to AND functionality. How to avoid null parameter in this situation.

If one parameter is null then how to avoid that parameter and get result according to that parameter which is not null.

AccountController

@RestController
@RequestMapping("/SpaceStudy/SpaceAdmin")
public class AccountController {

    @Autowired
    AccountService accService;

    @CrossOrigin(origins = "*")
    @GetMapping("/AccountMaintenance/LoadGrid")
    public Set<AccountModel> GridLoad( String sclientacctid, String sacctdesc) {
        return accService.gridLoad("1124100",null);

AccountService

@Service
public class AccountService {
    @Autowired
    AccountRepository accRepo;
public Set<AccountModel> gridLoad( String sclientacctid,String sacctdesc) {

        if (sclientacctid != null || sacctdesc != null) {
            Set<AccountModel> gridObj1 = accRepo.findBySclientacctidAndSacctdesc(sclientacctid, sacctdesc);
            return gridObj1;
        }       
            Set<AccountModel> gridObj = accRepo.findBySclientacctid();

        return gridObj;
    }

AccountRepository

@Repository
public interface AccountRepository extends JpaRepository<AccountModel, Integer>,AccontRepositoryCustom {

    @Query("select distinct(a.slocation) from AccountModel a where a.slocation !=null")
    List<AccountModel> findBySlocation();   

    @Query("select new map(acct.sclientacctid as sclientacctid ,acct.sacctdesc as sacctdesc ,"
            + "acct.slocation as slocation,invest.sinvestigatorname as sinvestigatorname ,"
            + "dept.sclientdeptid as sclientdeptid,dept.sdeptname as sdeptname ,acp.sccpcode as sccpcode)"
            + " from AccountModel acct join acct.department dept"
            + " join acct.investigator invest join acct.accountCPC acp" + " where acct.ninstid= 60")
    Set<AccountModel> findBySclientacctid();

AccountRepositoryCustomImpl

@Repository
public class AccountRepositoryCustomImpl implements AccontRepositoryCustom {

    @PersistenceContext
    EntityManager entityManager;

    @SuppressWarnings("unchecked")
    @Override
    public Set<AccountModel> findBySclientacctidAndSacctdesc(String sclientacctid, String sacctdesc) {

        Query query = entityManager
                .createQuery("select new map(acct.sclientacctid as sclientacctid ,acct.sacctdesc as sacctdesc ,"
                        + "acct.slocation as slocation,invest.sinvestigatorname as sinvestigatorname ,"
                        + "dept.sclientdeptid as sclientdeptid,dept.sdeptname as sdeptname ,acp.sccpcode as sccpcode)"
                        + " from AccountModel acct join acct.department dept"
                        + " join acct.investigator invest join acct.accountCPC acp" 
                        + " where acct.sclientacctid=?1 and acct.acctdesc=?2 ");
        query.setParameter(1, sclientacctid);
        query.setParameter(2, sacctdesc);
        return (Set<AccountModel>) query.getResultList();
    }     
}

I am new in spring. Can any one help me,what I need to change in above code?

can I used if conditions inside Query – SpringUser Feb 28 '18 at 08:40 — SpringUser, Feb 28 '18 at 08:40

score 0 · Answer 1 · answered Feb 27 '18 at 14:20

0

Your may try something like below (not tested though):

    Query query = entityManager
            .createQuery("select new map(acct.sclientacctid as sclientacctid ,acct.sacctdesc as sacctdesc ,"
                    + "acct.slocation as slocation,invest.sinvestigatorname as sinvestigatorname ,"
                    + "dept.sclientdeptid as sclientdeptid,dept.sdeptname as sdeptname ,acp.sccpcode as sccpcode)"
                    + " from AccountModel acct join acct.department dept"
                    + " join acct.investigator invest join acct.accountCPC acp" 
                    + " where acct.sclientacctid=?1 and (?2 is null or acct.acctdesc =?2) ");

answered Feb 27 '18 at 14:20

Ram

1,743
2
18
40

Post the hibernate generated query – Ram Feb 27 '18 at 15:25
which Query I didn't understood – SpringUser Feb 27 '18 at 15:28
Query which gets printed in your console by hibernate when running the above query – Ram Feb 27 '18 at 15:53
Nothing is printing in console – SpringUser Feb 28 '18 at 03:05
If you're using spring boot, add this in your application properties file and post the sql here. `spring.jpa.properties.hibernate.show_sql=true` – Ram Feb 28 '18 at 03:24
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/165949/discussion-between-jusermar10-and-r-shinde). – Ram Feb 28 '18 at 07:31

How to avoid null parameter in And condition using Entity Manager in spring data JPA

1 Answers1