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

Question

我们只想在 MyBatis 中使用注释；我们确实在努力避免使用 xml。我们尝试使用“IN”子句：

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

MyBatis 似乎无法挑选出整数数组并将其放入结果查询中。它似乎“温和地失败”，我们没有得到任何结果。

看起来我们可以使用 XML 映射来完成此任务，但我们确实希望避免这种情况。是否有正确的注释语法？

Answer 1

我相信答案与 这个问题。您可以通过执行以下操作在注释中使用 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);

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

Answer 2

我相信这是 jdbc 准备好的语句的细微差别，而不是 MyBatis。 此处有一个链接解释了此问题并提供了各种解决方案。不幸的是，这些解决方案都不适合您的应用程序，但是，它仍然是了解准备好的语句与“IN”子句相关的限制的好读物。可以在特定于数据库的方面找到解决方案（可能不是最佳的）。例如，在 postgresql 中，可以使用：

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

“ANY”与“IN”相同，而“::int[]”是将参数类型转换为整数数组。输入到语句中的参数应该类似于：

"{1,2,3,4}"

Answer 3

对这个主题进行了一些研究。

1. mybatis 的官方解决方案之一是将动态 sql 放入 @Select("") 中。然而，在java注解中写xml是相当不优雅的。考虑这个 @Select("")
2. @SelectProvider 工作正常。但读起来有点复杂。
3. ReadyStatement 不允许您设置整数列表。 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 索引。

查看mybatis的动态sql机制，它是通过SqlNode.apply(DynamicContext)实现的。但是，没有 注释的 @Select 将不会通过 DynamicContext 传递参数，

另请参阅

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

所以，

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

我的项目采用解决方案 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=\"$1\" item=\"__item\" separator=\",\" >#{__item}</foreach>)");
        }
        script = "<script>" + script + "</script>";
        return super.createSqlSource(configuration, script, parameterType);
    }
}

以及用法：

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

Answer 4

我在代码中做了一个小技巧。

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

我在 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;

它现在可以工作了:)
我希望这会对某人有所帮助。

叶夫根尼

Answer 5

其他选项可以是

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

Answer 6

在我的项目中，我们已经在使用 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));
    }
}

Answer 7

在 Oracle 中，我使用 的变体Tom Kyte 的分词器 用于处理未知的列表大小（考虑到 Oracle 对 IN 子句的 1k 限制以及执行多个 IN 来绕过它的加剧）。这是针对 varchar2 的，但它可以针对数字进行定制（或者您可以仅依赖 Oracle 知道 '1' = 1 /shudder）。

假设您传递或执行 myBatis 咒语以字符串形式获取 ids ，并使用它：

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

代码：

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;

Answer 8

您可以使用自定义类型处理程序来执行此操作。例如：

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
}

然后您可以像这样使用它们

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

但是，这对 MySQL 不起作用，因为 MySQL 连接器不支持setArray() 用于准备好的语句。

MySQL 的一个可能的解决方法是使用 FIND_IN_SET 而不是 IN：

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

并且您的类型处理程序变为：

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

注意：我不知道 FIND_IN_SET 的性能>，所以测试一下它是否重要

Answer 9

我是用 postgresql 完成的。

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

ANY 的工作方式与 IN 类似。

上面的代码使用的是groovy，但可以通过将单引号替换为双引号来转换为java。