how to write join query in hibernate

Question

I have created two beans User and VirtualDomain with many to many relationship

@Entity
@Table(name = "tblUser")
public class User implements Serializable {
    private Long id;
    private String username;
    private Set<VirtualDomain> virtualdomainset;
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
public Long getId() {
 return id;
}

public void setId(Long id) {
 this.id = id;
}

@Column(name = "username", length = 50, nullable = false)
public String getUsername() {
 return username;
}

public void setUsername(String username) {
 this.username = username;
}
   @ManyToMany(targetEntity = VirtualDomain.class, cascade = {CascadeType.PERSIST},fetch=FetchType.EAGER)
    @JoinTable(name = "tblUserDomainRel", joinColumns = @JoinColumn(name = "userid"), inverseJoinColumns = @JoinColumn(name = "domainid"))
    public Set<VirtualDomain> getVirtualdomainset() {
     return virtualdomainset;
    }

    public void setVirtualdomainset(Set<VirtualDomain> virtualdomainset) {
     this.virtualdomainset = virtualdomainset;
    }

}

@Entity
@Table(name = "tblVirtualDomain")
public class VirtualDomain  {
    private Long id;
    private String domainname;
    private Set<User> userset;
@Id
@JoinColumn(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
public Long getId() {
 return id;
}

public void setId(Long id) {
 this.id = id;
}

@Column(name = "domain_name")
public String getDomainname() {
 return domainname;
}

public void setDomainname(String domainname) {
 this.domainname = domainname;
}
@ManyToMany(cascade = {CascadeType.ALL},fetch=FetchType.EAGER, mappedBy = "virtualdomainset", targetEntity = User.class)

public Set<User> getUserset() {
 return userset;
}

public void setUserset(Set<User> userset) {
 this.userset = userset;
}
}

how to get data of user like username related to particular domain through hibernate.

Answer 1

Always differcult todo HQL with out a test system...but here we go:

select u from VirtualDomain vd join User vd.usersset u 
       where vd.domainname = 'example.com' and u.username like 'foo%'

Let me know how you get on.

One tip I often did prior to buying Intellji was to stop the app in the debugger and then use the immediate window to experiment with HQL.

The hibernate documentation on joins has always been a bit cryptic in my opinion.

Answer 2

To add to gid's answer, if for some reason you need to eagerly fetch an entites relations, then the join syntax would be join fetch.

from VirtualDomain vd join fetch vd.usersset u 
   where vd.domainname = 'example.com' and u.username like 'foo%'