java - 刷新 Hibernate session 需要很长时间

Question

我正在使用 Hibernate 5.2.8.Final 版本，我们有一个要求，即从数据库读取数百万数据并通过某些业务逻辑更新数据，因为我的数据库很大，我想在达到批量大小后提交数据所以我写了下面的代码

Session session = HibernateUtil.getSessionFactory().getCurrentSession();
session.getTransaction().begin();
Query<Object> query = session.createQuery(SQL, Object.class);
ScrollableResults scrollableResults = query.setCacheMode(CacheMode.IGNORE)
        .scroll(ScrollMode.FORWARD_ONLY);
int count = 0;
while (scrollableResults.next())
{
    Object object = (Object) scrollableResults.get(0);
    process(object)
    session.update(object);
    if (++count % batchSizeDeclare== 0)
    {
        session.flush();
        session.clear();
        LOGGER.info("Updated batch records");
    }
}
session.getTransaction().commit();
LOGGER.info("commited in Hibernate ");
}

下面是我的 hibernate.cfg.xml 文件

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!-- Database connection settings -->
        <property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
        <property name="connection.url">jdbc:sqlserver://com;database=DEV</property>
        <property name="connection.username">user</property>
        <property name="connection.password">pass</property>

        <property name="hibernate.default_schema">schema</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">5</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.SQLServer2012Dialect</property>

        <!-- Disable the second-level cache -->
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <!-- <property name="show_sql">true</property> -->
        <!-- <property name="format_sql">true</property> -->

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">update</property>

        <!-- org.hibernate.HibernateException: No CurrentSessionContext configured! -->
        <property name="hibernate.current_session_context_class">thread</property>

        <property name="hibernate.jdbc.batch_size">100</property>

        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_size">20</property>
        <property name="hibernate.c3p0.timeout">300</property>
        <property name="hibernate.c3p0.max_statements">50</property>
        <property name="hibernate.c3p0.idle_test_period">3000</property>

        <mapping class="com.beans.Object" />

    </session-factory>
</hibernate-configuration>

下面是我的Object.java

 public class Object implements Serializable
    {
        private static final long serialVersionUID = 1L;

        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "ID", nullable = false, unique = true, updatable = false)
        private int id;
        private String filePath;

    private String fileName;
    private String mimeType;
    private double fileSize;
    // removed getters and setters

    }

一旦我的代码到达 session.flush()，即使等待 30 分钟，它也不会执行任何操作。这是批量提交的正确方法吗？如何批量更新？

Answer 1

Once my code reached session.flush it is not doing anything evenafter waiting for 30 min.

相反，数据库似乎做得太多了。只是您看不到任何进展，因为数据库正在努力应对您提交的大量工作。

Is this the correct way to batch commit?

简短的回答是否。

您不必从数据库中获取数百万行。您有更好的选择:

1. 您可以在数据库中进行处理，这样您就不必付出提取数据并通过网络发送数据的代价，而只需在 Java 中进行处理即可。
2. 如果您无法在数据库中处理它，那么您需要使用一次仅获取小块数据的批处理处理器。这样，您甚至可以parallelize the batch processing ，这应该会减少总体处理时间。