Delete Query in Spring Boot with Optional Parameter

Question

I am trying to implement Delete query in Spring Boot, however the parameters are optional. How do I write JPA query for same. Here is how I have implemented for mandate Request Params:

@Transactional
@Repository
public interface ABCRepo extends CrudRepository<ABC, Long>{

public List<ABC> findByABCIdAndStartYrAndStartMonth(String pilotId, int startYr, int startMonth);
public long deleteABCByABCId(String pilotId);
}

Controller.class

@RequestMapping(value="", method= RequestMethod.DELETE)
public Response delete(@PathVariable("abc-id")String pilotId)
{
    LOGGER.info("Trying to delete pilot bank using abc id : "+ abcId);
    long deletedRecords=abcBiz.deleteABCByABCId(abcId);
     if(deletedRecords==0)
     {
        throw new PilotNotFoundException("Entity not found "+abcId);
     }
    return Response.status(Response.Status.NO_CONTENT).entity(deletedRecords).build();
}

My new Controller.class after adding optional params

@RequestMapping(value="", method= RequestMethod.DELETE)
public Response delete(@PathVariable("abc-id")String abcId, @RequestParam(name = "bid-yr", required = false)
        int bidYr, @RequestParam(name = "bid-month", required = false) int bidMonth)
{
    LOGGER.info("Trying to delete pilot bank using abc id : "+ abcId);
    long deletedRecords=abcBiz.deleteABCByABCId(a);bcId
     if(deletedRecords==0)
     {
        throw new PilotNotFoundException("Entity not found "+abcId);
     }
    return Response.status(Response.Status.NO_CONTENT).entity(deletedRecords).build();
}

How do I handle this at JPA?

Jignesh M. Khatri · Accepted Answer · 2021-09-15 06:39:52Z

2

For optional parameters, you need to write the query. Something like below:

@Modifying
@Query("DELETE FROM ABC WHERE abcId=:pilotId AND (:otherOptionalParam IS NULL OR otherField=:otherOptionalParam)")
public long deleteABCByABCId(String pilotId, String otherOptionalParam);

If you want to create a complex query, with lot of optional parameters, then you can create custom repository, and develop native queries. Here I have already answered to how we can create custom repositories in Spring data JPA - https://stackoverflow.com/a/68721142/3709922

answered Sep 15, 2021 at 6:39

Jignesh M. Khatri

1,6561 gold badge16 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jignesh M. Khatri Over a year ago

Debug and check all the parameter values which are being passed to this repository method. There seems no issue with the query which you have stated in above comment.

Lucifer Geralt Over a year ago

here are the values: ("5422", null, null, null), these values are coming to repository method

Jignesh M. Khatri Over a year ago

But how come I know that these values exists in your database or not. Check with these values in your database.

Lucifer Geralt Over a year ago

5422 does exist but there is no column with bidyear or bidabbrev and bidmonth with null matching with 5422. My question is if incase of null, how can i ignore the fields and only fields which are having value.

Jignesh M. Khatri Over a year ago

By the above query they are ignored by default. (:bidYr IS NULL OR startYr=:bidYr) this will ignore the startYr comparison if bidYr parameter is null.

|

Lucifer Geralt · Accepted Answer · 2021-09-15 11:47:02Z

1

On Top of what Jignesh has said, don't forget to mark your parameters with Param annotation. Also jpa modification will return int/Integer but not long so I had to change return type too.

@Modifying
@Query("DELETE FROM ABC WHERE abcId=:pilotId AND (:otherOptionalParam IS NULL OR 
otherField=:otherOptionalParam)")
public long deleteABCByABCId(@Param("pilotId")String pilotId, @Param("otherOptionalParam")String 
otherOptionalParam);

answered Sep 15, 2021 at 11:47

Lucifer Geralt

1431 silver badge16 bronze badges

Collectives™ on Stack Overflow

Delete Query in Spring Boot with Optional Parameter

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related