How to simultaneously insert data in multiple table in Java using foreign key constraint for second table?

Question

I have two tables, user and account.

What I want to do is register a new user and create the user's account from single registration from using user id as foreign key in account table.

I first need a way to pass in the user id to account table on registering new user to create that user's account as well.

I have a register button and operation follows here:

public void actionPerformed(ActionEvent arg0) {
    try{
        User u = new User();
        Account a = new Account();

        u.setFname(fnameTextField.getText());
        u.setMname(mnameTextField.getText());
        u.setLname(lnameTextField.getText());

        if(maleRadioButton.isSelected()){
            u.setGender("Male");
        }
        if(femaleRadioButton.isSelected()){
            u.setGender("Female");
        }

        u.setAddress(addressTextField.getText());
        u.setCitizenship(Long.parseLong(citizenTextField.getText()));

        UserRegistraionService urs = new UserRegistrationServiceImpl();
        boolean resultUser = urs.addUser(u);

         /**
         *
         **Here i want to pass in the user id to the account table
         *
         */
        a.setId(u.getId());
        a.setAccountType(accountTypeComboBox.getSelectedItem().toString());
        a.setAccountNo(accountNoTextField.getText());
        a.setTotalBalance(Long.parseLong(initialDepositTextField.getText()));
        a.setUsername(usernameTextField.getText());
        a.setPassword(passwordField.getText());

        TranscationService trs = new TranscatiionSercieImpl();
        boolean resultAccount = trs.createAccount(a);

        if(resultUser == true && resultAccount == true){
            JOptionPane.showMessageDialog(null, "User Registered Sucessfully.");

            new UserLoginForm();
            dispose();
        }else{
            JOptionPane.showMessageDialog(null, "Operation Failed.");
        }
    }catch(Exception e){
        JOptionPane.showMessageDialog(null, "Invalid Input.");
    }
}

Here is my addUser method:

public class UserRegistrationServiceImpl implements UserRegistraionService{

Connection con = null;

public UserRegistrationServiceImpl() {

    con = DBConnection.getDBConnetion();

}

@Override
public boolean addUser(User u) {

    String sql = "INSERT INTO user (fname, mname, lname, gender, address, citizenship) VALUES (?, ?, ?, ?, ?, ?)";

    try {
        PreparedStatement pstm = con.prepareStatement(sql);

        pstm.setString(1, u.getFname());
        pstm.setString(2, u.getMname());
        pstm.setString(3, u.getLname());
        pstm.setString(4, u.getGender());
        pstm.setString(5, u.getAddress());
        pstm.setLong(6, u.getCitizenship());

        pstm.execute();
        return true;

    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

@Override
public boolean updateStudent(User u) {
    // TODO Auto-generated method stub
    return false;
}

@Override
public User getById(int id) {
    // TODO Auto-generated method stub
    return null;
}

}

Here is my createAccount method:

public class TranscatiionSercieImpl implements TranscationService{

Connection con = null;

public TranscatiionSercieImpl() {

    con = DBConnection.getDBConnetion();

}

@Override
public boolean createAccount(Account a) {

    String sql = "INSERT INTO account (user, accountType, accountNo, username, password, totalBalance, loan) VALUES (?, ?, ?, ?, ?, ?, ?)";


    try {
        PreparedStatement pstm = con.prepareStatement(sql);

        pstm.setInt(1, a.getId());
        pstm.setString(2, a.getAccountType());
        pstm.setString(3, a.getAccountNo());
        pstm.setString(4, a.getUsername());
        pstm.setString(5, a.getPassword());
        pstm.setLong(6, a.getTotalBalance());
        pstm.setLong(7, a.getLoan());

        pstm.execute();
        return true;

    } catch (SQLException e) {
        e.printStackTrace();
    }

    return false;
}

}

How to setup a relationship between tables? I am using core Java.

`UserRegistraionService`? `TranscationService`? Are those spelled right? — Catalina Island, Jun 16 '17 at 10:16
it depends how you want to implement this relationship, i suggest use the java persistence API which is easy to implement. — Ulug Toprak, Jun 16 '17 at 13:43
If `user.id` is auto-increment, try `getGeneratedKeys()`, for [example](https://stackoverflow.com/a/17438304/230513). If this is not a duplicate, please edit your question to include a [mcve] that shows your revised approach. — trashgod, Jun 16 '17 at 16:26
Possible duplicate of [Insert using PreparedStatement. How do I auto-increment the ID?](https://stackoverflow.com/questions/17438288/insert-using-preparedstatement-how-do-i-auto-increment-the-id) — Catalina Island, Jun 19 '17 at 10:08

How to simultaneously insert data in multiple table in Java using foreign key constraint for second table?

0 Answers0