java - 提高多对多关系中的 Hibernete 性能

Question

我有两个以多对多关系连接的表。数据库设置在另一台服务器上，当我尝试获取有关其中一条记录的信息(如果这些信息包括第二个表的总数)时，我发现非常大的性能问题。

第一个 bean :

package dbaccess.beans.newsletter;

import java.sql.Timestamp;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Column;
import javax.persistence.Table;

import dbaccess.beans.RegisteredUser;

@Entity
@Table(name="NEWSLETTER_LIST")
public class NewsletterList {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "G1")
    @SequenceGenerator(name = "G1", sequenceName = "NEWSLETTER_LIST_SEQ", allocationSize = 1, initialValue= 1)
    @Column(name = "LIST_ID", unique = true, nullable = false)
    private Long listID;

    @Column(name = "LIST_NAME", nullable = false, length = 50)
    private String listName;

    @ManyToMany(fetch = FetchType.LAZY, cascade = {})
    @JoinTable(name = "NEWSLETTERLISTS_USERS", joinColumns = { 
            @JoinColumn(name = "LIST_ID", nullable = false) }, 
            inverseJoinColumns = { @JoinColumn(name = "USER_ID", nullable = false) })
    private Set<RegisteredUser> users = new HashSet<RegisteredUser>(0);

    public Long getListID() {
        return listID;
    }

    public void setListID(Long listID) {
        this.listID = listID;
    }

    public Set<RegisteredUser> getUsers() {
        return users;
    }

    public void setUsers(Set<RegisteredUser> users) {
        this.users = users;
    }

}

第二个 bean :

package dbaccess.beans;

import java.sql.Timestamp;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Column;
import javax.persistence.Table;

import dbaccess.beans.newsletter.NewsletterList;

@Entity
@Table(name="USER")
public class RegisteredUser {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "G1")
    @SequenceGenerator(name = "G1", sequenceName = "USER_SEQ", allocationSize = 1, initialValue= 1)
    @Column(name = "USER_ID", unique = true, nullable = false)
    private Long usrID;

    @Column(name = "GIVENNAME", length = 20)
    private String usrGivenName;

    @Column(name = "FAMILYNAME", length = 20)
    private String usrFamilyName;

    @ManyToMany(fetch = FetchType.EAGER, mappedBy = "users", cascade = {})
    public Set<NewsletterList> newsletterList = new HashSet<NewsletterList>();

    public Long getUsrID() {
        return usrID;
    }

    public void setUsrID(Long usrID) {
        this.usrID = usrID;
    }

    public String getUsrGivenName() {
        return usrGivenName;
    }

    public void setUsrGivenName(String usrGivenName) {
        this.usrGivenName = usrGivenName;
    }

    public String getUsrFamilyName() {
        return usrFamilyName;
    }

    public void setUsrFamilyName(String usrFamilyName) {
        this.usrFamilyName = usrFamilyName;
    }

    public Set<NewsletterList> getNewsletterList() {
        return newsletterList;
    }

    public void setNewsletterList(Set<NewsletterList> newsletterList) {
        this.newsletterList = newsletterList;
    }

    @Override
    public String toString() {
        return "RegisteredUser[usrID=" + usrID + ", usrGivenName=" + usrGivenName + ", usrFamilyName=" + usrFamilyName + "]";
    }

}

问题是当我尝试执行这段代码时:

session = dbService.getSessionFactory().openSession();
Criteria c = session.createCriteria(NewsletterList.class);
c.add(Restrictions.eq("listID", listID));
List<NewsletterList> newsletterList = (List<NewsletterList>) c.list();

//below is most expensive
newsletterList.get(0).getUsers().size()

有什么办法可以提高这个性能吗？提前致谢。

PS 当我有大约。一个列表中有 70 个用户，请求上述代码大约需要 5-6 秒!

Answer 1

newsletterList.get(0).getUsers().size() 使 Hibernate 加载所有注册到新闻通讯的用户，仅获取注册用户的数量。

使用临时 HQL 查询来统计注册用户的数量:

select count(user.usrID) from RegisteredUser user
inner join user.newsletterList newsLetter
where newsLetter.listID = :listId

请注意，执行上述代码需要 5-6 秒，但时间太长了。您可能需要检查连接表的连接列上是否放置了索引。

另请注意，您可以简单地使用 session.get(NewsLetter.class, listId) 按 ID 获取列表。

最后，如果您的 ID 全部命名为 id，一切都会变得更容易且更具可读性。