1

I have created a delete request utilizing Spring Data JPA with the controller method below. When I hit this endpoint through Postman/Insomnia using a reason string found in the database table, localhost:8088/123456789/timeoff?reason=test, the delete request works as expected, deleting the row with the specified reason and returning the successful response status.

Working Controller Method:

@CrossOrigin
@DeleteMapping(value = "/{advisor:[0-9]{0,19}}/timeoff")
protected PostResponse deleteAdvisorTimeoffByReason(@PathVariable Long advisor, @RequestParam String reason) {
    PostResponse response = new PostResponse();

    advisorTimeoffService.deleteAdvisorTimeoffByReason(reason);

    response.setSuccessful(true);
    response.setStatus(200);
    response.setMessage("Successfully deleted Advisor time off.");

    return response;
}

However, I run into issues when I try to create a delete request using Timestamp request parameters. When I make another Postman/Insomnia request localhost:8088/123456789/timeoff?datetimeStart=2021-12-25 13:00:00&datetimeEnd=2021-10-25 14:00:00, I once again get a successful response, but the row corresponding to the datetimeStart and datetimeEnd timestamps is not deleted from the database.

Non-Working Controller:

@CrossOrigin
@DeleteMapping(value = "/{advisor:[0-9]{0,19}}/timeoff")
protected PostResponse deleteAdvisorTimeoffByDate(@PathVariable Long advisor, @RequestParam Timestamp datetimeStart, @RequestParam Timestamp datetimeEnd) {
    PostResponse response = new PostResponse();

    advisorTimeoffService.deleteAdvisorTimeoffByDate(advisor, datetimeStart, datetimeEnd);

    response.setSuccessful(true);
    response.setStatus(200);
    response.setMessage("Successfully deleted Advisor time off.");

    return response;
}

Entity

@Getter
@Setter
@Entity
@Table(name = "ADVISOR_TIMEOFF", schema = "OSS")
@IdClass(AdvisorTimeoffPK.class)
public class AdvisorTimeoff {

    @Id
    @Column(name = "ADVISOR")
    private long advisor;

    @Id
    @Column(name = "DATETIME_START")
    private Timestamp datetimeStart;

    @Id
    @Column(name = "DATETIME_END")
    private Timestamp datetimeEnd;

    @Column(name = "REASON")
    private String reason;

    @Column(name = "RECUR")
    private boolean recur;

    @Column(name = "STATUS")
    private String status;

    @Column(name = "DATETIME_CREATED")
    private Timestamp datetimeCreated;

    @Column(name = "DATETIME_MODIFIED")
    private Timestamp datetimeModified;

    //equals() and hashCode() methods
}

JPA Repository:

public interface AdvisorTimeoffRepository extends CrudRepository<AdvisorTimeoff, AdvisorTimeoffPK> {

    //Delete by reason
    @Transactional
    void deleteAdvisorTimeoffByAdvisorAndReason(Long advisor, String reason);

    //Delete by dates
    @Transactional
    void deleteAdvisorTimeoffByAdvisorAndDatetimeStartAndDatetimeEnd(Long advisor, Timestamp datetimeStart, Timestamp datetimeEnd);
}

Service Class:

public interface AdvisorTimeoffService {

    void deleteAdvisorTimeoffByReason(Long advisor, String reason);

    void deleteAdvisorTimeoffByDate(Long advisor, Timestamp datetimeStart, Timestamp datetimeEnd);

}

Implementation:

@Service
public class AdvisorTimeoffImpl implements AdvisorTimeoffService {

    @Autowired
    private AdvisorTimeoffRepository advisorTimeoffRepository;


    @Override
    public void deleteAdvisorTimeoffByReason(Long advisor, String reason) {
        advisorTimeoffRepository.deleteAdvisorTimeoffByAdvisorAndReason(advisor, reason);
    }

    @Override
    public void deleteAdvisorTimeoffByDates(Long advisor, Timestamp datetimeStart, Timestamp datetimeEnd) {
        advisorTimeoffRepository.deleteAdvisorTimeoffByAdvisorAndDatetimeStartAndDatetimeEnd(advisor, datetimeStart, datetimeEnd);
    }
}

I'm not sure why the first request would work, but not the second one, given the code that I wrote for both is nearly identical. Any direction would be appreciated!

AlgoTrading
  • 55
  • 2
  • 2
  • 9
  • 1
    you need to show us what your repository method looks like – Fikret Dec 01 '21 at 00:03
  • 1
    Please add the code for `deleteAdvisorTimeoff` method and all other relevant methods that are called within that one. Thanks! – João Dias Dec 01 '21 at 01:34
  • @JoãoDias I've updated the post to include additional relevant methods! – AlgoTrading Dec 01 '21 at 19:05
  • Can you please also add `AdvisorTimeoff`? Thanks! – João Dias Dec 01 '21 at 19:12
  • @JoãoDias Done! – AlgoTrading Dec 01 '21 at 19:29
  • 1
    Everything looks good code-wise, so the missing piece must be your data. You need to check two things: 1. Guarantee that both `2021-10-25 14:00:00` and `2021-12-25 13:00:00` are correctly deserialized to a `Timestamp` object. 2. If they do, then confirm that those values are in fact in your database. – João Dias Dec 01 '21 at 19:32
  • Additionally, you may want to have a look at https://stackoverflow.com/questions/3323618/handling-mysql-datetimes-and-timestamps-in-java. – João Dias Dec 01 '21 at 19:36
  • @JoãoDias Thanks for your insight, I really appreciate it. It looks like I have an issue involving time zones in my request parameters, and I need to convert everything to UTC to make the database happy. – AlgoTrading Dec 01 '21 at 22:00
  • That might be it indeed. – João Dias Dec 01 '21 at 23:40

0 Answers0