使用休眠从数据库中获取下一个序列值 [英] get next sequence value from database using hibernate

问题描述

我有一个实体，它有一个必须从序列中设置的非 ID 字段.目前，我获取序列的第一个值，将其存储在客户端，然后根据该值进行计算.

I have an entity that has an NON-ID field that must be set from a sequence. Currently, I fetch for the first value of the sequence, store it on the client's side, and compute from that value.

但是，我正在寻找一种更好"的方法来做到这一点.我已经实现了一种获取下一个序列值的方法:

However, I'm looking for a "better" way of doing this. I have implemented a way to fetch the next sequence value:

public Long getNextKey()
{
    Query query = session.createSQLQuery( "select nextval('mySequence')" );
    Long key = ((BigInteger) query.uniqueResult()).longValue();
    return key;
}

然而，这种方式会显着降低性能(创建约 5000 个对象的速度减慢了 3 倍 - 从 5740 毫秒到 13648 毫秒).

However, this way reduces the performance significantly (creation of ~5000 objects gets slowed down by a factor of 3 - from 5740ms to 13648ms ).

我试图添加一个假"实体:

I have tried to add a "fake" entity:

@Entity
@SequenceGenerator(name = "sequence", sequenceName = "mySequence")
public class SequenceFetcher
{
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequence")
    private long                      id;

    public long getId() {
        return id;
    }
}

但是这种方法也不起作用(返回的所有 ID 均为 0).

However this approach didn't work either (all the Ids returned were 0).

有人可以告诉我如何有效地使用 Hibernate 获取下一个序列值吗?

Can someone advise me how to fetch the next sequence value using Hibernate efficiently?

经过调查，我发现调用 Query query = session.createSQLQuery( "select nextval('mySequence')" ); 效率低得多而不是使用 @GeneratedValue - 因为 Hibernate 以某种方式 在访问 @GeneratedValue 描述的序列时设法减少了获取次数.

Upon investigation, I have discovered that calling Query query = session.createSQLQuery( "select nextval('mySequence')" ); is by far more inefficient than using the @GeneratedValue- because of Hibernate somehow manages to reduce the number of fetches when accessing the sequence described by @GeneratedValue.

例如，当我创建 70,000 个实体时(因此从同一序列中提取了 70,000 个主键)，我得到了我需要的一切.

For example, when I create 70,000 entities, (thus with 70,000 primary keys fetched from the same sequence), I get everything I need.

然而，Hibernate 只发出 1404 select nextval ('local_key_sequence') 命令.注意:在数据库端，缓存设置为 1.

HOWEVER , Hibernate only issues 1404 select nextval ('local_key_sequence') commands. NOTE: On the database side, the caching is set to 1.

如果我尝试手动获取所有数据，将需要 70,000 次选择，因此性能差异很大.有谁知道Hibernate的内部功能，以及如何手动重现?

If I try to fetch all the data manually, it will take me 70,000 selects, thus a huge difference in performance. Does anyone know the internal functioning of Hibernate, and how to reproduce it manually?

推荐答案

我找到了解决方案:

public class DefaultPostgresKeyServer
{
    private Session session;
    private Iterator<BigInteger> iter;
    private long batchSize;

    public DefaultPostgresKeyServer (Session sess, long batchFetchSize)
    {
        this.session=sess;
        batchSize = batchFetchSize;
        iter = Collections.<BigInteger>emptyList().iterator();
    }

        @SuppressWarnings("unchecked")
        public Long getNextKey()
        {
            if ( ! iter.hasNext() )
            {
                Query query = session.createSQLQuery( "SELECT nextval( 'mySchema.mySequence' ) FROM generate_series( 1, " + batchSize + " )" );

                iter = (Iterator<BigInteger>) query.list().iterator();
            }
            return iter.next().longValue() ;
        }

}

这篇关于使用休眠从数据库中获取下一个序列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！