get next sequence value from database using hibernate

Question

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?

Answer 1

You can use Hibernate Dialect API for Database independence as follow

class SequenceValueGetter {     private SessionFactory sessionFactory;      // For Hibernate 3     public Long getId(final String sequenceName) {         final List<Long> ids = new ArrayList<Long>(1);          sessionFactory.getCurrentSession().doWork(new Work() {             public void execute(Connection connection) throws SQLException {                 DialectResolver dialectResolver = new StandardDialectResolver();                 Dialect dialect =  dialectResolver.resolveDialect(connection.getMetaData());                 PreparedStatement preparedStatement = null;                 ResultSet resultSet = null;                 try {                     preparedStatement = connection.prepareStatement( dialect.getSequenceNextValString(sequenceName));                     resultSet = preparedStatement.executeQuery();                     resultSet.next();                     ids.add(resultSet.getLong(1));                 }catch (SQLException e) {                     throw e;                 } finally {                     if(preparedStatement != null) {                         preparedStatement.close();                     }                     if(resultSet != null) {                         resultSet.close();                     }                 }             }         });         return ids.get(0);     }      // For Hibernate 4     public Long getID(final String sequenceName) {         ReturningWork<Long> maxReturningWork = new ReturningWork<Long>() {             @Override             public Long execute(Connection connection) throws SQLException {                 DialectResolver dialectResolver = new StandardDialectResolver();                 Dialect dialect =  dialectResolver.resolveDialect(connection.getMetaData());                 PreparedStatement preparedStatement = null;                 ResultSet resultSet = null;                 try {                     preparedStatement = connection.prepareStatement( dialect.getSequenceNextValString(sequenceName));                     resultSet = preparedStatement.executeQuery();                     resultSet.next();                     return resultSet.getLong(1);                 }catch (SQLException e) {                     throw e;                 } finally {                     if(preparedStatement != null) {                         preparedStatement.close();                     }                     if(resultSet != null) {                         resultSet.close();                     }                 }              }         };         Long maxRecord = sessionFactory.getCurrentSession().doReturningWork(maxReturningWork);         return maxRecord;     }  } 

Answer 2

Here is what worked for me (specific to Oracle, but using scalar seems to be the key)

Long getNext() {
    Query query = 
        session.createSQLQuery("select MYSEQ.nextval as num from dual")
            .addScalar("num", StandardBasicTypes.BIG_INTEGER);

    return ((BigInteger) query.uniqueResult()).longValue();
}

Thanks to the posters here: springsource_forum

Answer 3

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

}