how to use dynamic parameters in select statement in iBatis 3

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

how to use dynamic parameters in select statement in iBatis 3

radha
In Ibatis 2 , I used below select statement.

<select id="getWPIData" parameterClass="java.lang.String"
resultMap="PMWSFTCSMap">
select
Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completion_Date,Model
from FTCS_WRK_PKG_INFO $whereClause$ order by Model,Line_No,ip_name
</select>

Now in Ibatis 3 I am supposed to use below select statement syntax
which is not working.

<select id="getWPIData" parameterType="java.lang.String"
resultType="WorkPkgInfo" resultMap="PMWSFTCSMap">
select
Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completion_Date,Model
from FTCS_WRK_PKG_INFO #{whereClause} order by Model,Line_No,ip_name
</select>

In this case whereClause variable holds the follwoing value "model =
'747' and Line_No in (1424) or model = '787' and Line_No in
(5,2,6,3,4) or model = '737' and Line_No in
(3356,2570,3464,2965,3052,3393,3138,3244)" which will be generated
dynamically based on the request that we get.

Is there any otherway to specify the parameters? According to iBatis
user guide it should be #{whereClause} .

Reply | Threaded
Open this post in threaded view
|

Re: how to use dynamic parameters in select statement in iBatis 3

Larry Meadors
${blah}

On Tue, Oct 5, 2010 at 4:51 PM, radh <[hidden email]> wrote:

> In Ibatis 2 , I used below select statement.
>
> <select id="getWPIData" parameterClass="java.lang.String"
> resultMap="PMWSFTCSMap">
> select
> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completion_Date,Model
> from FTCS_WRK_PKG_INFO $whereClause$ order by Model,Line_No,ip_name
> </select>
>
> Now in Ibatis 3 I am supposed to use below select statement syntax
> which is not working.
>
> <select id="getWPIData" parameterType="java.lang.String"
> resultType="WorkPkgInfo" resultMap="PMWSFTCSMap">
> select
> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completion_Date,Model
> from FTCS_WRK_PKG_INFO #{whereClause} order by Model,Line_No,ip_name
> </select>
>
> In this case whereClause variable holds the follwoing value "model =
> '747' and Line_No in (1424) or model = '787' and Line_No in
> (5,2,6,3,4) or model = '737' and Line_No in
> (3356,2570,3464,2965,3052,3393,3138,3244)" which will be generated
> dynamically based on the request that we get.
>
> Is there any otherway to specify the parameters? According to iBatis
> user guide it should be #{whereClause} .
>
>
Reply | Threaded
Open this post in threaded view
|

Re: how to use dynamic parameters in select statement in iBatis 3

nino martinez wael
In reply to this post by radha
        @Select("select * from rtcsqssummary where csqname = #{csqname} ")
        Rtcsqssummary selectSingleRtcsqssummary(String csqname);


2010/10/6 radh <[hidden email]>:

> In Ibatis 2 , I used below select statement.
>
> <select id="getWPIData" parameterClass="java.lang.String"
> resultMap="PMWSFTCSMap">
> select
> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completion_Date,Model
> from FTCS_WRK_PKG_INFO $whereClause$ order by Model,Line_No,ip_name
> </select>
>
> Now in Ibatis 3 I am supposed to use below select statement syntax
> which is not working.
>
> <select id="getWPIData" parameterType="java.lang.String"
> resultType="WorkPkgInfo" resultMap="PMWSFTCSMap">
> select
> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completion_Date,Model
> from FTCS_WRK_PKG_INFO #{whereClause} order by Model,Line_No,ip_name
> </select>
>
> In this case whereClause variable holds the follwoing value "model =
> '747' and Line_No in (1424) or model = '787' and Line_No in
> (5,2,6,3,4) or model = '737' and Line_No in
> (3356,2570,3464,2965,3052,3393,3138,3244)" which will be generated
> dynamically based on the request that we get.
>
> Is there any otherway to specify the parameters? According to iBatis
> user guide it should be #{whereClause} .
>
>
Reply | Threaded
Open this post in threaded view
|

Re: how to use dynamic parameters in select statement in iBatis 3

