Java Insert multiple records

Inserting a record in database using java

To insert data into a database using JDBC, you need to do the following steps:

Load the JDBC driver: You need to load the JDBC driver before you can use it to connect to a database. You can do this by calling the Class.forName() method, passing it the name of the JDBC driver class.

Establish a connection to the database: You can use the DriverManager.getConnection() method to establish a connection to the database. You will need to pass it the URL of the database, as well as the username and password to use when connecting.

Create a Statement object: You can use the createStatement() method of the Connection object to create a Statement object.

Execute the insert statement: You can use the execute Update() method of the Statement object to execute an insert statement. This method returns the number of rows affected by the insert statement.

Here is an example of how to insert data into a database using JDBC:

// Load the JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish a connection to the database
Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

// Create a Statement object
Statement stmt = conn.createStatement();

// Execute the insert statement
int numRows = stmt.executeUpdate("INSERT INTO table_name (column1, column2) VALUES (value1, value2)");

Inserting Multiple records at once

For inserting multiple records, one approach can be to loop over the executeUpdate and insert all the records. But this approach inserts records one by one.

for (int i = 0; i < numRecords; i++) {
  // Execute the insert statement
  int numRows = stmt.executeUpdate("INSERT INTO table_name (column1, column2) VALUES (value1, value2)");
}

If we go by this answer and concatenate the inserts into a single statement as follows, the statement works for small set of records, but not for 100,000 records.

INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

But when large number of records are inserted it results in errors like SQL Error: ORA-00913: too many values for oracle.

If you are looking insert 100,000 records at once, prefer batch insert over the above methods.

JDBC batch insert is a process of inserting multiple records into a database table in a single JDBC statement. This can be done to improve the performance of database operations, especially when inserting a large number of records.

Steps for JDBC batch insert

1. Set auto-commit to false. This will allow you to execute multiple statements as a batch and commit them all at once.

2. Use the addBatch() method of the Statement or PreparedStatement object to add individual insert statements to the batch.

3. Execute the batch using the executeBatch() method.

4. Commit the transaction using the commit() method of the Connection object.

Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

//Set auto-commit to false
conn.setAutoCommit(false);

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO table_name(column1, column2) VALUES (?, ?)");

for (int i = 0; i < numRecords; i++) {
  pstmt.setInt(1, i);
  pstmt.setString(2, "value " + i);
  pstmt.addBatch();
}

pstmt.executeBatch();

//Commit the transactoin
conn.commit();

This will insert numRecords number of records into the table_name table, with column1 and column2 being the respective column names.

If you have to reuse the preparedStatement for another batch clear the paramters using ps.clearParameters()

Drawback of JDBC Batch Insert

JDBC batch insert can be more efficient because it allows the database to optimize the insertion of multiple records as a single operation, rather than executing each insert statement individually.

However, JDBC batch insert also has some drawbacks:

1. It requires more memory to store the batch of insert statements before they are executed.
2. It can be more complex to implement because you need to manage the batch and the transaction yourself.
3. If there is an error executing the batch, it can be more difficult to identify which statement caused the error.