在Hibernate中查询组合表 [英] Querying composite table in Hibernate
本文介绍了在Hibernate中查询组合表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在研究一个Spring-MVC应用程序，我有一个多对多的关系，在这个关系中我必须在两个表中查询以获得我需要的值。我将更详细地解释。
- 我有2个表GroupAccount，GroupMembers与多对多
的关系。现在有一个名为membertable的联结表，其中存储了来自GroupMembers和GroupAccount的
id。
这就是我期待的for：
- 我传递一个groupAccounId和username作为参数。现在，在
GroupMembers表中，存储了一个用户名。在groupAccount中，
存储了groupAccountId。 - 现在在memberjunction中，我有组合键
memberid，GroupAccountId，我希望用户名的成员标识
，它有一个匹配的groupAccountId我提交。
以下是SQL代码和Spring-mvc代码，以便更好地理解。
CREATE TABLE public.groupaccount（
groupid NUMERIC NOT NULL，
groupname VARCHAR，
groupaccountstatus BOOLEAN DEFAULT false NOT NULL，
adminusername VARCHAR，
CONSTRAINT groupid PRIMARY KEY（groupid）
）;
CREATE TABLE public.groupmembers（
memberid INTEGER NOT NULL，
musername VARCHAR
CONSTRAINT memberid PRIMARY KEY（memberid）
）;
CREATE TABLE public.memberjunction（
memberid INTEGER NOT NULL，
groupid NUMERIC NOT NULL，
CONSTRAINT membergroupid PRIMARY KEY（memberid，groupid）
）;
GroupMembersDAOImpl：＃
@Override
public List< Integer> returnMemberIdWithMatchingUsername（String memberUsername）{
session = this.sessionFactory.getCurrentSession（）;
org.hibernate.Query query = session.createQuery（From GroupMembers as+
n where n.memberUsername =：memberUsername）;
query.setParameter（memberUsername，memberUsername）;
列表< GroupMembers> memberList = query.list（）;
列表<整数> memberIdList = new ArrayList<>（）; （GroupMembers members：memberList）
{
memberIdList.add（members.getMemberid（））;
}
return memberIdList;
GroupAccount模型：
@Entity
@Table（name =groupaccount）
public class GroupAccount {
@Id
@Column （name =groupid）
@GeneratedValue（strategy = GenerationType.SEQUENCE，generator =groupaccount_seq_gen）
@SequenceGenerator（name =groupaccount_seq_gen，sequenceName =groupaccount_seq）
private Long groupId;
@ManyToMany（cascade = CascadeType.ALL）
@JoinTable（name =memberjunction，joinColumns = {@JoinColumn（name =groupid）}，
inverseJoinColumns = {@JoinColumn（name =memberid）}）
private设置< GroupMembers> groupMembersSet = new HashSet<>（）;
public void setGroupMembersSet（Set< GroupMembers> groupMembersSet）{
this.groupMembersSet = groupMembersSet;
GroupMembers模型类：
@Entity
@Table（name =groupmembers）
public class GroupMembers {
@Id
@Column（name =memberid）
@GeneratedValue（strategy = GenerationType.SEQUENCE，generator =groupmembers_seq_gen）
@SequenceGenerator（name =groupmembers_seq_gen，sequenceName =groupmembers_seq）
private int memberid;
@ManyToMany（mappedBy =groupMembersSet）
private Set< GroupAccount> groupAccounts = new HashSet<>（）;
public void setGroupAccounts（Set< GroupAccount> groupAccounts）{
this.groupAccounts = groupAccounts;
}
public Set< GroupAccount> getGroupAccounts（）{
return this.groupAccounts;
我正在使用的查询：
@Override
public int getMemberIdForCanvas（String memberUsername，Long groupId）{
session = this.sessionFactory.getCurrentSession（）;
org.hibernate.Query query = session.createQuery（从GroupMembers m \\\
中选择不同的m.memberId+
join m.groupAccounts a \\\
+
where a.memberUsername =：userName和m.groupId =：groupId）;
query.setParameter（memberUsername，memberUsername）;
query.setParameter（String.valueOf（groupId），groupId）;
int memberid =（Integer）query.uniqueResult（）;
return memberid;
}
任何帮助都不错。非常感谢。
解决方案
这里是连接和HQL的文档。请阅读它。
查询非常简单，只需
select不同的m.memberId from GroupMembers m
join m.groupAccounts a
其中a.memberUsername =：userName
请修复您的命名。 GroupMembers 实例是一个单个组成员。所以这个类应该被命名为 GroupMember ，而不是 s 。在该类的字段中重复类的名称也是多余的： member.getId（）比更具可读性并且更简洁。member.getMemberId （）。相同的其他领域。
I am working on a Spring-MVC application where I have a many-to-many relationship in which I have to query in 2 tables to get the values I require. I will explain in more detail.
- I have 2 tables GroupAccount, GroupMembers with many-to-many relationship. Now there is a junction table called membertable where id from GroupMembers and GroupAccount is stored.
This is what I am looking for :
- I pass a groupAccounId and username as parameters. Now, in the GroupMembers table, there is a username stored. In groupAccount, there is groupAccountId is stored.
- Now in the memberjunction, I have composite key memberid,GroupAccountId, I would like the member id for the username which has a matching groupAccountId I submit.
Below is the SQL code and Spring-mvc code to understand more better.
CREATE TABLE public.groupaccount (
groupid NUMERIC NOT NULL,
groupname VARCHAR,
groupaccountstatus BOOLEAN DEFAULT false NOT NULL,
adminusername VARCHAR,
CONSTRAINT groupid PRIMARY KEY (groupid)
);
CREATE TABLE public.groupmembers (
memberid INTEGER NOT NULL,
musername VARCHAR
CONSTRAINT memberid PRIMARY KEY (memberid)
);
CREATE TABLE public.memberjunction (
memberid INTEGER NOT NULL,
groupid NUMERIC NOT NULL,
CONSTRAINT membergroupid PRIMARY KEY (memberid, groupid)
);
GroupMembersDAOImpl :#
@Override
public List<Integer> returnMemberIdWithMatchingUsername(String memberUsername) {
session = this.sessionFactory.getCurrentSession();
org.hibernate.Query query = session.createQuery("From GroupMembers as " +
"n where n.memberUsername=:memberUsername");
query.setParameter("memberUsername",memberUsername);
List<GroupMembers> memberList = query.list();
List<Integer> memberIdList = new ArrayList<>();
for(GroupMembers members :memberList){
memberIdList.add(members.getMemberid());
}
return memberIdList;
}
GroupAccount model :
@Entity
@Table(name="groupaccount")
public class GroupAccount {
@Id
@Column(name="groupid")
@GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "groupaccount_seq_gen")
@SequenceGenerator(name = "groupaccount_seq_gen",sequenceName = "groupaccount_seq")
private Long groupId;
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = "memberjunction", joinColumns = {@JoinColumn(name = "groupid")},
inverseJoinColumns = {@JoinColumn(name = "memberid")})
private Set<GroupMembers> groupMembersSet = new HashSet<>();
public void setGroupMembersSet(Set<GroupMembers> groupMembersSet){
this.groupMembersSet = groupMembersSet;
}
}
GroupMembers model class :
@Entity
@Table(name="groupmembers")
public class GroupMembers {
@Id
@Column(name="memberid")
@GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "groupmembers_seq_gen")
@SequenceGenerator(name = "groupmembers_seq_gen",sequenceName = "groupmembers_seq")
private int memberid;
@ManyToMany(mappedBy = "groupMembersSet")
private Set<GroupAccount> groupAccounts = new HashSet<>();
public void setGroupAccounts(Set<GroupAccount> groupAccounts){
this.groupAccounts = groupAccounts;
}
public Set<GroupAccount> getGroupAccounts(){
return this.groupAccounts;
}
}
Query I am using :
@Override
public int getMemberIdForCanvas(String memberUsername, Long groupId) {
session = this.sessionFactory.getCurrentSession();
org.hibernate.Query query = session.createQuery("select distinct m.memberId from GroupMembers m\n" +
"join m.groupAccounts a\n" +
"where a.memberUsername = :userName and m.groupId=:groupId");
query.setParameter(memberUsername,"memberUsername");
query.setParameter(String.valueOf(groupId),"groupId");
int memberid = (Integer)query.uniqueResult();
return memberid;
}
Any help would be nice. Thanks a lot.
解决方案
Here's the documentation for joins and HQL. Please read it.
The query is as simple as
select distinct m.memberId from GroupMembers m
join m.groupAccounts a
where a.memberUsername = :userName
Please also fix your naming. A GroupMembers instance is a single group member. So the class should be named GroupMember, without s. Repeating the name of the class in the fields of this class is also redundant: member.getId() is more readable and less verbose than member.getMemberId(). Same for the other fields.
这篇关于在Hibernate中查询组合表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
