A Java PreparedStatement INSERT example

In an earlier example I shared a Java PreparedStatement example that demonstrated how to insert nearly 1,500 records into a Drupal database table. In this brief tutorial I'll share the source code for a more complicated PreparedStatement example.

What I'm trying to do in this code is make a Drupal module named "Workflow" happy. When I originally converted my data over to Drupal, I didn't populate the Workflow database tables correctly, so I'm trying to fix those problems. Specifically for this example, the Workflow module works best if it sees that every Node has passed through a series of states, and these states are represented by the numbers 1 through 4.

Therefore, in this example, I perform a series of SQL INSERT statements for each Node object that has been passed into my method, inserting a record for each node, showing that the node was originally in state 1, then in state 2, state 3, and finally state 4.

The cool thing about this example is that it is all done with one SQL INSERT statement, and one Java PreparedStatement. The PreparedStatement is used over and over again as I loop through the Nodes and the states, which is exactly how a PreparedStatement should be used.

The Java PreparedStatement INSERT source code

Given that background, here is the Java source code for this PreparedStatement INSERT example:

private void insertWorkflowHistoryNodeEntries(List<Node> nodes)
throws SQLException
{
  // they can all have the same timestamp
  int stamp = 1250629585;
  
  // our sql INSERT statement
  String query = " insert into workflow_node_history (nid, old_sid, sid, uid, stamp)"
               + " values (?, ?, ?, 3, ?)";

  PreparedStatement preparedStmt = null;
  try
  {
    // create the prepared statement
    preparedStmt = mysqlConn.prepareStatement(query);
   
    // do three inserts into the history table for each node
    for (Node n : nodes)
    {
      // each node starts like this
      int oldSid = 1;
      int sid = 2;

      // do the first sql insert
      preparedStmt.setInt(1, n.nid);
      preparedStmt.setInt(2, oldSid);
      preparedStmt.setInt(3, sid);
      preparedStmt.setInt(4, stamp++);
      preparedStmt.execute();

      // move the node to the second state with another sql insert
      oldSid = 2;
      sid = 3;
      preparedStmt.setInt(1, n.nid);
      preparedStmt.setInt(2, oldSid);
      preparedStmt.setInt(3, sid);
      preparedStmt.setInt(4, stamp++);
      preparedStmt.execute();

      // move the node to the final state with this sql insert
      oldSid = 3;
      sid = 4;
      preparedStmt.setInt(1, n.nid);
      preparedStmt.setInt(2, oldSid);
      preparedStmt.setInt(3, sid);
      preparedStmt.setInt(4, stamp++);
      preparedStmt.execute();
    }
  }
  catch (SQLException se)
  {
    se.printStackTrace();
    throw se;
  }
  finally
  {
    preparedStmt.close();
  }
}

// the Node class is very simple:
class Node
{
  int nid;
  String type;
  int uid;
  int status;
}

Java PreparedStatement insert code - discussion

I tried to document the code reasonably well, so there isn't much to add about it here. I've added the Node class to the listing here in case it helps to see what it looks like.

I also need to note that the mysqlConn is our java.sql.Connection to the MySQL database. This connection needs to be created before this method is called.

I can't think of anything else to add, but if you have any questions, just leave a comment below and I'll get back with you.

Comments

Permalink

Good example, thanks!