Get maximum number from table using hibernate

Question

when I'm making one object I need to look into database.object and find maximum key for that object in table. When I find maximum number, I need it to increase it by 1 and my new object will have that new key. These keys are dependent of object type, and there are many types, so I can't use id.

Example: Table objects

Id, Name, Type, Key
1,  One,   T1,   6
2,  Two,   T1,   3
3,  Three, T2,   88
4,  Four,  T1,   101

Issue I'm facing: more users can make new object (and there is some calculations, so this operation is not very fast) and sometimes two or more users end's up with a same key?!

Using jdbc I could use SELECT .. FOR UPDATE and lock table.

Is there any recommended solution how I can achieve this using hibernate?

Thanks

you can use composite ids http://stackoverflow.com/a/2301332/892994 Type and Key fields are both Ids and Key is autoincrement. is it cover your requirement? — erencan, May 10 '13 at 13:34
I think this may cover. It is just an idea. I did not checked if itis works. You can set Type and Key fields as composite keys and making the Key field autoincrement may behave in a similar result. Please see my answer it is a different approach. — erencan, May 10 '13 at 18:40

erencan · Answer 1 · 2013-05-10T20:08:36.593

You need an ACID operation when you save new values in the DB. Accessing max number of required field and population of new id creation and commit operations should be a single unit and Transactions provide this capability.

I am not sure about your configuration. You can use Hibernate Transactions.

You can access the max row value which also has some conditions. See also the docs.

public Long maxRowValue()
    {

        Criteria crit = sess().createCriteria(YourEntity.class);    
        crit.setProjection(Projections.max("Key"));
        crit.add( Restrictions.eq("Type", 1));
        List<Long> teams = crit.list(); 
        return teams.get(0);

    }

P.S. I do not know your configuration details. The code should be changed accordingly.

nakosspy · Answer 2 · 2013-05-10T13:20:49.393

0

I had the same need in some occasions. What I did is create a new table that keeps the maximum value. This table has only one row, so it is easily read, and you don't need to lock the whole table. Only this table whenever you want to get a new key, or to update one. Then your transaction

first locks this table
then it reads its content,
then uses it to set the key value in the new entity,
then store the new entity
update the max_key table with the new value
and finally commit and release the lock.

edited May 10 '13 at 13:20

answered May 10 '13 at 12:56

nakosspy

3,904
1
26
31

please read again carefully. I have id, but I need to generate max+1 of values from column key, based on column type, and they are not sequental – mikipero May 10 '13 at 13:09
You are right, I didn' get that. I edit and suggest something different. – nakosspy May 10 '13 at 13:15
"first locks this table" but how to achieve this using hibernate? – mikipero May 10 '13 at 13:25
the easiest way is to execute an sql update (set key=key+1). This way the table row is locked until you commit the transaction. – nakosspy May 10 '13 at 13:30
You can also add optimistic locking feature in this table (lastModified column). So you can read and update this column and make sure that others won't mess with your updates. The problem with this approach is that others won't be locked but the will get a ConcurrentModificationsException. – nakosspy May 10 '13 at 13:40

score 0 · Answer 3 · answered May 10 '13 at 12:59

0

First and foremost, what is the underlying DBMS you are using?

Some databases (most of them actually) have special features to handle automatic id generation, while keeping transactions isolated. For instance, an Oracle database has "on before insert" triggers that could be used to achieve what you need.

By the way, the "key" value must be sequential, or just being unique is enough?

answered May 10 '13 at 12:59

Robson França

661
8
15

I'm using PostgreSQL. And keys are not sequential, just unique. – mikipero May 10 '13 at 13:08
but one of the reasons I'm using hibernate is that i don't want to depend from database. – mikipero May 10 '13 at 13:20

score 0 · Answer 4 · answered May 10 '13 at 13:25

0

I have once had such issue. The tables have multi-column primary keys, with last element that was unique for the prefix-columns.

I've made a table SEQUENCES with colums KEY and CURR_VALUE. The keys looked like that:

tablename_col1value_col2value

for example:

cities_1_2
cities_1_3
cities_3_4

With that, you can write stored procedure that will lock given key, get the value, increase the value in table and return the value. It is much faster than issuing SELECT MAX each time.

answered May 10 '13 at 13:25

Danubian Sailor

1
38
145
223

but can I do this from java, I would not like to have stored procedures – mikipero May 10 '13 at 14:38
you have method in Hibernate Session for locking rows, I'm not sure of its name, but there is (was?) error in PostgreSQL implementation, where the FOR UPDATE was not added to the query, I'll lookup if I have the example but I can't promise anything – Danubian Sailor May 10 '13 at 14:42

Get maximum number from table using hibernate

4 Answers4