Custom Join with composite key Hibernate

user_1232131 0
Viewed 242

thanks for your time! I have a complex scenario that I'm trying to map with JPA/Hibernate in spring.

First, I have a function that generates dates dynamically, which outputs the following:

id                                  |starts_at              |ends_at                |frequency|separation|count|until|timezone_name|is_full_day|location_id                         |created_at             |updated_at             |version|name                         |description      |picture|
------------------------------------+-----------------------+-----------------------+---------+----------+-----+-----+-------------+-----------+------------------------------------+-----------------------+-----------------------+-------+-----------------------------+-----------------+-------+
d255afb6-3730-4845-8e5e-18baa909dcfe|2021-12-21 18:00:00.000|2021-12-22 02:00:00.000|ONCE     |         1|     |     |UTC          |false      |fe88fa63-360e-4281-a383-35826a759e59|2021-10-29 00:22:46.109|2021-10-29 00:22:46.109|      0|                             |                 |       |
e7193453-4c1b-433d-8127-5eedf2efd011|2022-02-20 20:00:00.000|2022-02-20 23:00:00.000|WEEKLY   |         1|     |     |UTC          |false      |fe88fa63-360e-4281-a383-35826a759e59|2021-11-02 19:23:31.029|2021-11-02 19:23:31.029|      0|Festa da minha graaande amiga|A Fofinha nao vai|       |
e7193453-4c1b-433d-8127-5eedf2efd011|2022-02-21 20:00:00.000|2022-02-21 23:00:00.000|WEEKLY   |         1|     |     |UTC          |false      |fe88fa63-360e-4281-a383-35826a759e59|2021-11-02 19:23:31.029|2021-11-02 19:23:31.029|      0|Muito massa essa fita        |Mudanca drastica |       |
e7193453-4c1b-433d-8127-5eedf2efd011|2022-02-22 20:00:00.000|2022-02-22 23:00:00.000|WEEKLY   |         1|     |     |UTC          |false      |fe88fa63-360e-4281-a383-35826a759e59|2021-11-02 19:23:31.029|2021-11-02 19:23:31.029|      0|Festa da minha graaande amiga|A Fofinha nao vai|       |
e7193453-4c1b-433d-8127-5eedf2efd011|2022-02-24 20:00:00.000|2022-02-24 23:00:00.000|WEEKLY   |         1|     |     |UTC          |false      |fe88fa63-360e-4281-a383-35826a759e59|2021-11-02 19:23:31.029|2021-11-02 19:23:31.029|      0|Festa da minha graaande amiga|A Fofinha nao vai|       |
e7193453-4c1b-433d-8127-5eedf2efd011|2022-02-25 20:00:00.000|2022-02-25 23:00:00.000|WEEKLY   |         1|     |     |UTC          |false      |fe88fa63-360e-4281-a383-35826a759e59|2021-11-02 19:23:31.029|2021-11-02 19:23:31.029|      0|Festa da minha graaande amiga|A Fofinha nao vai|       |

The important parts are: id (which is the same, since it's the same entity in the database; starts_at: the initial date of the event and the metadata part (which is a left join with another table);

Here is the query that outputs the example above:

    select
    e.*,
    coalesce (em.name,
    em2.name) as name,
    coalesce (em.description ,
    em2.description) as description,
    coalesce (em.picture ,
    em2.picture) as picture
from
    recurring_events_for('2021-12-03T10:15:30',
    '2022-12-03T10:15:30',
    'UTC',
    5,
    false) e
left join events_metadata em on
    em.event_id = e.id
    and em.starts_at = e.starts_at
left join events_metadata em2
on
    em2.event_id = e.id
    and em2.starts_at = (
    select
        MIN(starts_at)
    from
        events_metadata
    where
        event_id = e.id
    limit 1)

How I've mapped this using Hibernate:

Event (output from recurring_events_for):

@Entity
@Table(name = "events")
public class Event extends BaseEntity {

    @Column
    private LocalDateTime startsAt;

    @Column
    private LocalDateTime endsAt;

    @Column
    @Enumerated(EnumType.STRING)
    private EventFrequency frequency;

    @Column(nullable = false)
    private Integer separation;

    @Column
    private Integer count;

    @Column
    private LocalDate until;

    @Column(nullable = false)
    private String timezoneName;

    @Column(nullable = false)
    private boolean isFullDay;

    @ManyToMany
    @JoinTable(
            name = "event_organizers",
            joinColumns = @JoinColumn(name = "event_id"),
            inverseJoinColumns = @JoinColumn(name = "user_id"))
    private Set<User> organizers;

    @OneToMany(mappedBy = "product")
    private Set<EventProduct> products;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH})
    @JoinColumn(name = "location_id")
    private Location location;

    @OneToMany(mappedBy = "event",
            orphanRemoval = true,
            cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH})
    private Set<EventRecurrence> recurrences;

    @OneToOne(mappedBy = "id.event", optional = false, orphanRemoval = true, fetch = FetchType.LAZY)
    private EventMetadata metadata;

The event_metadata table:

@Table(name = "events_metadata")
@Entity
public class EventMetadata extends BaseEntityNoId {

    @EmbeddedId
    private EventMetadataPK id;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private String description;

    @Column
    private String picture;

    @Column(nullable = false)
    private boolean isPrivate;

    @Column(nullable = false)
    private boolean showGuestList;

