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

问题描述

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

然而，我正在寻找一种更好的方式来做到这一点。我已经实现了获取下一个序列值的方法：

  public Long getNextKey（）
 {
 Query query = session.createSQLQuery（select nextval（'mySequence'））; 
 Long key =（（BigInteger）query.uniqueResult（））。longValue（）; 
返回键; 
} 
  

然而，这种方式显着降低了性能（创建〜5000个对象变得缓慢下降了3倍 - 从5740ms到13648ms）。

我试图添加一个假实体：

  @Entity 
 @SequenceGenerator（name =sequence，sequenceName =mySequence）
 public class SequenceFetcher 
 {
 @I $ 
 @GeneratedValue（strategy = GenerationType.SEQUENCE，generator =sequence）
私人长ID; 
 
 public long getId（）{
 return id; 
 
 
 
 
 
 
 $ b 
然而这种方法并不奏效
 
 
有人可以告诉我如何有效地使用Hibernate获取下一个序列值吗？
 
 
 
 编辑：经调查，我发现调用 Query query = session.createSQLQuery（select nextval（'mySequence'））; 是比使用 @GeneratedValue 更低效 - 因为Hibernate 不知何故设法在访问<$ c描述的序列时减少获取次数$ C> @GeneratedValue 。 
 
 
 
例如，当我创建70,000个实体时（因此具有从同一序列中获取的70,000个主键），我得到了我需要的一切。 b 
 
  HOWEVER ，Hibernate只发布 1404   select nextval（'local_key_sequence'）命令。注意：在数据库端，缓存设置为1. 
 
 
 
如果我尝试手动提取所有数据，它将花费我7万次选择，因此有很大的区别在表现。有没有人知道Hibernate的内部功能，以及如何手动重现它？  解决方案

我找到了解决方案：

  public class DefaultPostgresKeyServer 
 {
 private Session session; 
私人迭代器< BigInteger> ITER; 
私有long batchSize; 
 
 public DefaultPostgresKeyServer（Session sess，long batchFetchSize）
 {
 this.session = sess; 
 batchSize = batchFetchSize; 
 iter = Collections。< BigInteger> emptyList（）。iterator（）; 
 
 $ b $ @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（）; 
} 
返回iter.next（）。longValue（）; 
} 
 
} 
  

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;
}

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;
    }
}

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

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

Edit: 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.

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

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

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?

解决方案

I found the solution:

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() ;
        }

}

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