Spring Data Jdbc 是否启用连接外键而不是 id? [英] Does Spring Data Jdbc enables join foreign key rather than id's?

问题描述

尝试了很多选择，但一直都出错了.而不是通过其在供应商合同表的状态"列中填充的 ID 获取实际状态名称，存储库返回 2 个表的连接 ID.那么我如何通过状态列supplierContractRepository.findById(3L)的int检索status_name?

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

状态实体

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

合同实体

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

推荐答案

您有多种选择:

1. 默认变体是通过单独调用单独的 ContractStatusRepository 来加载状态.由于这些状态可能不会经常更改，因此您可以考虑为此进行缓存.

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.

如果您只想要状态而不关心合同，您可以使用 @Query 注释向您的存储库添加一个附加方法，该注释基于状态选择两个字段在使用 SQL 连接的合同的 id 上.

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.

这个仅适用于只读访问！您可以创建另一个实体 ReadOnlyContract，其中包含标记为嵌入属性的 ContractStatuses 引用.您现在可以将其映射到包含附加状态字段的视图.为了避免意外使用写入方法，您应该让存储库从 Repository 继承并仅添加您实际需要的读取方法.

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.

这篇关于Spring Data Jdbc 是否启用连接外键而不是 id?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！