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.

Blake Pettersson · Accepted Answer · 2009-06-04 15:39:59Z

13

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%'

edited Jun 4, 2009 at 15:39

answered Jun 4, 2009 at 12:31

Blake Pettersson

9,1273 gold badges29 silver badges36 bronze badges

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

4 Comments

Jugal Over a year ago

hello Blake. please help me When I used this query it is giving me the error java.lang.NoSuchMethodError: org.hibernate.hql.antlr.HqlBaseParser.recover(Lantlr/RecognitionException;Lantlr/collections/impl/BitSet;)V

Blake Pettersson Over a year ago

I've edited the query, there shouldn't have been any User in the query.

Jugal Over a year ago

your edited query looks ok but I want only the username field from User so when i add select clause like select u.username it gives me error org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list

Blake Pettersson Over a year ago

How about when reversing the query? select u.username from User u join u.virtualdomainset vd where vd.domainname = "example.com" and v.name like 'foo%'

Daksh · Accepted Answer · 2019-11-30 17:40:23Z

4

Always difficult to write HQL without 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.

edited Nov 30, 2019 at 17:40

Daksh

1,15412 silver badges23 bronze badges

answered Jun 4, 2009 at 12:20

Gareth Davis

28.1k12 gold badges77 silver badges107 bronze badges

7 Comments

Jugal Over a year ago

Thanks but i can't understand tha last condition v.name like 'foo%'

Blake Pettersson Over a year ago

That's the same kind of like clause that is found in SQL. sql-tutorial.net/SQL-LIKE.asp

Gareth Davis Over a year ago

just corrected the typo. as Blake says the like clause is just the same as the SQL one

Jugal Over a year ago

When I used this query it is giving me the error java.lang.NoSuchMethodError: org.hibernate.hql.antlr.HqlBaseParser.recover(Lantlr/RecognitionException;Lantlr/collections/impl/BitSet;)V

Gareth Davis Over a year ago

that'll be an issue with the libraries that you have included in your application. I'm guessing that antlr.jar is the wrong version

|

Collectives™ on Stack Overflow

how to write join query in hibernate

2 Answers 2

4 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related