aspose file tools*
The moose likes Java in General and the fly likes Generate unique numbers from a table's primary key, to be used as an external reference. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Generate unique numbers from a table Watch "Generate unique numbers from a table New topic
Author

Generate unique numbers from a table's primary key, to be used as an external reference.

shehzaad goonoo
Greenhorn

Joined: Mar 08, 2012
Posts: 4
Hello,

I need to generate a number, based on the primary key of an table, which will be used as an external reference (e.g. to label a product, or generate a barcode).
The number will be stored on database and later used to retrieve the actual record it was derived from.
The number has to be of a fixed length, say max 8 characters to minimize the length of the barcode.

Are there any APIs I can use to generate this number?

The solution need not imperatively derive the number form the primary key; I only need to ensure it will be unique across all records.

Thanks,
Shehzaad
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38353
    
  23
How can you be sure the numbers will be unique? You must ensure the number of entries is fewer than the range of the numerical type.
Why don’t you simply use the key from the table? DBMSs are designed to work out that sort of thing quickly.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38353
    
  23
And welcome to the Ranch
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7646
    
  19

Campbell Ritchie wrote:How can you be sure the numbers will be unique?

Well, if it's a primary key, the chances are that it has to be (although some databases do allow tables with no key).

@shehzaad: First off, do you actually need a number? Or will a code do?
Second: is the barcode itself a numeric code? 8 digits seems awfully short for any sort of standard barcode (eg, a UPC or SKU). If so, I'd just use it and not worry about the length (a Java long can hold numbers up to 18 digits).

Winston


Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
shehzaad goonoo
Greenhorn

Joined: Mar 08, 2012
Posts: 4
Thanks! its actually my first ever go at forums!

some precisions:
1) I need to keep the length of the reference to a minimum, so that the "barcode lines" generated are also a minimum. (I am using code-39)
I tried using a hash (md5, sha-1) of the primary key, but this generates 32-character long hex strings.
2) Any of the characters allowed by code-39 will do; I do not have to stick to purely numbers.
3) I have to stick to a java solution, not to a db-tied one.
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14107
    
  16

shehzaad goonoo wrote:The number will be stored on database and later used to retrieve the actual record it was derived from.

shehzaad goonoo wrote:I tried using a hash (md5, sha-1) of the primary key, but this generates 32-character long hex strings.

If this is one of your requirements, then using a hash code is not going to work.

Hashing algorithms such as MD5 and SHA-1 are one way. You can calculate the hash over some data, but it is impossible to get back the original data if you have only the hash. So if you need to be able to find the record back later, then that's not possible when you use a hash. You'll need some algorithm that is reversible.

Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 7 API documentation
Scala Notes - My blog about Scala
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7646
    
  19

shehzaad goonoo wrote:1) I need to keep the length of the reference to a minimum, so that the "barcode lines" generated are also a minimum. (I am using code-39)
I tried using a hash (md5, sha-1) of the primary key, but this generates 32-character long hex strings.

So, wait a minute...
Are you trying to find a way of generating a code-39 barcode that will cover your entire stock (or whatever) in 8 characters? because that's a completely different question. And the answer to that is almost certainly 'yes'.

Or are you trying to find a way of condensing a code-39 barcode to a unique 8-digit number? The answer to that is most assuredly 'NO'.

Or are you looking for something in between (like a hashcode) - an 8-digit number that is probably unique. The answer to that is, absolutely, 'YES'.

I think you need to tell us exactly what it is you want, and then move on from there.

It should probably also be pointed that "keep the length of the reference to a minimum, so that the "barcode lines" generated are also a minimum" is a very artificial (and arbitrary) restriction that has no basis in normal stock-keeping, so you might want to revise it.

Winston
shehzaad goonoo
Greenhorn

Joined: Mar 08, 2012
Posts: 4
Think it will be better if I focus on a single requirement. Lets forget about the barcode stuff.

To put it simply:

1. as input, I have a table's primary key

2. as output, I want another unique number (It is not a requirement for it to be based on the primary key or not, as long as it stays unique across the rows of the table) of a reasonable length, say 16 digits max.

3. It has to be a Java solution

Thanks
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7646
    
  19

shehzaad goonoo wrote:1. as input, I have a table's primary key
2. as output, I want another unique number (It is not a requirement for it to be based on the primary key or not, as long as it stays unique across the rows of the table) of a reasonable length, say 16 digits max.
3. It has to be a Java solution.

OK, now you're getting the idea. Some questions back to you (based on your numbering):
1:
(a) How long is the primary key?
(b) Is it numeric or alphanumeric?
(c) Does it contain any redundancy (for example, a prefix which is the same for all items)?
2:
(a) Does it have to be a number? Or can it be a code?

The simplest form of unique identifier is a sequence number and, unless you have a computer bigger than my apartment block, that will almost certainly fit into 16 digits. You may however, need a way to persist the "last number used" value.

Java also provides a UUID class, which is designed specifically for creating unique IDs. In fact, it is not guaranteed to produce a unique ID, but the chances of it doing so are so small as to be basically nil. Also, the only automated form is a time-based UUID. Unfortunately, a UUID consists of two longs (ie, 32 hexadecimal digits or 36 decimals) so it's probably too long for your purposes.

Finally, you could generate one yourself. One possible suggestion:
42 bits contains enough space to store approximately 135 years worth of milliseconds.
10 bits contains enough space to store the fraction of a millisecond that makes a microsecond.
which leaves 11 bits left over to store a random number between 0 and 2047.

Highly likely to be unique (NOT guaranteed though), fits in a long (18 digits), and could be generated easily and quickly from System.currentTimeMillis(), System.nanotime() and Random.nextInt(2048) (or indeed, simply cycling a sequence). Furthermore, if you put it together properly, it will sort in the order that IDs were created (or very close).

Personally, I'd look at a sequence number first though.

Winston
shehzaad goonoo
Greenhorn

Joined: Mar 08, 2012
Posts: 4
Sorry for the delay in following up with this topic. Actually, due to tight deadlines, back in March, I came up with a "temporary solution" and delivered that module, intending to look into that at a later stage.

But like every other "temporary solution", it is in production since 3 weeks now! I am therefore sharing the "solution" which went into prod; it would be great to have your views on it, as well as a heads-up on any imminent crash!

Basically, a 9-digit 'code' is now being generated using Java's Random class, passing as the seed, the primary-key of a freshly created barcode row:

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Why can't you use the primary key "as is"? It would be the simplest solution. Do you want to prevent obtaining the primary key from the code?

(Your current code doesn't guarantee uniqueness, but you're very probably aware of that.)
 
 
subject: Generate unique numbers from a table's primary key, to be used as an external reference.