Hibernate database relation annotations

Question

I've been struggling with this for so long now. I have a database with two tables "product" and "categories"

CREATE TABLE `product` (
  `idproduct` int NOT NULL AUTO_INCREMENT,
  `idcategory` int DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `product_category` varchar(255) DEFAULT NULL,
  `product_description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`idproduct`),
   KEY `fkcat` (`idcategory`),
   CONSTRAINT `fkcat` FOREIGN KEY (`idcategory`) REFERENCES `categories` (`idcategory`)
 ) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `categories` (
  `idcategory` int NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) NOT NULL,
  `category_description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`idcategory`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Now I'm trying to get a hibernate join query so I can retrieve let's say product_name and category_name

One product belongs only to one category (for example, if the product is "black t-shirt", its value for the column "idcategory" would be 2. This is enforced by the foreign key. The table categories entries can be associated with more than one product (for example, "category_name" = 2 can be associated with many products.

How can this design be implemented in hibernate entities? I've tried this but isn't working...

@Entity
@Table(name = "product")
public class Product implements Serializable{
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "idproduct")
    private int idproduct;
    @Column(name = "idcategory")
    private int idcategory;
    @Column(name = "product_name")
    private String productName;
    @Column(name = "product_description")
    private String productdescription;
    @Column(name = "product_category")
    private String productcategory;
    @OneToMany(targetEntity = Categories.class, cascade = CascadeType.ALL)
    @JoinColumn(name = "idcategory",referencedColumnName="idcategory")
    private List<Categories> category;

---

@Entity
@Table(name = "categories")
public class Categories {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "idcategory")
    private int idcategory;
    @Column(name = "category_name")
    private String category_name;
    @Column(name = "category_description")
    private String category_description;

and the query is

SELECT p, c FROM Product p INNER JOIN p.category c

Answer 1

this is not correct

 @OneToMany(targetEntity = Categories.class, cascade = CascadeType.ALL)
 @JoinColumn(name = "idcategory",referencedColumnName="idcategory")
 private List<Categories> category;

Product can't have many categories... it is actually the reverse ->

@Entity
@Table(name = "categories")
public class Categories {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "idcategory")
    private int idcategory;

    @Column(name = "category_name")
    private String category_name;

    @Column(name = "category_description")
    private String category_description;

    @OneToMany(cascade = CascadeType.ALL, mappedBy="category")
    private List<Product> products;

and Product

@Entity
@Table(name = "product")
public class Product implements Serializable{
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "idproduct")
    private int idproduct;
    @Column(name = "idcategory")
    private int idcategory;
    @Column(name = "product_name")
    private String productName;
    @Column(name = "product_description")
    private String productdescription;
    @Column(name = "product_category")
    private String productcategory;
    @ManyToOne
    private Categories categories;

Suggestion : rename Categories to Category