CODEWITHSUNDEEP
sqlhibernateormjoinhibernate-mapping
alwaysLearning
alwaysLearning

Reputation: 99

joining two tables with differnt join column names

I have two tables A -> B with many-to-one mapping/associations.

Table B's primary key is foreign key in table A.

The problem is column names in both tables are different. let's say B has primary key column "typeNumId" which is foreign key in table A as "type". How can I join both tables on this column? how can I specify mapping to indicate that tables have to join on "typeNumId" and "type" which is essentially same.

especially is this possible through hibernate config ( hbm files) ?

something like

<many-to-one name="Type" class="com.domain.ProcedureType" update="false" insert="false" fetch="join" lazy="false">
    <column name="? <this is in questions? >" not-null="true" />
</many-to-one>

Upvotes: 2

Views: 9236

Answers (3)

Pascal Thivent
Pascal Thivent

Reputation: 570385

You declare the name of the foreign key using the column attribute of the many-to-one element or the equivalnent nested column element. From the documentation:

5.1.12. Many-to-one

An ordinary association to another persistent class is declared using a many-to-one element. The relational model is a many-to-one association; a foreign key in one table is referencing the primary key column(s) of the target table.

<many-to-one
        name="propertyName"                                          (1)
        column="column_name"                                         (2)
        class="ClassName"                                            (3)
        cascade="cascade_style"                                      (4)
        fetch="join|select"                                          (5)
        update="true|false"                                          (6)
        insert="true|false"                                          (6)
        property-ref="propertyNameFromAssociatedClass"               (7)
        access="field|property|ClassName"                            (8)
        unique="true|false"                                          (9)
        not-null="true|false"                                        (10)
        optimistic-lock="true|false"                                 (11)
        lazy="proxy|no-proxy|false"                                  (12)
        not-found="ignore|exception"                                 (13)
        entity-name="EntityName"                                     (14)
        formula="arbitrary SQL expression"                           (15)
        node="element-name|@attribute-name|element/@attribute|."
        embed-xml="true|false"
        index="index_name"
        unique_key="unique_key_id"
        foreign-key="foreign_key_name"
/>
  1. name: the name of the property.
  2. column (optional): the name of the foreign key column. This can also be specified by nested element(s).
  3. class (optional - defaults to the property type determined by reflection): the name of the associated class.
  4. cascade (optional): specifies which operations should be cascaded from the parent object to the associated object.
  5. fetch (optional - defaults to select): chooses between outer-join fetching or sequential select fetching.
  6. update, insert (optional - defaults to true): specifies that the mapped columns should be included in SQL UPDATE and/or INSERT statements. Setting both to false allows a pure "derived" association whose value is initialized from another property that maps to the same column(s), or by a trigger or other application.
  7. property-ref (optional): the name of a property of the associated class that is joined to this foreign key. If not specified, the primary key of the associated class is used.
  8. access (optional - defaults to property): the strategy Hibernate uses for accessing the property value.
  9. unique (optional): enables the DDL generation of a unique constraint for the foreign-key column. By allowing this to be the target of a property-ref, you can make the association multiplicity one-to-one.
  10. not-null (optional): enables the DDL generation of a nullability constraint for the foreign key columns.
  11. optimistic-lock (optional - defaults to true): specifies that updates to this property do or do not require acquisition of the optimistic lock. In other words, it determines if a version increment should occur when this property is dirty.
  12. lazy (optional - defaults to proxy): by default, single point associations are proxied. lazy="no-proxy" specifies that the property should be fetched lazily when the instance variable is first accessed. This requires build-time bytecode instrumentation. lazy="false" specifies that the association will always be eagerly fetched.
  13. not-found (optional - defaults to exception): specifies how foreign keys that reference missing rows will be handled. ignore will treat a missing row as a null association.
  14. entity-name (optional): the entity name of the associated class.
  15. formula (optional): an SQL expression that defines the value for a computed foreign key.

So something like this should do it:

<many-to-one name="Type" class="com.domain.ProcedureType" update="false" insert="false" fetch="join" lazy="false">
    <column name="type" not-null="true" />
</many-to-one>

Reference

Upvotes: 1

Scott Stafford
Scott Stafford

Reputation: 44786

The ON clause:

select * from A join B on A.type = B.typeNumId

Upvotes: 3

BarsMonster
BarsMonster

Reputation: 6585

Something like this?

LEFT JOIN B on A.field1 = B.field2

Choose type of JOIN on your taste

Upvotes: 0

Related Questions