custom query with 'like' string

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

custom query with 'like' string

gfrancis1@gmail.com
I have a query that I'm migrating from iBatis to MyBatis.  A map is
passed in with a String value for key 'search_text' used as follows:

<select id="searchInvoices" parameterType="java.util.Map"
resultType="com.myco.myprod.model.Invoice">
    select i.* from invoice i,account a
    where
    invoice_desc like '#{search_text}'
    and
    a.carrier_id = #{carrier_id}
...

This currently throws an exception:
DataIntegrityViolationException: SqlSession operation; SQL []; The
column index is out of range: 2, number of columns: 1.;

Is there a different way to denote the quoted search_text parameter in
myBatis?
Reply | Threaded
Open this post in threaded view
|

Re: custom query with 'like' string

Nathan Maves
I don't think you need the single quotes around #{search_text}.  If it is a String MyBatis will handle that for you.

You may or may not know but your query is going to return cartesian results.  Your two tables are not joined.

Nathan


On Tue, Oct 26, 2010 at 9:12 AM, blackfrancis <[hidden email]> wrote:
I have a query that I'm migrating from iBatis to MyBatis.  A map is
passed in with a String value for key 'search_text' used as follows:

<select id="searchInvoices" parameterType="java.util.Map"
resultType="com.myco.myprod.model.Invoice">
   select i.* from invoice i,account a
   where
   invoice_desc like '#{search_text}'
   and
   a.carrier_id = #{carrier_id}
...

This currently throws an exception:
DataIntegrityViolationException: SqlSession operation; SQL []; The
column index is out of range: 2, number of columns: 1.;

Is there a different way to denote the quoted search_text parameter in
myBatis?

Reply | Threaded
Open this post in threaded view
|

Re: custom query with 'like' string

gfrancis1@gmail.com
Thanks Nathan,
Yeah, I truncated the join just to illustrate my issue.
Thanks for the help - removing the quotes worked.
Out of interest - how does the engine know to insert quotes?  does it
do that for all String parameters?  What if I wanted to insert a
string into a query without quotes (eg: to be treated as SQL
keywords)?
thx

On Oct 26, 11:46 am, Nathan Maves <[hidden email]> wrote:

> I don't think you need the single quotes around #{search_text}.  If it is a
> String MyBatis will handle that for you.
>
> You may or may not know but your query is going to return cartesian results.
>  Your two tables are not joined.
>
> Nathan
>
>
>
>
>
>
>
> On Tue, Oct 26, 2010 at 9:12 AM, blackfrancis <[hidden email]> wrote:
> > I have a query that I'm migrating from iBatis to MyBatis.  A map is
> > passed in with a String value for key 'search_text' used as follows:
>
> > <select id="searchInvoices" parameterType="java.util.Map"
> > resultType="com.myco.myprod.model.Invoice">
> >    select i.* from invoice i,account a
> >    where
> >    invoice_desc like '#{search_text}'
> >    and
> >    a.carrier_id = #{carrier_id}
> > ...
>
> > This currently throws an exception:
> > DataIntegrityViolationException: SqlSession operation; SQL []; The
> > column index is out of range: 2, number of columns: 1.;
>
> > Is there a different way to denote the quoted search_text parameter in
> > myBatis?
Reply | Threaded
Open this post in threaded view
|

Re: custom query with 'like' string

Larry Meadors
On Tue, Oct 26, 2010 at 11:13 AM, blackfrancis <[hidden email]> wrote:
> Thanks for the help - removing the quotes worked.
> Out of interest - how does the engine know to insert quotes?  does it
> do that for all String parameters?  What if I wanted to insert a
> string into a query without quotes (eg: to be treated as SQL
> keywords)?

If you want to inject SQL you would use ${blah} instead of #{blah}.

The #{blah} marker is changed to a "?" in the PreparedStatement used
to execute the query, the ${blah} marker is replaced with the value in
blah.

Larry