[hibernate-users] Hibernate - ManyToMany relationship w/ Shared key between Entities

[Bruno Oliveira]

Hello everyone!

I'm having a pretty hard time in a not-so-usual (I guess?) scenario, that
I'd like to ask your expertise for your enlightment.

This one is kind of tricky, and I have looked up in, pretty much, all
related questions in SO and forums, but I couldn't find an definitive
answer. Let me get you the context:

The scenario is the following:

 Table: Product    Table: ProductCategory  Table:
Category----------------|    ----------------|     |---------------|
         | partner_Id    |    | product_Id    |     | partner_Id    |
| product_Id    |    | category_Id   |     | category_Id   |
|---------------|    | partner_Id    |     |---------------|| name
     |    |---------------|     | name          || description   |
| extra_fields  |     | description   || extra_fields  |    |
     |     | extra_fields  ||---------------|    |---------------|
|---------------|

And here are the classes:

Product.java

@Entity at Table(name = "EC_PRODUCT")public class Product implements Serializable {

    private ProductPK productPK;
    private List<ProductCategory> productCategories;
    // Other Atributes

    @Embeddable
    public static class ProductPK implements Serializable {
        private String partnerId;
        private String productId;

        public String getPartnerId() {
            return this.partnerId;
        }

        public void setPartnerId(String partnerId) {
            this.partnerId = partnerId;
        }

        public String getProductId() {
            return this.productId;
        }

        public void setProductId(String productId) {
            this.productId = productId;
        }
    }

    public Product() {
        this.productPK = new ProductPK();
        this.productCategories = new ArrayList<ProductCategory>();
    }

    @Id
    @AttributeOverrides({
            @AttributeOverride(name = "partnerId", column =
@Column(name = "partner_Id")),
            @AttributeOverride(name = "productId", column =
@Column(name = "product_Id"))
    })

    public ProductPK getProductPK() {
        return this.productPK;
    }

    public void setProductPK(ProductPK productPK) {
        this.productPK = productPK;
    }

    /*
    ** Other Getters and Setters
    */}


Category.java

@Entity at Table(name = "EC_CATEGORY")public class Category implements
Serializable {

    private CategoryPK categoryPK = new CategoryPK();
    private List<ProductCategory> productCategories;
    // Other Attributes

    @Embeddable
    public static class CategoryPK implements Serializable {
        private String partnerId;
        private String categoryId;

        public String getPartnerId() {
            return this.partnerId;
        }

        public void setPartnerId(String partnerId) {
            this.partnerId = partnerId;
        }

        public String getCategoryId() {
            return this.categoryId;
        }

        public void setCategoryId(String categoryId) {
            this.categoryId = categoryId;
        }
    }

    @Id
    @AttributeOverrides({
            @AttributeOverride(name = "partner_Id",  column =
@Column(name = "partner_id")),
            @AttributeOverride(name = "category_Id", column =
@Column(name = "category_cod"))
    })
    public CategoryPK getCategoryPK() {
        return this.categoryPK;
    }

    public void setCategoryPK(CategoryPK productPK) {
        this.categoryPK = productPK;
    }

    @OneToMany(mappedBy = "category", cascade = CascadeType.ALL)
    public List<ProductCategory> getProductCategories() {
        return this.productCategories;
    }

    public void setProductCategories(List<ProductCategory> productCategories) {
        this.productCategories = productCategories;
    }

    /*
    ** Other Getters and Setters
    */
}


ProductCategory.java

@Entity at Table(name = "EC_PRODUCT_CATEGORY")public class
ProductCategory implements Serializable {

    private Product product = new Product();
    private Category category = new Category();

    public ProductCategory() { }

    public ProductCategory(Product product, Category category) {
        this.setProduct(product);
        this.setCategory(category);
    }

    @Id
    @ManyToOne
    @JoinColumns({@JoinColumn(name = "product_Id"), @JoinColumn(name =
"partner_Id") })
    public Product getProduct() {
        return product;
    }

    public void setProduct(Product product) {
        this.product = product;
    }

    @Id
    @ManyToOne
    @JoinColumns({@JoinColumn(name = "category_Id"), @JoinColumn(name
= "partner_Id")})
    public Category getCategory() {
        return category;
    }

    public void setCategory(Category category) {
        this.category = category;
    }
}


