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.
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.