oracle 不使用数据库生成主键

Generate Primary key without using Database

提问by Vicky

I came across a question recently that was for "Generating primary key in a clustered environment of 5 App-Servers - [OAS Version 10] without using database".

我最近遇到了一个问题，即“在 5 个应用程序服务器的集群环境中生成主键 - [OAS 版本 10] 而不使用数据库”。

Usually we generate PK by a DB sequence, or storing the values in a database table and then using a SP to generate the new PK value...However current requirement is to generate primary key for my application without referencing the database using JDK 1.4.

通常我们通过 DB 序列生成 PK，或者将值存储在数据库表中，然后使用 SP 生成新的 PK 值……但是当前的要求是为我的应用程序生成主键，而不使用 JDK 1.4 引用数据库。

Need expert's help to arrive on better ways to handle this.

需要专家的帮助才能找到更好的方法来处理这个问题。

Thanks,

谢谢，

回答by Randolpho

Use a UUIDas your primary key and generateit client-side.

使用UUID作为主键并在客户端生成它。

Edit:
Since your comment I felt I should expand on why this is a good way to do things.

编辑：
由于您的评论，我觉得我应该扩展为什么这是做事的好方法。

Although sequential primary keys are the most common in databases, using a randomly generated primary key is frequently the best choice for distributed databases or (particularly) databases that support a "disconnected" user interface, i.e. a UI where the user is not continuously connected to the database at all times.

尽管顺序主键在数据库中最常见，但对于分布式数据库或（特别是）支持“断开连接”用户界面（即用户未持续连接到的 UI）的数据库，使用随机生成的主键通常是最佳选择。任何时候都在数据库中。

UUIDs are the best form of randomly generated key since they are guaranteed to be very unique; the likelyhood of the same UUID being generated twice is so extremely low as to be almost completely impossible. UUIDs are also ubiquitous; nearly every platform has support for the generation of them built in, and for those that don't there's almost always a third-party library to take up the slack.

UUID 是随机生成密钥的最佳形式，因为它们保证非常独特；同一个 UUID 被生成两次的可能性非常低，几乎完全不可能。UUID 也无处不在；几乎每个平台都支持内置它们的生成，而对于那些没有的平台，几乎总是有第三方库来填补空缺。

The biggest benefit to using a randomly generated primary key is that you can build many complex data relationships (with primary and foreign keys) on the client side and (when you're ready to save, for example) simply dump everything to the database in a single bulk insert without having to rely on post-insert steps to obtain the key for later relationship inserts.

使用随机生成的主键的最大好处是您可以在客户端构建许多复杂的数据关系（带有主键和外键），并且（例如，当您准备保存时）只需将所有内容转储到数据库中单个批量插入，而不必依赖插入后步骤来获取用于后续关系插入的密钥。

On the con side, UUIDs are 16 bytes rather than a standard 4-byte int-- 4 times the space. Is that really an issue these days? I'd say not, but I know some who would argue otherwise. The only real performance concern when it comes to UUIDs is indexing, specifically clustered indexing. I'm going to wander into the SQL Server world, since I don't develop against Oracle all that often and that's my current comfort zone, and talk about the fact that SQL Server will by default create a clustered index across all fields on the primary key of a table. This works fairly well in the auto-increment int world, and provides for some good performance for key-based lookups. Any DBA worth his salt, however, will cluster differently, but folks who don't pay attention to that clustering and who also use UUIDs (GUIDs in the Microsoft world) tend to get some nasty slowdowns on insert-heavy databases, because the clustered index has to be recomputed every insert and if it's clustered against a UUID, which could put the new key in the middle of the clustered sequence, a lotof data could potentially need to be rearranged to maintain the clustered index. This may or may not be an issue in the Oracle world -- I just don't know if Oracle PKs are clustered by default like they are in SQL Server.

另一方面，UUID 是 16 字节，而不是标准的 4 字节 int-- 4倍的空间。这真的是这些天的问题吗？我会说不是，但我知道有些人会反对。当涉及到 UUID 时，唯一真正的性能问题是索引，特别是聚集索引。我将进入 SQL Server 世界，因为我不经常针对 Oracle 进行开发，这是我目前的舒适区，并讨论 SQL Server 将在默认情况下跨所有字段创建聚集索引这一事实表的主键。这在自动增量 int 世界中非常有效，并为基于键的查找提供了一些良好的性能。然而，任何称职的 DBA 都会以不同的方式进行集群，但是那些不关注集群并且还使用 UUID（Microsoft 世界中的 GUID）的人往往会在大量插入的数据库上出现一些令人讨厌的减速，许多数据可能需要重新排列以维护聚集索引。这在 Oracle 世界中可能是也可能不是问题——我只是不知道 Oracle PK 是否像在 SQL Server 中一样默认集群。

If that run-on sentence was too hard to follow, just remember this: if you use a UUID as your primary key, do not cluster on that key!

如果那个连续的句子太难理解，请记住这一点：如果您使用 UUID 作为主键，请不要聚集在该键上！

回答by djna

You may find it helpful to look up UUID generation.

您可能会发现查找 UUID 生成很有帮助。

In the simple case, one program running one thread on each machine, you can do something such as

在简单的情况下，一个程序在每台机器上运行一个线程，你可以做一些事情，比如

MAC address + time in nanseconds since 1970.

回答by Sebastian

Take a look at these strategiesused by Hibernate (section 5.1.5 in the link). You will surely find it useful. It explains several methods, its pros and cons, also stating if they are safe in a clustered environment.

看看Hibernate 使用的这些策略（链接中的第 5.1.5 节）。你一定会发现它很有用。它解释了几种方法及其优缺点，还说明了它们在集群环境中是否安全。

Best of all, there is available code that already implements it for you :)

