You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Chad McHenry <mc...@gmail.com> on 2009/04/15 16:48:27 UTC
Nested resultMaps and ambiguous columns - can I force qualified name?
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 <http://agency.id>
and person.id <http://collection.id> = #id:INTEGER#
</select>
</sqlMap>
----------
Modifying the column name in the location map failed.
<resultMap class="Location" id="ibatorgenerated_baseResultMap">
<result column="location.id <http://address.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
Re: Nested resultMaps and ambiguous columns - can I force qualified
name?
Posted by Chad McHenry <mc...@gmail.com>.
Ok, that worked for me. I am able to copy/paste the fields from the
generated selects, and reuse the generated resultMaps. Thanks for the quick
response.
...Chad
== ibatorConfig.xml =======================
...
<table tableName="person" alias="p" />
<table tableName="location" alias="loc" />
...
== person_SqlMap.xml =======================
...
<resultMap class="Person" id="baseResultMap">
<result property="id" column="p_id" />
<result property="name" column="p_name" />
<!-- uses generated resultMap in location_SqlMap.xml -->
<result property="location"
resultMap="location.ibatorgenerated_BaseResultMap"
/>
</resultMap>
<select id="selectByPrimaryKey" parameterClass="Person"
resultMap="baseResultMap">
select p.id as p_id, p.name as p_name,
loc.id as loc_id, loc.name as loc_name
from person p, location loc
where p.location_id = loc.id <http://a.id>
and p.id <http://c.id> = #id:INTEGER#
</select>
...
== query results =======================
person: {id:1, name:"bob", location_id:9}
location: {id:9, name:"home", ...}
== combined results =======================
person: {id:1, name:"bob", location_id:9,
location:{id:9, name:"home", ...}}
On Wed, Apr 15, 2009 at 12:27 PM, Jeff Butler <je...@gmail.com> wrote:
> 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 <mc...@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
> >
> >
>
--
On April 20, I will be running 26.2 miles, my first marathon, through the
city of Boston!
Help me do this, while raising funds for Melanoma awarness at
http://firstgiving.com/chadmchenry
...and don't forget your sunscreen!
Re: Nested resultMaps and ambiguous columns - can I force qualified
name?
Posted by Jeff Butler <je...@gmail.com>.
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 <mc...@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
>
>