Vfrdtyky

This is a mix of the two previous answers:

  PreparedStatement ps = c.prepareStatement("INSERT INTO employees VALUES (?, ?)");



  ps.setString(1, "John");

  ps.setString(2,"Doe");

  ps.addBatch();



  ps.clearParameters();

  ps.setString(1, "Dave");

  ps.setString(2,"Smith");

  ps.addBatch();



  ps.clearParameters();

  int results = ps.executeBatch();

Though the question asks inserting efficiently to Oracle using JDBC, I'm currently playing with DB2 (On IBM mainframe), conceptually inserting would be similar so thought it might be helpful to see my metrics between

• inserting one record at a time




• inserting a batch of records (very efficient)

Here go the metrics

1) Inserting one record at a time

public void writeWithCompileQuery(int records) {

    PreparedStatement statement;



    try {

        Connection connection = getDatabaseConnection();

        connection.setAutoCommit(true);



        String compiledQuery = "INSERT INTO TESTDB.EMPLOYEE(EMPNO, EMPNM, DEPT, RANK, USERNAME)" +

                " VALUES" + "(?, ?, ?, ?, ?)";

        statement = connection.prepareStatement(compiledQuery);



        long start = System.currentTimeMillis();



        for(int index = 1; index < records; index++) {

            statement.setInt(1, index);

            statement.setString(2, "emp number-"+index);

            statement.setInt(3, index);

            statement.setInt(4, index);

            statement.setString(5, "username");



            long startInternal = System.currentTimeMillis();

            statement.executeUpdate();

            System.out.println("each transaction time taken = " + (System.currentTimeMillis() - startInternal) + " ms");

        }



        long end = System.currentTimeMillis();

        System.out.println("total time taken = " + (end - start) + " ms");

        System.out.println("avg total time taken = " + (end - start)/ records + " ms");



        statement.close();

        connection.close();



    } catch (SQLException ex) {

        System.err.println("SQLException information");

        while (ex != null) {

            System.err.println("Error msg: " + ex.getMessage());

            ex = ex.getNextException();

        }

    }

}

The metrics for 100 transactions :

each transaction time taken = 123 ms

each transaction time taken = 53 ms

each transaction time taken = 48 ms

each transaction time taken = 48 ms

each transaction time taken = 49 ms

each transaction time taken = 49 ms

...

..

.

each transaction time taken = 49 ms

each transaction time taken = 49 ms

total time taken = 4935 ms

avg total time taken = 49 ms

The first transaction is taking around 120-150ms which is for the query parse and then execution, the subsequent transactions are only taking around 50ms. (Which is still high, but my database is on a different server(I need to troubleshoot the network))

2) With insertion in a batch (efficient one) - achieved by preparedStatement.executeBatch()

public int writeInABatchWithCompiledQuery(int records) {

    PreparedStatement preparedStatement;



    try {

        Connection connection = getDatabaseConnection();

        connection.setAutoCommit(true);



        String compiledQuery = "INSERT INTO TESTDB.EMPLOYEE(EMPNO, EMPNM, DEPT, RANK, USERNAME)" +

                " VALUES" + "(?, ?, ?, ?, ?)";

        preparedStatement = connection.prepareStatement(compiledQuery);



        for(int index = 1; index <= records; index++) {

            preparedStatement.setInt(1, index);

            preparedStatement.setString(2, "empo number-"+index);

            preparedStatement.setInt(3, index+100);

            preparedStatement.setInt(4, index+200);

            preparedStatement.setString(5, "usernames");

            preparedStatement.addBatch();

        }



        long start = System.currentTimeMillis();

        int inserted = preparedStatement.executeBatch();

        long end = System.currentTimeMillis();



        System.out.println("total time taken to insert the batch = " + (end - start) + " ms");

        System.out.println("total time taken = " + (end - start)/records + " s");



        preparedStatement.close();

        connection.close();



        return inserted;



    } catch (SQLException ex) {

        System.err.println("SQLException information");

        while (ex != null) {

            System.err.println("Error msg: " + ex.getMessage());

            ex = ex.getNextException();

        }

        throw new RuntimeException("Error");

    }

}

The metrics for a batch of 100 transactions is

total time taken to insert the batch = 127 ms

and for 1000 transactions

total time taken to insert the batch = 341 ms

So, making 100 transactions in ~5000ms (with one trxn at a time) is decreased to ~150ms (with a batch of 100 records).

NOTE - Ignore my network which is super slow, but the metrics values would be relative.

The Statement gives you the following option:

Statement stmt = con.createStatement();



stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");

stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");

stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)");



// submit a batch of update commands for execution

int updateCounts = stmt.executeBatch();

You'll have to benchmark, obviously, but over JDBC issuing multiple inserts will be much faster if you use a PreparedStatement rather than a Statement.

How about using the INSERT ALL statement ?

INSERT ALL



INTO table_name VALUES ()



INTO table_name VALUES ()



...



SELECT Statement;

I remember that the last select statement is mandatory in order to make this request succeed. Don't remember why though.
You might consider using PreparedStatement instead as well. lots of advantages !

Farid

You can use addBatch and executeBatch for batch insert in java See the Example : Batch Insert In Java

In my code I have no direct access to the 'preparedStatement' so I cannot use batch, I just pass it the query and a list of parameters. The trick however is to create a variable length insert statement, and a LinkedList of parameters. The effect is the same as the top example, with variable parameter input length.See below (error checking omitted).
Assuming 'myTable' has 3 updatable fields: f1, f2 and f3

String args={"A","B","C", "X","Y","Z" }; // etc, input list of triplets

final String QUERY="INSERT INTO [myTable] (f1,f2,f3) values ";

LinkedList params=new LinkedList();

String comma="";

StringBuilder q=QUERY;

for(int nl=0; nl< args.length; nl+=3 ) { // args is a list of triplets values

    params.add(args[nl]);

    params.add(args[nl+1]);

    params.add(args[nl+2]);

    q.append(comma+"(?,?,?)");

    comma=",";

}      

int nr=insertIntoDB(q, params);

in my DBInterface class I have:

int insertIntoDB(String query, LinkedList <String>params) {

    preparedUPDStmt = connectionSQL.prepareStatement(query);

    int n=1;

    for(String x:params) {

        preparedUPDStmt.setString(n++, x);

    }

    int updates=preparedUPDStmt.executeUpdate();

    return updates;

}