How to use Annotations with iBatis (myBatis) for an IN query?

Question

We'd like to use only annotations with MyBatis; we're really trying to avoid xml. We're trying to use an "IN" clause:

@Select("SELECT * FROM blog WHERE id IN (#{ids})") 
List<Blog> selectBlogs(int[] ids); 

MyBatis doesn't seem able to pick out the array of ints and put those into the resulting query. It seems to "fail softly" and we get no results back.

It looks like we could accomplish this using XML mappings, but we'd really like to avoid that. Is there a correct annotation syntax for this?

Softball

Normal SQL requires dynamic SQL to use a variable that represents a comma separated list of values. – Derive

@OMG Ponies: My apologies, I'm not sure what you're trying to say? If I were to take your wisdom and apply it to this problem, what would my solution look like specifically? – Softball

I've never worked with iBatis, but can you create the SQL statement as a string (including variable contents) before anything else happens? That's all dynamic SQL really is... – Derive

Answer 1

I believe this is a nuance of jdbc's prepared statements and not MyBatis. There is a link here that explains this problem and offers various solutions. Unfortunately, none of these solutions are viable for your application, however, its still a good read to understand the limitations of prepared statements with regards to an "IN" clause. A solution (maybe suboptimal) can be found on the DB-specific side of things. For example, in postgresql, one could use:

"SELECT * FROM blog WHERE id=ANY(#{blogIds}::int[])"

"ANY" is the same as "IN" and "::int[]" is type casting the argument into an array of ints. The argument that is fed into the statement should look something like:

"{1,2,3,4}"

Mali

The JavaRanch link presents an interesting idea of breaking the array into multiple chunks and executing batches. This is not postgres specific and could be implemented in iBatis with a TypeHandler like @pevgen's suggestion. – Whitener

In MySQL, use the following query, passing "blogIds" as a String with the ids separated by comma: "SELECT * FROM blog WHERE FIND_IN_SET(id, #{blogIds}) <> 0" – Alded

This was a great first step, but did not work for me. Ultimately I had to write a typehandler for the ArrayList (using connection.createArrayOf()), then reference the typehandler directly in the {} section before ::int[]. Thanks for the good lead, however. – Invoke

Answer 2

I believe the answer is the same as is given in this question. You can use myBatis Dynamic SQL in your annotations by doing the following:

@Select({"<script>",
         "SELECT *", 
         "FROM blog",
         "WHERE id IN", 
           "<foreach item='item' index='index' collection='list'",
             "open='(' separator=',' close=')'>",
             "#{item}",
           "</foreach>",
         "</script>"}) 
List<Blog> selectBlogs(@Param("list") int[] ids);

The <script> element enables dynamic SQL parsing and execution for the annotation. It must be very first content of the query string. Nothing must be in front of it, not even white space.

Note that the variables that you can use in the various XML script tags follow the same naming conventions as regular queries, so if you want to refer to your method arguments using names other than "param1", "param2", etc... you need to prefix each argument with an @Param annotation.

Windage

Whenever I do this, I get an exception: "org.apache.ibatis.binding.BindingException: Parameter 'item' not found." What is the minimum version of mybatis required for this to work? – Doubleganger

This only works with myBatis 3. I'm not sure exactly which minor versions support it. Also make absolutely sure that there is no whitespace before the first < of the <script> tag, or you'll get all sorts of strange errors. – Windage

For anybody experiencing this problem, I was previously using version 3.1.1 and it wasn't working. I updated to version 3.2.7 and now it works, so it must have been fixed somewhere between those two versions. – Doubleganger

Is this functionality official? – Highborn

Answer 3

Had some research on this topic.

1. one of official solution from mybatis is to put your dynamic sql in @Select("<script>...</script>"). However, writing xml in java annotation is quite ungraceful. think about this @Select("<script>select name from sometable where id in <foreach collection=\"items\" item=\"item\" seperator=\",\" open=\"(\" close=\")\">${item}</script>")
2. @SelectProvider works fine. But it's a little complicated to read.
3. PreparedStatement not allow you set list of integer. pstm.setString(index, "1,2,3,4") will let your SQL like this select name from sometable where id in ('1,2,3,4'). Mysql will convert chars '1,2,3,4' to number 1.
4. FIND_IN_SET don't works with mysql index.

Look in to mybatis dynamic sql mechanism, it has been implemented by SqlNode.apply(DynamicContext). However, @Select without <script></script> annotation will not pass parameter via DynamicContext

see also

• org.apache.ibatis.scripting.xmltags.XMLLanguageDriver
• org.apache.ibatis.scripting.xmltags.DynamicSqlSource
• org.apache.ibatis.scripting.xmltags.RawSqlSource

So,

• Solution 1: Use @SelectProvider
• Solution 2: Extend LanguageDriver which will always compile sql to DynamicSqlSource. However, you still have to write \" everywhere.
• Solution 3: Extend LanguageDriver which can convert your own grammar to mybatis one.
• Solution 4: Write your own LanguageDriver which compile SQL with some template renderer, just like mybatis-velocity project does. In this way, you can even integrate groovy.

My project take solution 3 and here's the code:

public class MybatisExtendedLanguageDriver extends XMLLanguageDriver 
                                           implements LanguageDriver {
    private final Pattern inPattern = Pattern.compile("\\(#\\{(\\w+)\\}\\)");
    public SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType) {
        Matcher matcher = inPattern.matcher(script);
        if (matcher.find()) {
            script = matcher.replaceAll("(<foreach collection=\"$1\" item=\"__item\" separator=\",\" >#{__item}</foreach>)");
        }
        script = "<script>" + script + "</script>";
        return super.createSqlSource(configuration, script, parameterType);
    }
}

And the usage:

