I'm having a database performance concern.
Let's suppose my data model looks like follows.
UserTable.java
@Id
@Column(name = "USER_ID", nullable = false)
private Long userId;
// other user fields
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
private Set<ProfilePhotoTable> photos = new HashSet<>();
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
private Set<AlbumPhotoTable> photos = new HashSet<>();
PhotoTable.java
@Id
@Column(name = "PHOTO_ID", nullable = false)
private Long photoId;
// other photo fields
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "USER_ID")
private UserTable user;
ProfilePhotoTable.java
@Entity
@Table(name = "PROFILEPHOTO")
@PrimaryKeyJoinColumn(name = "PROFILEPHOTO_ID", referencedColumnName = "PHOTO_ID")
public class ProfilePhotoTable extends PhotoTable
AlbumPhotoTable.java
@Entity
@Table(name = "ALBUMPHOTO")
@PrimaryKeyJoinColumn(name = "ALBUMPHOTO_ID", referencedColumnName = "PHOTO_ID")
public class ProfilePhotoTable extends PhotoTable
Now let's say I want to write a query that would fetch all the photos for the user - all profile photos and all album photos.
However, I don't want the photos to be fetched every time that I'm requesting the User information from the database, and this is why I specified the fetch = FetchType.LAZY on the photo fields.
Basically, I have a doubt between the two approaches.
Two independent queries, the first one fetching the
UserTablefrom the database by the ID, and the second fetching the photos, something likeSELECT * FROM Photo WHERE userId = :userId.One single query that would
join fetchthe user with the corresponding photos. However, I'm not quite sure how this query would go, since the photos are separated in the Album and Profile photos. I found in this article that something likecomments = entityManager.createQuery(
"select pc " + "from PostComment pc " + "join fetch pc.post " + "where pc.review = :review", PostComment.class) .setParameter("review", review) .getResultList();
should be used, but I'm not sure how to apply it to my use-case.
The question is, which approach is better in terms of performance, and if 2nd, how the query should be structured?
usereager (="not lazy") (to achieve what you want (as I understand) ..and "lazyness" makes most sense on the "N:-" side of the association)