休眠与聚合的关系 [英] Hibernate relation with aggregation

问题描述

@Entity
public class Device {
    private long id;
    private String deviceName; //column D_NAME
    private Set<LoginDate> loginDates;
}

/**
    Audit table being filled by some other processes on the network
 */

@Entity
public class LoginDate {
    private long id; //pk
    private String deviceName; //column D_NAME
    private Date loginDate;
}

我的目标是在报告屏幕上显示设备的最后登录日期；我已经实现了Device和LoginDate表之间的OneToMany关系，该关系使用OrderBy对降序的loginDates进行排序，以便可以选择要在屏幕上显示的第一项.但是，当登录数量变得越来越大时，这是一个有点昂贵的操作.我应该怎么做才能只选择最后一个登录日期；

• 我可以使用Hibernate的@Formula通过它生成Pojo 具有相关参数的构造函数.但这会触发 选择报告屏幕中的每个项目.

• 我可以在业务层上实现另一种方法，以便一旦 列表已加载；我可以启动另一个具有相关D_NAMES的HQL来获取 D_NAME，loginDate配对并在它们之间循环.哪一个 从数据库中选择一个，但在应用程序上进行映射 一侧.

那么，您是否还有其他建议要我提出一些建议，例如带有连接的查询以选择Device(设备)，并且它是最后一次loginDate(而不是日期)?我认为这应该以某种方式要求加入站点上的where子句，但后来我碰壁了，无法弄清楚该怎么做.

您的建议是什么?

顺便说一句，我没有发布Device的所有属性，但是您可能会意识到那里的许多属性以及大多数其他属性是通过选择使用HQL和LEFT OUTER连接到其他相关表/实体而获取的加入.

解决方案

我通常创建一个数据库视图，例如device_summary_data，然后使用@SecondaryTable批注(或使用@OneToOne作为另一个实体)将其映射到Device. /p>

https://docs.oracle.com/javaee /7/api/javax/persistence/SecondaryTable.html

查看:

create view device_summary_data as 
select d.id as device_id, max(l.login_date), count(l.device_name)
from logins l
inner join devices d on d.D_NAME = l.D_NAME
group by d.id

实体:

@Entity
@SecondaryTable(name = "device_summary_data",  
   pkJoinColumns=@PrimaryKeyJoinColumn(name="device_id", referencedColumnName = "id"))
public class Device {
    private long id;

    private String deviceName; 

    private Set<LoginDate> loginDates;

    @Column(table="device_summary_data", insertable=false, updateable = false)
    private Date lastLogin;

    @Column(table="device_summary_data", insertable=false, updateable = false)
    private Integer numberOfLogins;
}

优于Hibernate的@Formula:

• 与任何JPA实现一起使用
• 可以按照任何其他属性在数据库级别应用过滤，排序和分页.

@Entity
public class Device {
    private long id;
    private String deviceName; //column D_NAME
    private Set<LoginDate> loginDates;
}

/**
    Audit table being filled by some other processes on the network
 */

@Entity
public class LoginDate {
    private long id; //pk
    private String deviceName; //column D_NAME
    private Date loginDate;
}

My aim to display the last login date of the device on the report screens; I have implemented OneToMany relation between Device and LoginDate tables where it is using OrderBy to order loginDates descending so that I can choose the first item to display on the screen. But that's a bit expensive operation when it comes to the number of logins becomes larger and larger. What should I do to achieve to select only the last login date;

• I can use @Formula of Hibernate to generate a Pojo via it's constructor with related parameters. But that will trigger number of selects per item in the report screen.

• I can implement another method on the business layer so that once the list loaded; I can initiate another HQL with related D_NAMES to fetch the D_NAME, loginDate couples and loop through them. Which is one single select from the DB but a mapping work on the application side.

So do you have some other things for me to suggest something like a query with joins to select Device and it's last loginDate (not dates) at once ? I think This should somehow require where clause on the join site but then I hit the wall and could not figured out how to do that.

What is your suggestion ?

BTW I did not post all the properties of the Device but as you might realize that a lot of properties there and most of the other properties are getting fetched by a select with joins to other related tables/entites using HQL and LEFT OUTER JOINS.

解决方案

I normally create a database view say, device_summary_data and then map that to Device using the @SecondaryTable annotation (or as another Entity using @OneToOne).

https://docs.oracle.com/javaee/7/api/javax/persistence/SecondaryTable.html

View:

create view device_summary_data as 
select d.id as device_id, max(l.login_date), count(l.device_name)
from logins l
inner join devices d on d.D_NAME = l.D_NAME
group by d.id

Entity:

@Entity
@SecondaryTable(name = "device_summary_data",  
   pkJoinColumns=@PrimaryKeyJoinColumn(name="device_id", referencedColumnName = "id"))
public class Device {
    private long id;

    private String deviceName; 

    private Set<LoginDate> loginDates;

    @Column(table="device_summary_data", insertable=false, updateable = false)
    private Date lastLogin;

    @Column(table="device_summary_data", insertable=false, updateable = false)
    private Integer numberOfLogins;
}

Advantages over Hibernate's @Formula:

• works with any JPA implementation
• filtering, sorting and paging can be applied at the DB level as per any other property.

这篇关于休眠与聚合的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！