MyBatis Parameter index out of range

Question

I'm trying to write this query into MyBatis

    (select * from table1 where field1 like '%"name":"appname"%' limit 1)
    union all
    (select * from table2 where field1 like '%"name":"appname"%' limit 1)
    limit 1

So I created this annotation:

@Select({
    "(select * from table1 where field1 like '%\"name\":\"#{name,jdbcType=VARCHAR}\"%' limit 1)",
    "union all",
    "(select * from table2 where field1 like '%\"name\":\"#{name,jdbcType=VARCHAR}\"%' limit 1)",
    "limit 1"
})
@Results({
    @Result(column="ID", property="id", jdbcType=JdbcType.INTEGER, id=true)
})
Object hasName(@Param("name")String name);

But when I run it I'm getting this exception:

Could not set parameters for mapping: ParameterMapping{property='name', mode=IN, javaType=class java.lang.String, jdbcType=VARCHAR, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

Can I map the annotation like that or am I missing something? I dont want to use XML for this.

Can you pass two parameters with different names and check? hasName(@Param("name")String name, @Param("otherName")String otherName); I guess the problem is mybatis is expecting two parameters but you are passing one. — Lucky
@Lucky I'm getting the same error.. I think there's something wrong with the \" — feco
Can you also post the mysql equivalent of this query in your question and try if this works in you mysql workbench? — Lucky
@Lucky The query is in the first paragraph and it works, I have found a workaround. Thanks :) — feco

feco feco · Accepted Answer · 2016-03-09T07:40:16

I decided to change part of my query from

field1 like '%\"name\":\"#{name,jdbcType=VARCHAR}\"%'

to

field1 like #{name,jdbcType=VARCHAR}

and have the parameter name changed in Java

parameter = "%\"name\":\"" + parameter + "\"%";

I followed the explanation from a post I found

'%'||${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.

MyBatis Parameter index out of range

3 Answers