hibernate 4.2.5 + mssql 2008 + like + setmaxresults = fail

Question

I need to make a paginated query SQLServer2008 using hibernate 4.2.5.

The database connection is Ok.

Simple queries like "FROM ENTITY E ORDER BY E.NOME" works fine, even paginated.

When I add a where clause with LIKEs the resultset returns empty and if I get the SQL printed by hibernate submited to the database manually through a client the result is correct.

I searched the internet and found some bugs complaining about using hql with setmaxresults(), but, my simple query works, why it doesn't on my second scenario?

I'm using SQLServer2008Dialect.

Answer 1

After a lot of research, I found out that Hibernate + MSSQL have a problem regarding using JPA Queries with setFirstResult(int) and setMaxResults(int).

The solution was to use a NativeQuery. Example:

            StringBuffer sql = new StringBuffer()
            .append("WITH PAGINATEDQUERY AS ( ")
            .append("     SELECT Q.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as ROWNUM ")
            .append("       FROM ( SELECT TOP(?) * FROM GDHTB_USUARIO ");

        if(!StringUtils.isEmpty(parametro)) {
             sql.append("           WHERE USU_NO like ? ")
                .append("              OR USU_DS_EMAIL like ? ")
                .append("              OR USU_NU_CPF = ? ");
        }

        sql.append("                ORDER BY USU_NO ) Q ) ")

            .append("SELECT USU_CD, USU_IC_ATIVO, USU_NU_CPF, USU_NU_DDD, USU_NU_TELEFONE, USU_DS_EMAIL, USU_ENT_CD, USU_NO, USU_DS_OBSERVACAO, USU_DT_CADASTRO ")
            .append("  FROM PAGINATEDQUERY ")
            .append(" WHERE ROWNUM >= ? ")
            .append("   AND ROWNUM < ? ");

        Query q = getEntityManager().createNativeQuery(sql.toString());

        int i = 1;
        q.setParameter(i++, inicio + pagina);

        if(!StringUtils.isEmpty(parametro)) {
            q.setParameter(i++, "%" + parametro + "%");
            q.setParameter(i++, "%" + parametro + "%");
            q.setParameter(i++, parametro);
        }

        q.setParameter(i++, inicio);
        q.setParameter(i++, inicio + pagina);

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");

        List<Object[]> resultset = q.getResultList();
        ...