As mentioned in the previous question here , .getGeneratedKeys just does not work after .executeBatch for SQL Server. I just confirmed that this is still the case using the latest versions
- SQL Server JDBC Driver (4.1 Preview) and
- jTDS (1.3.1)
So, it looks like you just need to do the inserts separately, without the package. 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 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 inserts in a transaction, not in a batch.
As for why , this operation is not supported, the jTDS function request was submitted here more than nine (9) years ago, and one of the answers was
I need to see how such a function can be implemented in jTDS before deciding whether to do it.
Since neither jTDS nor the SQL Server JDBC driver implemented it (at least not yet, the Microsoft JDBC driver is on the radar for Microsoft), this may not be enough.
Adding
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 returns only one generated key for the last row inserted.
If sending a very large number of separate (unpaired) inserts over a network connection is a problem, then this workaround can 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 take into account the AutoCommit parameter in Java code, so rollback is not possible.