JPA or Hibernate to generate a (non primary key) column value, not starting from 1

Question

I want a JPA/Hibernate (preferably JPA) annotation that can generate the value of a column, that is not a primary key and it doesn't start from 1.

From what I have seen JPA cannot do that with @GeneratedValue and @SequenceGenerator and @TableGenerator. Or with anything else.

I have seen a solution with an extra table, which I find is not elegant.

I can live with a Hibernate annotation, because I already have hibernate annotations.

I want to use @Generated but I cannot make it work and people claim that it is possible.

@Generated(GenerationTime.INSERT)
private long invoiceNumber;//invoice number

Update: an extra requirement, if the transaction is rolled back, we can't have a gap in the numbering. Anyone?

Saying "I can't make it work" is not very helpful. What is the test case? What happens when you execute the test case code, and what do you expect to happen instead? How did you configure your database to make it generate the value at insertion time? — JB Nizet, Apr 18 '15 at 12:00
First, I cannot find a place where to specify the initial value. Second with only this annotation I get an error: **java.sql.SQLException: Field 'invoiceNumber' doesn't have a default value** — mist, Apr 18 '15 at 12:37
Have you read the javadoc of Generated? It says that this annotation is used to signal that "The annotated property is generated by the database". So Hibernate won't generated. It's up to you to configure a database trigger that will generate this value when a row is inserted in the table, and thanks to the annotation, Hibernate will read this value after the insert, so that the entity has the value generated by the database. — JB Nizet, Apr 18 '15 at 12:41

score 8 · Answer 1 · edited May 23 '17 at 12:17

8

The @GeneratedValue only works for identifiers and so you can't use it. If you use MySQL, you are quite limited, since database sequences are not supported.

InnoDB doesn't support multiple AUTO_INCREMENT columns and if your table PK is AUTO_INCREMENTED, then you have two options:

Go for a separate table that behaves like a sequence generator, the solution you already said you are not happy about.
Use an INSERT TRIGGER to increment that column.

edited May 23 '17 at 12:17

Community

1
1

answered Apr 18 '15 at 12:52

Vlad Mihalcea

142,745
71
566
911

Thanks for the reply. Trigger is not an option, because we want an auto generated DB schema without extra code. An extra table is also not an option, because if we have a rollback, the increment is going to skip a number, which is not allowed. – mist Apr 18 '15 at 16:21
PostgreSQL is much more flexible. You can have sequences on any column. – Vlad Mihalcea Apr 18 '15 at 16:29
1

Well, unfortunately, not my choice in this project. it sucks that hibernate fails on this too. – mist Apr 18 '15 at 16:34

mist · Accepted Answer · 2015-04-18T16:36:17.560

Here's what worked for me - we coded all of it in the service. Here's the entity:

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
public class Registrant extends AbstractEntity {
    //....
    private long invoiceNumber;//invoice number

    @Entity
    public static class InvoiceNumberGenerator {
        @Id
        @GeneratedValue
        private int id;
        private long counter;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public long getCounter() {
            return counter;
        }

        public void setCounter(long counter) {
            this.counter = counter;
        }
    }
}

And then we have a service that does the magic (actually there's no magic, all is done manually):

public synchronized Registrant save(Registrant registrant) {
    long counter = getInvoiceNumber();
    registrant.setInvoiceNumber(counter);

    return registrantRepository.save(registrant);
}

private long getInvoiceNumber() {
    //mist: get the invoice number from the other table
    long count = registrantInvoiceNumberGeneratorRepository.count();
    if(count > 1) {
        throw new RuntimeException(": InvoiceNumberGenerator table has more than one row. Fix that");
    }

    Registrant.InvoiceNumberGenerator generator;
    if(count == 0) {
        generator = new Registrant.InvoiceNumberGenerator();
        generator.setCounter(1000001);
        generator = registrantInvoiceNumberGeneratorRepository.save(generator);
    } else {
        generator = registrantInvoiceNumberGeneratorRepository.findFirstByOrderByIdAsc();
    }


    long counter = generator.getCounter();
    generator.setCounter(counter+1);
    registrantInvoiceNumberGeneratorRepository.save(generator);
    return counter;
}

Note the synchronized method - so that nobody can get the same number.

I can't believe there's nothing automatic that can do that.

This can only work out if the current transaction boundary is contained within the synchronized block. If the `synchronized` method is wrapped by the `TransactionInterceptor` then there's a time window between you release the service object lock and the transaction commit, meaning you can get other transaction updating the same counter value. — Vlad Mihalcea, Apr 18 '15 at 21:48
There are no `TransactionInterceptors` and I cannot think of a better way than the `synchronized` block — mist, Apr 19 '15 at 18:02
You need to make sure there's no Transaction annotation on this method or up the stack and move the transaction boundary programmatically inside the synchronized block. Or use pessimistic locking as synchronized works on one JVM node only. — Vlad Mihalcea, Apr 19 '15 at 18:30
What happens if there is `@Transaction`. I know the `synchronized` works on single jvm only, but that's our current case. — mist, Apr 19 '15 at 20:53

score 1 · Answer 3 · answered Jun 10 '20 at 18:10

Related to @Vlad Mihalcea, now you can use @GeneratorType to generate your own custom value for non id column. For example:

Entity:

    import org.hibernate.annotations.GeneratorType

    @GeneratorType(type = CustomGenerator.class, when = GenerationTime.INSERT)
    @Column(name = "CUSTOM_COLUMN", unique = true, nullable = false, updatable = false, lenght = 64)
    private String custom;

ValueGenerator implementation:

public class CustomGenerator extends ValueGenerator<String> {
        private static final String TODAY_EXAMPLE_QUERY = "from Example where createDate>:start and createDate<:end order by createDate desc";
        private static final String START_PARAMETER = "start";
        private static final String END_PARAMETER = "end";
        private static final String NEXTVAL_QUERY = "select EXAMPLE_SEQ.nextval from dual";
        private final SimpleDateFormat dataFormat = new SimpleDateFormat("yyyyMMdd");

        @Override
        public String generateValue(Session session, Object owner) {
            Date now = new Date();
            Query<Example> todayQuery = session.createQuery(TODAY_EXAMPLE_QUERY, Example.class);
            query.setParameter(START_PARAMETER, start(now));
            query.setParameter(END_PARAMETER, end(now));
            Example lastExample = todayQuery.setMaxResult(1).setHibernateFlushMode(COMMIT).uniqueResult();

            NativeQuery nextvalQuery = session.createSQLQuery(NEXTVAL_QUERY);
            Number nextvalValue = nextvalQuery.setFlushMode(COMMIT).uniqueResult();
            return dataFormat.format(now) + someParameter(lastExample) + nextvalValue.longValue();
        }
    }

JPA or Hibernate to generate a (non primary key) column value, not starting from 1

3 Answers3

Linked