Hibernate generating a query from Named Query that joins on the wrong column?

Question

I am using named query (hibernate 4).Entity defined as below.

    @Entity
       @NamedQuery(
        name = "findAllProduct",
        query = "SELECT PC.pincode,PO.description"
        +" FROM PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION PVPOL" 
        +" INNER JOIN PVPOL.paymentId PID" 
        +" INNER JOIN PVPOL.pincode PC"
        +" INNER JOIN PVPOL.paymentOptions PO"
        +" where PVPOL.id = :id"

        )


    public class PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION extends baseEntity.Entity {

        @Id
        @GeneratedValue(strategy=GenerationType.AUTO)
        private int id;

        @Column(name="Payment_Id")
        @OneToMany(cascade=CascadeType.ALL,fetch=FetchType.EAGER,mappedBy="id")
        private Set<Product_Catalog_Vendor> paymentId; 

        @Column(name="pincode_id")
        @OneToMany(cascade=CascadeType.ALL,fetch=FetchType.EAGER,mappedBy="pincode_id")
        private Set<Pincodes> pincode;      

        @Column(name = "payment_options")

        @OneToMany(cascade=CascadeType.ALL,fetch=FetchType.EAGER,mappedBy="paymentOptions") 
        private Set<Payment_Options> paymentOptions;

//Protected setter getter here

}

Hibernate generating below sql:-

    select  pincode2_.pincode as col_0_0_,    paymentopt3_.Description as col_1_0_ 
from    PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION product_ve0_ 
inner join    Product_Catalog_Vendor paymentid1_ 
        on product_ve0_.id=paymentid1_.Id 
inner join    Pincodes pincode2_ 
        on product_ve0_.id=pincode2_.pincode_id 
inner join    payement_options paymentopt3_ 
        on product_ve0_.id=paymentopt3_.payment_options 
where    product_ve0_.id=?

Instead of

select pincode2_.pincode as col_0_0_,  paymentopt3_.Description as col_1_0_ 
from PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION product_ve0_
INNER JOIN product_catalog_vendor paymentid1_ 
        ON **product_ve0_.payment_id = paymentid1_.id** 
INNER JOIN PINCODES pincode2_ 
        ON **product_ve0_.pincode_id = pincode2_.pincode_id**
INNER JOIN payement_options paymentopt3_ 
        ON **product_ve0_.payment_options=paymentopt3_.payment_options**
where product_ve0_.id=1;

Product_catalog_vendor class:

@Entity
    public class Product_Catalog_Vendor extends baseEntity.Entity {

            @Id
            @Column(name="Id")
            private int id ;



//Setters and getters here

}

Pincodes Entity:

@Entity
public class Pincodes extends baseEntity.Entity {



    @Id
    private int pincode_id;

    @Column(name="pincode")
    private int pincode;


//Setters and getters here
}

payment_options Entity below:

@Entity
@Table(name="payement_options")     
public class Payment_Options extends baseEntity.Entity {

    @Id
    @Column(name="payment_options")
    private int paymentOptions;


//Setter getter

}

I have searched on many sites but unable to find the cause behind the scene. Please give me suggestions if i am doing something wrong. some good references would be appreciated. Thanks

@bellabax please find requested entity classes in the edited question. looking forward for your solution.Thanks — AskSharma, Aug 09 '13 at 07:58

Sarajog · Answer 1 · 2013-08-09T07:29:36.747

0

just to get your problem correcty, your query joins on paymentid1_.Id instead of paymentid1_.id? or am I missing the differenz between the expected and the real query?

I'm not a pro but just guessing I would say your query is joining to the id of the Product_Catalog_Vendor:

@Id
@Column(name="Id")
private int id ;

so because thats why its Id and not id...

edited Aug 09 '13 at 07:29

answered Aug 09 '13 at 06:50

Sarajog

164
16

i have added Product_Catalog_Vendor class in my question. please refer my question again. Thanks – AskSharma Aug 09 '13 at 07:08
it was my mistake i have written a little wrong generated query so i edited in it. my problem is, i want join on product_ve0_.payment_id = paymentid1_.id Instead of product_ve0_.id=paymentid1_.Id. hibernate taking my entity class id in every join which is not correct. – AskSharma Aug 09 '13 at 08:18

score 0 · Answer 2 · answered Aug 09 '13 at 07:57

0

I think you don't need mappedBy at all due to unidirectional mapping or,anyway ,you are using them in a weird way. mappedBy is necessary only if association is bidirectional (not your case) and should refers to a field which type is of the same type of entity where mappedBy is declared (and not a String type like in your case).
You used mappedBy in a way like referencedColumnName property

In your example:

@Entity
public class Product_Catalog_Vendor extends baseEntity.Entity {
  @ManyToOne
  private PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION pvpol;
}

and

public class PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION extends baseEntity.Entity {
  @Column(name="Payment_Id")
  @OneToMany(cascade=CascadeType.ALL,fetch=FetchType.EAGER,mappedBy="pvpol")
  private Set<Product_Catalog_Vendor> paymentId;
}

answered Aug 09 '13 at 07:57

Luca Basso Ricci

17,829
2
47
69

if i follow your suggestion then i have to create one more column in database table (product_catalog_vendor) named pvpol_id. is it? – AskSharma Aug 09 '13 at 08:20
yep. but the real question is: do you need that or you can be fine without `mappedBy`? I think you can do the same work without mappedBy – Luca Basso Ricci Aug 09 '13 at 08:45
i dont want to add new column in my table (product_catalog_vendor). how do i refer primary key of table product_catalog_vendor from PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION entity without using mappedBy. Because Payment_Id,pincode_id,payment_options are foreign keys in my table with composite key (ID,Payment_Id, PINCODE_ID). I am working on hibernate from last one and half months so may be i am missing something, will u pls help me to get the solution here. pls find tables here http://stackoverflow.com/questions/18118440/org-hibernate-mappingexception-named-query-not-known – AskSharma Aug 09 '13 at 10:27
From a single row of `PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION` how can you refer to multiple `Product_Catalog_Vendor` rows if you don't put `PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION` id to `Product_Catalog_Vendor`? Or I am missing something? Use `@OneToOne` or `@ManyToOne` in PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION – Luca Basso Ricci Aug 09 '13 at 10:36
Yes you are right, but in my PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION table, i made composite primary key (ID,Payment_Id, PINCODE_ID) where i am using Payment_Id to refer the Product_Catalog_Vendor Table's primary key id and similarly i am refering PINCODES table's primary key PINCODE_ID with PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION table's PINCODE_ID key which is foreign key and primary key too. i think you were asking me this? am i on the right direction? – AskSharma Aug 09 '13 at 11:13
This is the problem: you can't have a Set<> of elements if you hold in table just a single ID and don't have ppovl on the other side to allow multiplicity. Maybe you can use `@ManyToMany` (with jointable) or `@OneToOne`. Look at your table (on database): how many Pincodes can you have in a single PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION? Can you get a list of Pincodes from a single row of PRODUCT_VENDOR_PAYMENT_OPTION_LOCATION? In my opinionin what you are trying is impossibile with this table stucture – Luca Basso Ricci Aug 09 '13 at 11:29

Hibernate generating a query from Named Query that joins on the wrong column?

2 Answers2