And here's a Snippet of what my Main class does:

public class App {

   public static void main(String[] args) {

        // Sets up the Product
        Product product = new Product();
        product.getProductPK().setPartnerId("1");
        product.getProductPK().setProductId("99");

        // Sets up the Category
        Category category = new Category();
        category.getCategoryPK().setPartnerId("1");
        category.getCategoryPK().setCategoryId("10");

        // Sets up the ProductCategory
        product.getProductCategories().add(new
ProductCategory(product, category));

        // Data persistence w/ Hibernate
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();

        session.save(product)

        session.getTransaction().commit();
        session.flush();
        session.close();

    }
}


And that's the stacktrace (I formated a bit to make it more legible):

(...)Hibernate:

select
   productcat_.product_id,
   productcat_.partner_id,
   productcat_.category_id,
   productcat_.partner_id
from ECOM_STAGE_REL_CATEG_PROD productcat_
where
    productcat_.product_id=? and
    productcat_.partner_id=? and
    productcat_.category_id=? and
    productcat_.partner_id=?
Aug 26, 2015 9:53:25 PM
org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl release
INFO: HHH000010: On release of batch it still contained JDBC statements
Aug 26, 2015 9:53:25 PM
org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions

WARN: SQL Error: 0, SQLState: S1009
Aug 26, 2015 9:53:25 PM
org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions

ERROR: Parameter index out of range (4 > number of parameters, which is 3).
org.hibernate.exception.GenericJDBCException: could not insert:
[obscured_package_name.model.ProductCategory]
Hibernate: insert into EC_PRODUCT_CATEGORY (product_id, partner_id,
category_id) values (?, ?, ?)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)(...)

*Please notice that the 2nd and 4th line of the WHERE clause (in the SELECT
statement) are the same*

It's trying to get the partner_id for the Product (since its ProductPK is:
productId, partnerId) AND the partner_id for the Category (since its
CategoryPK is: categoryId, partnerId)

It doesn't fail for the SELECT statement, because SQL-wise, it doesn't
matter (but it does when it's doing an INSERT). And notice this at the end
of the stack trace:

ERROR: Parameter index out of range (4 > number of parameters, which is 3).
org.hibernate.exception.GenericJDBCException: could not insert:
[obscured_package_name.model.ProductCategory]

Hibernate: insert into EC_PRODUCT_CATEGORY(product_id, partner_id,
category_id) values (?, ?, ?)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)

It says It's trying to insert 4 FIELDs in a table that expects 3. (The only
reason the INSERT statement has 3 fields -- which is supposedly correct --
is because "partner_id" is a common name between the Product and Category).

IF, and ONLY IF, in ProductCategory.java, I were to rewrite the
@JoinColumns in the getters, like this:

 @Id
    @ManyToOne
    @JoinColumns({@JoinColumn(name = "product_Id"), @JoinColumn(name =
"PRODUCT_partner_Id") })
    public Product getProduct() {
        return product;
    }

    @Id
    @ManyToOne
    @JoinColumns({@JoinColumn(name = "category_Id"), @JoinColumn(name
= "CATEGORY_partner_Id")})
    public Category getCategory() {
        return category;
    }


The error would be like this:

Hibernate: insert into EC_PRODUCT_CATEGORY(product_id,
PRODUCT_partner_id, category_id, CATEGORY_partner_id) values (?, ?, ?,
?)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)

Did I make it clear ?

I tried to provide as much detail as possible, since it's tricky to
understand. But I truly have searched everything I could and didn't come up
with a solution.

Please, may I have your advice/expertise on how to solve this ?


Thank you very much in advance!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-users/attachments/20150827/ac5d6285/attachment-0001.html