JPA / Hibernate pagination in MS SQL Server 2008 eror mapping to object

Question

Given my code below:

Query q = em.createNativeQuery(sql.toString(), SearchDTO.class);
for (String k : parameters.keySet()) {
    q.setParameter(k, parameters.get(k));           
}
q.setFirstResult((criteria.getPage()-1) * criteria.getLimit());
q.setMaxResults(criteria.getLimit());       

return q.getResultList();

Where page is > 1, the sql generated is incorrect:

WITH query AS (SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as hibernate_row_nr FROM ( select TOP(?) cc.company_id as page0_, cc.long_name as page1_, cc.reuters_org_id as page2_, ccdom.country_name as country_of_domicile, ccinc.country_name as country_of_incorporation, ccr.region_name as region, ci.industry_name as industry from zz_prp_common_company cc left join zz_prp_common_country ccdom on cc.country_of_domicile = ccdom.country_id left join zz_prp_common_region ccr on ccr.region_id = ccdom.region_id left join zz_prp_common_country ccinc on cc.country_of_domicile = ccinc.country_id left join zz_prp_common_industry ci on cc.industry_id = ci.industry_id where 1=1 order by cc.long_name ) inner_query ) SELECT page0_, page1_, page2_, country_of_domicile, country_of_incorporation, region, industry FROM query WHERE hibernate_row_nr >= ? AND hibernate_row_nr < ?

I cannot understand why it is replacing the alias to my columns to page0_, page1_ and page2_. Due to this the where page0_ replaced the company_id column alias, I am getting this error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name company_id is not valid.

I am using MS SQL Server 2008 R2, and I have setup my hibernate dialect to use org.hibernate.dialect.SQLServer2008Dialect.

Answer 1

I have found the answer to this issue. The sql native query I was using did NOT have any alias on the first 3 columns (company_id, long_name and reuters_org_id). When I debugged the SQLServer2008Dialect, it lead to the method getProcessedSql():

public String getProcessedSql() {
        StringBuilder sb = new StringBuilder( sql );
        if ( sb.charAt( sb.length() - 1 ) == ';' ) {
            sb.setLength( sb.length() - 1 );
        }

        if ( LimitHelper.hasFirstRow( selection ) ) {
            final String selectClause = fillAliasInSelectClause( sb );

            int orderByIndex = shallowIndexOfWord( sb, ORDER_BY, 0 );
            if ( orderByIndex > 0 ) {
                // ORDER BY requires using TOP.
                addTopExpression( sb );
            }

            encloseWithOuterQuery( sb );

            // Wrap the query within a with statement:
            sb.insert( 0, "WITH query AS (" ).append( ") SELECT " ).append( selectClause ).append( " FROM query " );
            sb.append( "WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?" );
        }
        else {
            hasOffset = false;
            addTopExpression( sb );
        }

        return sb.toString();
    }

The private method that sets the page0_, page1_, and page2, is done by fillAliasInSelectClause, an excerpt that does this is:

// Inserting alias. It is unlikely that we would have to add alias, but just in case.
alias = StringHelper.generateAlias( "page", unique );

The solution that worked for me is to provide the column alias on the 3 columns that initially did not have any. So basically, you will need to put alias on all your columns.