最重要的是，有可用的代码已经为您实现了它:)

回答by Anton Gogolev

If you cannot use database at all, GUID/UUID is the only reliable way to go. However, if you can use database occasionally, try HiLo algorithm.

如果你不能使用数据库的所有，GUID / UUID是唯一可靠的路要走。但是，如果您偶尔可以使用数据库，请尝试HiLo 算法。

回答by Riduidel

You should consider using ids in the form of UUID. Java5 has a classfor representing them (and must also have a factory to generate them). With this factory class, you can backport the code to your anticated Java 1.4 in order to have the identifiers you require.

您应该考虑使用 UUID 形式的 ID。Java5 有一个类来表示它们（并且还必须有一个工厂来生成它们）。使用这个工厂类，您可以将代码向后移植到您期待的 Java 1.4 中，以便获得您需要的标识符。

回答by gahooa

If it fits your application, you can use a larger string key coupled with a UUID() function or SHA1(of random data).

如果它适合您的应用程序，您可以使用更大的字符串密钥和 UUID() 函数或 SHA1（随机数据）。

For sequential int's, I'll leave that to another poster.

对于连续整数，我会将其留给另一张海报。

回答by Gopi

You can generate a key based on the combination of below three things

您可以根据以下三件事的组合生成密钥

1. The IP address or MAC address of machine
2. Current time
3. An incremental counter on each instance (to ensure same key does not get generated twice on one machine as time may appear same in two immediate key creations because of underlying time precision)

1. 机器的IP地址或MAC地址
2. 当前时间
3. 每个实例上的增量计数器（以确保不会在一台机器上生成两次相同的密钥，因为由于潜在的时间精度，时间可能在两个即时密钥创建中出现相同）

回答by Muhammad Saifuddin

by using Statement Object you can called statement.getGeneratedKeys(); method to retrieve the auto-generated key(s) generated by the execution of this Statement object.

通过使用 Statement 对象，您可以调用 statement.getGeneratedKeys(); 方法来检索由执行此 Statement 对象生成的自动生成的密钥。

Java doc

Java 文档

回答by TTT

Here is how it's done in MongoDB: http://www.mongodb.org/display/DOCS/Object+IDs

这是在 MongoDB 中的实现方式：http: //www.mongodb.org/display/DOCS/Object+IDs

They include a timestamp.

它们包括时间戳。

But you can also install Oracle Express and select sequences, you can select in bulk:

但是也可以安装Oracle Express并选择序列，可以批量选择：

SQL> select mysequence.nextval from dual connect by level < 20;

SQL> select mysequence.nextval from dual connect by level < 20;

NEXTVAL

下一个值

     1
     2
     3
     4
     5
    ..  
    20

Why are you not allowed to use the database? Money (Oracle express is free) or single point of failure? Or do you want to support other databases than Oracle in the future?

为什么不允许使用数据库？钱（Oracle express 是免费的）还是单点故障？或者你以后想支持Oracle以外的其他数据库吗？

回答by Farrukh Chishti

Its shipped OOB in many Spring-basedapplications like Hybris-
The typeCodeis the name of your table like, User, Address, etc.

在许多运OOB基于Spring的应用程序，如款Hybris-
该typeCode是你的表像，名字User，Address等等。

private PK generatePkForCode(final String typeCode)
    {
        final TypeInfoMap persistenceInfo = Registry.getCurrentTenant().getPersistenceManager().getPersistenceInfo(typeCode);
        return PK.createCounterPK(persistenceInfo.getItemTypeCode());
    }