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

Question

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.}

I do not really understand your question. What do you want to achieve? The row number in any DBMS is a very tricky thing. Most DBMS will count row numbers starting at 1 for each new result set (which could be a join as well). I recommend not relying on row numbers. Especially not if you want to support different DBMS. — Jemolah, Dec 07 '14 at 22:08
A row number is needed in situations, for example, [this](http://stackoverflow.com/a/1804983) link, if clicked, directly points to an answer available in the **fourth page**. When a user passes an `id` (primary key value) by clicking a link, the row is required to be highlighted which is possible only if the row number corresponding to that row is returned - same as that SO answer is highlighted by **automatically selecting the corresponding page**. — Tiny, Dec 08 '14 at 02:14
Starting from MySQL 8, you don't need a special syntax anymore. It supports window functions now — Lukas Eder, Aug 23 '21 at 08:49

Vlad Mihalcea · Accepted Answer · 2018-01-05T19:33:25.370

1

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.

edited Jan 05 '18 at 19:33

answered Dec 08 '14 at 09:31

Vlad Mihalcea

142,745
71
566
911

What is `$db_query_name`? Is it something special? Thank you. – Tiny Dec 08 '14 at 15:33
It's just a naming convention I came up with. $db would be 'oracle' or 'mysql' while query_name the actual query name. – Vlad Mihalcea Dec 08 '14 at 15:54

score 0 · Answer 2 · answered Aug 23 '21 at 08:54

Starting from MySQL 8, you don't need a special syntax for MySQL anymore as it now supports window functions. However, if it didn't your particular query can always be rewritten in a SQL-92 fashion without window functions easily:

If you already know that your country_id parameter exists

SELECT count(*) FROM country WHERE country_id >= ?

If you don't know whether your country_id parameter exists

SELECT count(*)
FROM country
WHERE country_id >= ?
AND ? IN (SELECT country_id FROM country)

The translation isn't entirely correct if country_id isn't unique (I'm assuming it is). If it isn't, then this corresponds to RANK(), not ROW_NUMBER().

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

2 Answers2