nino martinez wael
In reply to this post by radha
heres how I do it:

 @Select("select * from rtcsqssummary where csqname = #{csqname} ")
       Rtcsqssummary selectSingleRtcsqssummary(String csqname);

2010/10/6 radh <[hidden email]>:

> In Ibatis 2 , I used below select statement.
>
> <select id="getWPIData" parameterClass="java.lang.String"
> resultMap="PMWSFTCSMap">
> select
> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completion_Date,Model
> from FTCS_WRK_PKG_INFO $whereClause$ order by Model,Line_No,ip_name
> </select>
>
> Now in Ibatis 3 I am supposed to use below select statement syntax
> which is not working.
>
> <select id="getWPIData" parameterType="java.lang.String"
> resultType="WorkPkgInfo" resultMap="PMWSFTCSMap">
> select
> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completion_Date,Model
> from FTCS_WRK_PKG_INFO #{whereClause} order by Model,Line_No,ip_name
> </select>
>
> In this case whereClause variable holds the follwoing value "model =
> '747' and Line_No in (1424) or model = '787' and Line_No in
> (5,2,6,3,4) or model = '737' and Line_No in
> (3356,2570,3464,2965,3052,3393,3138,3244)" which will be generated
> dynamically based on the request that we get.
>
> Is there any otherway to specify the parameters? According to iBatis
> user guide it should be #{whereClause} .
>
>
Reply | Threaded
Open this post in threaded view
|

Re: how to use dynamic parameters in select statement in iBatis 3

nino martinez wael
In reply to this post by radha
I've tried now four time to reply to this but are getting bounced
(apparently my answer are considered spam):

Hi. This is the qmail-send program at burntmail.com.
I'm afraid I wasn't able to deliver your message to the following addresses.
This is a permanent error; I've given up. Sorry it didn't work out.

<[hidden email]>:
Sorry - The message you have sent was identified as spam by Spam
Assassin (message bounced)

--

2010/10/6 radh <[hidden email]>:

> In Ibatis 2 , I used below select statement.
>
> <select id="getWPIData" parameterClass="java.lang.String"
> resultMap="PMWSFTCSMap">
> select
> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completion_Date,Model
> from FTCS_WRK_PKG_INFO $whereClause$ order by Model,Line_No,ip_name
> </select>
>
> Now in Ibatis 3 I am supposed to use below select statement syntax
> which is not working.
>
> <select id="getWPIData" parameterType="java.lang.String"
> resultType="WorkPkgInfo" resultMap="PMWSFTCSMap">
> select
> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completion_Date,Model
> from FTCS_WRK_PKG_INFO #{whereClause} order by Model,Line_No,ip_name
> </select>
>
> In this case whereClause variable holds the follwoing value "model =
> '747' and Line_No in (1424) or model = '787' and Line_No in
> (5,2,6,3,4) or model = '737' and Line_No in
> (3356,2570,3464,2965,3052,3393,3138,3244)" which will be generated
> dynamically based on the request that we get.
>
> Is there any otherway to specify the parameters? According to iBatis
> user guide it should be #{whereClause} .
>
>
Reply | Threaded
Open this post in threaded view
|

Re: how to use dynamic parameters in select statement in iBatis 3

Andy Law
In reply to this post by radha
Short Answer:
Change #{whereClause} to ${whereClause}?

Long Answer:
Read page 44 et seq. in the MyBatis-3-User-Guide.pdf document.

Hope those help.

Later,

Andy

On Oct 5, 11:51 pm, radh <[hidden email]> wrote:

> In Ibatis 2 , I used below select statement.
>
> <select id="getWPIData" parameterClass="java.lang.String"
> resultMap="PMWSFTCSMap">
> select
> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completi on_Date,Model
> from FTCS_WRK_PKG_INFO $whereClause$ order by Model,Line_No,ip_name
> </select>
>
> Now in Ibatis 3 I am supposed to use below select statement syntax
> which is not working.
>
> <select id="getWPIData" parameterType="java.lang.String"
> resultType="WorkPkgInfo" resultMap="PMWSFTCSMap">
> select
> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completi on_Date,Model
> from FTCS_WRK_PKG_INFO #{whereClause} order by Model,Line_No,ip_name
> </select>
>
> In this case whereClause variable holds the follwoing value "model =
> '747' and Line_No in (1424) or model = '787' and Line_No in
> (5,2,6,3,4) or model = '737' and Line_No in
> (3356,2570,3464,2965,3052,3393,3138,3244)" which will be generated
> dynamically based on the request that we get.
>
> Is there any otherway to specify the parameters? According to iBatis
> user guide it should be #{whereClause} .
Reply | Threaded
Open this post in threaded view
|

