USChH2HN

1import java.sql.BatchUpdateException;
2import java.sql.Connection;
3import java.sql.DatabaseMetaData;
4import java.sql.DriverManager;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9import java.util.Arrays;
10
11public class PostgresBatchUpdate {
12
13    public static void main(String[] args) throws ClassNotFoundException, SQLException {
14        Class.forName("org.postgresql.Driver");
15
16        String jdbcurl = "jdbc:postgresql:test";
17        String user = "postgres";
18        String passwd = "geheim";
19
20        Connection con = DriverManager.getConnection(jdbcurl, user, passwd);
21        con.setAutoCommit(true);
22
23        DatabaseMetaData dmd = con.getMetaData();
24        System.out.println("DB: " + dmd.getDatabaseProductName() + " " + dmd.getDatabaseProductVersion());
25        System.out.println("Driver: " + dmd.getDriverName() + " " + dmd.getDriverVersion());
26
27        // create table when absent
28        Statement s = con.createStatement();
29        s.execute("create table if not exists TEST (ID varchar(512) not null primary key, DATA varchar(512))");
30        s.close();
31
32        // clean up
33        PreparedStatement st = con.prepareStatement("DELETE FROM test WHERE ID in (?, ?)");
34        st.setString(1, "key-1");
35        st.setString(2, "key-2");
36        st.executeUpdate();
37        st.close();
38
39        // insert "key-2"
40        st = con.prepareStatement("INSERT INTO test (id) VALUES (?)");
41        st.setString(1, "key-2");
42        st.executeUpdate();
43        st.close();
44
45        // update as batch
46        PreparedStatement batchSt = con.prepareStatement("INSERT INTO test (id) VALUES (?)");
47        batchSt.setString(1, "key-1");
48        batchSt.addBatch();
49
50        batchSt.setString(1, "key-2");
51        batchSt.addBatch();
52
53        boolean wasKey1Inserted = false;
54        try {
55            int[] batchResult = batchSt.executeBatch();
56            batchSt.close();
57            System.out.println("update unexpectedly succeeded: " + Arrays.toString(batchResult));
58        }
59        catch (BatchUpdateException ex) {
60            int[] batchResult = ex.getUpdateCounts();
61            System.out.println("update failed as expected: " + Arrays.toString(batchResult));
62            wasKey1Inserted = batchResult.length >= 1 && batchResult[0] == 1;
63            if (wasKey1Inserted) {
64                System.out.println("batch result implies that first operation (insert of key-1) suceeded");
65            }
66        }
67        con.close();
68
69        if (wasKey1Inserted) {
70            con = DriverManager.getConnection(jdbcurl, user, passwd);
71            con.setAutoCommit(true);
72            boolean foundKey1 = false;
73
74            PreparedStatement q = con.prepareStatement("SELECT ID FROM test");
75            ResultSet r = q.executeQuery();
76            while (r.next()) {
77                System.out.println("Found: " + r.getString(1));
78                foundKey1 |= ("key-1".equals(r.getString(1)));
79            }
80
81            if (!foundKey1) {
82                System.out.println("Error: 'key-1' not found");
83            }
84        }
85    }
86}