Hi Jeff,
thanks for your quick reply!
Defining an alias is only half way in my opinion:
Using an alias helps to avoid a join column name collision but you still
cannot use "*" in your queries and have to type every column and column
alias in your queries ("select a.id as a_id, ...") as definied in the
resultmap.
Writing the tablename as a prefix to every column in the resultmap helps
to resolve collisions automatically AND still allows you to use "*"
selects where needed.
Another advantage is, that your columns are still named like your
columns in the database and you can add additional queries to your
sqlmap without knowing about aliases and synching every column with the
resultmap aliases.
B. Behler
Von: Jeff Butler [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 13. August 2008 16:51
An: user-java@ibatis.apache.org
Betreff: Re: tablename in resultmap column
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 <[EMAIL PROTECTED]>
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/html
doc/configreference/table.html
Jeff Butler
On Wed, Aug 13, 2008 at 9:23 AM, BWSO, Bernd Behler <[EMAIL PROTECTED]> 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
