• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Paging and data change

 
Ranch Hand
Posts: 77
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 77
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 4501
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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)
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 4501
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 77
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 77
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think is the right way, it's simpler and it also reduces the calls to the database. Thank you for the help.
 
reply
    Bookmark Topic Watch Topic
  • New Topic