Obtaining a row number of a given row from MySQL via JPA

Question

I'm executing the following SQL in JPA 2.0 that retrieves the row number of a given row.

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

I'm using the following method in JPA to execute this native SQL query.

Object object = entityManager.createNativeQuery("aboveQuery")
                             .setParameter("zone_id", id).getSingleResult();

object.toString() returns like

10.0
11.0
12.0
13.0

and so on based on the id supplied. So, this value can't be parsed to java.lang.Long. I expect it to display a value without a decimal point. Why does this query produce this result. It is more related to MySQL rather than JPA.

I'm using MySQL 5.6.11

EDIT:

I can't verify what datatype it returns in MySQL, since it returns the result with no decimal point in MySQL (the primary key which is taken into account here has a type of BIGINT).

When I tried this query in PHP, it also produced the result with no decimal point. So, I may believe it has to do something with JPA.

score 0 · Answer 1 · answered Jul 09 '13 at 13:19

0

It is most likely returning a BigDecimal which is the standard type for database NUMBER types. If you want a Long just cast it to Number and call longValue().

((Number)result).toLong()

If it is a String for some reason then just use,

new BigDecimal((String)result).toLong()

answered Jul 09 '13 at 13:19

James

17,965
11
91
146

In Hibernate, we can obtain a specific type by setting a scalar like, `sessionFactory.getCurrentSession().createSQLQuery("quert").addScalar("rownum", LongType.INSTANCE).setParameter("id", id).uniqueResult();`. Can this be done in JPA? I can't find such a method as `public SQLQuery addScalar(String string, Type type)` in JPA. – Tiny Jul 10 '13 at 06:33
[This](http://stackoverflow.com/a/6082296/1391249) is an ugly workaround (I didn't give it a try though). – Tiny Jul 10 '13 at 06:37

Obtaining a row number of a given row from MySQL via JPA

1 Answers1