CODEWITHSUNDEEP
javamysqljdbcxampp
Muhammad Sayed
Muhammad Sayed

Reputation: 21

it takes too much time to insert 5000 record in mysql database using jdbc

I'm using jdbc, mysql-connector-java-8.0.20 and xampp as mysql server to insert 5000 record in database I used this link to test that, and here is the code that I tested with

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Random;

public class test {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sonoo", "root", "");
            // here sonoo is database name, root is username and password
            String sql = "insert into emp(name, age) values(?, ?)";
            long startTime = System.currentTimeMillis();
            for (int i = 0; i < 5000; i++) {
                PreparedStatement ps = con.prepareStatement(sql);
                Random rnd = new Random();
                int age = rnd.nextInt(60);
                byte [] name = new byte[30];
                rnd.nextBytes(name);
                ps.setString(1, name.toString());
                ps.setInt(2, age);
                ps.executeUpdate();
                System.out.println(i);
            }
            long endTime = System.currentTimeMillis();
            System.out.println("taken time: " + (endTime - startTime));
            con.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

and the output is:

taken time: 315309

Upvotes: 0

Views: 395

Answers (1)

Elliott Frisch
Elliott Frisch

Reputation: 201507

First, and easiest, prepare the PreparedStatement once before the loop. Like,

PreparedStatement ps = con.prepareStatement(sql);
for (int i = 0; i < 5000; i++) {
    Random rnd = new Random();
    int age = rnd.nextInt(60);
    byte[] name = new byte[30];
    rnd.nextBytes(name);
    ps.setString(1, name.toString());
    ps.setInt(2, age);
    ps.executeUpdate();
    System.out.println(i);
}

Second, if that is still not fast enough, use batching. Like,

PreparedStatement ps = con.prepareStatement(sql);
for (int i = 0; i < 5000; i++) {
    Random rnd = new Random();
    int age = rnd.nextInt(60);
    byte[] name = new byte[30];
    rnd.nextBytes(name);
    ps.setString(1, name.toString());
    ps.setInt(2, age);
    ps.addBatch();
    System.out.println(i);
}
ps.executeBatch();

BTW, use Arrays.toString(name); (you're currently storing a hashcode).

Upvotes: 3

Related Questions