Batch Insert with JTA (EntityManager cannot use getTransaction)

Question

I'm working in a project with spring data and apache camel, we have 2 databases, Sql Server and Oracle with JTA. The problem arises because I need to insert data from a large file (about 10000000 records), so I decided to do using batch insert as:

@PersistenceContext(unitName="persistenceUnitSql")
EntityManager em;

public void insertBatch() {
    em.getTransaction().begin();
    for (int i = 1; i <= 1000000; i++) {
      Point point = new Point(i, i);
      em.persist(point);
      if ((i % 10000) == 0) {
          em.getTransaction().commit();
          em.clear();          
          em.getTransaction().begin();
      }
    }
    em.getTransaction().commit();
}

but this problem happened:

    A JTA EntityManager cannot use getTransaction()

Any help...

Answer 1

It seems much harder than expected to self-control JTA transaction. One workaround I normally use is to have a separate "service" which do one batch of insert, and you setup REQUIRES_NEW transaction propagation policy on that method, so it looks like:

class FooService {

    private PointPersister pointPersister;

    @Transactional(propagation=REQUIRED)
    public void insertBatch() {
        List<Point> points = new ArrayList<Point>(10000);
        for (int i = 1; i <= 1000000; i++) {
            points.add(new Point(i,1));
            if ((i % 10000) == 0) {
                pointPersister.insertPointBatch(points);
            }
        }
    }
}

class PointPersisterImpl implements PointPersister {
    @PersistenceContext
    private EntityManager em;

    @Transactional(propagation=REQUIRES_NEW)    // in a separate txn
    public void insertPointBatch(List<Point> points) {
        // persist points
    }
}

There are also other choices you can make to avoid dealing with the troublesome and error-prone manual transaction handling. Spring Batch is one of the possible solution.