What database are you using? What you describe is some kind of auto
aliasing that I have not seen before. For example,
select product.*, category.*
from product join category on product.id = category.id
should return ID, NAME, ID, NAME
NOT
PRODUCT.ID, PRODUCT.NAME, CATEGORY.ID, CATEGORY.NAME
I tried your syntax with both HSQLDB and MySQL and it didn't work.
Jeff Butler
On Wed, Aug 13, 2008 at 10:11 AM, BWSO, Bernd Behler <[EMAIL PROTECTED]> wrote:
> 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/htmldoc/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
>
>
>
>
>
>
