Paging and data change

[Emanuele Mazzante]

Hello everyone,
I am doing a small application to learn how to use MySQL, the MVC pattern and Java in general. The application in question is a simple frame that connects to a MySQL database which contains a table that stores the expenditure incurred by a family (the fields are date, transaction type, amount, notes) and displays it using a JTable. Several JCombo allow the user to filter the transactions by year / month / type.

The paging that I would use is the classic next/previous button and a label that shows the current page number out of a total of n pages. A further JCombo allows the user to choose the number of lines per page.

Now I can do this type of paging with a first query that counts the total number of records (to calculate the total of pages) and a subsequent query with LIMIT clause to show results.

Then insert the buttons for the "Add operation", "change operation" and "delete operation." The addition and modification are then carried out with a new frame. But at this point I'm stuck.

The question that haunts me and that I can not solve is the following: if I'm flipping through the pages on my pc while my wife with her pc is inserting / modifying / deleting an expense, my pagination may fail.

How handles the paging while multiple users are connected simultaneously reading and changing values ​​to the same table?

I hope I have explained understandably the problem. Thank you for your help.

[Ulf Dittmer]

I think most implementations I've seen ignore this problem. So if you now see records 1-20, and before you click "next" #20 is deleted (thus making #21 #20), the next page would show you former #22 to #41 - so you would never see former #21.

If that is not acceptable for some reason, then you would have to implement some sort of user session that caches which results the query originally returned (maybe as a list of all IDs), and then subsequent pages would retrieve the next 20 records via the IDs in that list (skipping those that no longer exist).

[Emanuele Mazzante]

Thanks for the help.

Ulf Dittmer wrote:I think most implementations I've seen ignore this problem. So if you now see records 1-20, and before you click "next" #20 is deleted (thus making #21 #20), the next page would show you former #22 to #41 - so you would never see former #21.

this situation is acceptable but I can not figure out how to solve the problem with the label that displays "Current page / total pages". Let me give an example to better explain:

)- We assume that there are 21 records in the database and that the user chooses to display 20 records per page

)- The program starts with the first query like SELECT COUNT (*) ... to find the total number of records and calculate the total pages (in this case 2)

)- A second query with the LIMIT clause shall recover the data, then populates a CachedRowSet that is passed to the table model

)- A label will show "Page 1 of 2" and the button "Next Page" will be enabled

Now if another user logs on to the table and delete any record, the total would become 20 and page 2 would not exist. If the user pressed the "next page" would find an empty table.

How can I handle this situation?

[Ulf Dittmer]

The DB query would return no records, so you would display a page that says something like "page 10 of 10" and "0 records".

Throughout your code you always need to handle the case that a DB query returns no results, even if you expect it to return some. This case is no different.

[Ron McLeod]

MySQL has a feature where you can run a CHECKSUM on the table -- the value returned will change after a INSERT/UPDATE/DELETE operation. Maybe use could use this with your pagination logic to determine if the table data has changed, and then query the database again for updated data.

mysql> CHECKSUM TABLE EquipmentBundles;
+-----------------------------+------------+
| Table                       | Checksum   |
+-----------------------------+------------+
| Subscriber.EquipmentBundles | 1155734498 |
+-----------------------------+------------+
1 row in set (0.00 sec)00 sec)

[Paul Clapham]

Yeah, but then you're wading into a swamp. Suppose you are looking at rows 1 to 10, and in the meantime somebody deletes rows 6, 7, and 12 and inserts another row between 8 and 9. Then you click "next", expecting to get rows 11 to 20. Which rows should the application show you?

Which is why most paging applications (including the Ranch) don't try to be clever and just ignore the issue entirely.

[Ron McLeod]

That's true, but the paginator could just use this to pop-up a dialogue to inform the user that the data has changed, and offer an option to reload everything (or not). It might be annoying though if the data was constantly changing.

[Paul Clapham]

I've seen the problem in action right here, for example with the "Recent Topics" page. It's paginated and if you page backwards and forwards while new posts are being made, you'll notice changes at the top of the pages as you do that. Or more likely you won't even notice. In that case I'd consider your warning to be just annoying. But sure, it might be warranted in other applications where it was important that the user see everything while paging through.

[Emanuele Mazzante]

Thank you all for taking the time to me

I did not know the checksum function of the mysql table.
I could for example add a label in the frame that alerts the user about changes when he presses the next/previous button without using annoying pop-ups.

Personally I do not like the situation in which a label showing the word "page 1 of 10", and when you get to "page 10/10" show an empty table because in the meantime other users have deleted rows by reducing the number of pages to 9.
Maybe it's better to display only the current page without showing the total number of pages.

[Dave Tolls]

Tom Kyte (of Oracle's "Ask Tom" column) usually argues against bothering with the "page x of y" thing, for just this reason.
Simply provide a "Next" button if there is (at the time of generating the page) another page, and a "Previous" one if you're not on the first page.
If you move to Next and there aren't anymore, then simply display the last page, this time with no Next button.

[Emanuele Mazzante]

I think is the right way, it's simpler and it also reduces the calls to the database. Thank you for the help.