Hibernate 4.3.10.Final, Postgresql 9.4

There is a web page to which table entries are displayed, there is also a pagination. Records in the table can be about a million. Records are displayed for 20,50,100 pieces, taking into account the filters set by the user. If the user has added a new record, it flies away to the server on AJAX, the server saves this entry to the database, now we have the id of the newly created entry and if we find out what account the record with this id will take into account the specified sorts, we can determine What page will this record be in order to give to the client only it, and focus on this new record.

I googled and read, mostly people write how easy it is to put a sequence number in front of the records in the database output, but is it that, to determine the page, you need to pull the entire million records every time?

Or a similar problem can somehow be organically solved using HQL?

Addition.

A million entries are supposed, but for the test I have not yet gotten them, I don’t know how it will work, but as a “temporary” solution, it’s kind of like you can use this nested query

select * from (select row_number() over (order by last_name, first_name, middle_name) as number, c.* from myschema.table c) as output where output.id = 5
  • one
    I can offer two alternatives that can solve the problem, but the answer to the question is not. 1 - After adding a new record, in the AJAX response, return the contents of the record to the client part, add it to the first place in the existing list and make it selected. Minus - when you update the list entry from it disappears. 2 - After adding a record, automatically change the user filters so that only the new record is displayed. Minus - the user hardly expects such software behavior. - bobzer

1 answer 1

From the moment of the question, I learned a lot about Hibernate, and there seems to be no solution in the box, so here’s an example of a solution:

 select * from (select row_number() over (order by last_name, first_name, middle_name) as number, c.* from myschema.table c) as output where output.id = 5