Many-to-many mapping with custom selection of attribute types

Hibernate ORM
1

Hi there,

I currently have a mapping / casting problem in Hibernate 5.6.11 when comparing key fields. I get an error that reads something like

operator does not exist: character varying = bigint Note: No operator matches the given name and argument types. You might need to add explicit type casts.

Let’s assume I have three tables

Table 1: User (ID of type Int8 as PK)
Table 2: Role (ID of type Int8 as PK)
Table 3 User_Role (fk_user_id of type varchar + fk_role_id of type varchar).

I have .hbm.xml mapping where I now want to add a many-to-many relationship to User entity but in my legacy app, someone decided to not store the rowguid of table user in fk_user_id from user_role table, but the loginId. Hence, I get an error when lazily fetching the tuple because hibernate tries to join tables by using User.rowguid with User_Role.fk_user_id (which is a varchar). Is there a way to tell hibernate to not use User.rowguid for the join but User.loginId?

User.hbm.xml

<hibernate-mapping>
    <class name="com.myapp.ums.business.impl.UmsUserVOImpl" table="ums_t_user" proxy="com.myapp.ums.common.business.UmsUserVO">        
        <id name="rowguid" type="java.lang.Long"> 
	    	<generator class="sequence">
		        <param name="sequence_name">ums_t_user_rowguid_seq</param>
		    </generator>
	    </id> 
            <property name="loginId" type="java.lang.String" column="usr_login_id" unique="true" not-null="true"/>

        <set name="umsRoles" table="ums_t_user_role" inverse="false" lazy="true" fetch="select">
            <key>
                <column name="fk_user_id" not-null="true"  />
            </key>
            <many-to-many entity-name="com.myapp.ums.business.impl.UmsRoleVOImpl" >
                <column name="fk_role_id" not-null="true" />
            </many-to-many>
        </set>
    </class>
</hibernate-mapping>

Role.hbm.xml

<hibernate-mapping>
    <class name="com.myapp.ums.business.impl.UmsRoleVOImpl" table="ums_t_role"
           proxy="com.myapp.ums.common.business.UmsRoleVO">
        <id name="rowguid" type="java.lang.Long">
            <generator class="sequence">
                <param name="sequence_name">ums_t_role_rowguid_seq</param>
            </generator>
        </id>

        <property name="roleName" type="java.lang.String" column="umsr_name"/>

        <property name="owner" type="java.lang.String" column="role_owner"/>
        <property name="creationDate" type="timestamp" column="role_creation_date"/>
        <property name="creationUser" type="java.lang.String" column="role_creation_user"/>
        <property name="modificationDate" type="timestamp" column="role_modification_date"/>
        <property name="modificationUser" type="java.lang.String" column="role_modification_user"/>
        <list name="functions" cascade="all">
            <key column="rowguid"></key>
            <index column="type"></index>
            <one-to-many class="com.myapp.ums.business.impl.UmsUserRoleFunctionVOImpl"/>
        </list>
    </class>
</hibernate-mapping>

User_Role.hbm.xml

<hibernate-mapping>
    <class name="com.myapp.ums.business.impl.UmsUserRoleVOImpl" table="ums_t_user_role"
           proxy="com.myapp.ums.common.business.UmsUserRoleVO">
        <composite-id>
            <key-property name="userName" type="java.lang.String" column="fk_user_id"/>
            <key-property name="roleName" type="java.lang.String" column="fk_role_id"/>
        </composite-id>
    </class>
</hibernate-mapping>

Here’s the DB properties:

image
image1191×269 15.2 KB

2

Hey there. I think the following mapping should work:

        <set name="umsRoles" table="ums_t_user_role" inverse="false" lazy="true" fetch="select">
            <key property-ref="loginId">
                <column name="fk_user_id" not-null="true"  />
            </key>
3

Yes, that did the trick. Thank you!