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 jaybytez <ja...@gmail.com> on 2007/01/24 04:22:55 UTC
Inline Parameter Mapping - Update Statement
The documentation shows these advanced properties for inline parameter
mapping:
#propertyName,javaType=?,jdbcType=?,mode=?,nullValue=?,handler=?,numericScale=?#
That is fine for syntax like an insert statement, but for an update
statement....where we need to say columnName = value...how do I do this with
inline parameter mapping?
Any examples.
Thanks,
jay
--
View this message in context: http://www.nabble.com/Inline-Parameter-Mapping---Update-Statement-tf3078944.html#a8554335
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: [Table field names VS. Object properties names]
Posted by Carlos Cajina - Hotmail 1 <ce...@hotmail.com>.
Hi again!
Thanks for the quick comment Larry.
You're absolutely right, I does make sense at any level (Java/SQL)... I
guess my brain took a short vacation... :^)
Sorry for asking the obvious.
Regards,
Carlos
----- Original Message -----
From: "Larry Meadors" <lm...@apache.org>
To: <us...@ibatis.apache.org>
Sent: Wednesday, January 24, 2007 6:23 AM
Subject: Re: [Table field names VS. Object properties names]
> That is correct, and makes perfect sense if you think about it at the SQL
> level.
>
> If you run that SQL statement in a SQL tool, and do not alias that
> field, how would you look at the results and know which was which?
>
> Larry
>
>
> On 1/23/07, Carlos Cajina <ce...@hotmail.com> wrote:
>> Hi!
>>
>> I have a question regarding the implementation of the N+1 solution.
>> Couldn't
>> find any reference in the mailing list/docs, so here's the thing:
>>
>> Preconditions:
>> 1. Using iBATIS version 2.3.0.677
>> 2. Applied the N+1 solution to a One-to-Many tables relationship
>>
>> Problem details:
>> 1. table_b references table_a with a FK
>> 2. table_a and table_b both have a 'name' field
>> 3. Java classes representing those tables also have a 'name' property
>>
>> Problem:
>> Since both tables have a 'name' field, when doing a queryForList and
>> traversing the resulting list of A objects that contain a list of B
>> objects,
>> and calling getName() for both the parent and children objects, the
>> returned
>> value is always the 'name' property value of the children objects.
>>
>> I aliased the 'name' field of table_b in my query (and in the appropiate
>> SqlMap) and the traversing and calling of getName() in parents and
>> children
>> worked as expected.
>>
>> I'm guessing -from the behaviour I see- that when mapping tables that
>> share
>> field names (i.e. id, name, description) the query in the SqlMap must
>> alias
>> those fields so that the SqlMaps/Java objects don't "get confused". Sorry
>> I
>> can't describe this in a more technical way but Is this behaviour
>> correct?
>>
>> Below is a mapping that mimics what I'm doing/using:
>>
>> <resultMap class="some.package.ClassA" id="resultA" groupBy="aId">
>> <result column="a_id" jdbcType="INTEGER" property="aId" />
>> <result column="name" jdbcType="VARCHAR" property="name" />
>> <!-- 1:N solution -->
>> <result property="bList" resultMap="someNameSpace.resultB" />
>> </resultMap>
>>
>> <resultMap class="some.package.ClassB" id="resultB">
>> <result column="b_id" jdbcType="INTEGER" property="bId" />
>> <result column="b_name" jdbcType="VARCHAR" property="name" />
>> <result column="componentType" jdbcType="INTEGER"
>> property="component_type" />
>> <result column="a_id" jdbcType="INTEGER" property="aId" />
>> </resultMap>
>>
>> <select id="selectAB" parameterClass="int" resultMap="resultA">
>> SELECT a.a_id, a.name, b.b_id, b.nname, b.component_type, b.a_id
>> FROM table_a a INNER JOIN table_b b
>> ON (a.a_id = b.a_id)
>> WHERE b.component_type = #value#
>> ORDER BY a.name, b.name
>> </select>
>>
>> Any comments, thoughts, ideas will be appreciated.
>>
>> Regards,
>>
>> Carlos
>>
>>
>
Re: [Table field names VS. Object properties names]
Posted by Larry Meadors <lm...@apache.org>.
That is correct, and makes perfect sense if you think about it at the SQL level.
If you run that SQL statement in a SQL tool, and do not alias that
field, how would you look at the results and know which was which?
Larry
On 1/23/07, Carlos Cajina <ce...@hotmail.com> wrote:
> Hi!
>
> I have a question regarding the implementation of the N+1 solution. Couldn't
> find any reference in the mailing list/docs, so here's the thing:
>
> Preconditions:
> 1. Using iBATIS version 2.3.0.677
> 2. Applied the N+1 solution to a One-to-Many tables relationship
>
> Problem details:
> 1. table_b references table_a with a FK
> 2. table_a and table_b both have a 'name' field
> 3. Java classes representing those tables also have a 'name' property
>
> Problem:
> Since both tables have a 'name' field, when doing a queryForList and
> traversing the resulting list of A objects that contain a list of B objects,
> and calling getName() for both the parent and children objects, the returned
> value is always the 'name' property value of the children objects.
>
> I aliased the 'name' field of table_b in my query (and in the appropiate
> SqlMap) and the traversing and calling of getName() in parents and children
> worked as expected.
>
> I'm guessing -from the behaviour I see- that when mapping tables that share
> field names (i.e. id, name, description) the query in the SqlMap must alias
> those fields so that the SqlMaps/Java objects don't "get confused". Sorry I
> can't describe this in a more technical way but Is this behaviour correct?
>
> Below is a mapping that mimics what I'm doing/using:
>
> <resultMap class="some.package.ClassA" id="resultA" groupBy="aId">
> <result column="a_id" jdbcType="INTEGER" property="aId" />
> <result column="name" jdbcType="VARCHAR" property="name" />
> <!-- 1:N solution -->
> <result property="bList" resultMap="someNameSpace.resultB" />
> </resultMap>
>
> <resultMap class="some.package.ClassB" id="resultB">
> <result column="b_id" jdbcType="INTEGER" property="bId" />
> <result column="b_name" jdbcType="VARCHAR" property="name" />
> <result column="componentType" jdbcType="INTEGER"
> property="component_type" />
> <result column="a_id" jdbcType="INTEGER" property="aId" />
> </resultMap>
>
> <select id="selectAB" parameterClass="int" resultMap="resultA">
> SELECT a.a_id, a.name, b.b_id, b.nname, b.component_type, b.a_id
> FROM table_a a INNER JOIN table_b b
> ON (a.a_id = b.a_id)
> WHERE b.component_type = #value#
> ORDER BY a.name, b.name
> </select>
>
> Any comments, thoughts, ideas will be appreciated.
>
> Regards,
>
> Carlos
>
>
[Table field names VS. Object properties names]
Posted by Carlos Cajina <ce...@hotmail.com>.
Hi!
I have a question regarding the implementation of the N+1 solution. Couldn't
find any reference in the mailing list/docs, so here's the thing:
Preconditions:
1. Using iBATIS version 2.3.0.677
2. Applied the N+1 solution to a One-to-Many tables relationship
Problem details:
1. table_b references table_a with a FK
2. table_a and table_b both have a 'name' field
3. Java classes representing those tables also have a 'name' property
Problem:
Since both tables have a 'name' field, when doing a queryForList and
traversing the resulting list of A objects that contain a list of B objects,
and calling getName() for both the parent and children objects, the returned
value is always the 'name' property value of the children objects.
I aliased the 'name' field of table_b in my query (and in the appropiate
SqlMap) and the traversing and calling of getName() in parents and children
worked as expected.
I'm guessing -from the behaviour I see- that when mapping tables that share
field names (i.e. id, name, description) the query in the SqlMap must alias
those fields so that the SqlMaps/Java objects don't "get confused". Sorry I
can't describe this in a more technical way but Is this behaviour correct?
Below is a mapping that mimics what I'm doing/using:
<resultMap class="some.package.ClassA" id="resultA" groupBy="aId">
<result column="a_id" jdbcType="INTEGER" property="aId" />
<result column="name" jdbcType="VARCHAR" property="name" />
<!-- 1:N solution -->
<result property="bList" resultMap="someNameSpace.resultB" />
</resultMap>
<resultMap class="some.package.ClassB" id="resultB">
<result column="b_id" jdbcType="INTEGER" property="bId" />
<result column="b_name" jdbcType="VARCHAR" property="name" />
<result column="componentType" jdbcType="INTEGER"
property="component_type" />
<result column="a_id" jdbcType="INTEGER" property="aId" />
</resultMap>
<select id="selectAB" parameterClass="int" resultMap="resultA">
SELECT a.a_id, a.name, b.b_id, b.nname, b.component_type, b.a_id
FROM table_a a INNER JOIN table_b b
ON (a.a_id = b.a_id)
WHERE b.component_type = #value#
ORDER BY a.name, b.name
</select>
Any comments, thoughts, ideas will be appreciated.
Regards,
Carlos
Re: Inline Parameter Mapping - Update Statement
Posted by jaybytez <ja...@gmail.com>.
I found this example in the .Net documentation:
<update id="UpdateAccountViaInlineParameters" parameterClass="Account">
update Accounts set
Account_FirstName = #FirstName#,
Account_LastName = #LastName#,
Account_Email =
#EmailAddress,type=string,dbType=Varchar,nullValue=no_email@provided.com#
where
Account_ID = #Id#
</update>
Thanks,
jay
--
View this message in context: http://www.nabble.com/Inline-Parameter-Mapping---Update-Statement-tf3078944.html#a8554716
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.