Java – how to insert millions of data into a database

I tried to insert a million records into the database table

I want to create at least three threads, each thread triggers an insert, and then we can get three parallel requests every second We can control the insertion trigger by making each thread wait and wake up together, and the same interrupt occurs at the same time Then each thread goes to sleep until a one second window passes Then the whole process will be repeated How can I finish it?

Any suggestion or hint will help

Solution

This is based on http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/ A quick example of batch insertion of mykong code

This basically provides you with the speed of sqlloader, which can perform batch insertion And only 1 thread should be used

What I do here is put the insert into a loop, indicating that you must clear the batch every thousands of records

You will delete the infinite loop and let it insert data instead of hard coded mkyong data

String insertTablesql = "INSERT INTO DBUSER"
        + "(USER_ID,USERNAME,CREATED_BY,CREATED_DATE) VALUES"
        + "(?,?,?)";

PreparedStatement preparedStatement = dbConnection.prepareStatement(insertTablesql);
try {
    dbConnection.setAutoCommit(false);

    int batchTotal=0;
    for  (;;) { // infinate loop? change this to get your data here
        preparedStatement.setInt(1,101);
        preparedStatement.setString(2,"mkyong101");
        preparedStatement.setString(3,"system");
        preparedStatement.setTimestamp(4,getCurrentTimeStamp());
        preparedStatement.addBatch();
        if (batchTotaL++ == 4096) {
            int[] result = preparedStatement.executeBatch();
            preparedStatement.clearBatch();
            batchTotal=0;                    
        }
    }
    if (batchTotal > 0) {
        int[] result = preparedStatement.executeBatch();
    }

    dbConnection.commit();
}  finally {
    preparedStatement.close();
}