[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2996) Allow expression in a @JoinColumn for a composite foreign key

[Darren Bell (JIRA)]

Allow expression in a @JoinColumn for a composite foreign key
-------------------------------------------------------------

                 Key: HHH-2996
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2996
             Project: Hibernate3
          Issue Type: New Feature
    Affects Versions: 3.2.5
         Environment: hibernate 3.2.5, db2/400 v5r4
            Reporter: Darren Bell


I am mapping our Mapics COM history with hibernate and have been pretty successful so far, but have hit a stumbling block.

There is a table in Mapics that stores history comments for many different other tables, hence each table has a different foreign key.

The table is outlined below for those that don't know Mapics ;)

	COL_NO	NAME	COL_TYPE	LENGTH	DP	NULL	DFT	DEFAULT	DESCRIPTION
	1	GAAENB	NUMERIC	2	0	N	Y	0	Company number
	2	GAGGNB	NUMERIC	6	0	N	Y	0	Invoice number
	3	GAHYNB	DECIMAL	7	0	N	Y	0	Invoice sequence
	4	GAKBNB	DECIMAL	5	0	N	Y	0	Comment line sequence number
	5	GAAKCD	CHAR	3		N	Y	' '	Language code
	6	GAAAD9	CHAR	1		N	Y	' '	Internal print only?
	7	GAHDTX	CHAR	25		N	Y	' '	Comment line text
	8	GAHXCD	CHAR	5		N	Y	' '	Comment user reference
	9	GADCCD	CHAR	1		N	Y	' '	Internal header type
	10	GACVNB	CHAR	7		N	Y	' '	Quote/order number
	11	GAK4NB	DECIMAL	7	0	N	Y	0	Shipment header number
	12	GAAFAD	DECIMAL	3	0	N	Y	0	Shipment consolidate seq
	13	GALCNB	DECIMAL	7	0	N	Y	0	Ship release sequence
	14	GAAASZ	DECIMAL	5	0	N	Y	0	Kit release sequence
	15	GAAAD2	DECIMAL	7	0	N	Y	0	Special charge sequence #
	16	GAAD1N	CHAR	1		N	Y	' '	Text line print control

To get the comments from the shipment release we use the following:

	@OneToMany()
	@JoinColumns ({
		@JoinColumn(name=HistoricalComment.Columns.COMPANY_NUMBER, referencedColumnName=ShipRelease.Columns.COMPANY_NUMBER),
		@JoinColumn(name=HistoricalComment.Columns.INTERNAL_HEADER_TYPE, referencedColumnName=ShipRelease.Columns.INTERNAL_HEADER_TYPE),
		@JoinColumn(name=HistoricalComment.Columns.ORDER_NUMBER, referencedColumnName=ShipRelease.Columns.ORDER_NUMBER),
		@JoinColumn(name=HistoricalComment.Columns.SHIPMENT_HEADER_NUMBER, referencedColumnName=ShipRelease.Columns.SHIPMENT_HEADER_NUMBER),
		@JoinColumn(name=HistoricalComment.Columns.SHIP_RELEASE_SEQUENCE, referencedColumnName=ShipRelease.Columns.SHIP_RELEASE_SEQUENCE),
		@JoinColumn(name=HistoricalComment.Columns.KIT_RELEASE_SEQUENCE, referencedColumnName=ShipRelease.Columns.KIT_RELEASE_SEQUENCE)
	})
	@Sort(type=SortType.NATURAL)
	private SortedSet<HistoricalComment> historicalComments;

Which works well, but to the the comments for the ship header the following is needed

	@OneToMany()
	@JoinColumns ({
		@JoinColumn(name=HistoricalComment.Columns.COMPANY_NUMBER, referencedColumnName=ShipHeader.Columns.COMPANY_NUMBER),
		@JoinColumn(name=HistoricalComment.Columns.INTERNAL_HEADER_TYPE, referencedColumnName=ShipHeader.Columns.INTERNAL_HEADER_TYPE),
		@JoinColumn(name=HistoricalComment.Columns.ORDER_NUMBER, referencedColumnName=ShipHeader.Columns.ORDER_NUMBER),
		@JoinColumn(name=HistoricalComment.Columns.SHIPMENT_HEADER_NUMBER, referencedColumnName=ShipHeader.Columns.SHIPMENT_HEADER_NUMBER)
	})
	@Sort(type=SortType.NATURAL)
	private SortedSet<HistoricalComment> historicalComments;

As you can see there are two keys missing on the second one to many.  This has the undesired effect of retriving all the comments for the entire shipment, whether they are related to a relase, the header itself, or charge codes.

Now, what i'm suggesting is to allow an expression for one of the join colums.  The following would solve the problem.

@OneToMany()
	@JoinColumns ({
		@JoinColumn(name=HistoricalComment.Columns.COMPANY_NUMBER, referencedColumnName=ShipRelease.Columns.COMPANY_NUMBER),
		@JoinColumn(name=HistoricalComment.Columns.INTERNAL_HEADER_TYPE, referencedColumnName=ShipRelease.Columns.INTERNAL_HEADER_TYPE),
		@JoinColumn(name=HistoricalComment.Columns.ORDER_NUMBER, referencedColumnName=ShipRelease.Columns.ORDER_NUMBER),
		@JoinColumn(name=HistoricalComment.Columns.SHIPMENT_HEADER_NUMBER, referencedColumnName=ShipRelease.Columns.SHIPMENT_HEADER_NUMBER),
		@JoinColumn(name=HistoricalComment.Columns.SHIP_RELEASE_SEQUENCE, expression=":shipReleaseSequence > 0"),
		@JoinColumn(name=HistoricalComment.Columns.KIT_RELEASE_SEQUENCE, expression=":kitReleaseSequence = 0")
	})

This would put the expressions into the join clause.

What do you think?  Have i explained this well enough?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira