I am using hibernate 5.0 with Oracle DB and have the following issue.I have to mention that I saw the issue HB-958 it is a really old issue, but I need such feature.

I want to produce

select dbms_random.value l ,e.* from my_table e order by l

but hibernate generates the following for me

select dbms_random.value formula2_4_ ,e.* from evoucher_scm.evoucher e order by dbms_random.value

The second query is totally different from first one.
I resolved this problem by NativeSQLOrder and hard-coding "formula2_4_".
But it's not the good or even correct solution.

Any help is appreciated.
Thanks in advance.
Pedram

What exactly is your use case? I don't understand what is your requirement. Do you want a randomly assigned value for your entity upon persisting it, or do you want a random value every time you fetch it from DB?

I want randomly generate a value for each row and then ordering them with this dummy field and then pick the first one to reduce
concurrent access to the same row. By ordering on random values, I want to make chance of selecting each row uniform. I am using Optimistic lock and dont want to use pessimistic locking mechanism of db.

If you're trying to implement a Job queue, you might be interested in SKIP_LOCK support that Hibernate offers for Oracle and PostgreSQL 9.5.

Otherwise, you can still implement your design using insert-generated values instead of @Formula. @Formula is useful when you want to fetch a value from the database, while @ValueGenerationType is what you want to insert random values. Then you can ORDER BY tht specific property that you only generate a value for during entity persisting.

Thank you mihalcea_vlad for prompt answers and suggesting these interesting features.

But my requirements is different.
Suppose two "select * from my_table where ..." queries come to DB simultaneously. Each query have to fetch one record (outer select with "where rownum=<1" is omitted) with different ID, so I need some type of random sort to fetch different rows with these simultaneous queries. I want a dummy column with an alias like "select dbms_random.value as rand_val ... order by rand_val" to distribute chance of selection by each simultaneous query among all rows.

Thanks
Pedram

And what if by chance two selects will fetch the same row because both used the same random value?

I understand what you are trying to do. I don;t understand what is the actual reason for doing such a hack. Is it related to concurrency control? Do you plan on modifying thos eentities and you don't want a conflict? There are better ways to solve such a requirement.

I am trying to prevent such conflicts on best-efforts basis, also in our case we have lots of rows which satisfy our select query(typically about 10000 rows) so I believe that the probability of such situations are very low.



Yes exactly, the fetched rows are going to be modified and an update query will be sent to DB. We are using Optimistic Lock by hibernate to prevent conflicts.

Note that I am forced not to use pessimistic lock.
What is your suggestion?

Thanks
Pedram

There are better ways to do that. Since you are "forced" not to use pessimistic locking, you can still workaround conflicts by using a versionless optimistic locking approach.

The versionless optimistic locking minimizes the chance of conflict while still ensuring that lost updates are prevented.

But that's not all. Depending on your use case, you could also try an automated retry mechanism. However, the automated retry mechanism is double edged sword. If you are simply overwriting a database record whose state has been changed since you first read it, you are going to lose updates. But you are the only one to tell if that's ok or not for your use case.

hi vlad,

Sorry for my late reply.
After these days we eventually decided to use "for update of [column] skip locked".
Now I`m trying to fill [column] by using


getAlias returns "this" and if i change the value,it seems hibernate does not generate for update.
How can I handle this problem?

Thanks
Pedram

The legacy Criteria is deprecated. Try using Criteria API from JPA or JPQL/HQL queries instead. Even if it's a bug in legacy Criteria, it won't be addressed by the Hibernate team, we'll only integrate a fix that you provide as a Pull Request.

Thanks for your informative posts.
I will try JPA api.