SQL 使用休眠从数据库中获取下一个序列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6386888/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
get next sequence value from database using hibernate
提问by iliaden
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.
我有一个实体，它有一个必须从序列中设置的非 ID 字段。目前，我获取序列的第一个值，将其存储在客户端，并根据该值进行计算。
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 ).
但是，这种方式会显着降低性能（创建 ~5000 个对象会减慢 3 倍 - 从 5740ms 到 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).
但是，这种方法也不起作用（返回的所有 ID 均为 0）。
Can someone advise me how to fetch the next sequence value using Hibernate efficiently?
有人可以建议我如何有效地使用 Hibernate 获取下一个序列值吗？
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 somehowmanages to reduce the number of fetches when accessing the sequence described by @GeneratedValue.
编辑：经过调查，我发现，调用Query query = session.createSQLQuery( "select nextval('mySequence')" );远远大于使用更低效@GeneratedValue-因为Hibernate的某种方式设法减少取的次数访问顺序来描述时@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.
例如，当我创建 70,000 个实体时（因此从同一序列中提取了 70,000 个主键），我得到了我需要的一切。
HOWEVER, Hibernate only issues 1404select nextval ('local_key_sequence')commands. NOTE: On the database side, the caching is set to 1.
然而，Hibernate 只发出1404 个select nextval ('local_key_sequence')命令。注意：在数据库端，缓存设置为 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?
如果我尝试手动获取所有数据，将需要 70,000 次选择，因此性能差异很大。有谁知道 Hibernate 的内部功能，以及如何手动重现它？
采纳答案by iliaden
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() ;
}
}
回答by Mike
Here is what worked for me (specific to Oracle, but using scalarseems to be the key)
这是对我有用的（特定于 Oracle，但使用scalar似乎是关键）
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
感谢这里的海报：springsource_forum
回答by Punit Patel
You can use Hibernate Dialect API for Database independence as follow
您可以使用 Hibernate Dialect API 实现数据库独立性，如下所示
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;
}
}
回答by Olaf
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.
如果您使用 Oracle，请考虑为序列指定缓存大小。如果您经常批量创建 5K 的对象，您可以将其设置为 1000 或 5000。我们为用于代理主键的序列进行了设置，并惊讶于用 Java 手写的 ETL 过程的执行时间下降了一半。
I could not paste formatted code into comment. Here's the sequence DDL:
我无法将格式化的代码粘贴到评论中。这是序列 DDL：
create sequence seq_mytable_sid
minvalue 1
maxvalue 999999999999999999999999999
increment by 1
start with 1
cache 1000
order
nocycle;
回答by Bohemian
To get the new id, all you have to do is flushthe entity manager. See getNext()method below:
要获得新的 id，您所要做的就是flush实体管理器。请参见getNext()下面的方法：
@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();
}
}
回答by E L
POSTGRESQL
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
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();
回答by M46
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 转换为查询”。--> 因此我的解决方案如下：
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 + ")");
回答by eztam
Spring 5 has some builtin helper classes for that: org/springframework/jdbc/support/incrementer
Spring 5 有一些内置的帮助类： org/springframework/jdbc/support/incrementer
回答by razvanone
Your idea with the SequenceGenerator fake entity is good.
您对 SequenceGenerator 假实体的想法很好。
@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.
使用带有键名“sequence_name”的参数很重要。在休眠类 SequenceStyleGenerator 上运行调试会话，在 final QualifiedName sequenceName =determineSequenceName( params, dialect, jdbcEnvironment ); 行的 configure(...) 方法；查看有关 Hibernate 如何计算序列名称的更多详细信息。您也可以使用一些默认值。
After the fake entity, I created a CrudRepository:
在伪造实体之后，我创建了一个 CrudRepository：
public interface SequenceRepository extends CrudRepository<SequenceGenerator, Long> {}
In the Junit, I call the save method of the SequenceRepository.
在 Junit 中，我调用了 SequenceRepository 的 save 方法。
SequenceGenerator sequenceObject = new SequenceGenerator(); SequenceGenerator result = sequenceRepository.save(sequenceObject);
SequenceGenerator sequenceObject = new SequenceGenerator(); SequenceGenerator 结果 = 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".
如果有更好的方法来做到这一点（也许支持任何类型的字段上的生成器，而不仅仅是 Id），我会很乐意使用它而不是这个“技巧”。
回答by John Tumminaro
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);
