37

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?

Improve this question

11 Answers 11

Reset to default
31

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

}
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This is lovely, except that
+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!
Actually, I do have to add that this uses dialect.getSequenceNextValString(), which will not work for databases not supporting sequences...
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 at the time of this comment. So don't get the sequence name from user input, which is pretty unlikely anyways.
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

Improve this answer

2 Comments

As a note, you can also use StandardBasicTypes.LONG instead of StandardBasicTypes.BIG_INTEGER...
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...
6

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

}
Improve this answer

1 Comment

Just a note: As the class indicates this works on PostgreSQL only, because the SQL syntax is referent from Oracle.
4

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

Improve this answer

1 Comment

this does not seem to have any caching of any sort, so 1 roundtrip to db with each increment...
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;
Improve this answer

6 Comments

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: I specified cache size in the sequence DDL. I couldn't paste code into the comment, so I edited the answer.
@Olaf: cache 1000 seems to be the needed field, but how can I map it in Hibernate?
@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: I've investigated a bit deeper. And discovered some black voodoo magic. See the edited question
|
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();
    }
}
Improve this answer

3 Comments

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
The SequenceFether was an extra class approach, but hibernate forcefully maps it to a [non-existent] table.
"em.flush()" gave me an error. I removed it and it worked just fine. Thank you!
1

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();
Improve this answer

1 Comment

Your theory is sound, and it did help me, but your SQL is vulnerable to injection. That's never good.
1

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);
Improve this answer

Comments

1

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 + ")");
Improve this answer

Comments

0

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".

Improve this answer

Comments

0

For me with postgres, this worked fine:


Long nextValue = session.createNativeQuery("select nextval('seq_name')", Long.class)
                .getSingleResult()
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.