Re: how to use dynamic parameters in select statement in iBatis 3

radha
when I changed to ${whereClause} I am getting below exception

Error in row handler
### Error querying database.  Cause:
org.apache.ibatis.reflection.ReflectionException: There is no getter
for property named 'whereClause' in 'class java.lang.String'
### The error may exist in com/boeing/siai/pmws/mapping/PmwsSqlMap.xml
### The error may involve com.boeing.siai.pmws.domain.WorkPkgInfo.getWPIData
### The error occurred while executing a query
### Cause: org.apache.ibatis.reflection.ReflectionException: There is
no getter for property named 'whereClause' in 'class java.lang.String'


Hi Nino - I don't know how to do the approach that you are saying. Can
you please take my example query and expalin how I can do.

On 10/6/10, Andy Law <[hidden email]> wrote:

> Short Answer:
> Change #{whereClause} to ${whereClause}?
>
> Long Answer:
> Read page 44 et seq. in the MyBatis-3-User-Guide.pdf document.
>
> Hope those help.
>
> Later,
>
> Andy
>
> On Oct 5, 11:51 pm, radh <[hidden email]> wrote:
>> In Ibatis 2 , I used below select statement.
>>
>> <select id="getWPIData" parameterClass="java.lang.String"
>> resultMap="PMWSFTCSMap">
>> select
>> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completi
>> on_Date,Model
>> from FTCS_WRK_PKG_INFO $whereClause$ order by Model,Line_No,ip_name
>> </select>
>>
>> Now in Ibatis 3 I am supposed to use below select statement syntax
>> which is not working.
>>
>> <select id="getWPIData" parameterType="java.lang.String"
>> resultType="WorkPkgInfo" resultMap="PMWSFTCSMap">
>> select
>> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completi
>> on_Date,Model
>> from FTCS_WRK_PKG_INFO #{whereClause} order by Model,Line_No,ip_name
>> </select>
>>
>> In this case whereClause variable holds the follwoing value "model =
>> '747' and Line_No in (1424) or model = '787' and Line_No in
>> (5,2,6,3,4) or model = '737' and Line_No in
>> (3356,2570,3464,2965,3052,3393,3138,3244)" which will be generated
>> dynamically based on the request that we get.
>>
>> Is there any otherway to specify the parameters? According to iBatis
>> user guide it should be #{whereClause} .
Reply | Threaded
Open this post in threaded view
|

RE: how to use dynamic parameters in select statement in iBatis 3

Poitras Christian
It seems that ${} behaves a little differently than #{}.
As a workaround, try to use ${value} or add a @Param("whereClause") annotation in the mapper's method.

Christian

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de radha
Envoyé : October-06-10 7:36 AM
À : [hidden email]
Objet : Re: how to use dynamic parameters in select statement in iBatis 3

when I changed to ${whereClause} I am getting below exception

Error in row handler
### Error querying database.  Cause:
org.apache.ibatis.reflection.ReflectionException: There is no getter
for property named 'whereClause' in 'class java.lang.String'
### The error may exist in com/boeing/siai/pmws/mapping/PmwsSqlMap.xml
### The error may involve com.boeing.siai.pmws.domain.WorkPkgInfo.getWPIData
### The error occurred while executing a query
### Cause: org.apache.ibatis.reflection.ReflectionException: There is
no getter for property named 'whereClause' in 'class java.lang.String'


Hi Nino - I don't know how to do the approach that you are saying. Can
you please take my example query and expalin how I can do.

On 10/6/10, Andy Law <[hidden email]> wrote:

