java - 在两个链接表(通过 fk 链接)的第二个表中创建行不起作用

Question

我正在使用 Eclipse Juno、GWT、java 和 MySQL。

我正在将行写入通过 fk 链接的两个表。写入第一个表工作正常。然后，我将这次写入的 key (使用 Statement.RETURN_GENERATED_KEYS)返回到下一次写入。执行此写入时，我收到错误“java.sql.SQLException:未为参数 1 指定值”。我已经显示了传递给每个参数的值，并且有值。

两个表中的第一列是自动增量 pk。第二个表中的第二列是 fk。

控制台是(显示的前五行是传递给参数的值，第一行是 fk):

cd_id = 6
section = Cubs
pack = Explorer
start date = 2013-06-28
end date = 2013-06-28
SQLException createYouthMember 1.
java.sql.SQLException: No value specified for parameter 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2611)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2586)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2432)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
at org.AwardTracker.server.MySQLConnection.createYouthMember(MySQLConnection.java:342)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:362)
at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:324)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)

开发模式是:

13:16:16.910 [WARN] [org.AwardTracker.AwardTracker] Something other than an int was returned from JSNI method '@com.google.gwt.dom.client.DOMImplIE9::getBoundingClientRectTop(Lcom/google/gwt/dom/client/Element;)': Rounding double (216.00999450683594) to int for int

相关的服务器端代码是:

public YthMmbrSectDtls createYouthMember(String youthMemberId,
        String surname, String firstname, Date dob,
        String password, Date archived, String scout_no, String sectionDetailsId,
        String sectionCubDetailsId, String section, String pack, Date startDate, Date endDate) {

    YthMmbrSectDtls ythMmbrSectDtls = null; // necessary unless you do something in the exception handler
    ResultSet result = null;
    PreparedStatement ps = null;
    PreparedStatement ps2 = null;
    Integer cd_id = 0;

    String queryCubDetailsTable = 
             "INSERT INTO at_cub_details "
             + "(cd_surname, cd_first_name, cd_dob, cd_archived, cd_scout_no) "
             + "VALUES (?, ?, ?, ?, ?)";

    String querySectionDetailsTable = 
             "INSERT INTO at_section_details "
             + "(cd_id, sd_section, sd_pack, sd_start_date, sd_end_date) "
             + "VALUES (?, ?, ?, ?, ?)";

    try {
      ps = conn.prepareStatement(queryCubDetailsTable, Statement.RETURN_GENERATED_KEYS);
      ps.setString(1, surname);
      ps.setString(2, firstname);
      ps.setDate(3, (java.sql.Date) dob);
      ps.setDate(4, (java.sql.Date) archived);
      ps.setString(5, scout_no);

      ps.executeUpdate();


      ps2 = conn.prepareStatement(querySectionDetailsTable);

      //Get foreign key from insert into at_cub_details
      ResultSet rs = ps.getGeneratedKeys();
      if (rs.next()) {
          cd_id = rs.getInt(1);
      };

      System.out.println("cd_id = " + cd_id);
      System.out.println("section = " + section);
      System.out.println("pack = " + pack);
      System.out.println("start date = " + startDate);
      System.out.println("end date = " + endDate);
      ps.setInt(1, cd_id);
      ps.setString(2, section);
      ps.setString(3, pack);
      ps.setDate(4, (java.sql.Date) startDate);
      ps.setDate(5, (java.sql.Date) endDate);

      ps2.executeUpdate();

    }
    catch (SQLException e) {
      //do stuff on fail
        System.out.println("SQLException createYouthMember 1.");
        e.printStackTrace();
        user = null;
    }
    finally {
        if (result != null) {
            try {
                result.close();
            }
            catch (SQLException e) {
                System.out.println("SQLException createYouthMember 2.");
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            }   
            catch (SQLException e) {
                System.out.println("SQLException createYouthMember 3.");
                e.printStackTrace();
            }
        }
        if (ps2 != null) {
            try {
                ps2.close();
            }   
            catch (SQLException e) {
                System.out.println("SQLException createYouthMember 4.");
                e.printStackTrace();
            }
        }
    }
return ythMmbrSectDtls;
}

表格是:

如果有任何帮助，我将不胜感激。此致，格林

Answer 1

ps 应该是 ps2

ps2.setInt(1, cd_id);
ps2.setString(2, section);
ps2.setString(3, pack);
ps2.setDate(4, (java.sql.Date) startDate);
ps2.setDate(5, (java.sql.Date) endDate);