What is the correct JPA entity mapping for this database?

Question

So, this is the database:

drop database if exists demo;
create database demo;
use demo;

create table user_entity(
user_id INT primary key auto_increment,
user_name varchar(30),
medicine_id int
);

create table medicine_entity(
medicine_id int primary key auto_increment,
medicine_name varchar(30)
);

alter table user_entity
add foreign key(medicine_id) references medicine_entity(medicine_id);

insert into medicine_entity(medicine_name) values('paracetamol');
insert into medicine_entity(medicine_name) values('vix');

insert into user_entity(user_name, medicine_id) values('a',1);
insert into user_entity(user_name, medicine_id) values('a',2);
insert into user_entity(user_name, medicine_id) values('b',2);
insert into user_entity(user_name, medicine_id) values('c',1);

What is the appropriate mapping for this database, The Entity classes :

UserEntity.class :

import java.util.Set;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;

@Entity
public class UserEntity
{
public UserEntity()
{
super();
}

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long userId;
private String userName;
@ManyToMany
private Set<MedicineEntity> medicineId;

public long getUserId()
{
return userId;
}

public void setUserId(long userId)
{
this.userId = userId;
}

public String getUserName()
{
return userName;
}

public void setUserName(String userName)
{
this.userName = userName;
}

public Set<MedicineEntity> getMedicineId()
{
return medicineId;
}

public void setMedicineId(Set<MedicineEntity> medicineId)
{
this.medicineId = medicineId;
}

}

MedicineEntity.class:

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class MedicineEntity
{

public MedicineEntity()
{
super();
}

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long medicineId;
private String medicineName;
@ManyToMany
private Set<UserEntity> userId;

public long getMedicineId()
{
return medicineId;
}

public void setMedicineId(long medicineId)
{
this.medicineId = medicineId;
}

public String getMedicineName()
{
return medicineName;
}

public void setMedicineName(String medicineName)
{
this.medicineName = medicineName;
}

}

I'm thinking of @ManyToMany relationship on both the entities but don't know for sure. So, help me by correcting the code with correct relationship mapping and say in detail why this relationship is used what the correction does, please don't close this question.

Answer 1

The mapping for that database:

@Entity
@Table(name="user_entity")
public class UserEntity {

public class UserEntity() {}

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="user_id")
private Integer userId;

@Column(name="user_name")
private String userName;

@ManyToOne
@JoinColumn(name="medicine_id")
private MedicineEntity medicineEntity;

...
}

@Entity
@Table(name="medicine_entity")
public class MedicineEntity() {}

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="medicine_id")
private Integer medicineId;

@Column(name="medicine_name")
private String medicineName;

// (Optional) If you want a bidirectional association
@OneToMany(mappedBy="medicine_id")
private Set<UserEntity> userId;
...
}

It's not a many-to-many because it would require an additional table mapping the relationship between UserEntity and MedicineEntity (a table user_medicine for example).

See Hibernate ORM documentation for more details about mapping associations.

Also, I would discourage the use of primitive types as identifier. You wouldn't be able to set them to null that it make it easier to figure out when an entity hasn't an id (otherwise you have to check for 0 or bigger)