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
– JB Nizet, Commented Apr 18, 2015 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
– mist, Commented Apr 18, 2015 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
– JB Nizet, Commented Apr 18, 2015 at 12:41

Community · Accepted Answer · 2017-05-23 12:17:29Z

11

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, 2017 at 12:17

CommunityBot

11 silver badge

answered Apr 18, 2015 at 12:52

Vlad Mihalcea

156k85 gold badges598 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mist Over a year ago

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.

Vlad Mihalcea Over a year ago

PostgreSQL is much more flexible. You can have sequences on any column.

mist Over a year ago

Well, unfortunately, not my choice in this project. it sucks that hibernate fails on this too.

mist · Accepted Answer · 2015-04-18 16:36:17Z

1

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.

edited Apr 18, 2015 at 16:36

answered Apr 18, 2015 at 16:24

mist

1,8632 gold badges19 silver badges34 bronze badges

4 Comments

Vlad Mihalcea Over a year ago

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.

mist Over a year ago

There are no TransactionInterceptors and I cannot think of a better way than the synchronized block

Vlad Mihalcea Over a year ago

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.

mist Over a year ago

What happens if there is @Transaction. I know the synchronized works on single jvm only, but that's our current case.

Mirimas · Accepted Answer · 2020-06-10 18:10:26Z

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();
        }
    }

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related