This discussion is archived
6 Replies Latest reply: Apr 21, 2013 8:04 AM by 991390 RSS

Pagination with Database

991390 Newbie
Currently Being Moderated
Hello. Is there any example with pagination and database integration? If not, is there an example how to handle more than 10000 of records with pagination. I tried out an example with some strings to present and of course that works fine. But what happens when i have to show more complex objects with 20 nodes and tousands of this objects.

Any advice and help would be great. Thanks in advance!
  • 1. Re: Pagination with Database
    shakir.gusaroff Expert
    Currently Being Moderated
    Hi. Try the following pagination control. The observable list contains 100,000 items.
    It works fine when the observableList contains fewer than 700,000 items.
    If you want to retrieve data from database populate the ObservableList with the resultset data.
    import java.util.List;
    import javafx.application.Application;
    import javafx.beans.property.SimpleStringProperty;
    import javafx.collections.FXCollections;
    import javafx.collections.ObservableList;
    import javafx.scene.Scene;
    import javafx.scene.control.Pagination;
    import javafx.scene.Node;
    import javafx.scene.control.TableColumn;
    import javafx.scene.control.TableView;
    import javafx.scene.control.TextArea;
    import javafx.scene.control.cell.PropertyValueFactory;
    import javafx.scene.layout.AnchorPane;
    import javafx.scene.layout.VBox;
    import javafx.stage.Stage;
    import javafx.util.Callback;
    
    public class DataPagination extends Application {
    
        final ObservableList<Person> data = FXCollections.observableArrayList();
        private Pagination pagination;
    
        public static void main(String[] args) throws Exception {
            launch(args);
        }
    
        public int itemsPerPage() {
            return 1;
        }
    
        public int rowsPerPage() {
            return 5;
        }
    
        public VBox createPage(int pageIndex) {
            int lastIndex = 0;
            int displace = data.size() % rowsPerPage();
            if (displace > 0) {
                lastIndex = data.size() / rowsPerPage();
            } else {
                lastIndex = data.size() / rowsPerPage() - 1;
    
            }
    
            VBox box = new VBox(5);
            int page = pageIndex * itemsPerPage();
    
            for (int i = page; i < page + itemsPerPage(); i++) {
                TableView<Person> table = new TableView<Person>();
                TableColumn numCol = new TableColumn("ID");
                numCol.setCellValueFactory(
                        new PropertyValueFactory<Person, String>("num"));
    
                numCol.setMinWidth(20);
    
                TableColumn firstNameCol = new TableColumn("First Name");
                firstNameCol.setCellValueFactory(
                        new PropertyValueFactory<Person, String>("firstName"));
    
    
                firstNameCol.setMinWidth(160);
    
                TableColumn lastNameCol = new TableColumn("Last Name");
                lastNameCol.setCellValueFactory(
                        new PropertyValueFactory<Person, String>("lastName"));
    
                lastNameCol.setMinWidth(160);
    
                table.getColumns().addAll(numCol, firstNameCol, lastNameCol);
                if (lastIndex == pageIndex) {
                    table.setItems(FXCollections.observableArrayList(data.subList(pageIndex * rowsPerPage(), pageIndex * rowsPerPage() + displace)));
                } else {
                    table.setItems(FXCollections.observableArrayList(data.subList(pageIndex * rowsPerPage(), pageIndex * rowsPerPage() + rowsPerPage())));
                }
    
    
                box.getChildren().add(table);
            }
            return box;
        }
    
        @Override
        public void start(final Stage stage) throws Exception {
            for (int k = 0; k < 100000; k++) {
                data.add( new Person("_" + k, "fname_" + k, "lname_" + k));
               
            }
    
            pagination = new Pagination((data.size() / rowsPerPage() + 1), 0);
            pagination.setStyle("-fx-border-color:red;");
            pagination.setPageFactory(new Callback<Integer, Node>() {
                @Override
                public Node call(Integer pageIndex) {
                    if (pageIndex > data.size() / rowsPerPage() + 1) {
                        return null;
                    } else {
                        return createPage(pageIndex);
                    }
                }
            });
    
            AnchorPane anchor = new AnchorPane();
            AnchorPane.setTopAnchor(pagination, 10.0);
            AnchorPane.setRightAnchor(pagination, 10.0);
            AnchorPane.setBottomAnchor(pagination, 10.0);
            AnchorPane.setLeftAnchor(pagination, 10.0);
            anchor.getChildren().addAll(pagination);
            Scene scene = new Scene(anchor, 400, 250);
            stage.setScene(scene);
            stage.setTitle("Table pager");
            stage.show();
        }
    
        public static class Person {
    
            private final SimpleStringProperty num;
            private final SimpleStringProperty firstName;
            private final SimpleStringProperty lastName;
    
            private Person(String id, String fName, String lName) {
                this.firstName = new SimpleStringProperty(fName);
                this.lastName = new SimpleStringProperty(lName);
                this.num = new SimpleStringProperty(id);
            }
    
            public String getFirstName() {
                return firstName.get();
            }
    
            public void setFirstName(String fName) {
                firstName.set(fName);
            }
    
            public String getLastName() {
                return lastName.get();
            }
    
            public void setLastName(String fName) {
                lastName.set(fName);
            }
    
            public String getNum() {
                return num.get();
            }
    
            public void setNum(String id) {
                num.set(id);
            }
        }
    }
  • 2. Re: Pagination with Database
    991390 Newbie
    Currently Being Moderated
    Shakir.Gusaroff, thanks for your fast response!

    The only missing puzzle now is the population of the ObservableList with database. So for example i have a searchresult with 100000 records. But i don't actually load all of this 100000 records from the beginning, do i? I only catch the first 25 results or whatever. When i then go to the second page i load 25-50 and so on. When and how do i populate the ObservableList... hm. How does the ObservableList gets the data from the database automatically. How does the ObservableList or the Paginationcontrol knows that there are 100000 items to show but only loads always pages from the database..

    In your example you have data.size() == 100000 from the beginning. But actually when using a database i would have only 25 at the first page.... I know that
    "pagination = new Pagination((data.size() / rowsPerPage() + 1), 0);". From my query i know that i have 100000 records to show so i can set "..new Pagination(TOTAL, 0);" but what do i have to do with the ObervableList..

    Damn i don't get it ;-) Thanks again for any help.
  • 3. Re: Pagination with Database
    shakir.gusaroff Expert
    Currently Being Moderated
    But i don't actually load all of this 100000 records from the beginning, do i?
    Yes, you load all 100,000 records .
    When and how do i populate the ObservableList... hm.
    You can use a task to populate an observablelist from the database
    Task task = new Task<Void>() {
      @Override
      public Void call() {
        Platform.runLater(new Runnable() {
          public void run() {
       
         //Use jdbc, jpa, hibernate to populate from database
          }
        });
      }
    };
    How does the ObservableList or the Paginationcontrol knows that there are 100000 items to show but only loads always pages from the database..
    First you populate all records from the database to the observableList . The observableList and the pagination control will take care everything else.
  • 4. Re: Pagination with Database
    drenda81 Newbie
    Currently Being Moderated
    Hi,
    We'are using Graniteds and seems to us a very nice implementation. It give you the ability of pagination. You can read a tutorial here: [http://java.dzone.com/articles/data-management-javafx-and]
  • 5. Re: Pagination with Database
    991390 Newbie
    Currently Being Moderated
    Ok, thanks again!

    Now it could be that this requires to load some MB loading it all. But when i am loading this list and then i search for a new resultset of only 10 records i have to load all records again. I have to reload all MB again and again. I can do the search localy but then i don't have searchcapabilities like i have on the serverside...

    One thing i can do is to hold this complete list in memory and use it when i need it. For temporary searchresults i use this smaller resultsets... Is this the proper way to go, holding a complete list in memory?

    I was thinking that there must be a solution where you know the max pagesize but loading the items while switing form page to page?
    Best regards.
  • 6. Re: Pagination with Database
    991390 Newbie
    Currently Being Moderated
    .. I am still lost in this detail. So, in your example you are loading all 10k items in the background. In my scenario i load them from a server and i don't want to load all items when the user only needs the first two pages for example.

    I was playing around with that and now i am lost somehow. Lets assume i need a solution where i only load 50 items per page. Where should i start my task to get them from server. When the task is finished i should show the next page with this 50 items. But how is that done with a task which loads the 50 items. When i write

    pagination.setPageFactory(new Callback<Integer, Node>() {
    @Override
    public Node call(Integer pageIndex) {         
    return createPage(pageIndex);
    }
    });

    the pagination does the rendering by itself within createPage. So in createPage i could start a task which loads the 50 items but meanwhile the the vbox with its items is already rendered.. rendered without the items because they are still loading.. The vbox should only be renedered after the load of this 50 items. Whats the correct approach?

    my obviously wrong implementation for createPage:

    public VBox createPage(int pageIndex) {
    ...
         final int page = pageIndex * itemsPerPage();
    ...
         
         loadItemsInATask();// where do i need to put this task.??
         
         
         VBox box = new VBox();     
    box.getChildren().add(... items ...); // which are not ready yet.
         

         return box;
    }

    Hm, sorry - hope anybody understands my silly problem. So i need a possibility to recognise the event of go to the next page, load items in background, when loading complete show the next page with the next 50 itmes..

    There ist pagination.currentPageIndexProperty().addListener... which let me know the change. If i use this event, how to use the pagefactory..
    Thanks again for any help!!

    Best regards, T.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points