Does Spring Data Jdbc enables join foreign key rather than id's?

Question

Tried lots of options but all the time i've got wrong join's. instead of get the actual status_name by its id populated in the 'status' column of supplier_contracts table the repository returns join i'ds of the 2 tables. so How do i retrive the status_name by the int of the status column supplierContractRepository.findById(3L)?

Statuses Entity

 CREATE TABLE `contract_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
);

public class ContractStatuses {
  
    @Id
    Integer id;
    String name;

    public ContractStatuses(int id, String name) {
        this.id = id;
        this.name = name;
    }
} 

Contracts Entity

    CREATE TABLE `suppliers_contracts` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `supplier` bigint(20) NOT NULL,
      `cabs_id` int(11) DEFAULT NULL,
      `start_date` date DEFAULT NULL,
      `end_date` date DEFAULT NULL,
      `attorny_end_date` date DEFAULT NULL,
      `number_of_payments` int(11) DEFAULT NULL,
      `payment_amount` int(11) DEFAULT NULL,
      `comments` varchar(2000) DEFAULT NULL,
      `close_date` timestamp NULL DEFAULT NULL,
      `status` int(11) NOT NULL,
      `created_on` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`)
    );

@Table("suppliers_contracts")
public class SupplierContract {
    @Id
    Long id;
    Long supplier;
    Long cabsId;
    Date startDate;
    Date endDate;
    int paymentAmount;
    Date attornyEndDate;
    int numberOfPayments;
    String comments;
    Date closeDate;
    @MappedCollection(idColumn = "id")
    ContractStatuses status;
   
    public SupplierContract(Long id, Long supplier, Long cabsId, Date startDate, Date endDate,Date attornyEndDate, int numberOfPayments, int paymentAmount,
                            String comments, Date closeDate,ContractStatuses status) {
        this.id = id;
        this.supplier = supplier;
        this.cabsId = cabsId;
        this.startDate = startDate;
        this.endDate = endDate;
        this.attornyEndDate = attornyEndDate;
        this.numberOfPayments = numberOfPayments;
        this.paymentAmount = paymentAmount;
        this.comments = comments;
        this.closeDate = closeDate;
        this.status = status;

    }
}
        //Getters and setters here

@GetMapping("/getContracts")
    public Optional<SupplierContract> getSupp(){
       Optional<SupplierContract>  contract = supplierContractRepository.findById(3L);
        return contract ;
    }

Answer 1

You have various options:

1. The default variant is to load the status with a separate call to a separate ContractStatusRepository. Since those statuses might not change very often you might consider caching for this.

2. If you only want the status and don't care about the contract you can add an additional method to your repository with a @Query annotation which selects the two fields of the status based on the id of a contract using a SQL join.

3. This one works only for Read Only access! You can create another entity ReadOnlyContract which contains the ContractStatuses reference, marked as an embedded attribute. You can now map this to a view containing the additional status fields. In order to avoid accidental use of writing methods you should have the repository inherit from Repository and add only the read methods you actually need.