CODEWITHSUNDEEP
javapostgresql
MaikB
MaikB

Reputation: 115

Make Batch Inserts without auto increment in Java

So I recently startet reading about sql best practices and found out about using Batch Statements for inserts and updates. So wanted to update some code using my new learned knowledge. For example I have a loop like this:

conn.setAutoCommit(false)

try {
    stmt = conn.prepareStatement("insert into TEST (ID,STR_TEST1) values (?,?)")

    for (String element : someList) {
        stmt.setInt(1, ???)
        stmt.setString(2, element)
    }
    stmt.executeBatch();
    conn.commit()
} finally {
    stmt.close()
}

Now unfortunately my tables don't auto increment ids and I can't change that for some reasons, what means, that I have to always select the next "free" id in my table. Problem with this is, it doesn't work, I guess because auto commit is set to false. Any ideas how to combat my problems? I thought about making sub-selects in my insert query, but I fear that this will weaken the performance.

Upvotes: 0

Views: 485

Answers (1)

user330315
user330315

Reputation:

A sequence is the safest and fastest way to generate unique numbers (PostgreSQL's "auto increment" types serial or identity use that in the background).

To use it, create one first:

create sequence test_id_seq;

If you already have rows in the target table, adjust the sequence to return values greater than the current maximum:

select setval('test_id_seq', (select max(id) from test));

Then inside your INSERT statement use nextval('test_id_seq') instead of supplying a value:

try {
    stmt = conn.prepareStatement( 
      "insert into TEST (ID,STR_TEST1) values (setval('test_id_seq'),?)")

    for (String element : someList) {
        stmt.setString(1, element)
    }
    stmt.executeBatch();
    conn.commit()
} finally {
    stmt.close()
}

Upvotes: 1

Related Questions