Java - String conversion for DB Primary Key

Question

I'd like to transform a String (from an XML file) to a DB Primary Key.

In particular, I would like to make sure that this value are unique, are all positive and respect the DB primary key specification that is: NUMBER(9).

The string that i try to transform have this characterization:

PDR_20140909150001

Obviously i can ignore first part of the string (because are the same for all the file), but i have to transform an a timestamp string like 20140909150001 in an int value that must be have maximum 9 digit of lenght.

How can i do?

score 2 · Answer 1 · edited Sep 18 '14 at 07:47

2

You can not use any hash function as primary key because of hash function nature: it is surjective and can not be unique.

edited Sep 18 '14 at 07:47

Keerthivasan

12,760
2
32
53

answered Sep 18 '14 at 07:39

Andremoniy

34,031
20
135
241

There are a lot of mechanisms for creating primary keys: part of them are implemented in db itself – Andremoniy Sep 18 '14 at 07:40
I cannot touch db, i just can work in a software-side – Zany Sep 18 '14 at 07:43
1

@Andremoniy Is it Surjective or Subjective? I think you meant it to be Surjective – Keerthivasan Sep 18 '14 at 07:45
1

@Keerthivasan surjective is in fact correct, making that edit an ankward one – LionC Sep 18 '14 at 07:46
Sure, **surjective**. Stupid misprint. Thanks a lot. – Andremoniy Sep 18 '14 at 08:16

score 1 · Answer 2 · answered Sep 18 '14 at 07:40

1

In theory hashes are created to avoid collisions as much as possible but none of them are guaranteed to be unique. Much less so within the very strict requirements of 9 numbers. This is not going to work.

Either use a database sequence or a UUID.

answered Sep 18 '14 at 07:40

nablex

4,635
4
36
51

Thank you! Have you got some idea that in software-side i can develop? – Zany Sep 18 '14 at 07:42
If you want a key generated by your software, use UUID.randomUUID() which generates a type 4 UUID. Other types are possible if necessary. Most optimal scenario (at least in some databases like oracle) is to store the UUID as a raw. – nablex Sep 18 '14 at 07:43
If you can not update the type from number(9) to raw/varchar nor can add a sequence generator to the database, you are essentially screwed. Generating unique numbers from software (I presume this is a clustered setup) is a hard problem – nablex Sep 18 '14 at 07:46
Unfortunately i cannot edit the database and the table on it. My problem is just, software-side, transform a string that contains this type of information: PDR_dateandhour, in a primary key. – Zany Sep 18 '14 at 07:48
I can understand why data type changes might be hard, but adding a sequence to a database is not allowed? Tell your boss it's by **far** the best solution. Anything else will take a lot of time to develop and will be prone to error. – nablex Sep 18 '14 at 07:52

icza · Answer 3 · 2014-09-18T07:54:22.367

I would call your "hash code" a transformation rather.

In general you can't make such transformation because if the String is long enough (e.g. longer than 10 characters), there are more possible values in that string than a number with 9 digits can represent.

IF the input string is guaranteed to be shorter than a limit, you can make such transformation. The max input limit is 2 characters because 1 character in Java is 16 bits which is 2 bytes, 3 characters would be 6 bytes (which doesn't fit into NUMBER(9)). Max value of NUMBER(9) is 999999999 which is 0x3B9AC9FF which is 4 bytes.

Conversion:

String key = ...; // Read key, must be 2 characters at the most

int dbKey = 0; // NUMBER(9) fits into a Java int
for (int i = 0; i < key.length(); i++) {
    dbKey <<= 16;
    dbKey += key.charAt(i);
}

you're right! but maybe is better if i open a question more specifical. — Zany, Sep 18 '14 at 07:52

score 0 · Answer 4 · edited May 23 '17 at 12:21

0

What you need is a bijective Integer to String function, that is discussed here.

edited May 23 '17 at 12:21

Community

1
1

answered Sep 18 '14 at 07:46

Marcel Härle

173
2
4
8

Just a note that it does not have to be bijective, injective would be enough (in theory that is, a bijective function can be more memory-efficient, but is not needed) – LionC Sep 18 '14 at 07:49

Java - String conversion for DB Primary Key

4 Answers4