How can I improve this method that adds data to three tables in the database?
The tables are:
UserTable
UserInfoTable
ContactTable
UserInfoTable
and ContactTable
have a one-to-many relationship, with ContactTable
having UserInfoID
as a foreign key.
public static boolean addUser(User user) {
Connection connection = getConnection();
try {
PreparedStatement insertUser = connection.prepareStatement(Insert_User);
insertUser.setString(1, user.getUsername());
insertUser.setString(2, user.getPassword());
insertUser.setString(3, user.getUserType());
insertUser.executeUpdate();
PreparedStatement insertUserInfo = connection.prepareStatement(Insert_UserInfo);
insertUserInfo.setString(1, user.getFirstName());
insertUserInfo.setString(2, user.getMiddleName());
insertUserInfo.setString(3, user.getLastName());
insertUserInfo.setString(4, user.getGender());
insertUserInfo.setString(5, user.getBirthdate());
insertUserInfo.executeUpdate();
//I need to get the UserInfoID to insert to ContactTable. I really hate to do this but I can't think of any way to insert the foreign key.
PreparedStatement getUserInfoID = connection.prepareStatement(GET_USER_INFO_ID);
getUserInfoID.setString(1, user.getFirstName());
getUserInfoID.setString(2, user.getMiddleName());
getUserInfoID.setString(3, user.getLastName());
getUserInfoID.setString(4, user.getGender());
getUserInfoID.setString(5, user.getBirthdate());
ResultSet rs = getUserInfoID.executeQuery();
PreparedStatement insertContact = connection.prepareStatement(Insert_Contact);
while (rs.next()) {
insertContact.setInt(1, rs.getInt(1));
insertContact.setString(2, user.getEmailAddress());
insertContact.setString(3, user.getAddress());
insertContact.setString(4, user.getContactNumber());
insertContact.executeUpdate();
}
} catch (SQLException sqle) {
sqle.printStackTrace();
return false;
}
return true;
}
I originally want to set the connections's autocommit to false so the connection will rollback if an insert statement fails, but since I need to get the UserInfo
ResultSet
to get the UserInfoID
, I can't do it anymore. How can I do this better?