Hibernate get next ID with string prefix

Question

I have one question regarding primary key generation in Hibernate. I'm working in maintaining existing registry system. Current design use string as a primary key. The rule is something like "EXE" + max(). Below is how the table look like.

+----------+---------------------------+----------------+
| ID       |   Email                   |   Name         |
+----------+---------------------------+----------------+
|EXE1      | email1@gmail.com          | Name 1         |
+----------+---------------------------+----------------+
|EXE5      | email5@gmail.com          | Name 5         |
+----------+---------------------------+----------------+
|EXE14     | email14@gmail.com         | Name 14        |
+----------+---------------------------+----------------+
|EXE15     | email15@gmail.com         | Name 15        |
+----------+---------------------------+----------------+

Currently im using below's code to generate the ID.

Long rowCount = (Long) getSession().createCriteria(Exemption168DB.class).setProjection(Projections.rowCount()).uniqueResult();
if(rowCount == null)
    rowCount = 0L;
return String.format("%s%d", CommonConstant.EXEMPTION_KEY_PREFIX, rowCount + 1);

But the problem is; it is using row count to get the next sequence digit. So in the above case, the method will return EXE5(this ID is already exist in the table thus exception is thrown) because the rowcount in the table is 4, then increment by 1. What I need is EXE16.

Any help is much appreciated. Extra info, we are using Informix as a database engine.

