I have a problem with using QueryDSL to create a query. I want to retrieve all groups of a certain user by its id. How does this works?
public List<Group> findGroupsByUser(Integer userId) {
JPQLQuery query = new JPAQuery(getEntityManager());
??????
return result;
}
Mapped classes:
@Entity(name = "user")
public class User {
@Id
private int id;
private String login;
@ManyToMany
@JoinTable(name = "user2group", joinColumns = @JoinColumn(name = "uid"), inverseJoinColumns = @JoinColumn(name = "gid"))
private Set<Group> groups;
...
}
@Entity(name = "group")
public class Group {
@Id
private int id;
private String name;
@ManyToMany
@JoinTable(name = "user2group", joinColumns = @JoinColumn(name = "uid"), inverseJoinColumns = @JoinColumn(name = "gid"))
private Set<User> users;
...
}
Database tables:
create table group(
id int(10) not null auto_increment primary key,
name varchar(255) not null,
creationdate datetime not null,
creator int(10) not null,
privacy enum('PUBLIC', 'PRIVATE') not null,
constraint foreign key (creator) references user(id)
)
create table user2group(
uid int(10) not null,
gid int(10) not null,
primary key (uid, gid),
constraint foreign key (uid) references user(id),
constraint foreign key (gid) references group(id)
)
create table user(
id int(10) not null auto_increment primary key,
lastname varchar(50) not null,
firstname varchar(50) not null,
createdate datetime not null,
login varchar(100) unique not null,
password varchar(40) not null
)
Subqueries To create a subquery you create a JPASubQuery instance, define the query parameters via from, where etc and use unique or list to create a subquery, which is just a type-safe Querydsl expression for the query. unique is used for a unique (single) result and list for a list result.
Querydsl is an extensive Java framework, which allows for the generation of type-safe queries in a syntax similar to SQL. It currently has a wide range of support for various backends through the use of separate modules including JPA, JDO, SQL, Java collections, RDF, Lucene, Hibernate Search, and MongoDB.
In JPA we use the @ManyToMany annotation to model many-to-many relationships. This type of relationship can be unidirectional or bidirectional: In a unidirectional relationship only one entity in the relationship points the other. In a bidirectional relationship both entities point to each other.
Something like the following should work
from(user).innerJoin(user.groups, group)
.where(user.id.eq(userId))
.list(group);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With