[FIXED] Hibernate, using non-primary keys for join table (xml configuration)

Issue

I'm working on legacy database and I have three tables (four actually), these are

+------------------+
| UserGroups       |
+----+------+------+
| ID | NAME | DESC |
+----+------+------+
|    |      |      |
+----+------+------+

+------------------------+
| PUB_ZT_GROUP           |
+----------+-------------+
| GROUP_ID | GROUPPUB_ID |
+----------+-------------+
|          |             |
+----------+-------------+

+---------------------+
| GROUP_PUBLICATIONS  |
+----+----------------+
| ID | PUBLICATION_ID |
+----+----------------+
|    |                |
+----+----------------+

+------------------------------+
| PUBLICATIONS                 |
+----+----------------+--------+
| ID | PUBLICATION_ID | maxuse |
+----+----------------+--------+
|    |                |        |
+----+----------------+--------+

ID in UserGroups, GROUP_PUBLICATIONS and PUBLICATIONS table are auto generated and unique. PUB_ZT_GROUP table connects UserGroups and Group_Publications (many-to-many)

What I want to do is, use maxuse column in Publications table in GROUP_PUBLICATIONS. I learned that XML configuration is same with @SecondaryTable annotation. So in group_publications I did something like that

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.myproject.model">
    <class name="GroupPublications" table="GROUP_PUBLICATIONS">
    
        <id name="dbId" column="ID" length="32">
            <generator class="uuid" />
        </id>
        
        <property name="publikationsId" not-null="true" />
        <!--  <property name="maxUse" not-null="true" /> -->
        
        <many-to-one name="userGroups" column="GROUP_ID"
            not-null="true" cascade="save-update" />
        
        
        <join table="PUBLICATIONS">
            <key column="PUBLIKATIONS_ID" property-ref="PUBLICATION_ID"></key>
            
            <property name="maxUse" column="maxuse" not-null="true" lazy="false"></property>
        </join>
        
    </class>
</hibernate-mapping>

above I used property-ref="PUBLICATION_ID (documentation says if you have a legacy db and need to map different from primary key use property-ref, https://docs.jboss.org/hibernate/core/3.3/reference/en/html/mapping.html#mapping-declaration-join) but for some reason hibernate still trying to use and map GROUP_PUBLICATIONS.ID = PUBLICATIONS_PUBLICATION_ID

I c/p actual hibernate query. (Table origin names are in German)

BenutzerGruppe = UserGroups

PUB_ZT_GROUP = PUB_ZT_GRUPPE

GRUPPE_PUB_ZUTEILUNGEN = GROUP_PUBLICATIONS

PUBLIKATIONSLIZENZEN = PUBLICATIONS

Hibernate: 
    /* load collection xxx.model.BenutzerGruppe.publikationszuteilungen */ select
        publikatio0_.FK_BENUTZER_GRUPPE as FK1_1_,
        publikatio0_.FK_PUB_ZT as FK2_1_,
        gruppepubl1_.ID as ID29_0_,
        gruppepubl1_.publikationsId as publikat2_29_0_,
        gruppepubl1_.FK_BENUTZER_GRUPPE as FK3_29_0_,
        gruppepubl1_1_.MAXUSE as MAXUSE5_0_ 
    from
        PUB_ZT_GRUPPE publikatio0_ 
    left outer join
        GRUPPE_PUB_ZUTEILUNGEN gruppepubl1_ 
            on publikatio0_.FK_PUB_ZT=gruppepubl1_.ID 
    left outer join
        PUBLIKATIONSLIZENZEN gruppepubl1_1_ 
            on gruppepubl1_.ID=gruppepubl1_1_.PUBLIKATIONSID 
    where
        publikatio0_.FK_BENUTZER_GRUPPE=?

as you see the last left outer join, hibernate tries to map ID and PUBLICATIONID, it should gruppepubl1_.publikationsId = ruppepubl1_1_.PUBLIKATIONSID

So am I doing something wrong or hibernate has no/limited capability when we want to use non-primary key in join table?

I've found similar (or maybe not) problems, most of them using many-to-one and trying to map non-primary keys for example: https://hibernate.atlassian.net/browse/HHH-4284

Solution

A secondary table always uses the primary key of the entity to join against some columns in the secondary table.

I would suggest one of the following:

• Define a composite PK (id, publicationId)
• Define a many-to-one publication that has proper join columns and join fetch that when you need it
• Define a maxuse property with a formula e.g. formula="(select p.maxuse from publications p where p.id = id and p.publicationid = publicationid)"

Answered By - Christian Beikov
Answer Checked By - Candace Johnson (JavaFixing Volunteer)