Hibernate- @OnetoMany with Where clause on Collection

Question

In my One-to-Many and Many-to-One bi-directional relation, I want execute the following sql-

select * from user_credential c join user_profile p on c.login_id = p.login_id join user_address a on p.address_id = a.address_id where p.profile_id = 1

But, I'm getting the result of the sql-

select * from user_credential c join user_profile p on c.login_id = p.login_id join user_address a on p.address_id = a.address_id where p.credential_id = 1

The hibernate entity details-

 @Entity @Table(name = "user_credential") public class UserCredential implements Serializable { private static final long serialVersionUID = -2839071606927921689L; @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "login_id", insertable = false, updatable = false, nullable = false) private int login_id = 0; @Column(name = "password", insertable = true, updatable = true, nullable = false) private String password = null; @OneToMany(mappedBy = "login_id", fetch = FetchType.EAGER, cascade = CascadeType.ALL) private Set<UserProfile> profiles = null; //getters/setters } @Entity @Table(name = "user_profile") public class UserProfile implements Serializable { private static final long serialVersionUID = 5765280899633539336L; @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "profile_id", length = 10, insertable = false, updatable = false, nullable = false) private int profile_id = 0; @ManyToOne @JoinColumn(name = "login_id", insertable = true, updatable = true, nullable = false) private UserCredential login_id = null; @Column(name = "name", length = 20, insertable = true, updatable = true, nullable = false) private String name = null; @Column(name = "age", length = 3, insertable = true, updatable = true, nullable = false) private byte age = 0; @OneToMany(mappedBy = "profile_id", fetch = FetchType.EAGER, cascade = CascadeType.ALL) private Set<UserAddress> address = null; //getters/setters } @Entity @Table(name = "user_address") public class UserAddress extends BaseTable{ private static final long serialVersionUID = 5036341911955664992L; @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "address_id", length = 10, insertable = false, updatable = false, nullable = false) private int address_id = 0; @ManyToOne @JoinColumn(name = "profile_id", insertable = true, updatable = true, nullable = false) private UserProfile profile_id = null; @Column(name = "state", length = 20, insertable = true, updatable = true, nullable = false) private String state = null; @Column(name = "city", length = 20, insertable = true, updatable = true, nullable = false) private String city = null; //getters/setters }

HQL:

select credential from UserCredential credential join credential.profiles profile where profile.profile_id = 1

I'm not understanding, why hibernate is filtering the data on parent id and also, how my required sql will be executed. I'm using hibernate 4.3.8

Please tell, if any other information is necessary.

Vlad Mihalcea · Accepted Answer · 2015-04-17 06:23:03Z

1

I would write the query like this:

select distinct c from Profile p join p.login_id c where p.profile_id = 1

The duplicates are removed and joining from Child to Parent might help you with your problem.

answered Apr 17, 2015 at 6:23

Vlad Mihalcea

156k85 gold badges598 silver badges984 bronze badges

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

3 Comments

joy-cs Over a year ago

This approach is useful, unless I add join and condition on the last child UserAddress. Also, join p.login_id c is not required in this case, since it will definitely make a join with UserProfile and will fetch an unique result. The distinct is also not required for the same reason. However, I'm getting a ClassCastException between UserCredential and UserProfile, while using the above query.

Vlad Mihalcea Over a year ago

You get a ClassCastException because the query returns a List of Profiles while you didn't change the previous List of Credentials.

joy-cs Over a year ago

I didn't debug that. It was actually returning the a single record of Credential, not Profile, as you're fetching distinct c. But still the result is same. If you need any other information, please tell.

Collectives™ on Stack Overflow

Hibernate- @OnetoMany with Where clause on Collection

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related