Home JPA: generate non pk unique and random alphanumeric value
Reply: 3

JPA: generate non pk unique and random alphanumeric value

Al1
1#
Al1 Published in 2017-09-13 17:00:11Z

I want to uniquely identity an entity without using the primary key. So I thought about generating an unique and random value. Moreover, value must be easy to read / manually copy and is expected to be 6 or 7 characters long.

Design

My entity A:

public class A{
    // ...
    @Column(name="value", unique=true, nullable=false, insertable=false, updatable=false)
    private String value;
    // ...

    public String getValue(){
        return value;
    }
    protected void setValue(String value){
        this.value = value;
    }
}

represented in the database by the table

CREATE TABLE IF NOT EXISTS schema.mytable{
    -- ...
    value TEXT NOT NULL DEFAULT generate_unique_value_for_mytable(),
    -- ...
    CONSTRAINT "un_value" UNIQUE (value),
    -- ...
}

I thought letting the database handling this and then fetch the value...

Problem

With the current design, value is correctly generated in the database but when JPA fetches A entities, value field is empty.

  1. I cannot remove insertable=false otherwise, it will hit against the NOT NULL constraint
  2. If I remove insertable=false and I put some dummy data, the data overrides the value generated by generate_unique_value_for_mytable()
  3. If I remove everything in the Column annotation, I can save the A entity but value is still empty

Ugly solution

I couldn't find a proof but it looks like having the database generating a value is a bad idea. I do have the same problem for a non-primary key field which is generated by a sequence: I cannot fetch the value from the database.

So my ugly solution is to decorate the create() method of the EJB responsible for A entities:

public class Aejb{

    public void create(A entity){

        // method kind of ensures randomness
        String value = MyUtil.generateRandomValue();
        A isThereAnyoneHere = findByValue(value);

        while(isThereAnyoneHere != null){
            String value = MyUtil.generateRandomValue();
            isThereAnyoneHere = findByValue(value);
        }

        // unicity is ensured
        entity.setValue(value);

        em.persist(entity);
    }

}

Questions

  1. Can I fetch a non-primary key value generated by the database from a JPA entity? Value can be generated by a function or a sequence.
  2. Is there a more elegant solution than my ugly workaround to provide an unique and random value?
Chris
2#
Chris Reply to 2017-09-13 17:52:57Z
  1. Yes.You haven't mentioned your database, but it is possible for Oracle to return the value inserted via triggers, and have Eclipselink obtain this value in your model - see https://www.eclipse.org/eclipselink/documentation/2.5/jpa/extensions/a_returninsert.htm
  2. Set the value using a @PrePersist method that will get executed before the entity is inserted, but if you are relying on one or more database queries, you will run into performance issues, as inserting a new A will be expensive. You might instead just insert the random value and deal with the occasional conflict, and pick some random that has less chance of overlaps, like a UUID.
Stas Shafeev
3#
Stas Shafeev Reply to 2017-09-14 07:46:46Z

If I understand correctly, @Generated annotation should do the trick. This annotation sets the value from database DEFAULT field value.

Example:

@Generated(GenerationTime.INSERT)
@Column(name="value", unique=true, nullable=false, insertable=false, updatable=false)
private String value;

However there is a drawback: if you decide to set value of your field in Java, it would be overwritten by Hibernate using the result from DEFAULT in your database.

Al1
4#
Al1 Reply to 2017-09-28 09:20:56Z

Self-answer to mark question as closed


Final solution

We finally went for a combination of

  • Stored procedures: the database will generate the value. The procedure also ensures that the value is unique across the table
  • Named queries: to fetch the generated value by the procedure. I did not use NamedStoredProcedures because we are using PostgreSQL and PostgreSQL JDBC driver did not support name parameters which raised some problems.

With this configuration, the EJB is sure to have at most one database call to fetch the requested value.

Response to other answers

Here is a summary of the other answers feedback for self-reference and next readers:

  • Oracle trigger: we're using PostgreSQL :(
  • UUID: We had the constraint of having our unique and random code human-readable. An end-user is assumed to be able to manually rewrite it. Consequently, we could not have a long String such as an UUID.
  • PrePersist: Other business actions take place after the code generation in the same transaction which means that those actions need to be redone in case of collision. I'm not very confident about managing JPA exception (transaction scope and so on) so I preferred not to play with it.
  • @Generated: This is a Hibernate specific feature. We're using EclipseLink
  • Database Trigger: If code were purely generated at database level, I encountered the same problems of not fetching the value: the value is properly generated as database level but the entity will have the value as null
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.317503 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO