How can I join two tables using JPA?-CodePudding

I have two entity classes Medicine and Medicine Group. A medicine belongs to a group . A group has more than one medicine. The relationship from medicine to group is one to one from group to medicine is one to many.

I have created the entity classes with the following properties

@Entity
public class Medicine {
    @Id
    String medicineId;
    String medicineName;
    int inStock;
    String lifetimeSupply;
    String lifetimeSales;
    String howToUse;
    String sideEffects;
 //their getters and setters
}

@Entity
public class MedicineGroup {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    int groupId;
    String groupName;
    String groupDescription;

  //their getters and setters.
}

In MySQL in the Medicine table I have a column called groupId which is a foreign key to the group table.

Normally in MySQL I could write a join statement like this

SELECT medicine_id ,medicine_name ,in_stock ,lifetime_supply, lifetime_sales, how_to_use, side_effects , group_name FROM medicine JOIN medicine_group on medicine.group_id = medicine_group.group_id;

In the above join I am able to get the group name column from the group table. How can I achieve this using JPA. If I add a field to the medicine class like this

@OneToOne
MedicineGroup medicineGroup;

when I do a get request I get an error Unknown column 'medicine0_.medicine_group_group_id' in 'field list' . How is the field unknown yet I have a column called group_id in the medicine table?

How can I achieve a way in which if I perform a get request I get the medicine information and also the group with which it is associated with? Please help.

CodePudding user response：

Simple join on the attribute:

SELECT m.medicineId ,m.medicineName ,m.inStock ,m.lifetimeSupply, m.lifetimeSales, m.howToUse, m.sideSffects , mg.groupName 
FROM medicine m join m.medicineGroup mg;

and use attribute names, not column names

CodePudding user response：

select m from medicine m left join fetch m.medecineGroup