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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira