CODEWITHSUNDEEP
javaoracle-databasejdbc
Pujan
Pujan

Reputation: 3254

JDBC Multiple Batch Execution Issue

Following code tries to execute 3 Batch inserts. Second batch's second statement is wrong intentionally (INSERTD). When I run this program, then 1st and 3rd batch should be executed and data should be inserted into the database.

But it is not inserting data. Can someone tell why ? And how can I fix so that correct batch should be executed irrespective of other batch. 

public class Test {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        ArrayList<String> finalsql1 = new ArrayList<String>();
        finalsql1.add("INSERT INTO M_TEST VALUES('PUJAN1','WRL')");
        finalsql1.add("INSERT INTO M_TEST VALUES('PUJAN2','WRL')");

        ArrayList<String> finalsql2 = new ArrayList<String>();
        finalsql1.add("INSERT INTO M_TEST VALUES('PUJAN1','WRL')");
        finalsql1.add("INSERTD INTO M_TEST VALUES('PUJAN2','WRL')");

        ArrayList<String> finalsql3 = new ArrayList<String>();
        finalsql1.add("INSERT INTO M_TEST VALUES('PUJAN1','WRL')");
        finalsql1.add("INSERT INTO M_TEST VALUES('PUJAN2','WRL')");

        System.out.println("OP1=" + insertEntity(finalsql1));
        System.out.println("OP2=" + insertEntity(finalsql2));
        System.out.println("OP3=" + insertEntity(finalsql3));

    }

public static boolean insertEntity(ArrayList<String> finalsql) throws SQLException {
        Connection conn = null;
        Statement ps = null;
        boolean result = false;

        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            ps = conn.createStatement();
            for (String sql : finalsql) {
                System.out.println("sql > " + sql);
                ps.addBatch(sql);
            }
            int[] count = ps.executeBatch();

            result = true;
        } catch (Exception e) {
            conn.rollback();
            e.printStackTrace();
            result = false;
        } finally {
            conn.commit();
            ps.close();
            conn.close();
        }
        return result;
    }
}

Output

OP1=true

OP2=false

OP3=true

Upvotes: 0

Views: 163

Answers (2)

user207421
user207421

Reputation: 310893

Regardless of any errors that may exist in your code, you're relying on undefined behaviour: "If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. "

Upvotes: 1

NickJ
NickJ

Reputation: 9559

There is only a single batch of inserts with any data, and this includes the INSERTD hence it is rolling back. Why? Here's why:

ArrayList<String> finalsql2 = new ArrayList<String>();
finalsql1.add("INSERT INTO M_TEST VALUES('PUJAN1','WRL')");
finalsql1.add("INSERTD INTO M_TEST VALUES('PUJAN2','WRL')");

You are still adding entries to finalsql1 here, so finalsql2 and finalsql2 are empty.

finalsql1 has all the entries, including the deliberate mistake, so the whole lot is rolled back.

Upvotes: 1

Related Questions