Database Reference
In-Depth Information
{
statement.setInt(1, i);
statement.setString(2, "... some text ...");
statement.
addBatch();
}
counts = statement.
executeBatch()
;
statement.close();
■
according to the JdBC standard, batch updates are supported by the
java.sql.Statement
interface
and its subinterfaces
java.sql.PreparedStatement
and
java.sql.CallableStatement
. even though oracle's
implementation supports the standard apI, you should expect performance improvements only when using the
java.sql.PreparedStatement
interface to repeatedly execute the same SQL statement with different bind variables.
Caution
ODP.NET
To use the array interface with ODP.NET, it's enough to define parameters based on arrays and to set the
ArrayBindCount
property to the number of values stored in the arrays. The following code snippet, which inserts
100,000 rows in a single execution, illustrates this. You can find a complete example in the C# program in the
ArrayInterface.cs
file:
Decimal[] idValues = new Decimal[100000];
String[] padValues = new String[100000];
for (int i=0 ; i<100000 ; i++)
{
idValues[i] = i;
padValues[i] = "... some text ...";
}
id = new OracleParameter();
id.OracleDbType = OracleDbType.Decimal;
id.Value = idValues;
pad = new OracleParameter();
pad.OracleDbType = OracleDbType.Varchar2;
pad.Value = padValues;
sql = "INSERT INTO t VALUES (:id, :pad)";
command = new OracleCommand(sql, connection);
command.ArrayBindCount = idValues.Length;
command.Parameters.Add(id);
command.Parameters.Add(pad);
command.ExecuteNonQuery();