CODEWITHSUNDEEP
javasqldynamicforeachmybatis
ffcc
ffcc

Reputation: 70

How to use <foreach> statement in @SelectProvider class with MyBatis3

I am trying to use @SelectProvider to create dynamic SQL by including the use of the tag foreach for a IN clause. I know how to use it using the @Select annotation into my mapper.

The problem is that when I translate the SQL into the SQLProvider it seem that the use it of the tag directly is not processed for the proxy that invoke SQLProvider method to get the SQL.

Here is the code example by using the @Select:

// imports...

public interface MyMapper {
    @Select({"<script>",
        "SELECT col_1, col_2", 
        "FROM table_1",
        "WHERE col_3 IN",
            "<foreach item='item' index='index' collection='items'",
                "open='(' separator=',' close=')'>",
                "#{item}",
            "</foreach>",
        "</script>"})
    HashMap<String, Object> select(@Param("items") String[] items);
}

The code above actually works, but when I try to use the @SelectProvider it doesn't works.

This is the code when I use @SelectProvider:

// imports...

public interface MyMapper {
    @SelectProvider(type = MySQLProvider.class, method = "select")
    HashMap<String, Object> select(@Param("items") String[] items);
}

public class MySQLProvider {
    public String select() {
        SQL sql = new SQL();
        sql.SELECT("col_1");
        sql.SELECT("col_2");
        sql.FROM("table_1");
        sql.WHERE("col_3 IN"
            + "<foreach item='item' index='index' collection='items'"
                + "open='(' separator=',' close=')'>"
                + "#{item}"
            + "</foreach>");
        return "<script>" + sql.toString() + "</script>";
    }
}

When I use the MyMapper it process the SQL well but ignore the script and foreach statements.

Could anyone provide a concrete solution including the code?

Upvotes: 0

Views: 2097

Answers (1)

Charles Bergeron
Charles Bergeron

Reputation: 87

As of MyBatis 3.5.1, <script> blocks defined in SqlProvider methods can now be processed. Your posted code should work if you upgrade to this version.

See the first bullet point under enhancements on the release notes here.

Upvotes: 3

Related Questions