Database agnostic query to get the row number based on the primary key

Let country be a table in an RDBMS system. Its columns are intuitive. Therefore, I do not go in depth nor they are required to know.

I needed SQL statements like the following at many places through out the application while using JPA (through EclipseLink / Hibernate).

MySQL :

SELECT rownum 
FROM   (SELECT @rownum := @rownum + 1 AS rownum, tbl.country_id 
        FROM   country tbl, (SELECT @rownum := 0) t 
        ORDER  BY tbl.country_id DESC)t 
WHERE  country_id =?

Oracle (using window analytic functions) :

SELECT row_num 
FROM   (SELECT ROW_NUMBER() 
               OVER (ORDER BY country_id DESC) AS row_num, country_id 
        FROM   country 
        ORDER  BY country_id DESC) 
WHERE  country_id = ?

This native statement is required to be executed directly through the use of the createNativeQuery() method as follows.

entityManager.createNativeQuery("Above Query")
             .setParameter(1, id)
             .getResultList();

These statements are meant to return a row number based on the given primary key value as set by using the setParameter() method.

They are not database agnostic. Does there exist any database agnostic stuff in JPA?

_{I do not use the getSingleResult() method to get a single (scalar) value as this method would unnecessarily throw an exception, if a query statement returned none or multiple rows accidently.}

Solution

JPQL and HQL are using a simplified querying syntax that dates back to SQL-92. That's because most databases are SQL-92 compliant.

So you can't emulate these queries with HQL or JPQL, meaning you have to use native queries.

To hide the database specific details you can define your queries as $db_query_name and then simply supply the $db at runtime.