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      | [email protected]          | Name 1         |
+----------+---------------------------+----------------+
|EXE5      | [email protected]          | Name 5         |
+----------+---------------------------+----------------+
|EXE14     | [email protected]         | Name 14        |
+----------+---------------------------+----------------+
|EXE15     | [email protected]         | 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.

Answer 1

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', '[email protected]', 'Name 1');
INSERT INTO registry VALUES('EXE5', '[email protected]', 'Name 5');
INSERT INTO registry VALUES('EXE14', '[email protected]', 'Name 14');
INSERT INTO registry VALUES('EXE15', '[email protected]', '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    [email protected]        Name 1
EXE14   [email protected]       Name 14
EXE15   [email protected]       Name 15
EXE37   [email protected]     User ID 37
EXE40   [email protected]     User ID 40
EXE43   [email protected]     User ID 43
EXE5    [email protected]        Name 5

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