How to create a customerNumber generator for each company in Hibernate

Question

I have a table of customers, each customer belongs to a company, companies want their customer numbers to start at 0 and increment as they add customers and when companyB adds a customer, companyA's customer numbers shouldn't be affected. CustomerId internally can be any number, customerNumber must be incremented gap-free in the context of the companyId (with gap-free I mean 0,1,2,3,4, if 2 is deleted, it's gone, the next insert should be 5 and not 2)

Example: 
    companyId customerNumber  customerId 
    0         0               0
    0         1               1
    0         2               2
    0         3               3
    0         4               4
    0         5               5
    1         0               6
    1         1               7
    1         2               8

I was wondering if there's a better way to do it than opening a transaction, finding the max customerNumber, inserting an entry using the max+1 as customerNumber and closing transaction

Is there some sort of annotation I can use where I can specify the criteria for generating a customerNumber? The next customerNumber should be the highest number available within that company. (I have about 20 other entities that have similar human-readable-incremental-id requirements based on date and comapnyId and I want to make the customerNumber-type fields generation as fool-proof as possible, I don't want to have to remember to do it everytime I persist a new entity)

Something like:

@SequenceGenerator(uniqueOver="companyId,customerNumber",strategy=GenerationType.AUTO)
private Long customerNumber;

The solution should be ACID compliant since I'm working with stock and financial data.

Update: I've renamed id to customerId and customerId to customerNumber to prevent confusion.

Update: When I mean gap-free, I mean that customerNumbers should be 0,1,2,3,4,5,6,7,8,9 - if I delete number 4 it is gone forever and the next insert should be 10 unless I create a new company, then their first customer should start at 0.

Update: I'm using spring with hibernate, so the @PrePersist annotation is not working for me. If @PrePersist is suggested as a solution, then it needs to work under spring, so an answer for Simon's question would be needed: Enable @PrePersist and @PreUpdate in Spring

Suggested Answer which I'm not sure about:

@Entity
@Table(name = "Customer")
public class Customer {

    @Table(name = "CustomerNumber")
    @Entity(name = "sequenceIdentifier")
    public static class CustomerNumberSequenceIdentifier {

        @Id
        @GenericGenerator(name = "sequence", strategy = "sequence", parameters = {
                @org.hibernate.annotations.Parameter(name = "sequenceName", value = "sequence"),
                @org.hibernate.annotations.Parameter(name = "allocationSize", value = "1"),
        })
        @GeneratedValue(generator = "sequence", strategy=GenerationType.SEQUENCE)
        private Long id;

    }


    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long customerId;

    @ManyToOne
    private Company company;

    @GeneratedValue(generator = "sequenceIdentifier", strategy=GenerationType.TABLE)
    private Long customerNumber

}

Answer 1

While I can't speak to the hibernate side of things (sorry), you can accomplish this with a table in mysql so long as it uses the myisam engine, with the following table structure:

create table ai_test (
    company_id integer,
    customer_number integer auto_increment,
    primary key (company_id, customer_number)
) engine=MyISAM;

Here's a fiddle showing that in action: http://sqlfiddle.com/#!9/45e78/1

Sorry i can't translate this to hibernate for you - never used it, but this may be a starting point.

edit
since inno is required, you could use this table and trigger:

create table ai_test (
    company_id integer,
    customer_number integer,
    primary key (company_id, customer_number)
) engine=InnoDB;


create trigger inno_composite_ai_ok before insert on ai_test
for each row 
begin
    set new.customer_number =     
        (select ifnull((select max(customer_number)+1 
                         from ai_test 
                           where 
                        company_id=new.company_id),1));
end//

Here is a sample fiddle for you: http://sqlfiddle.com/#!9/fffd0/14

edit
One more update to include requirement of single column PK

create table ai_test (
    company_id integer,
    customer_no integer,
    customer_id integer primary key auto_increment

) engine=InnoDB;

create trigger inno_composite_ai_ok before insert on ai_test
for each row 
begin
    set new.customer_no =     
        (select ifnull((select max(customer_no)+1 
                         from ai_test 
                           where 
                        company_id=new.company_id),1));
end//

fiddle here

Note you can (and probably should) put a unique constraint across company_d/customer_no

Answer 2

If you using hibernate; Example Query

Query query = session.createQuery("insert into customer (company_id, customer_id)" +"(select :company_id, IFNULL(max(customer_id),1)+1 from customer where company_id = :company_id)");

    //set the company_id here

    int result = query.executeUpdate();

Note: IFNULL is a MYSQL specific syntax

I suggest keeping the company id auto_increment field in a separate company master table.

Answer 3

You're looking for a sequence number generation without having to write the ddl to generate a new sequence number for each table. Do this with a separate sequence number table and create a select for update transaction to lock both tables for insert. Hibernate does this with enhanced-tables.

Here's a pretty good example: http://vladmihalcea.com/hibernate-identity-sequence-and-table-sequence-generator/

Scroll down to the TABLE(SEQUENCE) section, which is a table of sequence numbers solution.

In the example just specify the customer name as the sequence name to get the right sequence.

The overhead is generating a sequence row for every new company, and locking the table for every new customer.