I'm inserting multiple rows using a PreparedStatement with a single query:
我使用PreparedStatement和一个查询插入多个行:
String query = "insert into MyTable (a,b,c) values (?,?,?),(?,?,?),(?,?,?),(?,?,?)"; // insert 4 rows in a single query
PreparedStatement stmt = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
// .. here: loop to set all 4 x 3 values in my prepared statement
And I want to get the IDs generated by the MSSQL DB. I'm not sure which flavor of execute
to use.
我想获得MSSQL DB生成的id。我不确定执行的味道是什么。
The execute()
method doesn't return the expected ResultSet
:
execute()方法不返回预期的结果集:
boolean thingy = stmt.execute();
System.out.println("execute returned ", thingy); // false: meaning no result set
ResultSet result = stmt.getResultSet(); // result is null
The executeUpdate()
method returns only one ResultSet
:
executeUpdate()方法只返回一个ResultSet:
int rowCount = stmt.executeUpdate();
System.out.println(rowCount + " rows updated"); // 4 rows updated
ResultSet result = stmt.getGeneratedKeys();
// there's only one result:
result.next(); // true
int ID1 = result.getInt(1); // good
result.next(); // false, no more rows
Is it possible to get the generated IDs with a single INSERT? I do not want to send multiple INSERT queries for performance reasons.
是否可以使用单个插入来获取生成的id ?出于性能原因,我不想发送多个INSERT查询。
DB: MS SQL Server 11 (2012)
URL: jdbc:jtds:sqlserver://...
Driver: net.sourceforge.jtds.jdbc.Driver jtds version 1.3.1
DB: MS SQL Server 11 (2012) URL: jdbc:jtds:sqlserver://…司机:net.sourceforge.jtds.jdbc。司机jtds 1.3.1版本
2 个解决方案
#1
2
For SQL Server 2008 and later, adding an OUTPUT clause to the INSERT statement seems to do the trick:
对于SQL Server 2008和以后的版本,向INSERT语句中添加一个OUTPUT子句似乎可以达到以下目的:
try (Statement st = conn.createStatement()) {
// TEST ENVIRONMENT: in a real application, this would be our permanent table
st.execute("CREATE TABLE #tmp (id INT IDENTITY PRIMARY KEY, txtcol NVARCHAR(50))");
st.execute("INSERT INTO #tmp (txtcol) VALUES (N'existing data')");
st.execute("INSERT INTO #tmp (txtcol) VALUES (N'more existing data')");
}
StringBuilder sb = new StringBuilder();
sb.append("INSERT INTO #tmp (txtcol) ");
sb.append(" OUTPUT INSERTED.id ");
sb.append(" VALUES (?), (?), (?) ");
try (PreparedStatement ps = conn.prepareStatement(sb.toString())) {
ps.setString(1, "foo");
ps.setString(2, "bar");
ps.setString(3, "baz");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1));
}
}
}
which produces
生产
3
4
5
#2
1
At the time of writing this answer, neither jTDS nor mssql-jdbc support returning of multiple generated keys using Statement.RETURN_GENERATED_KEYS
, either from an INSERT statement with a TVC (table value constructor) or by using executeBatch
with a PreparedStatement
. However, the following code works with both drivers (tested with jTDS 1.3.1 and the dev branch of mssql-jdbc at the time of writing):
在编写此答案时,jTDS和mssql-jdbc都不支持使用语句返回生成的多个键。RETURN_GENERATED_KEYS,可以来自带有TVC(表值构造函数)的插入语句,也可以使用带有PreparedStatement的executeBatch。但是,以下代码同时适用于这两个驱动程序(编写时使用jTDS 1.3.1和mssql-jdbc的dev分支进行测试):
try (Statement st = conn.createStatement()) {
// TEST ENVIRONMENT: in a real application, this would be our permanent table
st.execute("CREATE TABLE #tmp (id INT IDENTITY PRIMARY KEY, txtcol NVARCHAR(50))");
st.execute("INSERT INTO #tmp (txtcol) VALUES (N'existing data')");
st.execute("INSERT INTO #tmp (txtcol) VALUES (N'more existing data')");
}
StringBuilder sb = new StringBuilder();
sb.append("SET NOCOUNT ON; ");
sb.append("DECLARE @seq INT, @txt NVARCHAR(50), @id INT; ");
sb.append("DECLARE @newIds TABLE (seq INT, id INT); ");
sb.append("DECLARE @toInsert TABLE (seq INT IDENTITY PRIMARY KEY, txt NVARCHAR(50)); ");
sb.append("");
sb.append("INSERT INTO @toInsert (txt) VALUES (?), (?), (?); ");
sb.append("");
sb.append("DECLARE crsr CURSOR FOR SELECT seq, txt FROM @toInsert ORDER BY seq; ");
sb.append("OPEN crsr; ");
sb.append("FETCH NEXT FROM crsr INTO @seq, @txt; ");
sb.append("WHILE @@FETCH_STATUS = 0 ");
sb.append("BEGIN ");
sb.append(" INSERT INTO #tmp (txtcol) VALUES (@txt); ");
sb.append(" SELECT @id = SCOPE_IDENTITY(); ");
sb.append(" INSERT INTO @newIds (seq, id) VALUES (@seq, @id); ");
sb.append(" FETCH NEXT FROM crsr INTO @seq, @txt; ");
sb.append("END ");
sb.append("SELECT id FROM @newIds ORDER BY seq; ");
try (PreparedStatement ps = conn.prepareStatement(sb.toString())) {
ps.setString(1, "foo");
ps.setString(2, "bar");
ps.setString(3, "baz");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1));
}
}
}
which produces
生产
3
4
5
#1
2
For SQL Server 2008 and later, adding an OUTPUT clause to the INSERT statement seems to do the trick:
对于SQL Server 2008和以后的版本,向INSERT语句中添加一个OUTPUT子句似乎可以达到以下目的:
try (Statement st = conn.createStatement()) {
// TEST ENVIRONMENT: in a real application, this would be our permanent table
st.execute("CREATE TABLE #tmp (id INT IDENTITY PRIMARY KEY, txtcol NVARCHAR(50))");
st.execute("INSERT INTO #tmp (txtcol) VALUES (N'existing data')");
st.execute("INSERT INTO #tmp (txtcol) VALUES (N'more existing data')");
}
StringBuilder sb = new StringBuilder();
sb.append("INSERT INTO #tmp (txtcol) ");
sb.append(" OUTPUT INSERTED.id ");
sb.append(" VALUES (?), (?), (?) ");
try (PreparedStatement ps = conn.prepareStatement(sb.toString())) {
ps.setString(1, "foo");
ps.setString(2, "bar");
ps.setString(3, "baz");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1));
}
}
}
which produces
生产
3
4
5
#2
1
At the time of writing this answer, neither jTDS nor mssql-jdbc support returning of multiple generated keys using Statement.RETURN_GENERATED_KEYS
, either from an INSERT statement with a TVC (table value constructor) or by using executeBatch
with a PreparedStatement
. However, the following code works with both drivers (tested with jTDS 1.3.1 and the dev branch of mssql-jdbc at the time of writing):
在编写此答案时,jTDS和mssql-jdbc都不支持使用语句返回生成的多个键。RETURN_GENERATED_KEYS,可以来自带有TVC(表值构造函数)的插入语句,也可以使用带有PreparedStatement的executeBatch。但是,以下代码同时适用于这两个驱动程序(编写时使用jTDS 1.3.1和mssql-jdbc的dev分支进行测试):
try (Statement st = conn.createStatement()) {
// TEST ENVIRONMENT: in a real application, this would be our permanent table
st.execute("CREATE TABLE #tmp (id INT IDENTITY PRIMARY KEY, txtcol NVARCHAR(50))");
st.execute("INSERT INTO #tmp (txtcol) VALUES (N'existing data')");
st.execute("INSERT INTO #tmp (txtcol) VALUES (N'more existing data')");
}
StringBuilder sb = new StringBuilder();
sb.append("SET NOCOUNT ON; ");
sb.append("DECLARE @seq INT, @txt NVARCHAR(50), @id INT; ");
sb.append("DECLARE @newIds TABLE (seq INT, id INT); ");
sb.append("DECLARE @toInsert TABLE (seq INT IDENTITY PRIMARY KEY, txt NVARCHAR(50)); ");
sb.append("");
sb.append("INSERT INTO @toInsert (txt) VALUES (?), (?), (?); ");
sb.append("");
sb.append("DECLARE crsr CURSOR FOR SELECT seq, txt FROM @toInsert ORDER BY seq; ");
sb.append("OPEN crsr; ");
sb.append("FETCH NEXT FROM crsr INTO @seq, @txt; ");
sb.append("WHILE @@FETCH_STATUS = 0 ");
sb.append("BEGIN ");
sb.append(" INSERT INTO #tmp (txtcol) VALUES (@txt); ");
sb.append(" SELECT @id = SCOPE_IDENTITY(); ");
sb.append(" INSERT INTO @newIds (seq, id) VALUES (@seq, @id); ");
sb.append(" FETCH NEXT FROM crsr INTO @seq, @txt; ");
sb.append("END ");
sb.append("SELECT id FROM @newIds ORDER BY seq; ");
try (PreparedStatement ps = conn.prepareStatement(sb.toString())) {
ps.setString(1, "foo");
ps.setString(2, "bar");
ps.setString(3, "baz");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1));
}
}
}
which produces
生产
3
4
5