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

user_109474 0
Viewed 44438

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?

mybatis ibatis annotations java
9 Answers

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

user_5462550

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

user_14581120

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.

user_103081110
Related