Bulk insertion of values in MySQL

Question

I am trying to take the output of a JAVA program consisting of an array of 12 for a bunch of different users and place them into a table. Is there anyway to do this in bulk, without having to do it one at a time?

Answer 1

I've done it like so:

INSERT INTO my_users (name, passwd, somefield)
VALUES 
 ('joe', 'pass', 'abc'),
 ('jeff', 'pass1', 'abcd'),
 ('jake', 'pass2', 'abcde')

Answer 2

INSERT allows you to insert multiple values at once:

INSERT
INTO
    table
    (column_a, column_b, column_c)
VALUES
    (1, 2, 3),
    (4, 5, 6),
    (7, 8, 9)
;

This is standard SQL, and should work on most databases. (There are a few exceptions I've come across: outdated DBs and MSSQL, but you're on MySQL, which supports this.)

Answer 3

Could always have the data written to a file, and use the LOAD DATA INFILE statement to bulk read them into the database. But depending on the scale, it might not be worth it worrying about speed here.

Answer 4

Use batch insert. Adapting from http://www.roseindia.net/jdbc/jdbc-mysql/PreparedStatementBatchUpdate.shtml :

  Connection conn;
  User[] users;

  PreparedStatement prest = con.prepareStatement("INSERT INTO users VALUES(?,?)");

  conn.setAutoCommit(false);
  for(user : users) {
      prest.setString(1, User.getUserName());
      prest.setInt(2, User.getAge());
  }
  prest.addBatch();

  int count[] = prest.executeBatch();

  conn.setAutoCommit(true);
  prest.close();

In the real world, you will want to sprinkle this with a bunch of try..finally blocks, but I will assume you know or can find elsewhere how to do that.