ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chad McHenry <mchen...@gmail.com>
Subject Re: Nested resultMaps and ambiguous columns - can I force qualified name?
Date Wed, 15 Apr 2009 22:04:26 GMT
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 <jeffgbutler@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 <mchenryc@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!

Mime
View raw message