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

User · Answer

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

User · Answer

I found the solution   public class DefaultPostgresKeyServer       private Session session      private Iterator lt BigInteger gt  iter      private long batchSize       public DefaultPostgresKeyServer  Session sess  long batchFetchSize                this session sess          batchSize   batchFetchSize          iter   Collections  lt BigInteger gt 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 lt BigInteger gt   query list   iterator                              return iter next   longValue

User · Answer

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 lt SequenceGenerator  Long gt       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

User · Answer

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

User · Answer

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

User · Answer

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 lt Long gt  ids   new ArrayList lt Long gt  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 lt Long gt  maxReturningWork   new ReturningWork lt Long gt                   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] get next sequence value from database using hibernate

Examples related to sql

Examples related to hibernate

Examples related to sequence

Examples related to nextval