Hibernate add sequence based on date

Question

I have this table on Sql Server

• id int
• journal_date datetime
• sequence int

What im trying to do is generate a sequence based on the datetime value.

example of the database

| id | journal_date | sequence |
--------------------------------
|  1 | 2012-01-01   |      1   |
|  2 | 2012-01-01   |      2   |
|  3 | 2012-01-02   |      1   |
|  4 | 2012-01-01   |      3   |

How to do this on hibernate model and the annotations ?

Answer 1

to fill up the sequence column the first time

UPDATE journaltable t1 SET sequence =
  (SELECT COUNT (*) FROM journaltable t2
   WHERE t2.journal_date = t1.journal_date AND t2.id < t1.id);

and to keep it in sync either use a trigger with

UPDATE journaltable t1 SET sequence =
  (SELECT COUNT (*) FROM journaltable t2 WHERE t2.journal_date = t1.journal_date AND t2.id < t1.id)
  WHERE t1.id = @newlyInsertedId;

or handle it in Code using Hibernate