ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: tablename in resultmap column
Date Wed, 13 Aug 2008 14:51:12 GMT
Sorry - just read your post a little closer and see that you know about
alias already.  Why won't alias work?

<resultMap class="test.Product" id="productResult">
       <result column="A_ID" jdbcType="INTEGER" property="id" />
       <result column="A_NAME" jdbcType="VARCHAR" property="name" />
</resultMap>

<resultMap class="test.Category" id="categoryResult">
       <result column="B_ID" jdbcType="INTEGER" property="id" />
       <result column="B_NAME" jdbcType="VARCHAR" property="name" />
</resultMap>


<resultMap class="test.Product" extends="productResult"
id="productWithCategoryResult">
   <result property="category" resultMap="CATEGORY.categoryResult " />
</resultMap>


<select id="selectProductsWithCategory"
  resultMap="productWithCategoryResult">
  select a.id as a_id, a.name as a_name, b.id as b_id, b.name as b_name
  from product a join category b on
  where a.id = b.id
</select>

Jeff Butler


On Wed, Aug 13, 2008 at 9:43 AM, Jeff Butler <jeffgbutler@gmail.com> wrote:

>  You can ask (a)ibator to generate an alias for the tables and column
> names - I think that work for this situation.  See the "alias" attribute on
> the <table> element:
>
>
> http://svn.apache.org/repos/asf/ibatis/trunk/java/tools/ibator/core/htmldoc/configreference/table.html
>
> Jeff Butler
>
>   On Wed, Aug 13, 2008 at 9:23 AM, BWSO, Bernd Behler <bb@bwso.de> wrote:
>
>> Is there a way to include the table name in the column resultmap
>> definitions generated by ibator?
>>
>> This would be very useful when having joins with column name collisions:
>>
>> An example:
>>
>> - TABLE "PRODUCT" having ID and NAME columns
>> - TABLE "CATEGORY" having ID and NAME columns
>>
>>
>> Ibator generated resultmaps currently look like:
>>
>> <resultMap class="test.Product" id="productResult">
>>        <result column="ID" jdbcType="INTEGER" property="id" />
>>        <result column="NAME" jdbcType="VARCHAR" property="name" />
>> </resultMap>
>>
>> <resultMap class="test.Category" id="categoryResult">
>>        <result column="ID" jdbcType="INTEGER" property="id" />
>>        <result column="NAME" jdbcType="VARCHAR" property="name" />
>> </resultMap>
>>
>>
>>
>> Now I want to extend the generated sqlmap to add the category to every
>> product as follows:
>>
>> <resultMap class="test.Product" extends="productResult"
>> id="productWithCategoryResult">
>>    <result property="category" resultMap="CATEGORY.categoryResult " />
>> </resultMap>
>>
>>
>> The select would look like:
>>
>> <select id="selectProductsWithCategory"
>> resultMap="productWithCategoryResult">
>>   select PRODUCT.*, CATEGORY.*
>>   where CATEGORY.ID <http://category.id/> = PRODUCT.CATEGORY_ID
>> </select>
>>
>>
>>
>> Without having the tablenames in the resultmap, ibatis does not know
>> which ID or NAME column to map to which object. So I end up having the
>> value of PRODUCT.ID <http://product.id/> and PRODUCT.NAME<http://product.name/>in
my created Category instance.
>>
>> Serveral posts suggest to not use wildcards in the select statement and
>> to define column aliases to avoid column name collisions. But this is
>> very tedious and you have to write your own join resultMaps to match
>> with the column defined aliases aswell.
>>
>>
>> A working solution is to change the ibator generated resultmaps to
>> include the table name in the column attribute:
>>
>> <resultMap class="test.Product" id="productResult">
>>        <result column="PRODUCT.ID <http://product.id/>"
>> jdbcType="INTEGER" property="id" />
>>        <result column="PRODUCT.NAME <http://product.name/>"
>> jdbcType="VARCHAR" property="name"
>> />
>> </resultMap>
>>
>> <resultMap class="test.Category" id="categoryResult">
>>        <result column="CATEGORY.ID <http://category.id/>"
>> jdbcType="INTEGER" property="id" />
>>        <result column="CATEGORY.NAME <http://category.name/>"
>> jdbcType="VARCHAR"
>> property="name" />
>> </resultMap>
>>
>>
>> Works like a charm, but every time I re-generate the sqlmaps my changes
>> are thrown away and I have to make them again by hand. I searched the
>> mailing list and found out that you can define an "alias" for each
>> table, but this isn't helping in my case either.
>>
>> If it can't already be done somehow, my suggestion would be to include
>> an "includeTablenameInColumns" switch in ibator.
>>
>> Thanks for your help,
>> B. Behler
>>
>>
>>
>>
>

Mime
View raw message