Nested ResultMaps with ambiguous database columns

Question

I have a couple of nested ResultMaps in iBatis that have exactly same database column names. This is causing ambiguity and resulting in incorrect result being retrieved for the different database tables.

For e.g., `

<sql namespace="Shipment">

       <resultMap id="consignment" class="com.model.Consignment">
              <result property="consignmentId" column="Consignment_cd" />
              <result property="shipmentCd" column="Shipment_cd" />
              <result property="shipmentUnit" column="Shipment_Unit" />
              <result property="location"  resultMap="Shipment.size" />
       </resultMap>

      <resultMap id="size" class="com.model.Size">
              <result property="consignmentId" column="Consignment_cd" />
              <result property="shipmentCd" column="Shipment_cd" />
              <result property="shipmentUnit" column="Shipment_Unit" />
      </resultMap>

    </sql>

`

Now when I write my select query joining the Size & Consignment tables, I get the same values for Shipment Code and Shipment Unit returned, whereas there are different values for these two columns in the database. Please note that I need both the Shipment Code and Unit from both the Size and Consignment levels pulled up in a single query.

Could someone help me solve this problem?

Answer 1

The only solution I found to this issue is prefixing the column names with the table name or short name. You are writing the queries yourself right?

Your select would look like

select consignment.Shipment_cd as consignment_Shipment_cd, 
       size.Shipment_cd as size_Shipment_cd
from consignment
join size on whatever

And yes that is pretty heavy if you want to get a lot of stuff in the same query