CODEWITHSUNDEEP
hibernatejpajoinormrelational-database
Olgun Kaya
Olgun Kaya

Reputation: 2579

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;
}

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;

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.

Upvotes: 0

Views: 1219

Answers (2)

pirho
pirho

Reputation: 12215

I would do it by inverting the mapping and making some JPQL pageable / HQL query.

So your LoginDate would be something like:

@Entity
public class LoginDate {
    private long id; //pk
    @ManyToOne
    private Device device;
    private Date loginDate;
}

and custom query (JPQL) would be something like

SELECT ld FROM LoginDate ld WHERE device=:device ORDER BY ld.loginDate DESC

Depending on your other libraries / frameworks you would need to make the JPQL query to limit into one result only unless you are able to use simple native query like:

SELECT ld.logindate
FROM logindate ld
WHERE ld.device_id=:device_id
ORDER BY ld.logindate DESC
LIMIT 1

You might still leave Set<LoginDate> to your Device but add fetch=FetchType.LAZY to it for other purposes.

Upvotes: 1

Alan Hay
Alan Hay

Reputation: 23226

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.

Upvotes: 1

Related Questions