3

I have some code that will potentially insert a huge number of rows into a table. I'm currently using Spring's JdbcTemplate.update to execute the SQL, but I have a concern.

The method, like bare JDBC's Statement.executeUpdate to which it delegates, returns an int, where the maximum value is of course 231 = 2147483648.

So, the question is, what does JDBC do in such a situation? Return MAX_INTEGER, some other number, throw an exception? Or perhaps the behavior is platform- (or driver-) specific? I'm supporting both SQL Server (through jTDS) and Oracle (through the thin driver).

Ray
  • 4,829
  • 4
  • 28
  • 55
  • 2
    Wait, will your application _really_ insert more than 2 billion rows in the same statement? Or is it a purely theoretical question? – Tunaki Nov 18 '15 at 12:30
  • Yes, in a rare circumstance; broadly speaking, it's copying (transformed) rows from one table to another. – Ray Nov 18 '15 at 12:33
  • 1
    I don't think you should use Java for this. If it's copying data from one table to another, use your DB tools (like a stored procedure). I can't even begin to realize how long serializing this data and sending to the DB back and forth will take, but I guess it will be enormous (enormous enough that there is no question anymore because the application will just die right there). – Tunaki Nov 18 '15 at 12:35
  • You would probably get a better performance by splitting the updates in several batches of say 1m records and probably even better by using the database batch import facility (via a csv file or similar). See also http://stackoverflow.com/questions/2993251/jdbc-batch-insert-performance – assylias Nov 18 '15 at 12:36
  • or use an ETL tool for this, like Pentaho Kettle – aurelius Nov 18 '15 at 12:38
  • 1
    FYI, in Java 8 there is a [`executeLargeUpdate`](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#executeLargeUpdate-java.lang.String-) which returns a `long` but you would have to make sure the JDBC driver supports it. – Tunaki Nov 18 '15 at 12:40
  • 1
    I might have given the wrong impression; data is not flowing through the Java app. When you execute an `insert` with a `select`, all the work is done in the DMBS. I appreciate the concern regarding the best approach (like a dedicated ETL tool). At this point, I'm primarily interested in the behavior under such a condition. – Ray Nov 18 '15 at 12:53

2 Answers2

1

In JDBC 4.2 (which is part of Java 8) we have: The Statement class has a method executeLargeUpdate for executing an update whose row count exceeds Integer.MAX_VALUE.

https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html

Ray
  • 4,829
  • 4
  • 28
  • 55
disable1992
  • 4,873
  • 3
  • 17
  • 25
  • That would help... it looks like Microsoft has a JDBC 4.2 compliant driver, but I don't find one for Oracle. – Ray Nov 18 '15 at 14:36
1

It's driver dependent, because Statement is an Interface that must be implemented by the vendor's jdbc driver.

For an example, this is what happens inside the MySQL jdbc driver

public int executeUpdate(String sql) throws SQLException {
    return Util.truncateAndConvertToInt(executeLargeUpdate(sql));
}

which means

public static int truncateAndConvertToInt(long longValue) {
    return longValue > Integer.MAX_VALUE ? Integer.MAX_VALUE : longValue < Integer.MIN_VALUE ? Integer.MIN_VALUE : (int) longValue;
}

So in this specific case, for this specific driver, it will return Integer.MAX_VALUE and no exception will be thrown.

MySQL jdbc driver and source code are available here http://dev.mysql.com/downloads/connector/j/

Leo
  • 751
  • 4
  • 29