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?

score 30 · Answer 1 · edited Apr 27 '16 at 14:52

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

}

+1 I think this is the only database-agnostic answer posted, which is what I need. (We've got an application using Hibernate 4 that needs to fetch a non-id value from a db sequence, using HSQLDB for tests and Oracle for the real deal.) Using java 7's try-with-resources you can shorten the code a bit. I did have to use a `DatabaseMetaDataDialectResolutionInfoAdapter` to wrap the result of `connection.getMetaData()` before passing it to `resolveDialect()`. @punitpatel thanks! — Ricardo van den Broek, Sep 16 '15 at 18:46
Actually, I do have to add that this uses `dialect.getSequenceNextValString()`, which will not work for databases not supporting sequences... — Ricardo van den Broek, Dec 15 '15 at 00:40
You might want to use `doReturningWork` (which may be more recent than 3) also, `getSequenceNextValString(...)` does not quote, or otherwise verify the sequence name, and is [vulnerable to injection](https://hibernate.atlassian.net/browse/HHH-11590) at the time of this comment. So don't get the sequence name from user input, which is pretty unlikely anyways. — xenoterracide, Mar 22 '17 at 15:50

score 25 · Answer 2 · edited Jan 04 '14 at 12:24

25

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

edited Jan 04 '14 at 12:24

Trygve Laugstøl

7,440
2
36
40

answered Jan 20 '12 at 23:32

Mike

257
3
3

3

As a note, you can also use StandardBasicTypes.LONG instead of StandardBasicTypes.BIG_INTEGER... – rogerdpack Jun 25 '13 at 22:32
Apprently you don't "need" the `addScalar` part (hibernate will return an Integer or BigInteger depending on what your DB sequence underlying type is), but since it may vary between DB's (and sequences), why not guarantee it always return a long, as this does... – rogerdpack Oct 20 '20 at 17:19

score 6 · Accepted Answer · answered Jun 20 '11 at 13:15

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

}

Just a note: As the class indicates this works on PostgreSQL only, because the SQL syntax is referent from Oracle. — Paulo Fidalgo, Aug 07 '13 at 15:10

Olaf · Answer 4 · 2011-06-17T14:20:00.553

3

If you are using Oracle, consider specifying cache size for the sequence. If you are routinely create objects in batches of 5K, you can just set it to a 1000 or 5000. We did it for the sequence used for the surrogate primary key and were amazed that execution times for an ETL process hand-written in Java dropped in half.

I could not paste formatted code into comment. Here's the sequence DDL:

create sequence seq_mytable_sid 
minvalue 1 
maxvalue 999999999999999999999999999 
increment by 1 
start with 1 
cache 1000 
order  
nocycle;

edited Jun 17 '11 at 14:20

answered Jun 17 '11 at 14:02

Olaf

6,249
1
19
37

that sounds like an interesting approach. Can you tell me what annotations you used to set the batch creation (and thus the batch fetching of sequence values)? – iliaden Jun 17 '11 at 14:13
@iliaden: I specified cache size in the sequence DDL. I couldn't paste code into the comment, so I edited the answer. – Olaf Jun 17 '11 at 14:20
@Olaf: `cache 1000` seems to be the needed field, but how can I map it in Hibernate? – iliaden Jun 17 '11 at 14:42
@iliaden: If your previous strategy is working, just slow, the change to the sequence definition should do the magic. In our case we haven't had to change anything in the app, or do a new build. – Olaf Jun 17 '11 at 14:52
@ Olaf: I've investigated a bit deeper. And discovered some black voodoo magic. See the edited question – iliaden Jun 17 '11 at 16:00
@iliaden: I see. My experience was for the sequences used to generate a surrogate primary key. – Olaf Jun 17 '11 at 16:45

score 2 · Answer 5 · answered Jun 17 '11 at 14:05

2

To get the new id, all you have to do is flush the entity manager. See getNext() method below:

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

    public long getId() {
        return id;
    }

    public static long getNext(EntityManager em) {
        SequenceFetcher sf = new SequenceFetcher();
        em.persist(sf);
        em.flush();
        return sf.getId();
    }
}

answered Jun 17 '11 at 14:05

Bohemian

412,405
93
575
722

1

that's the problem - the sequence is NOT for a primary key (NOT the ID field). Although I will investigate on the use of `SequenceFetcher` – iliaden Jun 17 '11 at 14:13
1

