How to use spring-batch to write the list of items returned by univocity into a database

I have a series of steps which involves:

Step1: Read from database and write to csv (first-leg)
Step2: Read from database and write to csv (second-leg)
Step3: Integrate spring-batch with Univocity to compare the CSVs from first-leg and second-leg
Step4: Return a list of matched items after the comparison
Step5: Write the list to a database
Step6: Read from database to csv

I got stuck at Step5. I have no idea how to use spring-batch to write directly from a list to a database.

I have tried using jdbc batchUpdate but I encountered "connection closed" issues.

Here is my RestController class: The first job "job" handles Steps 1-4. The second invocation(readFromPtrxList()) inserts the items in the list into database while the second job "pjob" reads from the database and writes to a csv file:

public void executeManualJob(@PathVariable String report) {
    try {
        JobParameters jobParameters = new JobParametersBuilder()
                .addString("report", report)
                .addLong("time", System.currentTimeMillis())
                .toJobParameters();
        jobLauncher.run(job, jobParameters).getExitStatus().getExitCode();
        ptrxList.readFromPtrxList();
        jobLauncher.run(pjob,jobParameters).getExitStatus().getExitCode();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Here is the error message at the point of executing step5:

Caused by: org.hibernate.TransactionException: Unable to commit against JDBC Connection
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:87) ~[hibernate-core-5.3.12.Final.jar:5.3.12.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:272) ~[hibernate-core-5.3.12.Final.jar:5.3.12.Final]
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:104) ~[hibernate-core-5.3.12.Final.jar:5.3.12.Final]
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:533) ~[spring-orm-5.1.10.RELEASE.jar:5.1.10.RELEASE]
... 74 common frames omitted
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:782) ~[postgresql-42.2.8.jar:42.2.8]
at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:768) ~[postgresql-42.2.8.jar:42.2.8]
at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:361) ~[HikariCP-3.2.0.jar:na]
at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java) ~[HikariCP-3.2.0.jar:na]
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:81) ~[hibernate-core-5.3.12.Final.jar:5.3.12.Final]
... 77 common frames omitted

Solution

I have no idea how to use spring-batch to write directly from a list to a database.

There is a ListItemReader that you can use to read items from a list. To write items to the database, you can use the JdbcBatchItemWriter. So your step 5 can be a chunk-oriented step that uses those reader/writer.

The first job "job" handles Steps 1-4. The second invocation(readFromPtrxList()) inserts the items in the list into database while the second job "pjob" reads from the database and writes to a csv file

Your error seems to be caused by the fact that your are concurrently running two jobs and sharing the same connection. I believe all steps you described can be part of a single job. The list produced by step 4 can be passed to step 5 for writing: if it is relatively small, you can use the execution context to pass it, otherwise you can use an intermediate temporary storage (like a staging table or a file) to share data between these steps.