garantuee unique invoice numbers with hibernate?

Heox · **Joined:** Mon Jan 17, 2005 1:09 pm **Posts:** 19

Hibernate 3
Mysql

The following code can be executed simultaneously.Is there a way to garanty that the invoice number will allways be unique?
I was trying to do this with with lockmodes, but I couldn't find a way to lock the "max(inv.nr)" result.
When I execute this code simultaneous, I end up whith non unique invoice numbers.

Code:

public static void saveInvoices(List invoices){
   Session hibSession = sessionFactory.openSession();
   Transaction tx = hibSession.beginTransaction();
   try{
       Query q = hibSession.createQuery("select max(inv.number) from Invoice as inv");            
       //doens't work (gives an error :invalid alias "inv")
       //q.setLockMode("inv",LockMode.UPGRADE);
       //
       Long lastInvoiceNumber = (Long)q.uniqueResult();

       Long newInvoiceNumber = lastInvoiceNumber + 1L;
       for(Iterator <Invoice>it=invoices.iterator();it.hasNext();){
          Invoice invoice2save = it.next();
          invoice2save.setNumber(newInvoiceNumber);
        
          hibSession.saveOrUpdate(invoice2save);

          newInvoiceNumber++;      
       }
       
       tx.commit();
   }catch (HibernateException he) {
       tx.rollback();
       throw he;
   }finally {
       hibSession.close();
   }   
}  

All sugestions are welcome

jamie_dainton · **Posted:** Wed Oct 12, 2005 9:18 am

Set the invoice number property to be unique in the database and when a duplicated invoice is saved an exception will be thrown.

That seems the simplest way to solve it. Of course you can quite easilly criticse it by pointing out that an exception should be exceptional and is not a part of business logic.

Heox · **Joined:** Mon Jan 17, 2005 1:09 pm **Posts:** 19

this solution would indeed keep the database consistent, but would cause an ungly exceptions to the user. The change that this exception occurs is really small. If somebody has an more ellegant solution (with DB locks?), I would appriciate to hear it.

kochcp · **Posted:** Wed Oct 12, 2005 11:16 am

why don't you just have the db generate the invoice numbers using a sequence generator instead of doing it in the code.

Heox · **Joined:** Mon Jan 17, 2005 1:09 pm **Posts:** 19

I want to be in control of my invoice numbers and the invoice number shouldn't be the primary key of my invoice table.

I should lock the entire invoices tables during the scope of the method.
I don't think there is as way to do this via Hibernate. I will try to do this with handcoded sql: " LOCK TABLES `Invoice` WRITE;"

shomburg · **Posted:** Mon Nov 07, 2005 5:43 pm

hi heox,

have the same problem here,
can you give me some hints.

Heox wrote:

I want to be in control of my invoice numbers and the invoice number shouldn't be the primary key of my invoice table.

I should lock the entire invoices tables during the scope of the method.
I don't think there is as way to do this via Hibernate. I will try to do this with handcoded sql: " LOCK TABLES `Invoice` WRITE;"

SleepyBear · **Joined:** Sat Oct 22, 2005 11:16 pm **Posts:** 40

Do you want users to be able to supply an invoice number? If that is the case, you'll need to start your transaction, check to see if the invoice number is already in use, and if it is, return a nice error message to the user.

Or, do you want to use auto-generated invoice numbers, but you want them to be of a particular form, so you don't start with Invoice #1 and go up from there? If that's the case, what I would do is use an integer identifier, generated by the increment or native generators, and then use some one-to-one formula to display to to the user. For example, if you have a sequence number called "id" you could say:

invoiceNumber = id * 7 + 1001;

That way it will sort of obscure exactly which invoice number you are looking at, and if some user somewhere supplies an invoice number, you can do a pre-check to see if it could even possibly be a valid one.

This kind of thing is done all the time. That's how credit card numbers work. Some of the digits indicate the credit card family (Visa, MC, etc). Some indicate the specific issuing bank. And then there is a checksum formula for the whole thing. Presumably the issuing bank has a plain old sequence number, and then some kind of transformation to use to generate the final CC number.

Does this help?

SleepyBear · **Joined:** Sat Oct 22, 2005 11:16 pm **Posts:** 40

By the way, I would definitely recommend that you NOT let users pick their own invoice numbers out of their imagination. That just doesn't make sense from a usability standpoint.

And of course, in my formula above, to go from the invoice number back to the id, you use:

id = (invoiceNumber - 1001) / 7

with appropriate checks to make sure that the invoice number supplied was a valid one and the result is an integer.