title: Java PreparedStatement multiple batch inserts with foreign keys
body: <p>I have a Java application and am trying to do a series of batch inserts to a MySQL database. However, many of my tables are linked by foreign keys. So I don't know the value of the foreign keys to use because they are generated in a previous batch.</p>
  
  <hr>
  
  <p>For example take these two tables:<br>
  <strong>parent</strong><br>
  id<br>
  name  </p>
  
  <p><strong>child</strong><br>
  id<br>
  parent_id <em>(required foreign key to parent.id)</em><br>
  name  </p>
  
  <hr>
  
  <p>I know how to import these without using batches:</p>
  
  <pre><code>//already initialized variables: Connection connection, List&lt;ParentObject&gt; parentObjects
  
  ResultSet rs = null;
  PreparedStatement psParent = null;
  PreparedStatement psChild = null;
  for(ParentObject parent: parentObjects){
      psParent = connection.prepareStatement("INSERT INTO product (name) VALUES (?)", PreparedStatement.RETURN_GENERATED_KEYS);
      psParent.setString(1, parent.getName());
      psParent.executeUpdate();
  
      int parentId = 0;
      rs = psParent.getGeneratedKeys();
      if (rs.next())
          parentId = rs.getInt(1);
  
      rs.close();
      psParent.close();
  
      for(ChildObject child : parent.getChildren()){
          psChild = connection.prepareStatement("INSERT INTO child (parent_id, name) VALUES (?,?)");
          psChild.setInt(1, parentId);
          psChild.setString(2, child.getName());
          psChild.executeUpdate();
          psChild.close();
      }
  }
  </code></pre>
  
  <p>Now I am trying to use batches:</p>
  
  <pre><code>PreparedStatement psParent = connection.prepareStatement("INSERT INTO product (name) VALUES (?)");
  PreparedStatement psChild = connection.prepareStatement("INSERT INTO child (parent_id, name) VALUES (?,?)");
  for(ParentObject parent: parentObjects){
      psParent.setString(1, parent.getName());
      psParent.addBatch();
  
      for(ChildObject child : parent.getChildren()){
          psChild.setInt(1, I_DONT_KNOW_HOW_TO_GET_THIS_ID_WHICH_HASNT_BEEN_INSERTED_YET);
          psChild.setString(2, parent.getName());
          psChild.addBatch();
      }
  }
  
  psParent.executeBatch();
  psParent.close();
  psChild.executeBatch();
  psChild.close();
  </code></pre>
  
  <p>The actual data structure I am using is much more complicated than what I have above, but it illustrates the basic issue. How would I do the above using batches? The issue I am running into is the child objects cannot be inserted without first knowing the parent_id foreign key to use.</p>
  
  <p>I've searched for answers elsewhere and I cannot find anything to resolve this. I read something about using a stored procedure (which I would like to avoid if possible). Efficiency is important here since I am dealing with potentially millions of records. I appreciate any insight anyone might have.</p>