# Inserting tables with foreign key

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(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(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?

janos recommended the Spring and Apache DbUtils frameworks, I can highly recommend using Hibernate.

Hibernate is a very commonly used ORM framework (Object-Relational-Mapping). By using Hibernate and the proper entity classes, you can do all this with only these lines:

try {
em.getTransaction().begin();
em.persist(user);
em.getTransaction().commit();
} catch (Exception ex) {
if (em.getTransaction().isActive()) {
em.getTransaction().rollback();
}
// Log/handle exception in another way as well
}


When using Hibernate, you have the ability to tell it all about the one-to-many/many-to-many/one-to-one/many-to-one relationships, and tell it when to cascade operations for you. Here's an example of how your UserInfo could look.

@Entity
public class UserInfo {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

private String firstName;


One formatting issue with your current code is that you're not indenting your code properly. Each { should add one indentation, each } should remove one. This makes things so much readable.
Rather than using the low level database APIs like managing Connection, PreparedStatement, ResultSet, yourself, I strongly recommend to use a framework for this, such as Spring's JDBC template or Apache's DbUtils. Once you step on that road life will get easier and you won't look back.