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 ?

I don't know how to do this in hibernate. But in general, you can try this. `select max(sequence)+1 from table where journal_date = input_date` — Vaandu, Jan 13 '12 at 06:40
And? What problem do you have? What have you tried? WHat's your question? — JB Nizet, Jan 13 '12 at 08:49
Im sorry i was in a hurry, how to do this automatically in hibernate ? I use hibernate with annotation in model. — ahmy, Jan 13 '12 at 09:56
There is no way to do this "automatically' in Hibernate. You'll have to implement something yourself, with Hibernate. — JB Nizet, Jan 13 '12 at 10:05

score 0 · Accepted Answer · answered Jan 13 '12 at 10:25

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

Hibernate add sequence based on date

1 Answers1