Complex properties and substitution strings parameter problem (updated)

[Nguyen Trong Hoan <ho...@vips.com.vn>]

In my previous mail, I had a few mistake. I have updated it and resend you 
my problem as the following:

I'm developing a web app with ibatis (2.2) and have got a problem with 
complex properties and passing substitution strings parameter for sql query 
of properties.

For example:

<sqlMap namespace="user">
    <resultMap id="User" class="bean.User">
        <result property="id" column="id"  />
        <result property="name" column="name" />
        <result property="address" column="id" select="getAddress"/>
    </resultMap>

    <select id="getUser" parameterClass="map"  resultMap="User">
        SELECT id,name FROM $schema$.tbl_user WHERE id = #value#
    </select>

    <select id="getAddress" parameterClass="int" resultClass="string">
        SELECT address FROM $schema$.tbl_address WHERE id = #value#
    </select>
</sqlMap>

Here, bean.User class is declared as the following:
public class User {
    private String schema;
    private int id;
    private String name;
}
So, in my program:

Map param = new HashMap();
parmam.put("schema", "schemaName");
param.put("id", id);
sqlMap.executeQueryForList(getUser,param);

but I only passed schema parameter for getUser sql, but I couldn't pass it 
for getAddress sql.
Now, I don't know how to pass schema parameter for getAddress sql.
Please help me to solve this problem.
Thank you very much!

Re: Complex properties and substitution strings parameter problem (updated)

[Brandon Goodin <br...@gmail.com>]

There is no way to pass the properties along from the parameter object to a
nested select.

Is it a requirement that you pass the schema name in for each select? Or
could you simply set the schema name globally using a properties file and
property replacement notation ${schema}.

If that is not an option you may consider adding the schema name value as a
column in your results. and then passing it along as part of a composite
key.

Ex. (UNTESTED!)
    <resultMap id="User" class="bean.User">
        <result property="id" column="id"  />
        <result property="name" column="name" />
       <result property="schema" column="schema" />
        <result property="address" column="{id=id, schema=schema}"
select="getAddress"/>
    </resultMap>
    <select id="getUser" parameterClass="map"  resultMap="User">
        SELECT id,name, '$schema$' as schema FROM $schema$.tbl_user WHERE id
= #id#
    </select>

    <select id="getAddress" parameterClass="bean.User" resultClass="string">
        SELECT address FROM $schema$.tbl_address WHERE id = #id#
    </select>
</sqlMap>


I would also like to point out that you still have several errors in your
example code:

1) getUser has id=#value# and the value being passed in is a map. The
#value# should be #id#
is:
 <select id="getUser" parameterClass="map"  resultMap="User">
        SELECT id,name FROM $schema$.tbl_user WHERE id = #value#
    </select>

should be:
 <select id="getUser" parameterClass="map"  resultMap="User">
        SELECT id,name FROM $schema$.tbl_user WHERE id = #id#
    </select>

2)  first parameter in queryForList is not a String and it omits the
namespace (assuming you have namespaces enabled).
is:
sqlMap.executeQueryForList(getUser,param);

should be:
sqlMap.executeQueryForList("user.getUser",param);

Hope that helps,
Brandon

On 1/22/07, Nguyen Trong Hoan <ho...@vips.com.vn> wrote:
>
>  In my previous mail, I had a few mistake. I have updated it and resend
> you my problem as the following:
>
> I'm developing a web app with ibatis (2.2) and have got a problem with
> complex properties and passing substitution strings parameter for sql query
> of properties.
>
> For example:
>
> <sqlMap namespace="user">
>     <resultMap id="User" class="bean.User">
>         <result property="id" column="id"  />
>         <result property="name" column="name" />
>         <result property="address" column="id" select="getAddress"/>
>     </resultMap>
>
>     <select id="getUser" parameterClass="map"  resultMap="User">
>         SELECT id,name FROM $schema$.tbl_user WHERE id = #value#
>     </select>
>
>     <select id="getAddress" parameterClass="int" resultClass="string">
>         SELECT address FROM $schema$.tbl_address WHERE id = #value#
>     </select>
> </sqlMap>
> Here, *bean.User* class is declared as the following:
> public class User {
>     private String schema;
>     private int id;
>     private String name;
> }
> So, in my program:
>
>  Map param = new HashMap();
> parmam.put("schema", "schemaName");
> param.put("id", id);
> sqlMap.executeQueryForList(getUser,param);
>
> but I only passed *schema* parameter for *getUser *sql, but I couldn't
> pass it for *getAddress* sql.
> Now, I don't know how to pass *schema* parameter for *getAddress* sql.
> Please help me to solve this problem.
> Thank you very much!
>
>