JDBC driver doesn't support batch update with retrieval of identity column. Why?

Question

My problem is I want to do JDBC batch insert and retrieve identity columns values. MS SQL driver does not support this functionality. Could anybody guide me on this, how to tackle this problem?

Answer 1

As mentioned in the earlier question here, .getGeneratedKeys simply does not work after .executeBatch for SQL Server. I just confirmed that this is still the case using the latest versions of

• SQL Server JDBC Driver (4.1 Preview), and
• jTDS (1.3.1)

So, it appears that you will simply have to execute the inserts individually, without a Batch. That is, instead of code like this

String[] stringsToInsert = new String[] { "foo", "bar", "baz" };
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO junk (textcol) VALUES (?)", 
        PreparedStatement.RETURN_GENERATED_KEYS)) {
    for (String s : stringsToInsert) {
        ps.setString(1, s);
        ps.addBatch();
    }
    ps.executeBatch();
    try (ResultSet rs = ps.getGeneratedKeys()) {
        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    }
}

you'll need to use code like this

String[] stringsToInsert = new String[] { "foo", "bar", "baz" };
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO junk (textcol) VALUES (?)", 
        PreparedStatement.RETURN_GENERATED_KEYS)) {
    for (String s : stringsToInsert) {
        ps.setString(1, s);
        if (ps.executeUpdate() > 0) {
            try (ResultSet rs = ps.getGeneratedKeys()) {
                rs.next();
                System.out.println(rs.getInt(1));
            }
        }
    }
}

Note that you can still use .setAutoCommit(false) and perform the inserts in a transaction, just not in a batch.

As to why that operation is not supported, a jTDS feature request here was filed over nine (9) years ago and one of the responses was

I'll have to take a look how such feature could be implemented in jTDS before deciding whether it's worth the effort.

Since neither jTDS nor the SQL Server JDBC driver has implemented it (at least not yet; it is on the radar for the Microsoft JDBC driver), perhaps there's just not enough demand for that feature.

Addendum

As a workaround, I thought this might work

String[] stringsToInsert = new String[] { "foo", "bar", "baz" };
try (Statement s = conn.createStatement()) {
    s.executeUpdate(
            "CREATE TABLE #StuffToInsert (" +
                "id INT IDENTITY(1,1) PRIMARY KEY, " +
                "textcol NVARCHAR(100)" +
            ")");
}
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO #StuffToInsert (textcol) VALUES (?)")) {
    for (String s : stringsToInsert) {
        ps.setString(1, s);
        ps.addBatch();
    }
    ps.executeBatch();
}
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO junk (textcol) SELECT textcol FROM #StuffToInsert", 
        Statement.RETURN_GENERATED_KEYS)) {
    ps.executeUpdate();
    try (ResultSet rs = ps.getGeneratedKeys()) {
        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    }
}

but unfortunately .getGeneratedKeys only returns a single generated key for the last row that gets inserted.

If sending a very large number of individual (un-batched) inserts over the network connection is going to be a problem, then this workaround might help:

String[] stringsToInsert = new String[] { "foo", "bar", "baz" };
try (Statement s = conn.createStatement()) {
    s.executeUpdate(
            "CREATE TABLE #StuffToInsert (" +
                "id INT IDENTITY(1,1) PRIMARY KEY, " +
                "textcol NVARCHAR(100)" +
            ")");
}
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO #StuffToInsert (textcol) VALUES (?)")) {
    for (String s : stringsToInsert) {
        ps.setString(1, s);
        ps.addBatch();
    }
    ps.executeBatch();
}
try (PreparedStatement ps = conn.prepareStatement(
        "SET NOCOUNT ON; " +
        "DECLARE @GeneratedKeys TABLE(id INT IDENTITY(1,1) PRIMARY KEY, newkey INT); " +
        "DECLARE @text NVARCHAR(100); " +
        "DECLARE crsr CURSOR FOR " +
        "   SELECT textcol FROM #StuffToInsert ORDER BY id; " +
        "OPEN crsr; " +
        "FETCH NEXT FROM crsr INTO @text; " +
        "WHILE @@FETCH_STATUS = 0 " +
        "BEGIN " +
        "   INSERT INTO junk (textcol) VALUES (@text); " +
        "   INSERT INTO @GeneratedKeys (newkey) SELECT @@IDENTITY; " +
        "   FETCH NEXT FROM crsr INTO @text; " +
        "END " +
        "CLOSE crsr; " +
        "DEALLOCATE crsr; " +
        "SELECT newkey FROM @GeneratedKeys ORDER BY id; ")) {
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    }
}

but this approach does not respect the AutoCommit setting in the Java code, so a rollback is not possible.