> Short Answer:
> Change #{whereClause} to ${whereClause}?
>
> Long Answer:
> Read page 44 et seq. in the MyBatis-3-User-Guide.pdf document.
>
> Hope those help.
>
> Later,
>
> Andy
>
> On Oct 5, 11:51 pm, radh <[hidden email]> wrote:
>> In Ibatis 2 , I used below select statement.
>>
>> <select id="getWPIData" parameterClass="java.lang.String"
>> resultMap="PMWSFTCSMap">
>> select
>> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completi
>> on_Date,Model
>> from FTCS_WRK_PKG_INFO $whereClause$ order by Model,Line_No,ip_name
>> </select>
>>
>> Now in Ibatis 3 I am supposed to use below select statement syntax
>> which is not working.
>>
>> <select id="getWPIData" parameterType="java.lang.String"
>> resultType="WorkPkgInfo" resultMap="PMWSFTCSMap">
>> select
>> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completi
>> on_Date,Model
>> from FTCS_WRK_PKG_INFO #{whereClause} order by Model,Line_No,ip_name
>> </select>
>>
>> In this case whereClause variable holds the follwoing value "model =
>> '747' and Line_No in (1424) or model = '787' and Line_No in
>> (5,2,6,3,4) or model = '737' and Line_No in
>> (3356,2570,3464,2965,3052,3393,3138,3244)" which will be generated
>> dynamically based on the request that we get.
>>
>> Is there any otherway to specify the parameters? According to iBatis
>> user guide it should be #{whereClause} .
Reply | Threaded
Open this post in threaded view
|

Re: how to use dynamic parameters in select statement in iBatis 3

radha
I have a question. In my case the whole where class I need to generate
dynamically. it is not like I am passing just parameter value like for
ex. where parameter=#{value}. That might be the issue. But it worked
in previous versions of iBatis.

Christian - I will try your suggested option and see if that works.

On 10/6/10, Poitras Christian <[hidden email]> wrote:

> It seems that ${} behaves a little differently than #{}.
> As a workaround, try to use ${value} or add a @Param("whereClause")
> annotation in the mapper's method.
>
> Christian
>
> -----Message d'origine-----
> De : [hidden email] [mailto:[hidden email]] De
> la part de radha
> Envoyé : October-06-10 7:36 AM
> À : [hidden email]
> Objet : Re: how to use dynamic parameters in select statement in iBatis 3
>
> when I changed to ${whereClause} I am getting below exception
>
> Error in row handler
> ### Error querying database.  Cause:
> org.apache.ibatis.reflection.ReflectionException: There is no getter
> for property named 'whereClause' in 'class java.lang.String'
> ### The error may exist in com/boeing/siai/pmws/mapping/PmwsSqlMap.xml
> ### The error may involve com.boeing.siai.pmws.domain.WorkPkgInfo.getWPIData
> ### The error occurred while executing a query
> ### Cause: org.apache.ibatis.reflection.ReflectionException: There is
> no getter for property named 'whereClause' in 'class java.lang.String'
>
>
> Hi Nino - I don't know how to do the approach that you are saying. Can
> you please take my example query and expalin how I can do.
>
> On 10/6/10, Andy Law <[hidden email]> wrote:
>> Short Answer:
>> Change #{whereClause} to ${whereClause}?
>>
>> Long Answer:
>> Read page 44 et seq. in the MyBatis-3-User-Guide.pdf document.
>>
>> Hope those help.
>>
>> Later,
>>
>> Andy
>>
>> On Oct 5, 11:51 pm, radh <[hidden email]> wrote:
>>> In Ibatis 2 , I used below select statement.
>>>
>>> <select id="getWPIData" parameterClass="java.lang.String"
>>> resultMap="PMWSFTCSMap">
>>> select
>>> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completi
>>> on_Date,Model
>>> from FTCS_WRK_PKG_INFO $whereClause$ order by Model,Line_No,ip_name
>>> </select>
>>>
>>> Now in Ibatis 3 I am supposed to use below select statement syntax
>>> which is not working.
>>>
>>> <select id="getWPIData" parameterType="java.lang.String"
>>> resultType="WorkPkgInfo" resultMap="PMWSFTCSMap">
>>> select
>>> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completi
>>> on_Date,Model
>>> from FTCS_WRK_PKG_INFO #{whereClause} order by Model,Line_No,ip_name
>>> </select>
>>>
>>> In this case whereClause variable holds the follwoing value "model =
>>> '747' and Line_No in (1424) or model = '787' and Line_No in
>>> (5,2,6,3,4) or model = '737' and Line_No in
>>> (3356,2570,3464,2965,3052,3393,3138,3244)" which will be generated
>>> dynamically based on the request that we get.
>>>
>>> Is there any otherway to specify the parameters? According to iBatis
>>> user guide it should be #{whereClause} .
>
Reply | Threaded
Open this post in threaded view
|

