CODEWITHSUNDEEP
oracle-databasebatch-filemybatis
climmunk
climmunk

Reputation: 1201

batch processing inserts to multiple tables in mybatis

When I insert data through a DAO which references a mybatis mapper, multiple tables are affected.

public void insertStuff(Collection<Stuff> data) {
   for (Stuff item : data) {
      mapper.insertT1(item.getT1Stuff());
      mapper.insertT2(item.getT2Stuff());
      Collection<MainStuff> mainData = item.getMainStuff();
      for (MainStuff mainItem : mainData) {
         mapper.insertMainData(mainItem);
      }
   }
}

I'm using mybatis' BATCH executor type, but I'm quickly reaching Oracle's MAX_CURSOR limit because a new PreparedStatement (and a new Connection) is created for each of the three mapper statements on each iteration through the main loop. I can avoid this by iterating multiple times through the loop:

public void insertStuff(Collection<Stuff> data) {
   for (Stuff item : data) {
      mapper.insertT1(item.getT1Stuff());
   }
   for (Stuff item : data) {
      mapper.insertT2(item.getT2Stuff());
   }
   for (Stuff item : data) {
      Collection<MainStuff> mainData = item.getMainStuff();
      for (MainStuff mainItem : mainData) {
         mapper.insertMainData(mainItem);
      }
   }
}

However, the latter code is less readable, costs a little bit performance-wise, and breaks modularity.

Is there a better way to do this? Do I need to use the SqlSession directly and flush statements after a certain number are queued?

Upvotes: 2

Views: 2629

Answers (1)

partlov
partlov

Reputation: 14277

If you want to use batches you should use second way. In first code you actually don't have any batches. Real batch have N same statements. If you executed 3 different queries and encapsulated them into batch, you jdbc driver will divide them in 3 batches with one query. In second code there will be three batches which is the fastest if you have a lot of data.

Upvotes: 3

Related Questions