Bidirectional mapping with multiple composite primary keys

Hibernate ORM
1

I have two tables. One is Property table and one is Num table. The table have columns as follows:

Property(Date, AssetId, Universe) - Here primary key is composite and combination of (Date, AssetId)

Num(Date, ItemId, AssetId, Value) - Here primary key is composite and combination of (Date, ItemId, AssetId). This table has a foreign key as well which is pk of Property i.e. (Date, AssetId)

Now i want to establish a OneToMany mapping with parent having the List collection.

My classes look like:

@Entity
@Table(name="Property")
public class Property implements Serializable {
	
	@Id
	@Column(name="Date")
	private LocalDate date;
	
	@Id
	@Column(name="AssetId")
	private String assetId;
	
	@Column(name="inUniverse")
	private Integer inUniverse;
	
	@OneToMany(mappedBy="property", cascade=CascadeType.ALL)
	private List<Num> nums = new ArrayList<>();

       //getters setter equals hashcode
}


@Entity
@Table(name="Num")
public class Num implements Serializable {

	@Id
	@Column(name="ItemId")
	private Integer itemId;
	
	@Column(name="Val")
	private BigDecimal val;
	
	@Id
	@ManyToOne(fetch=FetchType.LAZY)
	@JoinColumns({
		@JoinColumn(name="Date"),
		@JoinColumn(name="AssetId")
	})
	private Property property;

        //getters, setters, equals hashcode
}

I succeed to save the data but problem is when i want to have the children collection in List, the children are not fetched from the database. A very wierd query is generated which looks for columns redundantly. Also i would like to tell you that before this i also tried making the keys embeddable. Same wierd query is executed in that case also. I think there will be some method to map my scenario. Please help me as it will be a new learning for everybody.

2

You need to created an Embeddable to hold the PK columns: Date, ItemId, AssetId like this:

@Embeddable
public class DateAssetId implements Serializable {
    @Column(name="Date")
    public LocalDate  date;
	
	@Column(name="AssetId")
    public String assetId;
	
	//getters, setters, equals and hashCode omitted for brevity
}

@Embeddable
public class DateAssetIdItemId implements Serializable {
    private DateAssetId dateAssetId;
	
    @Column(name="ItemId")
	private Integer itemId;
	
	//getters, setters, equals and hashCode omitted for brevity
}

Then map your entities like this:

@Entity
@Table(name="Num")
public class Num implements Serializable {

	@EmbeddedId
	private DateAssetIdItemId id;
	
	@Column(name="Val")
	private BigDecimal val;
	
	@ManyToOne(fetch=FetchType.LAZY)
	@MapsId("dateAssetId")
    @JoinColumns({
        @JoinColumn(name="Date", referencedColumnName="Date"),
        @JoinColumn(name="AssetId", referencedColumnName="AssetId")
    })
	private Property property;

	//getters, setters, equals hashcode
}

@Entity
@Table(name="Property")
public class Property implements Serializable {
	
	@EmbeddedId
	private DateAssetId id;
	
	@Column(name="inUniverse")
	private Integer inUniverse;
	
	@OneToMany(mappedBy="property", cascade=CascadeType.ALL)
	private List<Num> nums = new ArrayList<>();

   //getters setter equals hashcode
}
4

This also doesn’t solve the problem. Infact it is printing an even more worse query by asking the same columns two times. Also i noticed that the parameters are also not binding properly. I am getting a feel that this kind of scenario cannot be mapped in Hibernate. It is worth giving a shot in IDE as i find it a good case study.

The query in logs is:

select nums0_.AssetId as AssetId3_0_0_, nums0_.Date as Date4_0_0_, nums0_.ItemId as ItemId1_0_0_, nums0_.AssetId as AssetId0_0_0_, nums0_.Date as Date0_0_0_, nums0_.ItemId as ItemId1_0_1_, nums0_.AssetId as AssetId0_0_1_, nums0_.Date as Date0_0_1_, nums0_.AssetId as AssetId3_0_1_, nums0_.Date as Date4_0_1_, nums0_.Val as Val2_0_1_ from Num nums0_ where nums0_.AssetId=? and nums0_.Date=?

Binds parameters as: [VARCHAR] - [B007G7-R], [DATE] - [2018-10-25]

Doesn’t extract any result.

5

Some Hibernate associations do that, but that’s not going to be the next performance bottleneck in your application since the data is fetched in pages, not in bytes.

Also i noticed that the parameters are also not binding properly. I am getting a feel that this kind of scenario cannot be mapped in Hibernate.

Then you need to replicate the issue using this test case template and open a new Jira issue.

Alternatively, you could try this mapping:

@Entity
@Table(name="Property")
public class Property implements Serializable {
	
	@Id
	@Column(name="Date")
	private LocalDate date;
	
	@Id
	@Column(name="AssetId")
	private String assetId;
	
	@Column(name="inUniverse")
	private Integer inUniverse;
	
	@OneToMany(mappedBy="property", cascade=CascadeType.ALL)
	private List<Num> nums = new ArrayList<>();

       //getters setter equals hashcode
}


@Entity
@Table(name="Num")
public class Num implements Serializable {

	@Id
	@Column(name="Date")
	private LocalDate date;
	
	@Id
	@Column(name="AssetId")
	private String assetId;

	@Id
	@Column(name="ItemId")
	private Integer itemId;
	
	@Column(name="Val")
	private BigDecimal val;
	
	@ManyToOne(fetch=FetchType.LAZY)
	@JoinColumns({
		@JoinColumn(name="Date", insertable = false, updatable = false),
		@JoinColumn(name="AssetId", insertable = false, updatable = false)
	})
	private Property property;

	//getters, setters, equals hashcode
}
6

No the alternate try also didn’t work. Thanks for your effort. I really appreciate.