Hibernate Criteria with Oracle analytic windowing function

Question

Trying to override Oracle10gDialect and adding in over and partition functions. I read on hibernate.org about how to override the dialect. I am using Hibernate 4.1.7.Final and cannot upgrade. I implemented it as specified, however i am getting this error.

     15:21:21,353  WARN SqlExceptionHelper:143 - SQL Error: 907, SQLState: 42000
[10/8/13 15:21:21:354 CDT] 00000021 SystemOut     O ORA-00907: missing right parenthesis

[10/8/13 15:21:21:354 CDT] 00000021 SystemOut     O 15:21:21,354 ERROR SqlExceptionHelper:144 - ORA-00907: missing right parenthesis

Here is the class that i build:

package com.edmann.util;

import org.apache.log4j.Logger;
import org.hibernate.dialect.Oracle10gDialect;
import org.hibernate.dialect.function.StandardSQLFunction;

/**
 * Because i need some analytic functions i am going to extend the
 * oracle10gDialect so i can register my functions i want to use.
 * 
 * @author Edward Mann
 * 
 */
public class Oracle10gDialectExtended extends Oracle10gDialect {
    // get log4j handler
    private static final Logger LOG = Logger
            .getLogger(Oracle10gDialectExtended.class);

    /**
     * Override registerFunctions so that we can register the ones we want to
     * use, then we will call the registerFunctions from the parent class.
     * 
     */
    @Override
    protected void registerFunctions() {
        LOG.info("Trying to register custom functions");
        registerFunction("over", new StandardSQLFunction("over"));
        registerFunction("partition", new StandardSQLFunction("partition"));
        super.registerFunctions();
    }
}

Here is my entry in my hbm.xml file:

<property name="rank" type="integer" formula="(ROW_NUMBER() over(partition by ENTRY_NUMBER ORDER BY ENTRY_DATE DESC))"/>

The challenge i am having is keeping hibernate from adding this_. to the partition. In the hibernate forum thread that i linked to, the last entry was a person having the same issue. I am not sure if this is even possible with Hibernate Criteria.

Thanks for any help.

Update: I found my answer i added

registerKeyword("partition");

To my Oracle10gDialectExtended class in the registerFunctions method and it now works as expected.

Answer 1

So for the long answer on how i got this to work. Here is how i was able to use my ROW_NUMBER() windowing function in hibernate.

I don't know if anyone else will be looking to do something similar and the answer i have is only partial. The update that i gave allowed the partition by to be called correctly in the query to the database, but i could not use the rank value to limit the results. The below code changes that i made allowed me to use the rank value in the query.

Known Limitations

1. The rank is coded in as 1, i did not provide a way to change the number other than to change it in the code and recompile the src.
2. This only works with setMaxResults. Because you can't use the rank value in the WHERE part of the same query, you need to wrap it and use it on another query. I needed limit on the queries i am running, so this works fine for me.
3. You can only use one partition by. I don't know why someone would use more than one, but i thought i would just put that out there.
4. partition needs to be all lower case. if not it won't be matched.
5. The "as" to name the alias is all lower case. Not sure if it will ever be upper case. If it is the indexOf will not catch it.

In the Oracle10gDialectExtended class mentioned above you need to add two more methods

/**
 * I need to override the getLimitString so that i can append the partition
 * by column on the end. If partition is not found in the string then it
 * will not be added.
 */
@Override
public String getLimitString(String sql, boolean hasOffset) {
    // LOG.info("Using our getLimitString value");
    sql = sql.trim();
    String forUpdateClause = null;
    boolean isForUpdate = false;
    final int forUpdateIndex = sql.toLowerCase().lastIndexOf( "for update") ;
    if ( forUpdateIndex > -1 ) {
        // save 'for update ...' and then remove it
        forUpdateClause = sql.substring( forUpdateIndex );
        sql = sql.substring( 0, forUpdateIndex-1 );
        isForUpdate = true;
    }
    String rank = "";
    if (sql.contains("partition")) {
        rank = findRank(sql);

    }
    StringBuilder pagingSelect = new StringBuilder( sql.length() + 100 );
    if (hasOffset) {
        pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
    }
    else {
        pagingSelect.append("select * from ( ");
    }
    pagingSelect.append(sql);
    if (hasOffset) {
        pagingSelect.append(" ) row_ where rownum <= ? ");
        pagingSelect.append(rank);
        pagingSelect.append(") where rownum_ > ?");
    }
    else {
        pagingSelect.append(" ) where rownum <= ?");
        pagingSelect.append(rank);
    }

    if ( isForUpdate ) {
        pagingSelect.append( " " );
        pagingSelect.append( forUpdateClause );
    }

    return pagingSelect.toString();
}

/**
 * Take our sql query find the partition line and pull off the hibernate
 * generated alias name.
 * 
 * @param sql
 * @return String - sql with rank limit
 */
private String addRank(String sql) {
    int partition = sql.indexOf("partition");
    String rank = "";
    if (partition != -1) {
        int start = partition;
        int end = sql.indexOf(',', start);
        String aliasName = end == -1 ? sql.substring(start) : sql
                .substring(start, end);
        int last = aliasName.indexOf("as");
        if (last != -1) {
           rank = " AND "+aliasName.substring(last + 2)+ " = 1";
        }
    }
    return rank;
}

With those changes i was able to use my rank value without changing anything else in my Criteria query. The output query looks similar to this:

SELECT * FROM (SELECT ...
    ROW_NUMBER() over(partition BY this_.ENTRY_NUMBER ORDER BY this_.ENTRY_DATE DESC) AS formula1_22_,
... ) 
WHERE rownum <= 250 AND formula1_22_ =1

Updated: Changes were made because i had not tested the offset query. The rank was being added to the wrong part of the query. It was easier to take the original getLimitString query and add in the rank =1 value where appropriate.