java - rs.getGeneratedKeys() 在 Derby 中不起作用

Question

我创建的表是这样的:

public final String CREATE_QUESTIONS_TABLE = "CREATE TABLE Questions(QuestionID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,"
        + "SubmittedTime TIMESTAMP NOT NULL,"
        + "Title VARCHAR(50) NOT NULL,"
        + "Text VARCHAR(300) NOT NULL,"
        + "Username VARCHAR(10) NOT NULL,"
        + "Rating INTEGER DEFAULT 0 NOT NULL)";

我的 INSERT 查询如下所示:

public final String INSERT_QUESTION_STMT = "INSERT INTO Questions(SubmittedTime, Title, Text, Username) VALUES(?,?,?,?)";

我插入的部分 servlet 代码如下所示:

// insert question into database
PreparedStatement pstmt = conn.prepareStatement(AppConstants.INSERT_QUESTION_STMT);
pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
pstmt.setString(2, newQuestion.getTitle());
pstmt.setString(3, newQuestion.getText());
pstmt.setString(4, (String)session.getAttribute("username"));
pstmt.executeUpdate();

到目前为止，一切正常，并将问题插入数据库。

我想获取问题的自动生成 ID，所以我尝试使用:

ResultSet rs = pstmt.getGeneratedKeys();
int id = -1;
if(rs.next())
id = rs.getInt(1);

我在 rs.next()

上遇到随机错误

SEVERE: Servlet.service() for servlet [AskQuestion] in context with path [/QnA] threw exception
java.lang.NullPointerException
at QnA.servlets.AskQuestion.doPost(AskQuestion.java:78)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:436)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

知道为什么它不起作用吗？

Answer 1

我找到了答案，我必须告诉语句返回生成的 key 。

PreparedStatement pstmt = conn.prepareStatement(AppConstants.INSERT_QUESTION_STMT, 
       Statement.RETURN_GENERATED_KEYS);

请注意 Statement.RETURN_GENERATED_KEYS 作为函数的第二个参数。