RE: how to use dynamic parameters in select statement in iBatis 3

Poitras Christian
Personally, I think the best option is to generate the SQL with SqlBuilder - add @SelectProvider to your mapper.

Beside that I don't think there is any problems with using SELECT * FROM table ${whereClause}  in MyBatis. The problem you are facing is that MyBatis tries to get whereClause property from the parameter (String).

Christian

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de radha
Envoyé : October-06-10 11:32 AM
À : [hidden email]
Objet : Re: how to use dynamic parameters in select statement in iBatis 3

I have a question. In my case the whole where class I need to generate
dynamically. it is not like I am passing just parameter value like for
ex. where parameter=#{value}. That might be the issue. But it worked
in previous versions of iBatis.

Christian - I will try your suggested option and see if that works.

On 10/6/10, Poitras Christian <[hidden email]> wrote:

> It seems that ${} behaves a little differently than #{}.
> As a workaround, try to use ${value} or add a @Param("whereClause")
> annotation in the mapper's method.
>
> Christian
>
> -----Message d'origine-----
> De : [hidden email] [mailto:[hidden email]] De
> la part de radha
> Envoyé : October-06-10 7:36 AM
> À : [hidden email]
> Objet : Re: how to use dynamic parameters in select statement in iBatis 3
>
> when I changed to ${whereClause} I am getting below exception
>
> Error in row handler
> ### Error querying database.  Cause:
> org.apache.ibatis.reflection.ReflectionException: There is no getter
> for property named 'whereClause' in 'class java.lang.String'
> ### The error may exist in com/boeing/siai/pmws/mapping/PmwsSqlMap.xml
> ### The error may involve com.boeing.siai.pmws.domain.WorkPkgInfo.getWPIData
> ### The error occurred while executing a query
> ### Cause: org.apache.ibatis.reflection.ReflectionException: There is
> no getter for property named 'whereClause' in 'class java.lang.String'
>
>
> Hi Nino - I don't know how to do the approach that you are saying. Can
> you please take my example query and expalin how I can do.
>
> On 10/6/10, Andy Law <[hidden email]> wrote:
>> Short Answer:
>> Change #{whereClause} to ${whereClause}?
>>
>> Long Answer:
>> Read page 44 et seq. in the MyBatis-3-User-Guide.pdf document.
>>
>> Hope those help.
>>
>> Later,
>>
>> Andy
>>
>> On Oct 5, 11:51 pm, radh <[hidden email]> wrote:
>>> In Ibatis 2 , I used below select statement.
>>>
>>> <select id="getWPIData" parameterClass="java.lang.String"
>>> resultMap="PMWSFTCSMap">
>>> select
>>> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completi
>>> on_Date,Model
>>> from FTCS_WRK_PKG_INFO $whereClause$ order by Model,Line_No,ip_name
>>> </select>
>>>
>>> Now in Ibatis 3 I am supposed to use below select statement syntax
>>> which is not working.
>>>
>>> <select id="getWPIData" parameterType="java.lang.String"
>>> resultType="WorkPkgInfo" resultMap="PMWSFTCSMap">
>>> select
>>> Line_No,ip_name,ip_title,cs_identifier,change_order,SOI_Status,SOI_Completi
>>> on_Date,Model
>>> from FTCS_WRK_PKG_INFO #{whereClause} order by Model,Line_No,ip_name
>>> </select>
>>>
>>> In this case whereClause variable holds the follwoing value "model =
>>> '747' and Line_No in (1424) or model = '787' and Line_No in
>>> (5,2,6,3,4) or model = '737' and Line_No in
>>> (3356,2570,3464,2965,3052,3393,3138,3244)" which will be generated
>>> dynamically based on the request that we get.
>>>
>>> Is there any otherway to specify the parameters? According to iBatis
>>> user guide it should be #{whereClause} .
>