Delete Query in Spring Boot with Optional Parameter-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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