The SequenceFether was an extra class approach, but hibernate forcefully maps it to a [non-existent] table. – iliaden Jun 17 '11 at 14:18
"em.flush()" gave me an error. I removed it and it worked just fine. Thank you! – roneo Jun 06 '16 at 12:35

E L · Answer 6 · 2013-12-04T13:06:26.677

POSTGRESQL

String psqlAutoincrementQuery = "SELECT NEXTVAL(CONCAT(:psqlTableName, '_id_seq')) as id";

Long psqlAutoincrement = (Long) YOUR_SESSION_OBJ.createSQLQuery(psqlAutoincrementQuery)
                                                      .addScalar("id", Hibernate.LONG)
                                                      .setParameter("psqlTableName", psqlTableName)
                                                      .uniqueResult();

MYSQL

String mysqlAutoincrementQuery = "SELECT AUTO_INCREMENT as id FROM information_schema.tables WHERE table_name = :mysqlTableName AND table_schema = DATABASE()";

Long mysqlAutoincrement = (Long) YOUR_SESSION_OBJ.createSQLQuery(mysqlAutoincrementQuery)
                                                          .addScalar("id", Hibernate.LONG)
                                                          .setParameter("mysqlTableName", mysqlTableName)                                                              
                                                          .uniqueResult();

Your theory is sound, and it did help me, but your SQL is vulnerable to injection. That's never good. — Makoto, Nov 20 '13 at 17:43

score 1 · Answer 7 · answered Aug 04 '16 at 08:04

Interesting it works for you. When I tried your solution an error came up, saying that "Type mismatch: cannot convert from SQLQuery to Query". --> Therefore my solution looks like:

SQLQuery query = session.createSQLQuery("select nextval('SEQUENCE_NAME')");
Long nextValue = ((BigInteger)query.uniqueResult()).longValue();

With that solution I didn't run into performance problems.

And don't forget to reset your value, if you just wanted to know for information purposes.

    --nextValue;
    query = session.createSQLQuery("select setval('SEQUENCE_NAME'," + nextValue + ")");

score 1 · Answer 8 · answered Jul 16 '19 at 15:44

1

Spring 5 has some builtin helper classes for that: org/springframework/jdbc/support/incrementer

answered Jul 16 '19 at 15:44

eztam

3,443
7
36
54

this does not seem to have any caching of any sort, so 1 roundtrip to db with each increment... – Martin Mucha May 09 '23 at 12:57

score 0 · Answer 9 · answered Mar 10 '16 at 18:52

Here is the way I do it:

@Entity
public class ServerInstanceSeq
{
    @Id //mysql bigint(20)
    @SequenceGenerator(name="ServerInstanceIdSeqName", sequenceName="ServerInstanceIdSeq", allocationSize=20)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="ServerInstanceIdSeqName")
    public Long id;

}

ServerInstanceSeq sis = new ServerInstanceSeq();
session.beginTransaction();
session.save(sis);
session.getTransaction().commit();
System.out.println("sis.id after save: "+sis.id);

score 0 · Answer 10 · answered Aug 10 '17 at 15:12

Your idea with the SequenceGenerator fake entity is good.

@Id
@GenericGenerator(name = "my_seq", strategy = "sequence", parameters = {
        @org.hibernate.annotations.Parameter(name = "sequence_name", value = "MY_CUSTOM_NAMED_SQN"),
})
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "my_seq")

It is important to use the parameter with the key name "sequence_name". Run a debugging session on the hibernate class SequenceStyleGenerator, the configure(...) method at the line final QualifiedName sequenceName = determineSequenceName( params, dialect, jdbcEnvironment ); to see more details about how the sequence name is computed by Hibernate. There are some defaults in there you could also use.

After the fake entity, I created a CrudRepository:

public interface SequenceRepository extends CrudRepository<SequenceGenerator, Long> {}

In the Junit, I call the save method of the SequenceRepository.

SequenceGenerator sequenceObject = new SequenceGenerator(); SequenceGenerator result = sequenceRepository.save(sequenceObject);

If there is a better way to do this (maybe support for a generator on any type of field instead of just Id), I would be more than happy to use it instead of this "trick".

get next sequence value from database using hibernate

10 Answers10

Linked