How to update a table column in Spring Boot?

Question

I'm trying to update a column in Mysql using @PostMapping.

The entity table has 5 columns

customer_id, customer_name, total_amount, amount_paid, amount_debt

We provide arguments for customer_name, total_amount, amount_paid.

amount_debt will be calculated as amount_total - amount_paid and written back to amount_debt column for each POST.

The equivalent SQL seems to be

update emi_details 
set amount_debt = amount_total - amount_paid;

I tried it, using

getAmount_debt() { return amount_total - amount_paid; }
setAmount_debt(Integer amount_debt) { this.amount_debt = amount_total - amount_debt; }

methods inside the entity class.

However, in POST request it seems, I need to provide the "amount_debt": 0 argument, otherwise it's not getting written into MySql.

When I tried removing the get & set methods and did the minus inside the constructor, and later POST request without "amount_debt": 0, it shows amount_debt has a value in response, but no value in the amount_debt column.

It seems, I may need to write some @Query inside the @PostMapping before

return emiDetailsRepository.save(emiDetails);

but I'm not sure about it.

Please guide

Things I tried are as follows:

1. Entity - EmiDetails.java

@Entity
@Table(name = "emi_details")
public class EmiDetails {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer customer_id;
    
    @Column(name = "customer_name")
    private String customer_name;
    
    @Column(name = "amount_total")
    private Integer amount_total;
    
    @Column(name = "amount_paid")
    private Integer amount_paid;
    
    @Column(name = "amount_debt")
    private Integer amount_debt;;

    public EmiDetails() {}
    
    public EmiDetails(String customer_name, Integer amount_total, Integer amount_paid, Integer amount_debt) {
        super();
        this.customer_name = customer_name;
        this.amount_total = amount_total;
        this.amount_paid = amount_paid;
        this.amount_debt = amount_total - amount_paid;
    }

    public Integer getCustomer_id() {
        return customer_id;
    }

    public void setCustomer_id(Integer customer_id) {
        this.customer_id = customer_id;
    }

    public String getCustomer_name() {
        return customer_name;
    }

    public void setCustomer_name(String customer_name) {
        this.customer_name = customer_name;
    }

    public Integer getAmount_total() {
        return amount_total;
    }

    public void setAmount_total(Integer amount_total) {
        this.amount_total = amount_total;
    }

    public Integer getAmount_paid() {
        return amount_paid;
    }

    public void setAmount_paid(Integer amount_paid) {
        this.amount_paid = amount_paid;
    }

    public Integer getAmount_debt() {
        return amount_total - amount_paid;
    }

    public void setAmount_debt(Integer amount_debt) {
        this.amount_debt = amount_total - amount_debt;
    }   
}

2. Repository EmiDetailsRepository.java

public interface EmiDetailsRepository extends JpaRepository<EmiDetails, Integer> {}

3. Controller - EmiDetailsContoller.java

@RestController
@RequestMapping("/api/v1")
public class EmiDetailsContoller {
    @Autowired
    private EmiDetailsRepository emiDetailsRepository;
    
    //get all data
    @GetMapping("/emi_details")
    public List<EmiDetails> getAllEmiDetails() {
        return emiDetailsRepository.findAll();
    }
    
    //post data
    @PostMapping("/emi_details")
    public EmiDetails createEmiRecord(@RequestBody EmiDetails emiDetails) {
        return emiDetailsRepository.save(emiDetails);
    }
}

4. POST Request- Writes data in each column

{
    "customer_name": "Sandeep Roy",
    "amount_total": 75,
    "amount_paid": 8,
    "amount_debt": 0
}

5. POST Request- Writes data but in amount_debt column

{
  "customer_name": "Sandeep Roy",
  "amount_total": 75,
  "amount_paid": 8,
}

Apologies for writing everything on the question itself, haven't tried out Heroku yet.

Answer 1

Short Answer

Calculate the amount debt inside your createEmiRecord method.

Just update your @PostMapping method to the below code

@PostMapping("/emi_details")
public EmiDetails createEmiRecord(@RequestBody EmiDetails emiDetails) {
    
    //Calculate your debt here using the values in the provided emiDetails
    
    return emiDetailsRepository.save(emiDetails);
}

Long Answer

Start by fixing your Entity class. Entity classes are plain POJOs and should not have any business logic.

@Entity
@Table(name = "emi_details")
public class EmiDetails {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer customer_id;
    
    @Column(name = "customer_name")
    private String customer_name;
    
    @Column(name = "amount_total")
    private Integer amount_total;
    
    @Column(name = "amount_paid")
    private Integer amount_paid;
    
    @Column(name = "amount_debt")
    private Integer amount_debt;

    .....SETTERS AND GETTERS

}

Update your createEmiRecord method to the following

@PostMapping("/emi_details")
public EmiDetails createEmiRecord(@RequestBody EmiDetails emiDetails) {
    
    //Calculate Amount debt here
    Integer amount_debt = emiDetails.getAmount_total() - emiDetails.getAmount_paid();
    
    emiDetails.setAmount_debt(amount_debt);
    
    return emiDetailsRepository.save(emiDetails);

}