CODEWITHSUNDEEP
javamysql
JKK
JKK

Reputation: 446

How to Iterate across records in a MySql Database using Java

I have a customer with a very small set of data and records that I'd normally just serialize to a data file and be done but they want to run extra reports and have expandability down the road to do things their own way. The MySQL database came up and so I'm adapting their Java POS (point of sale) system to work with it.

I've done this before and here was my approach in a nutshell for one of the tables, say Customers:

I setup a loop to store the primary key into an arraylist then setup a form to go from one record to the next running SQL queries based on the PK. The query would pull down the fname, lname, address, etc. and fill in the fields on the screen.

I thought it might be a little clunky running a SQL query each time they click Next. So I'm looking for another approach to this problem. Any help is appreciated! I don't need exact code or anything, just some concepts will do fine

Thanks!

Upvotes: 0

Views: 314

Answers (4)

Renato
Renato

Reputation: 13690

I would say the solution you suggest yourself is not very good not only because you run SQL query every time a button is pressed, but also because you are iterating over primary keys, which probably are not sorted in any meaningful order...

What you want is to retrieve a certain number of records which are sorted sensibly (by first/last name or something) and keep them as a kind of cache in your ArrayList or something similar... This can be done quite easily with SQL. When the user starts iterating over the results by pressing "Next", you can in the background start loading more records.

The key to keep usability is to load some records before the user actually request them to keep latency small, but keeping in mind that you also don't want to load the whole database at once....

Upvotes: 2

bpgergo
bpgergo

Reputation: 16037

the key concept here is pagination.

Let's say you set your page size to 10. This means you select 10 records from the database, in a certain order, so your query should have an order by clause and a limit clause at the end. You use this resultset to display the form while the users navigates with Previous/Next buttons.

When the user navigates out of the page then you fetch an other page.

https://www.google.com/search?q=java+sql+pagination

Upvotes: 0

Perception
Perception

Reputation: 80603

Use JPA with the built in Hibernate provider. If you are not familiar with one or both, then download NetBeans - it includes a very easy to follow tutorial you can use to get up to speed. Managing lists of objects is trivial with the new JPA and you won't find yourself reinventing the wheel.

Upvotes: 0

user854577
user854577

Reputation:

Take a look at indexing your database. http://www.informit.com/articles/article.aspx?p=377652

Upvotes: 0

Related Questions