我正在尝试将新记录插入MS SQL数据库,并且遇到了从未见过的异常。当我调用executeUpdate
时,抛出以下异常:com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
这是产生错误的Java代码:
// addComment method adds a new comment for a given requestId
public CommentBean addComment(CommentBean comment) {
PreparedStatement stmt = null;
INative nat = null;
Connection conn = null;
try {
nat = dbConn.retrieveNative();
conn = (Connection)nat.getNative("java.sql.Connection");
stmt = conn.prepareStatement(ADD_COMMENT);
stmt.setInt(1, comment.getRequestId());
stmt.setString(2, comment.getComment());
stmt.setString(3, new SimpleDateFormat("MM/dd/yyyy").format(comment.getDateCreated()));
stmt.setString(4, comment.getCreatedBy());
comment.setCommentId(stmt.executeUpdate()); // exception
} catch(Exception ex) {
System.err.println("ProjectRegistration::SQLDAO - addComment");
ex.printStackTrace();
} finally {
try {
if (stmt != null) stmt.close();
} catch (Exception e) {}
}
return comment;
}// end addComment
其中ADD_COMMENT定义为字符串:
private static final String ADD_COMMENT = "INSERT INTO RequestComments OUTPUT INSERTED.commentId VALUES(?,?,?,?)";
为了更全面,该表定义为:
CREATE TABLE RequestComments (
commentId int NOT NULL PRIMARY KEY IDENTITY(1,1),
requestId int FOREIGN KEY REFERENCES Requests(requestId),
comment varchar(400),
dateCreated date,
createdBy varchar(12)
);
我认为我在这里没有做任何非常复杂的事情,但是我无法想到为什么会遇到这种异常(exception)情况。我在同一个类中有一个方法,该方法执行完全相同的插入类型(字面相同的查询,但表名称和值的数量不同),并且没有问题。是否有人对如何解决此问题有任何想法?
最佳答案
该指令stmt.executeUpdate()
不返回commentId,而是返回ResultSet,您可以从中获取commentId。像这样
ResultSet rs = stmt.executeQuery(); // Not update, you're returning a ResultSet.
if (rs.next()) {
comment.setCommentId(rs.getInt(1));
}