    @Column(nullable = false)
    private boolean friendCanInviteFriends;

    @OneToMany(mappedBy = "event", orphanRemoval = true)
    private Set<EventAttendance> eventAttendances;

The embedded PK:

@Embeddable
public class EventMetadataPK implements Serializable {

    @OneToOne(orphanRemoval = true)
    @JoinColumn(name = "event_id", updatable = false, insertable = false)
    private Event event;

    @Column(nullable = false)
    private LocalDateTime startsAt;

Finally, how I'm calling this in JPA world:

@Query(nativeQuery = true,
            value = "select e.*, coalesce (em.*, em2.*) as metadata " +
                    "from recurring_events_for(:range_start, :range_end, :time_zone, :events_limit, false) e " +
                    "left join events_metadata em on em.event_id = e.id and em.starts_at = e.starts_at " +
                    "left join events_metadata em2 " +
                    "on em2.event_id = e.id " +
                    "and em2.starts_at = (select MIN(starts_at) from events_metadata where event_id = e.id limit 1)")
    List<Event> getEvents(@Param("range_start") LocalDateTime rangeStart,
                          @Param("range_end") LocalDateTime rangeEnd,
                          @Param("time_zone") String timezone,
                          @Param("events_limit") Integer limit);

The problem is, the starts_at column mapped in the embedded PK is joined virtually with the result of recurring_events_for, therefore not possible (I think) to map it using Hibernate. How can I handle this situation??

Thanks!

spring-data-jpa spring-boot hibernate spring java
1 Answers

You could try with constructor result set mapping

  1. Annotate the Event entity similar to this:
@Entity
@Table(name = "events")
@SqlResultSetMapping(
        name = "recurring_events_mapping",
        classes = {
                @ConstructorResult(
                        targetClass = Event.class,
                        columns = {
                                @ColumnResult(name = "ID", type = String.class),
                                @ColumnResult(name = "STARTS_AT", type = LocalDateTime.class),
                                @ColumnResult(name = "ENDS_AT", type = LocalDateTime.class),
                                @ColumnResult(name = "FREQUENCY", type = String.class),
                                @ColumnResult(name = "SEPARATION", type = Integer.class),
                                @ColumnResult(name = "COUNT", type = Integer.class),
                                @ColumnResult(name = "UNTIL", type = LocalDate.class),
                                @ColumnResult(name = "TIMEZONE_NAME", type = String.class),
                                @ColumnResult(name = "IS_FULL_DAY", type = Boolean.class),
                                @ColumnResult(name = "LOCATION_ID", type = String.class),
                                @ColumnResult(name = "CREATED_AT", type = LocalDateTime.class),
                                @ColumnResult(name = "UPDATED_AT", type = LocalDateTime.class),
                                @ColumnResult(name = "VERSION", type = Integer.class),
                                // metadata
                                @ColumnResult(name = "NAME", type = String.class),
                                @ColumnResult(name = "DESCRIPTION", type = String.class),
                                @ColumnResult(name = "PICTURE", type = String.class)
                        }
                )
        }
)
@NamedNativeQuery(
        name = "Event.RecurringEventsQuery",
        query = "select  e.*, " +
                "    coalesce (em.name, em2.name) as name, " +
                "    coalesce (em.description, em2.description) as description, " +
                "    coalesce (em.picture , em2.picture) as picture " +
                "from recurring_events_mock e " + // just for test
                "left join events_metadata em on " +
                "    em.event_id = e.id " +
                "    and em.starts_at = e.starts_at " +
                "left join events_metadata em2 on " +
                "    em2.event_id = e.id " +
                "    and em2.starts_at = ( " +
                "    select MIN(starts_at) " +
                "    from events_metadata " +
                "    where event_id = e.id " +
                "    limit 1)" +
                "where :time_zone IS NOT NULL " + // just for test
                "    and :events_limit > 0 " + // just for test
                "    and :range_start IS NOT NULL " + // just for test
                "    and :range_end IS NOT NULL ", // just for test
        resultSetMapping = "recurring_events_mapping")
  1. Then in your EventRepository use it like this
    @Query(name = "Event.RecurringEventsQuery")
    List<Event> getEvents(
            @Param("range_start") LocalDateTime rangeStart,
            @Param("range_end") LocalDateTime rangeEnd,
            @Param("time_zone") String timezone,
            @Param("events_limit") Integer limit
    );
    List<Event> findAll();
  1. Also you have to create proper constructors in the Event and the related classes, e.g.:
    public Event() {
    }

    public Event(
            String id,
            LocalDateTime startsAt,
            LocalDateTime endsAt,
            String frequency,
            Integer separation,
            Integer count,
            LocalDate until,
            String timezoneName,
            Boolean isFullDay,
            String locationId,
            LocalDateTime createdAt,
            LocalDateTime updatedAt,
            Integer version,
            // metadata
            String name,
            String description,
            String picture) {
        this.id = id;
        this.startsAt = startsAt;
        this.endsAt = endsAt;
        this.frequency = EventFrequency.valueOf(frequency);
        this.separation = separation;
        this.count = count;
        this.until = until;
        this.timezoneName = timezoneName;
        this.isFullDay = isFullDay;
        this.location = new Location(locationId);
        this.metadata = new EventMetadata(id, startsAt, name, description, picture);
    }

Here you can find the ugly POC repo https://bitbucket.org/kasptom/stackoverflow-69887977

user_48803790
Related