CODEWITHSUNDEEP
javahibernatejpa
fan
fan

Reputation: 2364

Jpa Hibernate map key in multi column relationship

I would like to map a Java Map, where all key values are stored in the same table. Something similar to https://en.wikibooks.org/wiki/Java_Persistence/Relationships#Example_of_a_map_key_column_relationship_database but with the key being an object and not a simple type.

Say I have an Entity "User"

@Entity
public class User{
    @Id
    private String userId;

    @OneToMany
    @MapKeyClass(CalenderWeek.class)
    private Map<CalenderWeek, WorkedTime> workedTimeMap;

The key CalendarWeek would be something like this

@Embeddable
public class CalenderWeek {
    int year;
    Month month; // Month is the enum java.time.Month

The WorkedTime would be something like

@Embeddable
public class WorkedTime {
    private long workedHours;

The corresponding worked time table should be like this

worked_time

user_id | year | month | worked_hours
---------|------|-------| ---
 1       | 2017 | 11    | 42

Is it possible to get that or do I have to do it as described here https://en.wikibooks.org/wiki/Java_Persistence/Relationships#Example_of_a_map_key_class_embedded_relationship_annotation

i.e., with three tables.

Upvotes: 1

Views: 1623

Answers (3)

fan
fan

Reputation: 2364

The answer of Maciej Kowalski was very useful and correct. Thank you.

I just want to complete here the answer and extend it a little as I used xml configuration at the end. The Annotated version ended like that

@Entity
public class UserWorklogAggregate {
@Id
private String userId;

@ElementCollection
@CollectionTable(
        name = "worked_time",
        joinColumns = @JoinColumn(name = "user_id")
)
@AttributeOverrides({
        @AttributeOverride(name = "key.year", column = @Column(name = "year")),
        @AttributeOverride(name = "key.week", column = @Column(name = "week")),
        @AttributeOverride(name = "value.workedDuration", column = @Column(name = "worked_duration"))
})
private final Map<CalenderWeek, WorkedTime> workedTimeMap = new HashMap<>();

and the xml version

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-access="field" default-cascade="all">

<class name="org.whatever.UserWorklogAggregate" table="user_worklog_aggregate">
    <id name="userId" length="10" column="user_id" />
    <map name="workedTimeMap" table="worked_time" >
        <key column="user_id" />
        <composite-map-key class="org.whatever.CalenderWeek">
            <key-property name="year" column="year"/>
            <key-property name="week" column="week"/>
        </composite-map-key>
        <composite-element class="org.whatever.WorkedTime" >
            <property name="workedDuration" column="worked_duration"/>
        </composite-element>
    </map>
</class>

Upvotes: 1

Maciej Kowalski
Maciej Kowalski

Reputation: 26572

In general if you want to have a Map u simply use @ElementCollection annotation and if you want to override some of the column or associations from the Embeddables then you use @AttributeOverride / @AssociationOverride:

@ElementCollection
@AttributeOverrides({
 @AttributeOverride(name="key.year",
 column=@Column(name="YEAR1")),
 @AttributeOverride(name="value.workedHours",
 column=@Column(name="WORKED_H"))
})
private Map<CalenderWeek, WorkedTime> workedTimeMap;

Depending whether you want to override the key or value attribute, you add those prefixes respectively for persistence provider to be able to recodgnize the difference.

Upvotes: 2

KLHauser
KLHauser

Reputation: 876

I assume you want to store working hours of a user per month+year. Looking at your result table I would recommend not using Embeddable. You could try this approach:

@Entity
public class User{

    @Id
    private String id;    

    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private List<WorkedTime> workedTime;

plus the back reference:

@Entity
public class WorkedTime {

    @Id
    private long id;

    @ManyToOne
    @JoinColumn(name = "user_id", nullable = false)
    private User user;

    private int year;

    private Month month;

    private long workedHours;

This would result in having only two tables.

Upvotes: 0

Related Questions