Java – MyBatis Parameter index out of range

javamybatisMySQL

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.

Best Answer

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.

Related Topic