mybatis like '%specific string%'

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

mybatis like '%specific string%'

Emi Lu
Good morning,

May I know how mybatis deal with like '%specific_string%' please?

like #{string} does not get my result, what I really need is like
#{%string%}

Thank you,
Lu Ying
Reply | Threaded
Open this post in threaded view
|

Re: mybatis like '%specific string%'

Nathan Maves
You have three options

'%'||${specific_string}||'%'

or

#{do_it_in_java}

or

someFunctionOrProc(#{specific_string}) where the function/proc will
handle it.  If you use this a lot in multiple SqlMaps then I might go
down this road.


On Wed, Dec 1, 2010 at 11:44 AM, Lu Ying <[hidden email]> wrote:
> Good morning,
>
> May I know how mybatis deal with like '%specific_string%' please?
>
> like #{string} does not get my result, what I really need is like
> #{%string%}
>
> Thank you,
> Lu Ying
Reply | Threaded
Open this post in threaded view
|

RE: mybatis like '%specific string%'

Poitras Christian
In reply to this post by Emi Lu
You have 3 options.
1. Use CONCAT or || function in your query - database dependent.
2. Add '%' in your service/dao layer before calling the query.
3. Use %${specific_string}% in your query. Remember that this opens you to SQL injection.

Christian

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de Lu Ying
Envoyé : December-01-10 1:45 PM
À : mybatis-user
Objet : mybatis like '%specific string%'

Good morning,

May I know how mybatis deal with like '%specific_string%' please?

like #{string} does not get my result, what I really need is like
#{%string%}

Thank you,
Lu Ying
Reply | Threaded
Open this post in threaded view
|

Re: mybatis like '%specific string%'

Emi Lu
In reply to this post by Nathan Maves
> You have three options
>
> '%'||${specific_string}||'%'

Thank you for all helps!

So, if I put
<mapper...>
...
    select * from table
    where  last_name ilike '%'||${last_name}||'%'
...
</mapper>

It will work, right?

Thank you,
Lu Ying

Reply | Threaded
Open this post in threaded view
|

Re: mybatis like '%specific string%'

Nathan Maves
How about you try it and let us know :)

PS.  it wont if you spell like as ilike

PSS.  be very carful with this approach because it can be hacked via
sql injection.

On Wed, Dec 1, 2010 at 1:21 PM, Emi Lu <[hidden email]> wrote:

>> You have three options
>>
>> '%'||${specific_string}||'%'
>
> Thank you for all helps!
>
> So, if I put
> <mapper...>
> ...
>   select * from table
>   where  last_name ilike '%'||${last_name}||'%'
> ...
> </mapper>
>
> It will work, right?
>
> Thank you,
> Lu Ying
>
>
Reply | Threaded
Open this post in threaded view
|

Re: mybatis like '%specific string%'

Ethan
i try it like this:
<mapper...>
...
    select * from table
    where  last_name ilike '%'||#{last_name}||'%'
...
</mapper>
but it doesnt work! result is nothing!

if i use '$' replace '#', there will be a error like:
Error querying database.  Cause: java.sql.SQLException: ORA-00904:
"AAAAAAAAAE0E": Invalid identifier

"AAAAAAAAAE0E" is the value of  #{last_name}

what's the problem?

Thanks a lot,
Ethan

On Dec 2, 9:21 am, Nathan Maves <[hidden email]> wrote:

> How about you try it and let us know :)
>
> PS.  it wont if you spell like as ilike
>
> PSS.  be very carful with this approach because it can be hacked via
> sql injection.
>
> On Wed, Dec 1, 2010 at 1:21 PM, Emi Lu <[hidden email]> wrote:
> >> You have three options
>
> >> '%'||${specific_string}||'%'
>
> > Thank you for all helps!
>
> > So, if I put
> > <mapper...>
> > ...
> >   select * from table
> >   where  last_name ilike '%'||${last_name}||'%'
> > ...
> > </mapper>
>
> > It will work, right?
>
> > Thank you,
> > Lu Ying
Reply | Threaded
Open this post in threaded view
|

Re: mybatis like '%specific string%'

Larry Meadors
Is the problem that you're searching for '%"AAAAAAAAAE0E"%' instead of
'%AAAAAAAAAE0E%'?

Larry


On Wed, Dec 29, 2010 at 4:03 AM, Ethan <[hidden email]> wrote:

