Java 如何使用带有 iBatis (myBatis) 的注释进行 IN 查询？

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

提问by dirtyvagabond

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 映射来实现这一点，但我们真的很想避免这种情况。是否有正确的注释语法？

采纳答案by user199341

I believe this is a nuance of jdbc's prepared statements and not MyBatis. There is a link herethat 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[]" 将参数类型转换为整数数组。输入到语句中的参数应该类似于：

"{1,2,3,4}"

回答by pevgen

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

叶夫根尼

回答by Mohit Verma

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

回答by LordOfThePigs

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 动态 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.

该<script>元素为注释启用动态 SQL 解析和执行。它必须是查询字符串的第一个内容。它前面不能有任何东西，即使是空白也不行。

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 注释为每个参数添加前缀。

回答by yegong

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

1. mybatis 的官方解决方案之一是将您的动态 sql 放入@Select("<script>...</script>"). 但是用java注解写xml是很不雅观的。想想这个@Select("<script>select name from sometable where id in <foreach collection=\"items\" item=\"item\" seperator=\",\" open=\"(\" close=\")\">${item}</script>")
2. @SelectProvider工作正常。但是读起来有点复杂。
3. PreparedStatement 不允许您设置整数列表。pstm.setString(index, "1,2,3,4")会让你的 SQL 像这样select name from sometable where id in ('1,2,3,4')。Mysql 会将字符转换'1,2,3,4'为数字1。
4. 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)。但是，没有<script></script>注释的@Select 不会通过DynamicContext

see also

也可以看看

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

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

• 解决方案1：使用@SelectProvider
• 解决方案 2：扩展 LanguageDriver，它将始终将 sql 编译为DynamicSqlSource. 但是，您仍然必须\"到处写。
• 解决方案三：扩展LanguageDriver，可以将自己的语法转换为mybatis one。
• 解决方案4：编写自己的LanguageDriver，它使用一些模板渲染器编译SQL，就像mybatis-velocity 项目所做的那样。这样，您甚至可以集成 groovy。

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

我的项目采用解决方案 3，这是代码：

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=\"\" 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);

回答by user2665773

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

回答by Llanfar

In Oracle, I use a variant of Tom Kyte's tokenizerto 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 标记器的变体来处理未知的列表大小（考虑到 Oracle 对 IN 子句的 1k 限制以及执行多个 IN 来绕过它的恶化）。这是针对 varchar2 的，但它可以针对数字进行定制（或者您可以只依靠 Oracle 知道 '1' = 1 /shudder）。

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

假设您通过或执行 myBatis 咒语以获取ids字符串，以使用它：

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;

回答by sgdesmet

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

在您的 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 notwork 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_SETinstead 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，所以如果它很重要，请测试它