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

CodeScale · Accepted Answer · 2020-04-22 13:48:26Z

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

answered Apr 22, 2020 at 13:48

CodeScale

3,3841 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

poldoj Over a year ago

Thank you very much. Indeed it was the other way around, just like you said. I even changed the table name to "category". All is working perflectly fine now.

Collectives™ on Stack Overflow

Hibernate database relation annotations

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related