Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

How do I implement pagination in JDBC?

 
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, ranch!

I am working on a very advanced media manager. I am using a javaDb emebedded database to store my data in. This is a very large question for me so I don't have any code samples to provide here just yet. I so far just wan't to try to understand the basics in implementing pagination in jdbc after a customized model and the fact that I know it differs from MySQL paginationation practices if I am not wrong.

Lets say I have a JFrame that contains a JTable with an AbstractTableModel registered with it, four buttons (first row,previous row,next row,last row) and a JComboBox that lets the user select the total records to be shown in the JTable ( 10,20,30 records or so on - this should be incremented until the limit of records in the database ).  

Heres an example of a ascii drawing of what I have in mind for the gui part of this question:

------------------------------------------------------------------
FIRST NAME   | LAST NAME | EMAIL                                 |
------------------------------------------------------------------
MARK            | STRONG     | markstrong@email.com        |
------------------------------------------------------------------
TOM              | SMITH        | tomsmith@email.com           |
------------------------------------------------------------------
REBECCA       | DOE           | rebeccadoe@email.com        |
------------------------------------------------------------------
+ MORE ROWS to the selected row limit (in the JComboBox )
------------------------------------------------------------------
| FIRST ROW (JButton) | | PREVIOUS ROW (JButton)  | | 10-limit (JComboBox goes here ) | | NEXT ROW (JButton)  | | LAST ROW (JButton)  |

I hope someone sees what I am trying to say. My knowledge when it comes to sql are on a basic level so far. I have encountered a little paging with mysql and PHP before
but that was through some tutorials that I studied very carefully. How could one implement paging like in the model that I have in mind using oracle ? I'm very curious ...

Best wishes and regards,
Robert!









 
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you're talking about a situation where you make a query, retrieve the first 10 records, make a query, retrieve the next 10 records, and so forth, that, unfortunately was not part of the original SQL spec and different vendors have implemented that process in different ways - if at all.

It gets messy also partly because on a multi-user database, other people can be adding, removing, or changing records in your original query set, throwing off the sequencing of the other records in the set (unless you capture a materialized View, which, again, isn't a universal SQL concept). Even on a static query, if there are columns whose return order isn't nailed down there can be some wobble.

On the whole, it's better to simply limit the query to return only a relatively small number of records than to try and paginate it. When presenting a table in a GUI, most people's eyes start watering after the first 200 rows or so, and they'll likely not look at anything further anyway. paged or not.
 
Marshal
Posts: 25452
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Robert Ingmarsson wrote:I hope someone sees what I am trying to say. My knowledge when it comes to sql are on a basic level so far.



I think so, although it looks like you're asking about paging in the database because you have this GUI design which is fencing you in. So there's the JTable, but instead of putting it into a scroll pane you're making the user click to see the next few rows. What for? Only 10 records at a time? Why can't you just put everything into the JTable and let the user scroll up and down?

Maybe you're going to say, but there's 2 million rows in my database table. It doesn't make any difference. Like Tim said, nobody is going to sit there and page through the 2 million rows, 10 rows at a time. Not even 1,000 rows at a time. You're going to provide them with some features to search for the rows they really want to see.

And I'm speaking as a person who wrote a JList and put 10,000 entries into it. It was damn hard to scroll up and down to find what you wanted, but having to click through 100 entries at a time would have been even harder. (That JList didn't stay in the design for long.) So I'm basically pushing back on the idea that you need to do paging to get subsets of a query. At least, not based on what you've shown us so far.
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, back a few years ago I was responsible for an app that had to cross-check people against official US Government lists of terrorist and drug people and organizations. Probably 50,000 or more entries produced each day. But we scored them based on how close a match was to the lists, so the actual app users probably only checked 100 people at most.

But God help you if your name was Guadalupe Ortiz. The offender on the list was actually a travel agency fronting drug smuggling, but it is also a very popular hispanic personal name. Might as well be José Garcia (most common name in the Miami phone book).
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best, Mr. Holloway!

Wow! I am impressed on the stories of your experiences you contribute with in your replies, only them enlighten me in this quest a lot, thank you very much for that. It sounds that you are saying something like "that I am better off skipping the paging part in my project since nobody cares about the latter halfs of the millions of entries in it anyway" - am I following you now or am I way off the course?