why don't you query for the last inserted id instead? Something like (needs adaption for informix). https://stackoverflow.com/questions/21829023/how-can-i-get-last-inserted-id-using-hibernate — lher, Jan 18 '18 at 08:22
check if this https://stackoverflow.com/questions/31158509/how-to-generate-custom-id-using-hibernate-while-it-must-be-primary-key-of-table will solve your problem — homik, Jan 18 '18 at 08:25
this won't solve the problem that rowcount won't work if entries were deleted. — lher, Jan 18 '18 at 08:28
@patrzjakafranca this is the same like what im doing basically. It count for total records then plus 1. — khairul.ikhwan, Jan 18 '18 at 08:31
@lher i dont think informix have built in last_inserted_id() function. The only way i can see now is by querying system table to get the id. — khairul.ikhwan, Jan 18 '18 at 08:33
@lher yes, this is the root cause of the problem now. After one record deleted then the next id will be duplicate. — khairul.ikhwan, Jan 18 '18 at 08:35
If your using hibernate, you can implement your own ID generator, as seen [here](https://vladmihalcea.com/how-to-implement-a-custom-string-based-sequence-identifier-generator-with-hibernate/). — Tom, Jan 18 '18 at 09:18
You could avoid the problem if you were able to use either an Informix SERIAL (or BIGSERIAL) column as well as the string column, or if you were able to use a SEQUENCE. The serial types allocate numbers sequentially, and keep a record of the largest one inserted so far and allocate new numbers after that. However, you'd have to devise a means of 'copying' the serial number to the existing character-based ID column. You could probably devise a trigger to do that; you'd have to avoid explicitly inserting a value into the ID column. — Jonathan Leffler, Jan 18 '18 at 17:31
Failing that, a sequence (and probably a stored procedure) could be used to generate the string ID from the next sequence number, and the result of calling the procedure could be used in the INSERT statement: `INSERT INTO Registry(ID, …) VALUES(get_next_registry_id(), …)`. The sequence _should_ only be used for this table; one of the problems is that cannot be enforced by the database. Serial columns are inherently limited to the one table, of course; the issue doesn't arise with them. — Jonathan Leffler, Jan 18 '18 at 17:32
@JonathanLeffler yes sir you are right. But right now, the design is already there for i dont know how many years and i cant change it. Yes, a procedure might be a great solution. — khairul.ikhwan, Jan 19 '18 at 01:25

lher · Answer 1 · 2018-01-18T10:27:31.470

1

Create a custom Id Generator class that queries the last inserted id and extracts the number part. Select all ids with a string length equal to the maximum string length of IDs, order descending and limit the resultset to 1. Then increment the number as you do in your question.

edited Jan 18 '18 at 10:27

answered Jan 18 '18 at 09:28

lher

126
7

By ordering the id column descending, for example in the table have 2 records EXE9 and EXE10. The return value is EXE9 because the column is varchar. – khairul.ikhwan Jan 18 '18 at 10:01
1

add a where-clause in which you specify that the id's string length has to be equal the maximum string length of all ids – lher Jan 18 '18 at 10:18
Thanks for the suggestion. Yes, i thought about that initially. To sort the id by string length first then get the longest, then only sort based on the result. But its a bit complex just to generate the id. – khairul.ikhwan Jan 18 '18 at 11:28
1

sorry, i don't know anything about informix (you may have guessed by now :)). last suggestion, create a second table that tracks the ids numeric, if that is possible. update it in your custom id generator class. Good luck. – lher Jan 18 '18 at 11:40
1

no problem. I guess ill stick with the logic to sort based on string length first. Creating new table to store current highest number is the best solution. Unfortunately we have no permission to do that and just request them to add new table, have to go through a lot of process. Sigh. Thanks again – khairul.ikhwan Jan 18 '18 at 11:52

score 1 · Accepted Answer · answered Jan 19 '18 at 08:20

As I noted in two comments, one technique available in Informix would use triggers and SERIAL columns. Another technique would use a SEQUENCE and a stored procedure.

Here's some demo code for the sequence plus stored procedure:

CREATE SEQUENCE registry_seq
    INCREMENT BY 3
    START WITH 37
    MINVALUE 21
    MAXVALUE 299
    CYCLE;

CREATE PROCEDURE get_next_registry_id() RETURNING VARCHAR(10) AS registry_id;

    DEFINE i INTEGER;
    DEFINE r VARCHAR(10);
    SELECT registry_seq.NEXTVAL INTO i FROM "informix".SysTables WHERE tabid = 1;

    LET r = "EXE" || i;

    RETURN r;

END PROCEDURE;

CREATE TEMP TABLE registry
(
    id              VARCHAR(10) NOT NULL UNIQUE,
    email           VARCHAR(64) NOT NULL UNIQUE,
    name            VARCHAR(64) NOT NULL UNIQUE
);

INSERT INTO registry VALUES('EXE1', 'email1@gmail.com', 'Name 1');
INSERT INTO registry VALUES('EXE5', 'email5@gmail.com', 'Name 5');
INSERT INTO registry VALUES('EXE14', 'email14@gmail.com', 'Name 14');
INSERT INTO registry VALUES('EXE15', 'email15@gmail.com', 'Name 15');

INSERT INTO registry VALUES(get_next_registry_id(), 'email' || registry_seq.currval || '@example.com', 'User ID ' || registry_seq.currval);
INSERT INTO registry VALUES(get_next_registry_id(), 'email' || registry_seq.currval || '@example.com', 'User ID ' || registry_seq.currval);
INSERT INTO registry VALUES(get_next_registry_id(), 'email' || registry_seq.currval || '@example.com', 'User ID ' || registry_seq.currval);

SELECT * FROM registry ORDER BY id;

Clearly, you'd choose different control values for the CREATE SEQUENCE statement. Those worked semi-conveniently for me for my testing (which started off working on a different table).

The FROM "informix".systables WHERE tabid = 1 is a standard Informix idiom for selecting a single row of data. The system catalog has the systables table recorded with tabid of 1. On modern versions of Informix (meaning anything that you should be running; there are probably some people still running older versions though), you can select from sysmaster:sysdual (or, if you're being really safe, sysmaster:"informix".sysdual) which is a single row table with a single column.

The final output is:

EXE1    email1@gmail.com        Name 1
EXE14   email14@gmail.com       Name 14
EXE15   email15@gmail.com       Name 15
EXE37   email37@example.com     User ID 37
EXE40   email40@example.com     User ID 40
EXE43   email43@example.com     User ID 43
EXE5    email5@gmail.com        Name 5

Note that one of the disadvantages of the alphanumeric ID is that the sort order is not numeric but lexicographic.

Hibernate get next ID with string prefix

2 Answers2