How do I do JPA Batches (Insert) in EclipseLink

Question

I want do use JPA Batches in my code.

I am using this Dependencies:

        <dependency>
            <groupId>org.eclipse.persistence</groupId>
            <artifactId>eclipselink</artifactId>
            <version>2.5.1</version>
        </dependency>
        <dependency>
            <groupId>org.eclipse.persistence</groupId>
            <artifactId>javax.persistence</artifactId>
            <version>2.1.0-RC1</version>
        </dependency>

Do I have to add sth in my persistence.xml file? If yes, what do I have to add?

This is how I wrte the Jobs in my DB at the moment:

    public void writeAllJobs(List<Jobs> jobs) {

        final EntityManager em = factory.createEntityManager();

        em.getTransaction().begin();

        int counter= 0;

        for (final Job j : jobs) {

            if (counter++ >= 100) {
                em.getTransaction().commit();
                em.getTransaction().begin();
                counter = 0;
            }

            final Job job = new Job();
            job.setJobkey(j.getJobkey());
            job.setDescription(b.getDescription());
            em.persist(job);
        }
        em.getTransaction().commit();

        em.close();
    }

After 100 Joby my program wirtes the values in the DB so that is not every time a DB access.

------------------------------ EDIT -----------------------------------

    public void writeAllJobs(List<Jobs> jobs) throws SQLException {

        final EntityManager em = factory.createEntityManager();
        final java.sql.Connection connection = em.unwrap(java.sql.Connection.class);

        final String compiledQuery = "INSERT INTO JOB(JOBKEY ,DESCRIPTION)" + " VALUES" + "(?, ?)";
        final PreparedStatement preparedStatement = connection.prepareStatement(compiledQuery);

        for (final Job j : jobs) {
            preparedStatement.setInt(1, j.getJobkey());
            preparedStatement.setString(2, j.getDescription());

            preparedStatement.addBatch();
        }

        preparedStatement.executeBatch();

        connection.close();
    }

Then I get the: java.lang.NullPointerException

------------------------------ EDIT 2 -----------------------------------

I got the NPE in the line
final java.sql.Connection connection = em.unwrap(java.sql.Connection.class);

But how do I solve this problem?

First, turn on EclipseLink's SQL logging to see what it issues and when (https://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging ). EclipseLink is perfectly capable of writing out multiple inserts in a single batch insert statement with JPA objects for you with some configuration, depending on driver support, and your entities themselves. It won't work well for instance if you have sequencing using identity, where it has to get the value after each individual statement (DB/driver dependent) or with some sequencing options where the same connection must be used to get more values — Chris, Jan 31 '22 at 20:10
See https://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/p_jdbc_batchwriting.htm for batch writing persistence properties, and try 'jdbc' or maybe 'oracle-jdbc' if using Oracle with older drivers (https://stackoverflow.com/a/15140521/496099 ). — Chris, Jan 31 '22 at 20:16
If you want to go with your own batch inserts statement approach (I don't know why you would), you didn't show where the NPE is coming from, but you have to manage the transaction and everything else or there is no connection to unwrap. Begin/commit transaction, then maybe unwrap the connection to prepare/excute your statement - but this is what EclipseLink/JPA does for you under the covers. — Chris, Jan 31 '22 at 20:19
I added the extension to my xml file but still got the NullPointerException. — Phil, Feb 01 '22 at 09:21
@Chris I just want to add the list from the method writeAllJobs to my DB via Batch and for this I use this Method writeAllJobs. — Phil, Feb 01 '22 at 09:22
Okay the NPE comes in this line final PreparedStatement preparedStatement = connection.prepareStatement(compiledQuery); — Phil, Feb 01 '22 at 09:38
Your comments are over the map. No xml change are going to affect your NPE, I've pointed you at settings to make your original for loop use batch insert statements. Why would you go with JDBC prepared statements when you are using JPA? Step back - what do you really want to accomplish now, and what have you tried.. and then what are the results you see? If you want go for a prepared statement approach, what docs are you referencing that shows you this was how to do that - https://wiki.eclipse.org/EclipseLink/Examples/JPA/EMAPI#Getting_a_JDBC_Connection_from_an_EntityManager — Chris, Feb 01 '22 at 14:54

score 1 · Accepted Answer · answered Jan 31 '22 at 08:46

1

You can unwrap the EntityManager to a java.sql.Connection: java.sql.Connection connection = em.unwrap(java.sql.Connection.class);

Then you can use PreparedStatements as in this answer: Efficient way to do batch INSERTS with JDBC

answered Jan 31 '22 at 08:46

frank

1,007
1
6
13

How do I implement this ? Edit is in the question. What did I do wrong? – Phil Jan 31 '22 at 09:48
Hard to say without the stacktrace. But I recognized a missing space between VALUES and (?,?). Maybe that causes the Exception, but I'm not sure. Try ```final String compiledQuery = "INSERT INTO JOB(JOBKEY ,DESCRIPTION) VALUES (?, ?)" ```; – frank Jan 31 '22 at 16:19
I tryed it but got the same error. – Phil Feb 01 '22 at 09:23

How do I do JPA Batches (Insert) in EclipseLink

1 Answers1