代码之家  ›  专栏  ›  技术社区  ›  NO_name

无法在jdbc prepared语句的外键表中插入数据

  •  0
  • NO_name  · 技术社区  · 7 年前

    我是新手。我有两张桌子。 registrationtable logintable 哪里 registerationid 登记表 和外键输入 哪里 注册ID 在里面 登记表 . 我可以插入 但不在 登录表 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();}
        }
    
    }
    
    1 回复  |  直到 7 年前
        1
  •  0
  •   eugene-nikolaev    7 年前

    首先,我建议在事务中插入这些内容: https://stackoverflow.com/a/5723862/3323777

    getGeneratedKeys 取而代之的是LAST\u INSERT\u ID: https://stackoverflow.com/a/1915197/3323777

    LAST\u INSERT\u ID有一些注意事项和限制: https://dba.stackexchange.com/a/21188