如何使用带有iBatis（myBatis）的注释进行IN查询？

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

我们只想使用MyBatis的注释;我们真的想避免使用xml。我们正在尝试使用“IN”子句：

@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.

MyBatis似乎无法选择整数数组并将其放入生成的查询中。似乎“软弱地失败”，我们得不到任何结果。

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?

看起来我们可以使用XML映射来实现这一点，但我们真的想避免这种情况。是否有正确的注释语法？

9 个解决方案

#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:

我相信这是jdbc准备好的陈述的细微差别，而不是MyBatis。这里有一个链接可以解释这个问题并提供各种解决方案。遗憾的是，这些解决方案都不适用于您的应用程序，但是，对于理解“IN”子句中预准备语句的限制仍然是一个很好的解读。可以在特定于DB的方面找到解决方案（可能是次优的）。例如，在postgresql中，可以使用：

"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:

“ANY”与“IN”相同，“:: int []”是将参数类型转换为int数组。提供给语句的参数应该类似于：

"{1,2,3,4}"

#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:

我相信答案与这个问题的答案是一致的。您可以通过执行以下操作在注释中使用myBatis Dynamic SQL：

@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.

请注意，您可以在各种XML脚本标记中使用的变量遵循与常规查询相同的命名约定，因此如果您想使用“param1”，“param2”等名称之外的名称来引用您的方法参数...需要使用@Param注释为每个参数添加前缀。

#3

Had some research on this topic.

有一些关于这个主题的研究。

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>")
mybatis的官方解决方案之一是将动态sql放在@Select（“
@SelectProvider works fine. But it's a little complicated to read.
@SelectProvider工作正常。但阅读起来有点复杂。
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.
PreparedStatement不允许您设置整数列表。 pstm.setString（index，“1,2,3,4”）会让你的SQL像这样选择名称，其中id为（'1,2,3,4'）。 Mysql会将字符'1,2,3,4'转换为数字1。
FIND_IN_SET don't works with mysql index.
FIND_IN_SET不适用于mysql索引。

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

查看mybatis动态sql机制，它已由SqlNode.apply（DynamicContext）实现。但是，没有

#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 :

我在SqlMapper中使用了这个MyHandler：

    @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

叶夫根尼·

#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);
        }
    }

#6

I'm afraid Evgeny's solution only seems to work because there is a small bug in the code sample:

我担心Evgeny的解决方案似乎只能起作用，因为代码示例中有一个小错误：

  inString = "," + element;

Which means that inString always only contains a single, last number (instead of a list of concatenated numbers).

这意味着inString始终只包含一个最后一个数字（而不是一个连接数字列表）。

This should in fact be

事实上这应该是

  inString += "," + element;

Alas, if this error is corrected the database starts reporting "incorrect number" exceptions because mybatis sets "1,2,3" as a string parameter and the database simply tries to convert this string to a number : /

唉，如果纠正了这个错误，数据库会开始报告“错误号码”异常，因为mybatis将“1,2,3”设置为字符串参数，数据库只是尝试将此字符串转换为数字：/

On the other hand, the @SelectProvider annotation, as described by Mohit, works fine. One must only be aware, that it creates a new statement each time we run the query with different parameters inside the IN-clause rather than reuse the existing PreparedStatement (as the parameters inside the IN-Clause are being hardcoded inside the SQL instead of being set as prepared statement's parameters). This can sometimes lead to memory leaks in the Database (as the DB needs to store more and more prepared statements and it will potentially not reuse existing execution plans).

另一方面，Mohit所描述的@SelectProvider注释工作正常。我们必须要注意的是，每次我们在IN子句中使用不同的参数运行查询时它会创建一个新的语句，而不是重用现有的PreparedStatement（因为IN-Clause中的参数在SQL内部被硬编码而不是设置为准备语句的参数）。这有时会导致数据库中的内存泄漏（因为数据库需要存储越来越多的预处理语句，并且可能无法重用现有的执行计划）。

One can try to mix both @SelectProvider and custom typeHandler. This way one can use the @SelectProvider to create a query with as many placeholders inside "IN (...)" as necessary and then replace them all in the custom TypeHandler. It gets a bit tricky, though.

可以尝试混合@SelectProvider和自定义typeHandler。这样，可以使用@SelectProvider根据需要在“IN（...）”内创建具有尽可能多的占位符的查询，然后在自定义TypeHandler中替换它们。不过，它有点棘手。

#7

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

在我的项目中，我们已经在使用Google Guava，因此快捷方便。

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));
    }
}

#8

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).

在Oracle中，我使用Tom Kyte的tokenizer的变体来处理未知的列表大小（给定Oracle对IN子句的1k限制以及多个IN的恶化以绕过它）。这是针对varchar2的，但它可以针对数字进行定制（或者您可以依靠Oracle知道'1'= 1 /颤抖）。

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

假设您传递或执行myBatis咒语以将ID作为String获取，则使用它：

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;

#9

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
}

在MyBatis配置中注册以下类型处理程序（或在注释中指定）：

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.

但是，这对MySQL不起作用，因为MySQL连接器不支持预处理语句的setArray（）。

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

MySQL的一个可能的解决方法是使用FIND_IN_SET而不是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

注意：我不知道FIND_IN_SET的性能，所以如果它很重要，请测试它

#1

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