I figure I need this paging implementation anyway. But I am so far away from any solution. I don't even know where to start. Does jdbc even supports the "LIMIT" clause in its SQL implementation like MySQL does?
How does a "simple" sql-query string looks like in jdbc that handles paginated data that shows ten,twenty,thirty records at a time?

Best regards,
Robert!
 
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
JDBC doesn't have its own "SQL", unlike JPA JPQL. So the limits of the SQL that you use with JDBC are whatever is possible with the SQL dialect implemented by that particular database.

I wouldn't say that there's never a use for pageable, much less scrollable data tables, but there's not nearly as much as people think.

Actually, that reminds me of a really old story from my first year in the profession. I was working for a service subsidiary of an insurance company and my department provided actuarial software and support. The client wanted an incredibly detailed report ouput every night. It was one of those things that organized by about 7 different levels. We estimated that it would, when printed at the standard 66 lines per page and about 2500 pages per carton of fan-fold printer paper output about 7 cartons of paper. Every night.

We were trying to talk them down, and I think eventually succeeded, but at that point, I'd moved to another job, so I missed the fun.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best Mr. Holloway!

That's what I call paper-work.

I have a question that has bothered me for quite some time now. If my database contains a very large set of records and I wan't to let the user select how many records that will be shown in the JTable through a JComboBox that splits the index of records in the database with numbers of 10,20,30 (and so on) all the way to the max size. In that case, whats the procedure in the real world for jdbc. Maybe more of a Math question here than an sql based one. My math is like my java, good in detail but still very basic.

Best wishes,
Robert!
 
Saloon Keeper
Posts: 6384
158
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This may also be of interest: https://coderanch.com/wiki/659956/Pagination-Paging
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Mr.Moores!

Thank you so much for the posted link. I have read it. But I'm still very stuck. It's tricky that I don't even know where to start. I only know that I must have four buttons which will take care of the navigation between the records in the database and a JComboBox allowing the user to select how many rows the JTable should hold at a time. Am I thinking correctly when it comes to the JComboBox part? Should the values in it be incremented by the tenfold from start at 10 to the size of the database or should the values in it be incremented after another pattern say 20,30,100. Im very confused at the moment sorry I am working very hard on this quest.

Best regards,
Robert!
 
Tim Moores
Saloon Keeper
Posts: 6384
158
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would say put as many rows/records on a page as can be displayed without scrolling. If the user is expected to page through results anyway, there's not much point in making him scroll as well. So it could be 10, or 20, of whatever fits on a screen in your app.

While it seems user-friendly to let the user enter any number he pleases, most often I see a small, fixed set of numbers to choose from - maybe 10, 25 and 50.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, ranch!

During my research on paging in jdbc I have encountered an sql-based term called ROWNUM. Could someone please explain to me how you use it in an sql-string and what it does.

Very kind regards,
Robert!  
 
Tim Moores
Saloon Keeper
Posts: 6384
158
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Searching for "SQL rownum" indicates that's an Oracle thing. What DB are you using?
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Mr. Moores

I am using the embedded apache derby database.

//Robert.
 
Tim Moores
Saloon Keeper
Posts: 6384
158
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Then it would appear not to be applicable.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Mr. Moores

This sql-string seems to work. I think I got it now what ROW_NUMBER() does, I was wrong about ROWNUM, I was thinking about the former.



I can see the solution to accomplish paging in jdbc so clearly before me. Everything is just a mess full of ideas.

Regards,
Robert!
 
Tim Moores
Saloon Keeper
Posts: 6384
158
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If I search for "pagination apache derby", the very first result (on StackOverflow) seems spot on. Have you seen that?
 
Rancher
Posts: 4549
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Derby uses OFFSET ad FETCH for pagination.


The offset '?' is where you got to on your previous SELECT (so 0 for page 1, or 20 for page 2 (assuming 20 rows per page)).
The fetch '?' is how many rows to bring back (so the number to show on a page).
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, ranch!

