How to call the fields of a table A from table B when table B is containing the primary key of A as a foreign key (Springboot+JPA repo+MySQL+postman)

Question

Can anyone let me know how to implement the joining of two tables using a foreign key?

The scenario here is as follows:

3 tables:

i. product_tbl : Having primary key with other fields.

ii. customer_tbl : Having primary key with other fields.

iii. purchase_tbl : Having a primary key, foreign key of above two tables, and some other fields.

I want to call other fields of 'product_tbl' with the foreign key present in 'purchase_tbl' and the same I want to do with 'customer_tbl' and 'purchase_tbl'.

I am using spring boot + JPA repository + MySQL 5.1.45+ postman.

The project structure is: project structure

My try was to implement the below code marked with orange color: I tried connect model class with one to one and other mapping style and wrote the '@Query(.....)' but I must be missing somewhere, I think. '@Query(...)'

This query is being called by the controller class (see the commented segment): Controller Class

Thanks

Answer 1

I am assuming that you already have relationship mapped in entity class. i.e. You purchaseModel entity should have a element

@OneToOne
@JoinColumn(name = "<<join field>>")
ProductModel productModel;

If yes, your relationship is already formed. All you need to do is to call the inner object using the outer object. In you case below will be the query

@Query("Select pt from PurchaseModel pt where pt.productModel.shoeCategory=?1)
List<PurchaseModel> findByCategory(String pc);