mysql - 无法在 jdbc 准备好的语句中的外键表中插入数据

Question

我对此很陌生。我有两张 table 。 registrationtable 和 logintable，其中 registerationid 是 registrationtable 中的主键，是 logintable 中的外键> 其中registerationid 是registrationtable 中的Auto_Increment。我可以在 registrationtable 中插入，但不能在 logintable 中插入。我正在使用 PreparedStatement 并拥有 Bean 类。请任何人都可以帮助我。预先感谢您的帮助。

try {               
        PreparedStatement preparedstatement=connection.prepareStatement("INSERT INTO registrationtable(firstname,lastname,dob,address,city,state,mob) VALUES (?,?,?,?,?,?,?)",PreparedStatement.RETURN_GENERATED_KEYS);
        preparedstatement.setString(1,bean.getFirstName());
        preparedstatement.setString(2,bean.getLastName());
        preparedstatement.setString(3,bean.getDOB());
        preparedstatement.setString(4,bean.getAddress());
        preparedstatement.setString(5,bean.getCity());
        preparedstatement.setString(6,bean.getState());
        preparedstatement.setString(7,bean.getMobile());

        System.out.println("Dao Rgisteration : "+bean.getFirstName());
        preparedstatement.executeUpdate();

        ResultSet resultset=preparedstatement.getGeneratedKeys();
        resultset.next();
        int autoGeneratedKeyRegisteration = resultset.getInt(1);

        preparedstatement.close();
    //  resultset=preparedstatement.executeQuery("SELECT LAST_INSERT_ID() AS last_id FROM registerationtable");



        String sql="INSERT INTO logintable(registerationid,username,password) VALUES (LAST_INSERT_ID(),'xyz@gmail.com','xyzabc123')";
        PreparedStatement preparedstatement1=connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
        preparedstatement1.setInt(1,bean.getRegistrationId());
        preparedstatement1.setString(2, bean.getUsername());
        preparedstatement1.setString(3, bean.getPassword());
        System.out.println("Dao Login : "+bean.getUsername());
        preparedstatement1.executeUpdate();

        ResultSet resultset1=preparedstatement1.getGeneratedKeys();
        if(resultset1.next()){
        int autoGeneratedkeyLogin=resultset1.getInt(1);
        }
        resultset1=preparedstatement1.executeQuery("SELECT registerationid FROM logintable WHERE registerationid=LAST_INSERT_ID()");
        System.out.println(resultset);
        preparedstatement1.close();
    } catch(Exception e){e.getMessage();}finally{
        try{
            connection.close();
        }catch(Exception ex){ex.getMessage();}
    }

}

Answer 1

首先，我建议在事务中进行这些插入: https://stackoverflow.com/a/5723862/3323777

并使用 getGenerateKeys 代替 LAST_INSERT_ID: https://stackoverflow.com/a/1915197/3323777

LAST_INSERT_ID 有一些注意事项和限制: https://dba.stackexchange.com/a/21188