How Insert 50K records to Oracle database? [closed]

Question

I have requirement to only insert some data to Oracle database to only one table and not any relationships.

I need to insert more than 50K records in the same transaction. What is the best way to do this? Using pure JDBC, JPA or Hibernate etc…

Which way (batch update or raw by raw update) is better to insert the around 50000 record in same transaction?

Answer 1

Hibernate : using batch update you can insert your data,

1. First save all object in session

   session.save(Object);

2. flush() and clear() your session

if ((batchCounter % 25000) == 0) {
  session.flush();
  session.clear();
}

3. Commit all the data

   tx.commit();

Answer 2

you can use Java Jdbc prepared statement.

// Create SQL statement
String SQL = "INSERT INTO Employee (id, first, last, age) " +
             "VALUES(?, ?, ?, ?)";

// Create PreparedStatement object
PreparedStatemen pstmt = conn.prepareStatement(SQL);

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

// Set the variables
pstmt.setInt( 1, 400 );
pstmt.setString( 2, "x" );
pstmt.setString( 3, "y" );
pstmt.setInt( 4, 33 );

// Add it to the batch
pstmt.addBatch();

// Set the variables
pstmt.setInt( 1, 401 );
pstmt.setString( 2, "p" );
pstmt.setString( 3, "q" );
pstmt.setInt( 4, 31 );

// Add it to the batch
pstmt.addBatch();

// Create an int[] to hold returned values
int[] count = stmt.executeBatch();

// Explicitly commit statements to apply changes
conn.commit();