Sorry for a late reply. I have been experimented a lot with java derby's sql functions offset and fetch functions. I managed to get it working in one way only and that was to select the first say 10 rows in my main table. Now I am working very hard on a way to select the previous 10 rows in my table using the sql code below this line



But this results in an error exception saying "Row count for FIRST/NEXT must be >= 1 and row count for LIMIT must be >= 0" - when I'm trying to select any previos rows in the table. Does anyone have an idea on how you can retrieve the previous 10 rows in a table using the highlighted sql code?

Very kind regards,
Robert!

 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You have to issue a whole new JDBC SQL request. The OFFSET is the absolute row number within the current result set, but the current result set is discarded when you have read all the rows - or abandon it. So to get the previous 10 rows, you have to replace the value you used for OFFSET with one that is 10 less. Provided that 10 less is still greater than 1. Otherwise use 1, since there's no such thing as a negative row number.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Mr. Holloway!

Thank you very much for your reply. My sql knowledge is very basic. I am having a big issue with the sql I so far got in my project. I am working on a small sample of code demonstrating what I have in mind for a paginated JTable to post here. But it's the sql part that is bothering me. Maybe you can provide a code snippet of an sql fetching the previous ten records in a table? This would help me a lot.

Kind regards,
Robert!    
 
Sheriff
Posts: 7056
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you know how to issue a prepared statement in Java?
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Mr. Snortum

I know about PreparedStatements in jdbc. I use them a lot. Below is a code snippet of a method I call populateTableModel which is adding rows in my JTable.



I'm very sorry for this small sample of code that I posted. But I just wan't to show that I both understand and use PreparedStatements in my project. Its the sql part that I am worried about. The above method doesn't apparently work for fetching previous rows in a table. I must be missing something really big here.

Best regards,
Robert.

 
Knute Snortum
Sheriff
Posts: 7056
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the full error message, with the stack trace, that you get when you execute that code?
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry. Here is the full stack trace.



//Robert.
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Also, I wouldn't fire model changes in the loop that's populating the model. Do all the populations and THEN fire the changes. Less overhead and probably less display flicker.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:Do all the populations and THEN fire the changes. Less overhead and probably less display flicker.



Corrected!



//Robert
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!

I am just wondering something. I'm having a lot of trouble with selecting the previous rows in a table. Should I have a sql statement that uses both the "order by column_name asc" and "order by column_name desc" to fetch previous rows? i have currently an sql string that looks like below:



... where the variable direction points to a boolean object, so the sql statement says if true "order by column_name asc" and if false "order by column_name desc".  Am I am on the right tracks or not? Any response are great much welcome here!

Best wishes,
Robert!

 
Paul Clapham
Marshal
Posts: 25452
65
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When you start, you want to select (let's say) rows 1 to 10. Then you choose "Next" and you then want to select rows 11 to 20.

Now you choose "Previous". Simple arithmetic says you want to select rows 1 to 10. There's no need for anything more complicated as far as I can see.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, all!

I am having a big issue with learning the offset -and fetch clauses in oracle. I can't understand how it is possible to select the previous 10 records in a situation when you can't use negative numbers against offset or fetch in oracle, because if you do you get the "Invalid row count" - exception. I have tried to use order by desc statement to fetch the previous rows but that doesn't work either. I need every help or hint I can get. I think I have managed to select the next 10 records in a table though but its more difficult with the previous ones.  

Very best wishes to all!
//Robert.

 
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's because you're thinking of an SQL statement as something that fetches from an existing pool of rows.

What actually happens is that a brand new pool is (effectively) created for each and every SQL SELECT statement that you execute. Even if the command you're executing is the exact same SQL as the last one you did.

You can't go "back", because there is no back. You are building this pool of rows and then telling Oracle to return a subset of those rows, starting at row "X" and continuing for "N" rows. But the next SQL request you make, a whole new pool of rows will be constructed. "X" is an absolute position, not a relative position.
 
Knute Snortum
Sheriff
Posts: 7056
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't think you need to use negative numbers at all.

User presses "next": offset 10, limit 10
User presses "back": offset 0, limit 10
If offset is 0, don't display the "back" button, or make it a no-op.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, again!

Thank you so much for your kind replies Mr. Holloway and Mr. Snortum! I can clearly see the solution before me now thanks to your hints and advices. From what you told me I understand that there is a fixed X position in the rows if I'm not wrong. But how can you then move the cursor back and forward in oracle? I have here posted code for two classes that I am working on in my project. Before I go on I must point out that the code in this post is NOT SSCCE-friendly, I am truly sorry for that, but I just wan't to show you how far I have gotten in this quest of mine. Right now I am working on the forward technique so to speak or the next (limited) rows action event. The error I am doing is somewhere in theese classes that I will post. I hope you can forgive me for this.

Here is the first class, my table model that extends AbstractTableModel which takes care of the table row data.



And the class below is called TableRowAction and extends AbstractAction and takes care of the event handling part. I am using four JButtons and a JComboBox to handle the pagination of records in my main JTable.



I hope someone can see what I am trying to do and what I am not doing. I am struggling incredibly much on this part of my project so I am very thankful for any kind of response or criticism.

Many best regards to all,
Robert!



 
 
Knute Snortum
Sheriff
Posts: 7056
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know if this will help, but I see two logic errors in your code.  The first is in MediaTitleModel, in the method getNext().  This method will always return false.  Somewhere you have your logic flipped.  I would probably change the if statement to this:
The next one is more minor, I think.  In TableRowAction you have two if statements where you should probably have just an if-then-else.  In the method jBtnNextRowsPressed(), I would change the two if statements to be this:
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best, Mr. Snortum!

Thank you very much for your reply. I have applied your suggested changes to my project. There is however a giant problem. If I change the number of rows in the jCbRowIndexSelected() - method via the JComboBox, then I can't set the cursor to fetch the chronological next 10 rows so to speak, it only selects the rows the current position X. What I wan't is to be able to select rows in natural order not always the next rows I mean.

For example if i have selected the first plus 10 rows with a JButton I wan't to show the 11 - 20 rows after I have changed the index via my JComboBox. Now it displays the rows 20 - 30 after a change in the JCbRowIndexSelected - method. I'm so "in the zone" right now, but I hope Im clear enough on what I am struggling with.

Very best regards,
Robert!

 
 
Paul Clapham
Marshal
Posts: 25452
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I find that hard to understand. So let me try paraphrasing it with an example:

"I displayed rows 1 to 10 in my GUI. The GUI had the side effect of changing the database so that the original row 10 is now no longer row 10. So now how do I display the next 10 rows?"

What I wan't is to be able to select rows in natural order not always the next rows I mean.



I find that impossible to understand. I was under the impression that you had defined "row number" to use sequential numbering based on some chosen index. But apparently there's more than one definition of "row number" in your mind.
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Near as I can make out, the system is supposed to be doing a single retrieval of a slice of results and to maintain a cursor so that multiple calls to it will return multiple rows. It sounds rather horrible, but that appears to be what it is.
 
Paul Clapham
Marshal
Posts: 25452
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The original post did say "very advanced"...
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello!

It seems that I have confused up things a little. Very sorry for that. I'll try to describe my problem more clearly. Ok, let's say I have 25 rows in a table in my database, and I wan't to let the user select how many rows there is to be shown in the JTable, I do this in a JComboBox. Say the user has selected the next 10 rows with the next button and is on page 2 showing rows 11- 20 then the user selects the first 10 rows and go back to page 1 with the JComboBox. If the user now presses the "Next" button the counter skips page 2 and jumps to page 3 showing rows 21-25 instead of 11 - 20. There lies the problem I have encountered now and trying to fix but I'm really lost here.

Im sorry for being so unclear earlier. This pagination thing is the toughest challenge I have encountered so far. But thanks to all that has replied to this post. Big help!

Regards,
Robert!
 
Knute Snortum
Sheriff
Posts: 7056
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Say the user has selected the next 10 rows with the next button and is on page 2 showing rows 11- 20 then the user selects the first 10 rows and go back to page 1 with the JComboBox.


I'm not sure what this means, but when this happens, your page number variable needs to decrement.  
 
Grow a forest with seedballs and this tiny ad:
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic