As far as I know, MySQL is the only database that supports qualifying
column names with the table. It is not standard for JDBC so it won't
work in general.
The only way to avoid this problem in most databases is to alias the
column names so they become unique in the request set. Ibator will
generate maps with aliased column names if you specify an alias in the
table configuration:
<table tableName="foo" alias="A" />
Jeff Butler
On Wed, Apr 15, 2009 at 9:48 AM, Chad McHenry <mchen...@gmail.com> wrote:
> I have generated a number of sqlMaps using ibator. These are working great,
> but now I would like to take advantage of compound properties to avoid N+1
> issues. I would like to minimize the amount of handwritten sqlmap code, and
> reuse ibators output as much as possible, so in the resultMap for the parent
> table (person), I reference the resultMap ibator generated for the child
> table (location).
>
> I run into problems because my tables use the same names for various fields.
> Here in this example, each field has an 'id' field. When using nested
> resultMaps for compound properties, this results in the nested resultMaps
> using the same id as the parent id - not good!
>
> person: {id:1, name:"bob", location_id:9}
> location: {id:9, name:"home", ...}
>
> combined result
> person: {id:1 name:"bob", location_id:9,
> location:{id:1, name:"home"}, ...}
>
> I tried modifying the resultMaps to reference the attributes by qualified
> name (e.g. address.id) but that caused the error:
>
> Error getting nested result map values for 'address'. Cause:
> java.sql.SQLException: Invalid column name address.id.
>
> Is there a way to force result maps to use the correct id value? Perhaps an
> undocumented attribute or element in the sqlMap to force resultMaps to use
> the qualified name.
> If I must rename the columns (e.g. person_id, location_id, etc., as primary
> key), must I do the same for other like-named columns, like "name"? In the
> example above, the 'id' field was obviously confused (the result set id=1
> for both the parent and child), yet it somehow got the name correct... how?
> Could I get it to do the same for id?
> Thanks in advance for any advice and insight,
> ...Chad
> == address_SqlMap.xml =======================
> <sqlMap namespace="location">
> <resultMap class="Location" id="ibatorgenerated_baseResultMap">
> <result column="id" property="id" />
> <result column="name" property="name" />
> </resultMap>
> ...
> </sqlMap>
>
> == person_SqlMap.xml =======================
>
> <sqlMap namespace="person">
> <resultMap class="Person" id="baseResultMap">
> <result column="id" property="id" />
> <result column="name" property="name" />
> <result property="location"
> resultMap="location.ibatorgenerated_BaseResultMap" />
> </resultMap>
> <select id="selectByPrimaryKey" parameterClass="Person"
> resultMap="baseResultMap">
> select *
> from person, location
> where person.location_id = location.id
> and person.id = #id:INTEGER#
> </select>
> </sqlMap>
> ----------
> Modifying the column name in the location map failed.
> <resultMap class="Location" id="ibatorgenerated_baseResultMap">
> <result column="location.id" property="id" />
> ...
> </resultMap>
>
> Error getting nested result map values for 'location'. Cause:
> java.sql.SQLException: Invalid column name location.id.; nested exception is
> com.ibatis.common.jdbc.exception.NestedSQLException
>
>
