[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2844) Limit and 'For Update' do not work on Oracle

[Aleksander Blomskøld (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2844?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=35600#action_35600 ] 

Aleksander Blomskøld commented on HHH-2844:
-------------------------------------------

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);
    }
}

> Limit and 'For Update' do not work on Oracle
> --------------------------------------------
>
>                 Key: HHH-2844
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2844
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-sql
>    Affects Versions: 3.2.2
>            Reporter: Michael Kopp
>
> 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;

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira