mybatis foreach but submiting individual inserts

Question

The following mybatis mapping works on all our supported databases except for one. This is because that database does not allow a bulk insert method (Intersystems Cache). Because of that I'd like to submit individual insert statements instead rather than one. How could I structure this mybatis statement so that it still reads from my java.util.List but it does multiple inserts?

  <insert id="bulkInsert" parameterType="java.util.List" >
    <foreach collection="list" item="resource" index="index">
      INSERT INTO ${prefix}ACT_APP_DEPLOYMENT_RESOURCE(ID_, NAME_, RESOURCE_BYTES_, DEPLOYMENT_ID_) VALUES
      (#{resource.id, jdbcType=VARCHAR},
      #{resource.name, jdbcType=VARCHAR},
      #{resource.bytes, jdbcType=${blobType}},
      #{resource.deploymentId, jdbcType=VARCHAR})
    </foreach>
  </insert>

I don't think foreach is ment for this. You can build **insert - foreach** structure but not **foreach - insert**. Will be glad to see a valid response to your question. — Evgeni Enchev, Dec 11 '18 at 06:54

Roman-Stop RU aggression in UA · Accepted Answer · 2019-01-04T09:41:27.477

If you are using java version 8+ you can use default method in mapper like this:

interface MyMapper {
    void insertResource(@Param("resource") MyResource resource);

    default void bulkInsert(List<MyResource> resources) {
         for(MyResource resource:resources) {
             insertResource(resource);
         }
    }
}

And modify the mapper xml:

<insert id="insertResource">
    INSERT INTO ${prefix}ACT_APP_DEPLOYMENT_RESOURCE(ID_, NAME_, RESOURCE_BYTES_, DEPLOYMENT_ID_) VALUES
    (#{resource.id, jdbcType=VARCHAR},
    #{resource.name, jdbcType=VARCHAR},
    #{resource.bytes, jdbcType=${blobType}},
    #{resource.deploymentId, jdbcType=VARCHAR})
</insert>

This looks promising, I'll verify here soon – RailRhoad Dec 11 '18 at 18:52 — RailRhoad, Dec 11 '18 at 18:52

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

First of all, check https://stackoverflow.com/a/40608353/5053214.

Additionally according to mybatis doc:

The one parameter that might be new to you is ExecutorType. This enumeration defines 3 values:

ExecutorType.SIMPLE: This type of executor does nothing special. It creates a new PreparedStatement for each execution of a statement.

ExecutorType.REUSE: This type of executor will reuse PreparedStatements.

ExecutorType.BATCH: This executor will batch all update statements and demarcate them as necessary if SELECTs are executed between them, to ensure an easy-to-understand behavior.

Default is ExecutorType.SIMPLE, you need to change it to ExecutorType.BATCH.

mybatis foreach but submiting individual inserts

2 Answers2