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
}
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With