java - mysql批量更新限制

Question

SimpleJdbcTemplate 中的batchUpdate() 函数是否有最大记录限制。友情链接 here .

我需要一次插入大约 100,000 条记录。目前我在以下行中超时

MapSqlParameterSource[] batchArgs = batchArguements.toArray(new MapSqlParameterSource[0]);
simpleJdbcTemplate.batchUpdate(SQL_INSERT, batchArgs);

有什么建议吗？

Answer 1

这是我们使用的类。有关解释，请参阅 Javadoc:

import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import com.google.common.base.Function;

/**
 * Performs a MySQL bulk insert.
 * <p>
 * A MySQL bulk insert statement allows for performing multiple insertions with
 * one insert statement:
 * 
 * <pre>
 *     INSERT INTO tbl (col1, col2, ..., colN) VALUES
 *        (val1.1, val1.2, ..., val1.N),
 *        (val2.1, val2.2, ..., val2.N),
 *           ...,
 *        (valM.1, valM.2, ..., valM.N);
 * </pre>
 * </p>
 * 
 * @param <T> the entity type to work on.
 */
public class MySqlBulkInsert<T extends Object> {
  private final EntityManager entityManager;
  private final String tableName;

  /**
   * Constructor.
   * 
   * @param entityManager the entity manager
   * @param tableName the name of the table
   */
  public MySqlBulkInsert(final EntityManager entityManager, final String tableName) {
    this.entityManager = entityManager;
    this.tableName = tableName;
  }

  /**
   * Executes the bulk insert.
   * 
   * @param entities the entities to insert
   * @param csvColumns the columns to insert as a comma separated values string
   * @param entityToValuesFunction the entity to values function.
   * Please note: Values have to be correct formatted. For example String values
   * must contain apostrophes.
   * @return the number of records inserted
   */
  public int execute(List<T> entities,
      String csvColumns,
      Function<T, Object[]> entityToValuesFunction) {
    return execute(entities, csvColumns.split("\\s*,\\s*"), entityToValuesFunction);
  }

  /**
   * Executes the bulk insert.
   * 
   * @param entities the entities to insert
   * @param columns the columns to insert
   * @param entityToValuesFunction the entity to values function
   * Please note: Values have to be correct formatted. For example String values
   * must contain apostrophes.
   * @return the number of records inserted
   */
  public int execute(List<T> entities,
      String[] columns,
      Function<T, Object[]> entityToValuesFunction) {
    if (CollectionUtils.isEmpty(entities)) {
      return 0;
    }

    final StringBuilder sql = new StringBuilder();
    sql.append("insert into ");
    sql.append(tableName);
    sql.append(" (");
    sql.append(StringUtils.join(columns, ','));
    sql.append(") values ");
    List<String> records = new ArrayList<String>();
    for (T entity : entities) {
      Object[] values = entityToValuesFunction.apply(entity);
      String csvValues = StringUtils.join(values, ',');
      if (columns.length != values.length) {
        throw new IllegalArgumentException("Number of values doesn't match number of columns ("
            + columns.length + "): " + csvValues);
      }
      records.add("(" + csvValues + ")");
    }
    sql.append(StringUtils.join(records, ','));
    Query query = entityManager.createNativeQuery(sql.toString());
    return query.executeUpdate();
  }
}