Spring数据JPA批量插入非常慢

Question

我正在尝试读取包含700K条记录的Excel文件，并将这些记录批量插入MySQL数据库表中。

请注意，Excel解析速度很快，我可以在50秒左右的时间内将实体对象放入ArrayList中。

我使用Spring Boot和Spring数据JPA。

下面是我的部分应用程序。属性文件：

hibernate.jdbc.batch_size=1000
spring.jpa.hibernate.use-new-id-generator-mappings=true

以及我的部分实体类：

@Entity
@Table(name = "WHT_APPS", schema = "TEST")
public class WHTApps {

    @Id
    @TableGenerator(name = "whtAppsGen", table = "ID_GEN", pkColumnName = "GEN_KEY", valueColumnName = "GEN_VAL")
    @GeneratedValue(strategy = GenerationType.TABLE, generator = "whtAppsGen")
    private Long id;

    @Column(name = "VENDOR_CODE")
    private int vendorCode;
    .
    .
    .
    .

以下是我的 DAO：

@Repository
@Transactional
public class JapanWHTDaoImpl implements JapanWHTDao {

    @Autowired
    JapanWHTAppsRepository appsRepo;

    @Override
    public void storeApps(List<WHTApps> whtAppsList) {
        appsRepo.save(whtAppsList);

    }

下面是存储库类：

@Transactional
public interface JapanWHTAppsRepository extends JpaRepository<WHTApps, Long> {

}

有人能告诉我我在这里做了什么不正确的事情吗？

编辑:

进程未完成并最终抛出错误：-

2017-08-15 15:15:24.516  WARN 14710 --- [tp1413491716-17] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08S01
2017-08-15 15:15:24.516 ERROR 14710 --- [tp1413491716-17] o.h.engine.jdbc.spi.SqlExceptionHelper   : Communications link failure

The last packet successfully received from the server was 107,472 milliseconds ago.  The last packet sent successfully to the server was 107,472 milliseconds ago.
2017-08-15 15:15:24.518  INFO 14710 --- [tp1413491716-17] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2017-08-15 15:15:24.525  WARN 14710 --- [tp1413491716-17] c.m.v.c3p0.impl.DefaultConnectionTester  : SQL State '08007' of Exception tested by statusOnException() implies that the database is invalid, and the pool should refill itself with fresh Connections.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_131]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_131]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_131]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_131]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    .
    .
    .
    .
    2017-08-15 15:15:24.526  WARN 14710 --- [tp1413491716-17] c.m.v2.c3p0.impl.NewPooledConnection     : [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
2017-08-15 15:15:24.527  WARN 14710 --- [tp1413491716-17] c.m.v2.c3p0.impl.NewPooledConnection     : [c3p0] Another error has occurred [ com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown. ] which will not be reported to listeners!

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_131]

谢谢

Answer 1

我还要指出一点。问题可能不仅在于hibernate，还在于DB。

当您在一个事务中插入700k个对象时，它可以存储在DB的回滚段中，等待事务提交。

如果可能的话，将逻辑分割成中间提交。

从主列表创建1k大小的子列表，保存子列表并在每次保存子列表后提交。