Unable to insert data in foreign key table in jdbc prepared statement

Question

I'm new to this. I'm having two tables. registrationtable and logintable, where registerationid is primary key in registrationtable and foreign key in logintable where registerationid is Auto_Increment in registrationtable. I'm able to insert in registrationtable but not in logintable. I'm using PreparedStatement and having Bean class. Please anybody can help me. Thank you in advance for helping.

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();}
    }

}

eugene-nikolaev · Accepted Answer · 2017-09-05T10:29:14.667

0

At first, I advice to make those inserts within a transaction: https://stackoverflow.com/a/5723862/3323777

And use getGeneratedKeys instead LAST_INSERT_ID: https://stackoverflow.com/a/1915197/3323777

LAST_INSERT_ID has some caveats and limitations: https://dba.stackexchange.com/a/21188

edited Sep 05 '17 at 10:29

answered Sep 05 '17 at 09:17

eugene-nikolaev

1,290
1
13
21

Unable to insert data in foreign key table in jdbc prepared statement

1 Answers1