How to get the last value of a column using HQL(Hibernate Query Language)

Question

As shown in the picture, I need the last value of bookingID column using hibernate in MySQL.

@SuppressWarnings("unchecked")
    @Override
    public Booking listBookingsID() {
        Session session = this.sessionFactory.getCurrentSession();
        Booking bookList =  (Booking) session.createQuery("from Booking ORDER BY bookid DESC")
                .setMaxResults(1).uniqueResult();


        return bookList;
    }

I tried that code which is found in this link, but it always shows as "9". If the last value is "1" then it should show as 1, but it's 9.

So my question is: "Are there any queries to get the last value of a column in hibernate?"

You are ordering by the column bookindId in descending order. According to the screenshot 9 is the highest value there. So what exactly isn't working as you expect it? If you want the last entry, don't use bookingId but sort by your primary key! — OH GOD SPIDERS, Feb 23 '17 at 13:37
@911DidBush even if we use primary key then the value will show as 20 but i need the last value of bookingID. Actually the requirement is like "records will be added to database but 9 is the limit. After 9 records,10th record should start again with bookingID as 1" — FIFA oneterahertz, Feb 23 '17 at 13:42
I said you should sort by your primary key. Not that you should select it. Sort by primary key, select bookingId. — OH GOD SPIDERS, Feb 23 '17 at 13:43
To me, that looks like the first value, not the last (assuming your ids run up, like everybody else's). To get the first value, order ASC, not DESC, and take the first one. — Joeri Hendrickx, Feb 23 '17 at 13:43
In relational databases there is no such concept of last or first record because the resultsets can be ordered in different ways. What you seem to want is to get bookingID of the record where id equals to the maximum of that field (20). — Shadow, Feb 23 '17 at 13:44
@Shadow Yeah but in hibernate i don't know whether that type of query still exists or not — FIFA oneterahertz, Feb 23 '17 at 13:45
Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Shadow, Feb 23 '17 at 13:47
@911DidBush If its normal SQL query i shouldn't have had this problem. But i need HQL(Hibernate Query Language) — FIFA oneterahertz, Feb 23 '17 at 13:48
@Shadow I am using hibernate,so it needs a query from Hibernate Query Language(HQL). not normal sql query — FIFA oneterahertz, Feb 23 '17 at 13:49
I assume Booking is an entity that contains id, bookingId etc, right? So `Booking booking = (Booking) session.createQuery("from Booking ORDER BY id DESC") .setMaxResults(1).uniqueResult(); return booking.getBookingId();` <- Something like that doesn't work in your case? — OH GOD SPIDERS, Feb 23 '17 at 13:51
@911DidBush Nope it didn't work in my case. At 9 it will stop and if a new record comes then it takes as 9 always. — FIFA oneterahertz, Feb 23 '17 at 13:53
@FIFAoneterahertz, it is a `ORDER BY` by `id` instead of `bookingid`. — N00b Pr0grammer, Feb 23 '17 at 13:54
@N00bPr0grammer then i will get id's as 20,21,22,23....etc. but i need to get the bookingID value relevant to id — FIFA oneterahertz, Feb 23 '17 at 13:56
@911DidBush, consider putting your comment as an answer - OP doesn't seem to understand the difference! — N00b Pr0grammer, Feb 23 '17 at 13:58
@911DidBush well i did changes to the answer you said and it worked. Please put that comment as answer. — FIFA oneterahertz, Feb 23 '17 at 14:03

score 2 · Accepted Answer · answered Feb 23 '17 at 14:25

A Database table doesn't have a order itself. So there is no direct way to query for the last entry.

But since your table uses an autoincrement primary key id you can get the last inserted entry by sorting the result by this id in descending order and select the first result

Booking booking =  (Booking) session.createQuery("from Booking ORDER BY id DESC")
            .setMaxResults(1).uniqueResult();

This will get you the Booking with the highest id, now all you have to do is select the bookingId of that entry:

  return booking.getBookingId();

Yep Thanks for solving my issue which was there for a day. Have a nice day :) — FIFA oneterahertz, Feb 23 '17 at 14:29

score 1 · Answer 2 · answered Feb 23 '17 at 13:49

1

You can get the Maximum ID of your table like:

HibernateEntityManager he;

long lastID = ((Number) he.createNativeQuery("select max(id) from table;").getSingleResult()).longValue();

then you can get the bookingID from it with searching for the row with lastID

if you use JPA, it would be like this:

long bookingID;
bookingID = bookRepository.findOne(lastID).getBookingID;

answered Feb 23 '17 at 13:49

Beytullah Güneyli

518
2
8
21

Is there a query through "session" and "session factory" rather that "HibernateEntityManager"?? – FIFA oneterahertz Feb 23 '17 at 13:51
I don't know. I am a rookie myself. **HibernateEntityManager** is the only way i know so far. – Beytullah Güneyli Feb 23 '17 at 13:59

score 0 · Answer 3 · answered Feb 23 '17 at 14:17

0

Try this:

Booking bookList =  (Booking) session.createQuery("from Booking ORDER BY bookid DESC")
    .setMaxResults(1).getSingleResult();

answered Feb 23 '17 at 14:17

nkmuturi

248
3
10

How to get the last value of a column using HQL(Hibernate Query Language)

3 Answers3