> i try it like this:
> <mapper...>
> ...
>    select * from table
>    where  last_name ilike '%'||#{last_name}||'%'
> ...
> </mapper>
> but it doesnt work! result is nothing!
>
> if i use '$' replace '#', there will be a error like:
> Error querying database.  Cause: java.sql.SQLException: ORA-00904:
> "AAAAAAAAAE0E": Invalid identifier
>
> "AAAAAAAAAE0E" is the value of  #{last_name}
>
> what's the problem?
>
> Thanks a lot,
> Ethan
>
> On Dec 2, 9:21 am, Nathan Maves <[hidden email]> wrote:
>> How about you try it and let us know :)
>>
>> PS.  it wont if you spell like as ilike
>>
>> PSS.  be very carful with this approach because it can be hacked via
>> sql injection.
>>
>> On Wed, Dec 1, 2010 at 1:21 PM, Emi Lu <[hidden email]> wrote:
>> >> You have three options
>>
>> >> '%'||${specific_string}||'%'
>>
>> > Thank you for all helps!
>>
>> > So, if I put
>> > <mapper...>
>> > ...
>> >   select * from table
>> >   where  last_name ilike '%'||${last_name}||'%'
>> > ...
>> > </mapper>
>>
>> > It will work, right?
>>
>> > Thank you,
>> > Lu Ying
Reply | Threaded
Open this post in threaded view
|

Re: mybatis like '%specific string%'

Ethan
thanks, Larry.
i try it like this:
<mapper...>
  ...
    select * from table
    where  last_name like '%'|| #{last_name} ||'%'
  ...
</mapper>
add a space between || and #{}, And it worked!!!
I don't know why? :(
is the PreparedStatement like what you said? i am not sure.

On Wed, Dec 29, 2010 at 8:52 PM, Larry Meadors <[hidden email]> wrote:
Is the problem that you're searching for '%"AAAAAAAAAE0E"%' instead of
'%AAAAAAAAAE0E%'?

Larry


On Wed, Dec 29, 2010 at 4:03 AM, Ethan <[hidden email]> wrote:
> i try it like this:
> <mapper...>
> ...
>    select * from table
>    where  last_name ilike '%'||#{last_name}||'%'
> ...
> </mapper>
> but it doesnt work! result is nothing!
>
> if i use '$' replace '#', there will be a error like:
> Error querying database.  Cause: java.sql.SQLException: ORA-00904:
> "AAAAAAAAAE0E": Invalid identifier
>
> "AAAAAAAAAE0E" is the value of  #{last_name}
>
> what's the problem?
>
> Thanks a lot,
> Ethan
>
> On Dec 2, 9:21 am, Nathan Maves <[hidden email]> wrote:
>> How about you try it and let us know :)
>>
>> PS.  it wont if you spell like as ilike
>>
>> PSS.  be very carful with this approach because it can be hacked via
>> sql injection.
>>
>> On Wed, Dec 1, 2010 at 1:21 PM, Emi Lu <[hidden email]> wrote:
>> >> You have three options
>>
>> >> '%'||${specific_string}||'%'
>>
>> > Thank you for all helps!
>>
>> > So, if I put
>> > <mapper...>
>> > ...
>> >   select * from table
>> >   where  last_name ilike '%'||${last_name}||'%'
>> > ...
>> > </mapper>
>>
>> > It will work, right?
>>
>> > Thank you,
>> > Lu Ying

Reply | Threaded
Open this post in threaded view
|

Re: mybatis like '%specific string%'

Jackie Li
Hi,

Is that a MyBatis issue, unable to recognize #{last_name}, if the '#' is close to another character in front?

Cheers,
Jackie


On Thu, Dec 30, 2010 at 10:21, LingJun Zhang <[hidden email]> wrote:
thanks, Larry.

i try it like this:
<mapper...>
  ...
    select * from table
    where  last_name like '%'|| #{last_name} ||'%'
  ...
</mapper>
add a space between || and #{}, And it worked!!!
I don't know why? :(
is the PreparedStatement like what you said? i am not sure.


On Wed, Dec 29, 2010 at 8:52 PM, Larry Meadors <[hidden email]> wrote:
Is the problem that you're searching for '%"AAAAAAAAAE0E"%' instead of
'%AAAAAAAAAE0E%'?

Larry


On Wed, Dec 29, 2010 at 4:03 AM, Ethan <[hidden email]> wrote:
> i try it like this:
> <mapper...>
> ...
>    select * from table
>    where  last_name ilike '%'||#{last_name}||'%'
> ...
> </mapper>
> but it doesnt work! result is nothing!
>
> if i use '$' replace '#', there will be a error like:
> Error querying database.  Cause: java.sql.SQLException: ORA-00904:
> "AAAAAAAAAE0E": Invalid identifier
>
> "AAAAAAAAAE0E" is the value of  #{last_name}
>
> what's the problem?
>
> Thanks a lot,
> Ethan
>
> On Dec 2, 9:21 am, Nathan Maves <[hidden email]> wrote:
>> How about you try it and let us know :)
>>
>> PS.  it wont if you spell like as ilike
>>
>> PSS.  be very carful with this approach because it can be hacked via
>> sql injection.
>>
>> On Wed, Dec 1, 2010 at 1:21 PM, Emi Lu <[hidden email]> wrote:
>> >> You have three options
>>
>> >> '%'||${specific_string}||'%'
>>
>> > Thank you for all helps!
>>
>> > So, if I put
>> > <mapper...>
>> > ...
>> >   select * from table
>> >   where  last_name ilike '%'||${last_name}||'%'
>> > ...
>> > </mapper>
>>
>> > It will work, right?
>>
>> > Thank you,
>> > Lu Ying