Limit and 'For Update' do not work on Oracle
Description
duplicates
Activity
Brett MeyerJanuary 7, 2013 at 7:44 AM
Aleksander BlomskøldFebruary 17, 2010 at 3:47 PM
BTW, this bug is probably a duplicate of https://hibernate.atlassian.net/browse/HHH-759#icft=HHH-759
Aleksander BlomskøldFebruary 17, 2010 at 3:38 PM
Hi, I created this dialect which seems to work in most cases. It might be a decent workaround to use as long as this issue is not fixed.
import java.util.*;
public class Oracle10gDialect extends org.hibernate.dialect.Oracle10gDialect {
@Override
public String applyLocksToSql(String sql, Map aliasedLockModes, Map keyColumnNames) {
if (sql.endsWith("where rownum <= ?")) {
StringBuilder builder = new StringBuilder(sql);
builder.append(" for update of ");
for (Map.Entry<String, String[]> entry : (Set<Map.Entry<String, String[]>>) keyColumnNames.entrySet()) {
String table = entry.getKey();
for (String column : entry.getValue()) {
String alias = findAlias(sql, table, column);
builder.append(alias);
builder.append(", ");
}
}
return builder.delete(builder.length() - 2, builder.length()).toString();
}
else
return super.applyLocksToSql(sql, aliasedLockModes, keyColumnNames);
}
private String findAlias(String sql, String table, String column) {
String regex = " " + table + "." + column + " as (
+),";
Pattern pattern = Pattern.compile(regex);
Matcher matcher = pattern.matcher(sql);
matcher.find();
return matcher.group(1);
}
}
Frederic LeitenbergerNovember 19, 2007 at 12:29 PM
The replacement works without a limit too, when adding 'sql.toLowercase().indexOf("rownum") > -1' to the if.
But it is NOT 100%, because when the word "rownum" is contained somewhere in the query without causing the actual limit it will fail again.
Michael KoppSeptember 14, 2007 at 2:11 PM
This replacement only works in case of a limit actually
Limits on oracle lead too:
select * from (select x.y as xy_1 from table x) where rownum <= 5
when doing a for update that leads too
select * from (select x.y as xy_1 from table x) where rownum <= 5 for update of x.y
The problem is that the x.y is invalid and not found within the temporary view and leads to an oracle error.
what would be valid is the name of the view column xy_1, meaning
select * from (select x.y as xy_1 from table x) where rownum <= 5 for update of xy_1
Actually this should be valid in all cases when doing a alias for update lock.
My Solution thus was to override the following in my own Oracle Dialect
public String applyLocksToSql(final String sql, final Map aliasedLockModes, final Map keyColumnNames)
{
final String s = new ForUpdateFragment(this, aliasedLockModes, keyColumnNames)
{
@Override
public ForUpdateFragment addTableAlias(final String alias)
{
// search for alias in sql
final int i = sql.indexOf(alias);
// check if the found string is followed by an ' as ' and thus has a column alias
if (i != -1 && sql.length() > (i + alias.length() + 4) && sql.substring(i + alias.length(), i + alias.length() + 4).equals(
" as "))
{
// use the column alias
return super.addTableAlias(sql.substring(i + alias.length() + 4, sql.indexOf(',',i + alias.length() + 4)));
}
return super.addTableAlias(alias);
}
}.toFragmentString();
return sql + s;