@Lang(MybatisExtendedLanguageDriver.class)
@Select("SELECT " + COLUMNS + " FROM sometable where id IN (#{ids})")
List<SomeItem> loadByIds(@Param("ids") List<Integer> ids);

Markman

Can't believe we still have to do this in 2016. Am I missing something? – Cyler

in 2019, still seems to be the best solution~ – Backblocks

Answer 4

I've made a small trick in my code.

public class MyHandler implements TypeHandler {

public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
    Integer[] arrParam = (Integer[]) parameter;
    String inString = "";
    for(Integer element : arrParam){
      inString = "," + element;
    }
    inString = inString.substring(1);        
    ps.setString(i,inString);
}

And I used this MyHandler in SqlMapper :

    @Select("select id from tmo where id_parent in (#{ids, typeHandler=ru.transsys.test.MyHandler})")
public List<Double> getSubObjects(@Param("ids") Integer[] ids) throws SQLException;

It works now :) I hope this will help someone.

Evgeny

Towardly

You are creating a single big String with all the values in it. Does this require casting on the DB? Not sure if this would work on all DBs. – Whitener

Thank's for your comment. You are right. I made it to the DB Oracle only. – Towardly

This won't work because it creates a query like where id_parent in ('1,2,3') with 1 parameter instead of where id_parent in (1,2,3) with 3 parameters. It's worse if you use strings as that would create where name in ('''Tom'',''Dick'',''Harry''') with 1 parameter and the quotes escaped with doubling. – Drais

Answer 5

Other option can be

    public class Test
    {
        @SuppressWarnings("unchecked")
        public static String getTestQuery(Map<String, Object> params)
        {

            List<String> idList = (List<String>) params.get("idList");

            StringBuilder sql = new StringBuilder();

            sql.append("SELECT * FROM blog WHERE id in (");
            for (String id : idList)
            {
                if (idList.indexOf(id) > 0)
                    sql.append(",");

                sql.append("'").append(id).append("'");
            }
            sql.append(")");

            return sql.toString();
        }

        public interface TestMapper
        {
            @SelectProvider(type = Test.class, method = "getTestQuery")
List<Blog> selectBlogs(@Param("idList") int[] ids);
        }
    }

Ducks

Answer 6

In my project, we are already using Google Guava, so a quick shortcut is.

public class ListTypeHandler implements TypeHandler {

    @Override
    public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, Joiner.on(",").join((Collection) parameter));
    }
}

Nightwalker

This won't work because it creates a query like where id_parent in ('1,2,3') with 1 parameter instead of where id_parent in (1,2,3) with 3 parameters. It's worse if you use strings as that would create where name in ('''Tom'',''Dick'',''Harry''') with 1 parameter and the quotes escaped with doubling. – Drais

Answer 7

In Oracle, I use a variant of Tom Kyte's tokenizer to handle unknown list sizes (given Oracle's 1k limit on an IN clause and the aggravation of doing multiple INs to get around it). This is for varchar2, but it can be tailored for numbers (or you could just rely on Oracle knowing that '1' = 1 /shudder).

Assuming you pass or perform myBatis incantations to get ids as a String, to use it:

select @Select("SELECT * FROM blog WHERE id IN (select * from table(string_tokenizer(#{ids}))")

The code:

create or replace function string_tokenizer(p_string in varchar2, p_separator in varchar2 := ',') return sys.dbms_debug_vc2coll is
    return_value SYS.DBMS_DEBUG_VC2COLL;
    pattern varchar2(250);
begin
    pattern := '[^(''' || p_separator || ''')]+' ;

    select
        trim(regexp_substr(p_string, pattern, 1, level)) token
    bulk collect into
        return_value
    from
        dual
    where
        regexp_substr(p_string, pattern, 1, level) is not null
    connect by
        regexp_instr(p_string, pattern, 1, level) > 0;

    return return_value;
end string_tokenizer;

Toed

Answer 8

You could use a custom type handler to do this. For example:

public class InClauseParams extends ArrayList<String> {
   //...
   // marker class for easier type handling, and avoid potential conflict with other list handlers
}

Register the following type handler in your MyBatis config (or specify in your annotation):

public class InClauseTypeHandler extends BaseTypeHandler<InClauseParams> {

    @Override
    public void setNonNullParameter(final PreparedStatement ps, final int i, final InClauseParams parameter, final JdbcType jdbcType) throws SQLException {

        // MySQL driver does not support this :/
        Array array = ps.getConnection().createArrayOf( "VARCHAR", parameter.toArray() );
        ps.setArray( i, array );
    }
    // other required methods omitted for brevity, just add a NOOP implementation
}

You can then use them like this

@Select("SELECT * FROM foo WHERE id IN (#{list})"
List<Bar> select(@Param("list") InClauseParams params)

However, this will not work for MySQL, because the MySQL connector does not support setArray() for prepared statements.

A possible workaround for MySQL is to use FIND_IN_SET instead of IN:

@Select("SELECT * FROM foo WHERE FIND_IN_SET(id, #{list}) > 0")
List<Bar> select(@Param("list") InClauseParams params)

And your type handler becomes:

@Override
    public void setNonNullParameter(final PreparedStatement ps, final int i, final InClauseParams parameter, final JdbcType jdbcType) throws SQLException {

        // note: using Guava Joiner! 
        ps.setString( i, Joiner.on( ',' ).join( parameter ) );
    }

Note: I don't know the performance of FIND_IN_SET, so test this if it is important

Psychodynamics

Answer 9

I had done this with postgresql.

    @Update('''
         UPDATE sample_table 
         SET start = null, finish = null
         WHERE id=ANY(#{id});
            ''')
    int resetData(@Param("id") String[] id)

ANY works like the IN.

Code above is using groovy but can be converted into java by replacing the single quotes into double.

Ainslee