0

I have a products table that contains especially a product_name and a product_type.

For certain product types, I'd like to create some kind of overlay mapping table that replaces the value in product_name.

In pure mysql, I would solve this as follows:

CREATE TABLE products (
 id int NOT NULL AUTO_INCREMENT,
 product_type varchar(20) NOT NULL,
 product_name varchar(255) NOT NULL,
 product_price;
 product_quantity;
 ...
)

@Entity
public class Product {
    long id;
    String product_type;
    String product_name;
    String product_price;
    ...
}

CREATE TABLE product_mapping (
 id int NOT NULL AUTO_INCREMENT,
 product_type varchar(20) NOT NULL,
 product_name varchar(255) NOT NULL,
 PRIMARY KEY (id)
);

My goal: if product_mapping contains the product_type, override the product_name. Else, stick to the value in products.product_name table.

SELECT ..., ifnull(product_mapping.product_name, products.product_name) AS product_name
   FROM products
   LEFT JOIN product_mapping ON products.product_type = product_mapping.product_type;

But how could I create the same mapping with a hibernate @Entity

Sidenote: A @Formula creates an additional SELECT for each query, whose result is merged into the @Entity. I'm looking for a JOIN!

membersound
  • 81,582
  • 193
  • 585
  • 1,120
  • Does this answer your question? [How to map calculated properties with JPA and Hibernate](https://stackoverflow.com/questions/2986318/how-to-map-calculated-properties-with-jpa-and-hibernate) – XtremeBaumer Aug 02 '22 at 12:42
  • No, a `@Formula` creates an **additional** `SELECT` for each query, whose result is merged into the @Entity. I'm looking for a **JOIN**! – membersound Aug 02 '22 at 12:45
  • [Perhaps a `@JoinFormula`](https://stackoverflow.com/questions/28675809/selecting-an-entity-from-another-table-using-a-hibernate-formula) – XtremeBaumer Aug 02 '22 at 12:52
  • The additional select query is run as subquery and is part of the main query, so the database will most certainly treat this as if you had left joined the table. Check the database query plan if you don't believe me. – Christian Beikov Aug 02 '22 at 15:00

0 Answers0