Obtain column data from One-To-Many join relation in Spring data JPA

Question

Suppose I have two database tables, Product and ProductDetails.

create table Product
{
    product_id int not null,
    product_name varchar(100) not null,
    PRIMARY KEY (product_id)
}
create table ProductDetails
{
    detail_id int not null,
    product_id int not null,
    description varchar(100) not null,
    PRIMARY KEY (detail_id,product_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
}

Each product can have multiple product detail entries, but each product detail can only belong to one product. In SQL, I want to be able to retrieve each product detail but with the product name as well, and I would do that with a join statement.

select p.product_id,pd.detail_id,p.product_name,pd.description
from Product p join ProductDetails pd on p.product_id=pd.product_id

Now I need to have that concept in Spring data JPA form. My current understanding is the following:

@Table(name = "Product")
public class ProductClass
{
    private int productID;
    private String productName;
}

@Table(name = "ProductDetails")
public class ProductDetailsClass
{
    private int detailID;
    private int productID;

    // this is the part I don't know how to set. @OneToMany? @ManyToOne? @JoinTable? @JoinColumn?
    private String productName;

    private String description;
}

(I didn't include any attributes such as @Id to keep the code minimal)

What do I need to write to get this private String productName; working? My research on the @JoinTable and @OneToMany and other attributes just confuses me more.

P.S. This is a legacy Java program I inherited. The private String productName; part wasn't in the original code, but now I need the ProductDetails class to have the productName available.

P.P.S. I want to have a clear understanding of what I'm doing before trying anything and deploying. This is a legacy program deployed to production, and from what I understand, any code changes here can change the database structure as well, and no amount of money is enough to make me want to restore the Java program, the Spring Framework, the Apache server and MySQL database to a working order if anything catastrophic happens. Also I don't really have a development environment to test this. Help...

Answer 1

You research already goes in the right direction: You would need a @OneToMany relationship. The best descriptions for Hibernate has Vlad Mihalcea. On his webpage you could also find a good explanation of those relationships: The best way to map a @OneToMany relationship with JPA and Hibernate.

Firstly, you would have to create the entities correctly (an entity is represented by a table in a relational database).

Unidirectional (@OneToMany)

@Entity
@Table(name = "product")
public class Product
{
    @Id
    @GeneratedValue
    private Long productID;

    private String productName;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    private List<ProductDetail> productDetails;

    //Constructors, getters and setters...
}

@Entity
@Table(name = "product_details")
public class ProductDetail
{
    @Id
    @GeneratedValue
    private Long detailID;

    private String description;

    //Constructors, getters and setters...
}

This is based on a unidirectional relationship. Therefore, each Product knows all the allocated ProductDetails. But the ProductDetails do not have a link to its Products. However, this unidirectional implementation is not recommended. It results in an increase of the size of the database, even its optimisation with @JoinColumn is not ideal because of more SQL calls.

Unidirectional (@ManyToOne)

@Entity
@Table(name = "product")
public class Product
{
    @Id
    @GeneratedValue
    private Long productID;

    private String productName;

    //Constructors, getters and setters...
}

@Entity
@Table(name = "product_details")
public class ProductDetail
{
    @Id
    @GeneratedValue
    private Long detailID;

    private String description;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = product_id)
    private Product product;

    //Constructors, getters and setters...
}

In this unidirectional relationship only the ProductDetails know which Product is assigned to them. Consider this for a huge number of ProductDetail objects for each Product.

The @JoinColumn annotation specifies the name of the column of the table product_details in which the foreign key to the Product (its id) is saved. It also works without but it is more efficient with this annotation.

Bidirectional (@OneToMany and @ManyToOne)

@Entity
@Table(name = "product")
public class Product
{
    @Id
    @GeneratedValue
    private Long productID;

    private String productName;

    @OneToMany(mappedBy = "product", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<ProductDetail> productDetails;

    //Constructors, add, remove method, getters and setters...
}

@Entity
@Table(name = "product_details")
public class ProductDetail
{
    @Id
    @GeneratedValue
    private Long detailID;

    private String description;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = product_id)
    private Product product;

    //Constructors, getters and setters...
}

With a bidirectional relationship objects of both sides (Product and ProductDetail) know which other objects got assigned to them.

But according to Vlad Mihalcea, this should not be used if too many ProductDetails exist per Product.

Also remember to implement proper add and remove methods for list entries (see article again, otherwise weird exceptions).

Miscellaneous

With the cascading, changes in a Product also get applied to its ProductDetails. OrphanRemoval avoids having ProductDetails without a Product.

Product product = new Product("Interesting Product");

product.getProductDetails().add(
    new ProductDetails("Funny description")
);
product.getProductDetails().add(
    new ProductDetails("Different description")
);

entityManager.persist(product);

Often the question about the correct equals and hashCode methods is a complex puzzle in your head. Especially for bidirectional relationships but also in other situations relying on a database connection it is recommendable to implement them quite simply as described by Vlad.

It is good practice to use objects for primitive data types as well. This gives you the option to retrieve a proper null when calling the getter.

Avoiding eager fetching should be quite clear...

When you now try to retrieve a Product out of the database, the object automatically has a list of all the ProductDetails assigned to it. To achieve this, JPA repositories in Spring could be used. Simple methods do not have to be implemented. When you have the need to customise the functionality more, have a look at this article by Baeldung.