Best method to load a database

[vicenrico]

Hello. As I'm a newbie, I would like to better learn use of databases.

Which is the best method to load a database, supposing I have about 4000-5000 rows that consist of 30 columns.

I don't know if it's better to load "Select * from table" and load all the objects into an array or it's better take row by row when constructing table (jface tableviewer in my case) .

I mean:

Form1:

select * from table

arraylist<movie> moviesList=new arraylist<movie>

iterate trough resultset filling movieslist

OR

Form2:

With tableviewer whenever it needs to be redraw:

tableviewer.setdata(xxx)

load a movieobject from the table

fill the item of the tableviewer.

The problem with form1 is memory comsumption, and the problem with form2 is when the list must be redraw can call this method a lot of times.

Do you know alternative ways of database loading. I know about chunks loading but I don't like because I don't know methods to do the replacement: less recent used, and so on...

Sorry for the question, I know it's a very newbie question, but I'm beginning and I don't know how real people load databases: all objects in a list, or when needed by the table.

Thanks!!!

[Thotheolh]

Your questions are somewhat ambiguous. You need to be more specific on your question. 

What are you trying to load ? How is your database table like ?

Using the "select * from xxxtable" SQL statement is indeed very very memory intensive. It would be better if you can use conditions in the SQL statement to specify exactly what you want and simply take the results returned for your own use.

You can lookup H2 database website for more in-depth ways to manipulate SQL statements to fit into your situation. Also, you may want to read up on the W3 Schools SQL tutorials on the basic SQL manipulations ( http://www.w3schools.com/sql/default.asp).

[vicenrico]

Well, in fact, what I wanted to know what is better:

1) Load ALL registers into an array of <objects>, that has the drawback of memory comsumption OR

2) Load an object when required. 

I mean:

I have a programm with a list of objects (movies in my case, like a videoclub). This list, contains 50 movies to show the title. When the user scrolls down, the list show the next registers. If I load all the objects, these objects are in memory, and when the user scrolls the page, we don't need to load from disk to memory, to show in the list. But this is memory intensive, as you know.

Other option that I have currently, is to detect when the user scrolls the bar and call the code to load a register from database. The drawback with this, is that when the user scrolls fast this bar, the program is always loading registers one by one, and thus, scratching the disk (sorry for this sentence, I'm spaniard).  I would like to know, how real developers handle this case. I suppose they load pages of 50 registers, and so on, but I don't know really.

Could anybody help me with that?

 

[Andreas Henningsson]

Depends on the application. There is no "silver bullet" when it comes
to software development.

/Andreas

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/3Pusss9Tzu4J.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.



--
Andreas Henningsson

"Vanligt sunt förnuft är inte särkilt vanligt." -- Voltaire

[Thirion]

Hi Vicenrico,

Why don't you create a table with a pageable resultset by doing something like this:

Select title, release_date, rating (... or whatever columns you want to display, assuming you don't show all 30 columns in the table)

from movies

order by title

limit 50 offset 0

This will return the first 50 records that you load into your table.  Just remember to add the 'order by' clause, else your data might not be returned in the order you expect.  

Limit is the number of records on a page, and the Offset is the number of records to skip before returning results.

Now add buttons for First, Previous, Next and Last, that will move your offset (the current page) forward or backward.

So, if the user clicks Next, call the same query again, but use 50 for the offset, meaning it will skip the first 50 records and bring back the next 50.  The first page will have an offset of 0, the second 50, third 100 etc.

Add logic to your table so that it knows what page its on.