Monitoring (and Java and Oracle Batching (for large number of inserts))

Background

I’ve been involved with a monitor project for a while – sadly I’m a bit of geek who enjoys looking at numbers etc. In the very early days we were looking at providing real time statistics just from VLE access, and then things evolved to look at bringing all the data together from various systems (e.g. the assignments system) to provide better metrics, and essentially a student dashboard as a early warning system. I blogged about the idea back in January of 2011 (in a work blog) and have since been involved with various prototypes to get the project off the ground. I think the original idea stemmed from this video showing some initial findings about VLE access and student performance (which originally came via a tweet by George Kroner). I’m pleased to say that the Student Dashboard is now being piloted a small set of users and more information can be found out about the project via the University of Hertfordshire LTI Blog.

Eh, I thought this was about Java and Oracle and Inserting Lots of Records…

Well it is, I got a bit side tracked there. Basically, the background was the scenario of having lots of records to be processed from various sources to generate the required structures for reporting from. And its grown over time, and today I needed to come back and revisit it. The good news is I found out about a Batch Insert for Oracle which meant inserted this number of records has just greatly decreased.

Sample Code

[Note – some lines have been removed for protection, should ultimately work though, but no promises of no syntax errors).

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
   conn = getDBConnection();
   conn.setAutoCommit(false);

   ps = conn.prepareStatement("INSERT INTO table_name (Field1, Field2,Field3,Field4,Field5) VALUES (?, ?, ?, ?, ?)");
   ((OraclePreparedStatement)ps).setExecuteBatch (1000);
   for (EntryType entry : myEntryList) {
      ps.setString(1, entry.getField1());
      ps.setString(2, entry.getField2());
      ps.setString(3, entry.getField3());
      ps.setString(4, entry.getField4());
      ps.setInt(5, 1);
      ps.executeUpdate();
   }
   ((OraclePreparedStatement)ps).sendBatch(); // JDBC sends the queued request
   conn.commit();
} catch (Exception e) {
   e.printStackTrace(System.err);
} finally {
   if (rs != null) {
      rs.close();
   }
   if (ps != null) {
      ps.close();
   }

   if (conn != null) {
      conn.close();
   }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s