java刷新Hibernate会话需要很长时间

Question

我正在使用Hibernate 5.2.8。最终版本，我们需要从数据库中读取数百万数据，并通过一些业务逻辑更新数据，因为我的数据库很大，我希望在达到batchsize后提交数据，所以我编写了以下代码

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

    }

一旦我的代码到达会话。flush（）即使等待30分钟，它也不会做任何事情。这是批量提交的正确方法吗？如何批量更新

Answer 1

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

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

Is this the correct way to batch commit?

简而言之，答案是否定的

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

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