Sorting huge data using ZK"

From Documentation
m (correct highlight (via JWB))
 
(26 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
{{Template:Smalltalk_Author|
 
{{Template:Smalltalk_Author|
 
|author=Jumper Chen, Engineer, Potix Corporation
 
|author=Jumper Chen, Engineer, Potix Corporation
|date=March 10, 2011
+
|date=March 16, 2011
 
|version=ZK 5.0.6
 
|version=ZK 5.0.6
 
}}
 
}}
 
   
 
   
{{Template:UnderConstruction}}
 
 
   
 
   
 
=Introduction =
 
=Introduction =
In the [[Small_Talks/2009/July/Handling_huge_data_using_ZK|previous article]], we taught you how to implement a paging with huge data. Here, we will extend that example to simplify its model implementation and add a sorting function to work with the huge data. In the following example, the paging navigation and the sorting function are handled by Database query result so that we can reduce the memory usage and load the data on demand.
+
In the [[Small_Talks/2009/July/Handling_huge_data_using_ZK|previous article]], we taught you how to implement paging with huge data. Here, we will extend that example to simplify its model implementation and add a sorting functionality to work with the huge data. In the following example, the paging navigation and the sorting function are handled by Database query so that we can reduce the memory usage and load the data on demand.
  
 
=Demo =
 
=Demo =
Line 15: Line 14:
  
 
=Creating the database =
 
=Creating the database =
If you have already seen the [[Small_Talks/2009/July/Handling_huge_data_using_ZK|previous article]], you can jump to the [[#SortingPagingListModel]] section directly.
+
If you have already seen the [[Small_Talks/2009/July/Handling_huge_data_using_ZK|previous article]], you can jump to the [[#SortingPagingListModel|SortingPagingListModel]] section directly.
 
   
 
   
 +
 
We create a database named livedata and create a table named tblusers. We will provide the following columns, id (INT, AI, PK), first_name (VARCHAR), last_name(VARCHAR) and phone(VARCHAR).
 
We create a database named livedata and create a table named tblusers. We will provide the following columns, id (INT, AI, PK), first_name (VARCHAR), last_name(VARCHAR) and phone(VARCHAR).
 
   
 
   
Line 28: Line 28:
 
                                       phone varchar(20) NOT NULL, PRIMARY KEY (id) )  
 
                                       phone varchar(20) NOT NULL, PRIMARY KEY (id) )  
 
                                       ENGINE=InnoDB DEFAULT CHARSET=utf8;</source>
 
                                       ENGINE=InnoDB DEFAULT CHARSET=utf8;</source>
+
 
 
=The database screen =
 
=The database screen =
 
   
 
   
Line 84: Line 84:
 
   
 
   
 
=SortingPagingListModel =
 
=SortingPagingListModel =
   
+
The implemenation of SortingPagingListModel is very simple and clear while handling both sorting and paging. It simply extends from the <javadoc>org.zkoss.zul.AbstractListModel</javadoc> implementing the '''sort''' method from the interface <javadoc type="interface">org.zkoss.zul.ListModelExt</javadoc>. Here's how it looks.
   
+
 
   
+
== Override from AbstractListModel ==
 +
<source lang="java" highlight="5">
 +
    @Override  
 +
    public Object getElementAt(int index) {
 +
      if (_cache == null || index < _beginOffset || index >= _beginOffset + _pageSize) {
 +
              _beginOffset = index;
 +
              loadToCache(index, _pageSize);
 +
          }
 +
          return _cache.get(index - _beginOffset);
 +
      }
 +
</source>
 +
 
 +
As you can see, in the getElementAt() method we use a ''_cache'' list object to cache the data fetched from the database. In this case, we specify the page size as 100 and when the index parameter goes out of the bounds of the cache, we reload the data to the ''_cache'' object again. Note that, the cached list must be cleared for better performance and less memory usage.
 +
 
 +
<source lang="java" highlight="4">
 +
    @Override
 +
    public int getSize() {  
 +
        if (_cachedSize < 0) {  
 +
          _cachedSize = DatabaseInformation.dao.getUserCount(); 
 +
        } 
 +
        return _cachedSize; 
 +
    }
 +
</source>
 +
In the example above, we use the variable ''_cachedSize'' to cache the total size coming from the database to avoid the redundant database connection when the method is invoked.
 +
 
 +
== Implement for ListModelExt==
 +
<source lang="java" highlight="2,3">
 +
 
 +
    @Override
 +
    public void sort(Comparator comparator, boolean flag) {
 +
      if (comparator instanceof FieldComparator) {
 +
          _dir = flag ? 0 : 1;
 +
          _cache = null;
 +
          _orderBy = ((FieldComparator)comparator).getRawOrderBy();
 +
          fireEvent(ListDataEvent.CONTENTS_CHANGED, -1, -1);
 +
        }
 +
    }
 +
</source>
 +
 
 +
As you can see, we cast the compartor ton a FieldComparator which we use for the sorting declaration in ZUML, example ('''sort="auto(fieldName)"'''). In this case, the fieldName should be the same as the field name created in the database.
 +
 
 +
For further details, please refer to [[ZK_Developer's_Reference/Performance_Tips/Listbox,_Grid_and_Tree_for_Huge_Data/Implement_ListModel_and_TreeModel|the document of Implement ListModel and TreeModel]].
 +
 
 
=Building the UI =
 
=Building the UI =
 
   
 
   
Line 102: Line 144:
 
The markup below is the ZUL code from our hugedata2.zul
 
The markup below is the ZUL code from our hugedata2.zul
 
   
 
   
<source lang="xml" high="11,12,13,14">
+
<source lang="xml" highlight="11,12,13,14">
<window title="Huge data demo" width="720px" border="normal" apply="org.zkoss.zklargelivelist.controllers.MainUserController2">
+
<window title="Huge data demo" width="720px" border="normal"
 +
apply="org.zkoss.zklargelivelist.controllers.MainUserController2">
 
           <label value="If you need to generate 1,000,000 entries please press this button!" />
 
           <label value="If you need to generate 1,000,000 entries please press this button!" />
 
           <button id="btnGenerate" label="Generate database entries" />
 
           <button id="btnGenerate" label="Generate database entries" />
Line 110: Line 153:
 
           <button id="btnDeletepage" label="Delete the page" />
 
           <button id="btnDeletepage" label="Delete the page" />
 
           <separator />
 
           <separator />
         
 
 
           <grid id="dataGrid" width="700px" mold="paging" pagingPosition="top">
 
           <grid id="dataGrid" width="700px" mold="paging" pagingPosition="top">
 
                   <columns>
 
                   <columns>
Line 120: Line 162:
 
           </grid>
 
           </grid>
 
</window></source>
 
</window></source>
+
 
 
 
=Controller functionality =
 
=Controller functionality =
 
   
 
   
 
   
 
   
The controller '''MainUserController2.java''' handles one GUI events and overrides the ''doAfterCompose'' method. Firstly let us deal with the ''doAfterCompose'' method and explain what it does. The method is called after the components have been initialized. This allows us to manipulate the components before the user interacts with them.
+
The controller '''MainUserController2.java''' handles two GUI events and overrides the ''doAfterCompose'' method. Firstly let us deal with the ''doAfterCompose'' method and explain what it does. The method is called after the components have been initialized. This allows us to manipulate the components before the user interacts with them.
 
   
 
   
 
   
 
   
 
== doAfterCompose ==
 
== doAfterCompose ==
+
<source lang="java" highlight="13">
 
<source lang="java" high="13">
 
 
           public void doAfterCompose(Component comp) throws Exception {
 
           public void doAfterCompose(Component comp) throws Exception {
 
                   super.doAfterCompose(comp);
 
                   super.doAfterCompose(comp);
Line 148: Line 187:
 
   
 
   
 
   
 
   
We create our own custom row renderer to format the data and then create a '''SortingPagingListModel''' for it. Notice the speed and low memory usage of this as we only pull the amount of users that we require for one page and hold that in memory!  
+
We create our own custom row renderer to format the data and then create a '''SortingPagingListModel''' instance for it. Notice the speed and low memory usage of this as we only pull the amount of users that we require for one page and hold that in memory!  
 
   
 
   
 
== GUI events ==
 
== GUI events ==
   
+
 
<source lang="java">
+
The other two functions are related to handling GUI events, the first is ''onClick$btnGenerate'' which is used to generate 1,000,000 entries in our database. And the other is the ''onClick$btnDeletepage'' which is used to remove the data within the page. For example,
 +
 
 +
  <source lang="java" highlight="17">
 
           public void onClick$btnGenerate() {       
 
           public void onClick$btnGenerate() {       
 
                   int numberOfUsers = DatabaseInformation.dao.getUserCount();
 
                   int numberOfUsers = DatabaseInformation.dao.getUserCount();
Line 167: Line 208:
 
                                        
 
                                        
 
                             DatabaseInformation.dao.insertUser(users);
 
                             DatabaseInformation.dao.insertUser(users);
                                                       
+
 
                             refreshModel(userPaging.getActivePage());
+
                             // reset new model
 +
                            dataGrid.setModel(new SortingPagingListModel());
 
                   }
 
                   }
 
                   else
 
                   else
Line 179: Line 221:
 
                   }
 
                   }
 
           }</source>
 
           }</source>
   
+
As you can see, we recreate the ''SortingPagingListModel'' object to the dataGrid when the data has been updated.
+
<source lang="java" highlight="7">
The other two functions are related to handling GUI events, the first is ''onClick$btnGenerate'' which is used to generate 1,000,000 entries in our database.
+
    public void onClick$btnDeletepage() {
 +
        //delete page from the database
 +
        SortingPagingListModel model = (SortingPagingListModel)dataGrid.getListModel();
 +
        int act = dataGrid.getActivePage();
 +
        int pgsz = dataGrid.getPageSize();
 +
        for(int i= act * pgsz, j = i + pgsz; i < j; i++) {  
 +
          model.remove(i);
 +
        }
 +
    }
 +
</source>
 +
In the example above, we introduce a method called '''remove''' to remove the item from the database. It is questionable why don't we remove the item outside the model? Yes, we can, but the data where cached in the model won't be up to date.
  
 
=Configuration=
 
=Configuration=
Turn on the setting of Render-on-Demand (ROD) for this example. You can refer to [[ZK_Developer's_Reference/Performance_Tips/Listbox,_Grid_and_Tree_for_Huge_Data/Turn_on_Render_on_Demand|this setting]].
+
As suggested in [[ZK_Developer's_Reference/Performance_Tips/Listbox,_Grid_and_Tree_for_Huge_Data/Turn_on_Render_on_Demand|Performance Tips]], we turn on render-on-demand (ROD) in this example. It is done by specifying the following in <code>WEB-INF/zk.xml</code>.
 +
 
 +
<source lang="xml">
 +
<library-property>
 +
<name>org.zkoss.zul.grid.rod</name>
 +
<value>true</value>
 +
</library-property>
 +
<library-property>
 +
<name>org.zkoss.zul.listbox.rod</name>
 +
<value>true</value>
 +
</library-property>
 +
</source>
 +
 
 +
Notice that ROD is available only in ZK EE. If you're using other editions, you could create an external paging component and then display only a page of data in the grid or listbox. Fore real example, please refer to [[Small_Talks/2009/July/Handling_huge_data_using_ZK|Small Talk: Handling huge data using ZK]].
  
 
=Download =
 
=Download =
 
+
 +
Please download the source [http://sourceforge.net/projects/zkforge/files/Small%20Talks/Handling%20sortable%20huge%20data%20using%20ZK/ZKHugeDataExample-src.zip/download here].
 +
 
 +
You can also download the WAR file [http://sourceforge.net/projects/zkforge/files/Small%20Talks/Handling%20sortable%20huge%20data%20using%20ZK/ZKHugeDataExample.war/download here].
 +
 
   
 
   
 
<comment>http://books.zkoss.org/wiki/Small_Talks/2011/March/Handling_sortable_huge_data_using_ZK</comment>
 
<comment>http://books.zkoss.org/wiki/Small_Talks/2011/March/Handling_sortable_huge_data_using_ZK</comment>

Latest revision as of 04:18, 20 January 2022

DocumentationSmall Talks2011MarchSorting huge data using ZK
Sorting huge data using ZK

Author
Jumper Chen, Engineer, Potix Corporation
Date
March 16, 2011
Version
ZK 5.0.6


Introduction

In the previous article, we taught you how to implement paging with huge data. Here, we will extend that example to simplify its model implementation and add a sorting functionality to work with the huge data. In the following example, the paging navigation and the sorting function are handled by Database query so that we can reduce the memory usage and load the data on demand.

Demo

Creating the database

If you have already seen the previous article, you can jump to the SortingPagingListModel section directly.


We create a database named livedata and create a table named tblusers. We will provide the following columns, id (INT, AI, PK), first_name (VARCHAR), last_name(VARCHAR) and phone(VARCHAR).

The SQL statement to create the table is provided below


CREATE TABLE tblusers (id int(10) NOT NULL AUTO_INCREMENT, 
                                      first_name varchar(200) NOT NULL, 
                                      last_name varchar(200) NOT NULL, 
                                      phone varchar(20) NOT NULL, PRIMARY KEY (id) ) 
                                      ENGINE=InnoDB DEFAULT CHARSET=utf8;

The database screen

We have introduced a database screen which allows you to quickly setup and test your database connection. This is the first page that you arrive at when running the sample, if you have any problems you can manually edit the DatabaseInteraction class and recompile it.

Interacting with the database

Linking the application with the database is the easy part and involves creating a DAO to handle the interaction. The DAO, in this instance, only consists of functions to insert and retrieve news items from the database. Please refer to the UserDAO for more details.

The connection is handled via the MySQL J/Connector with a new connection being made for each operation. Please note that a new connection for each operation is NOT the best method of implementation, a connection pool would be far more efficient so please do not use the code for production level software. Building the model

The model needs to represent our user utilizing Java’s Object Orientated features, hence we implement a Java class named User which includes getters and setters for every field present in the database.


The code within the User class is included below:

public class User {
 
          private int _id;
          private String _firstName;
          private String _lastName;
          private String _phone;
          
          public String getFirstName() {
                   return _firstName;
          }
          public void setFirstName(String firstName) {
                   this._firstName = firstName;
          }
          public String getLastName() {
                   return _lastName;
          }
          public void setLastName(String lastName) {
                   this._lastName = lastName;
          }
          public String getPhone() {
                   return _phone;
          }
          public void setPhone(String phone) {
                   this._phone = phone;
          }
          public void setId(int id) {
                   this._id = id;
          }
          public int getId() {
                   return _id;
          }
}

With the User class implemented along with the functions to retrieve them from the database we need to construct the model.


SortingPagingListModel

The implemenation of SortingPagingListModel is very simple and clear while handling both sorting and paging. It simply extends from the AbstractListModel implementing the sort method from the interface ListModelExt. Here's how it looks.

Override from AbstractListModel

    @Override  
    public Object getElementAt(int index) {
       if (_cache == null || index < _beginOffset || index >= _beginOffset + _pageSize) {
              _beginOffset = index;
              loadToCache(index, _pageSize);
          }
          return _cache.get(index - _beginOffset);
      }

As you can see, in the getElementAt() method we use a _cache list object to cache the data fetched from the database. In this case, we specify the page size as 100 and when the index parameter goes out of the bounds of the cache, we reload the data to the _cache object again. Note that, the cached list must be cleared for better performance and less memory usage.

    @Override
    public int getSize() {  
        if (_cachedSize < 0) {  
           _cachedSize = DatabaseInformation.dao.getUserCount();  
        }  
        return _cachedSize;  
    }

In the example above, we use the variable _cachedSize to cache the total size coming from the database to avoid the redundant database connection when the method is invoked.

Implement for ListModelExt

    @Override
    public void sort(Comparator comparator, boolean flag) {
       if (comparator instanceof FieldComparator) {
           _dir = flag ? 0 : 1;
           _cache = null;
           _orderBy = ((FieldComparator)comparator).getRawOrderBy();
           fireEvent(ListDataEvent.CONTENTS_CHANGED, -1, -1);
        }
    }

As you can see, we cast the compartor ton a FieldComparator which we use for the sorting declaration in ZUML, example (sort="auto(fieldName)"). In this case, the fieldName should be the same as the field name created in the database.

For further details, please refer to the document of Implement ListModel and TreeModel.

Building the UI

The basic GUI is very simple and consists of two parts

  1. The button which generates 1,000,000 database entries
  2. The paging area (including Grid and paging components)


Sorting Column Fields within Grid

One of the most important features of this small talk is that we use Grid in a paging mold and specify the sort attribute with "auto(field_name)".

The markup below is the ZUL code from our hugedata2.zul

<window title="Huge data demo" width="720px" border="normal"
 apply="org.zkoss.zklargelivelist.controllers.MainUserController2">
          <label value="If you need to generate 1,000,000 entries please press this button!" />
          <button id="btnGenerate" label="Generate database entries" />
          <separator />
          <label value="Press this to delete the page" />
          <button id="btnDeletepage" label="Delete the page" />
          <separator />
          <grid id="dataGrid" width="700px" mold="paging" pagingPosition="top">
                   <columns>
                             <column label="id" sort="auto(id)" width="70px"/>
                             <column label="firstName" sort="auto(first_name)"/>
                             <column label="lastName" sort="auto(last_name)"/>
                             <column label="phone" sort="auto(phone)"/>
                   </columns>
          </grid>
</window>

Controller functionality

The controller MainUserController2.java handles two GUI events and overrides the doAfterCompose method. Firstly let us deal with the doAfterCompose method and explain what it does. The method is called after the components have been initialized. This allows us to manipulate the components before the user interacts with them.


doAfterCompose

          public void doAfterCompose(Component comp) throws Exception {
                   super.doAfterCompose(comp);
 
                   dataGrid.setRowRenderer(new RowRenderer(){
                             @Override
                             public void render(Row row, Object data) throws Exception {
                                      User usr = (User) data;
                                      row.appendChild(new Label(String.valueOf(usr.getId())));
                                      row.appendChild(new Label(usr.getFirstName()));
                                      row.appendChild(new Label(usr.getLastName()));
                                      row.appendChild(new Label(usr.getPhone()));
                             }});
                   dataGrid.setModel(new SortingPagingListModel());
          }


We create our own custom row renderer to format the data and then create a SortingPagingListModel instance for it. Notice the speed and low memory usage of this as we only pull the amount of users that we require for one page and hold that in memory!

GUI events

The other two functions are related to handling GUI events, the first is onClick$btnGenerate which is used to generate 1,000,000 entries in our database. And the other is the onClick$btnDeletepage which is used to remove the data within the page. For example,

          public void onClick$btnGenerate() {      
                   int numberOfUsers = DatabaseInformation.dao.getUserCount();
                   final int numberOfEntriesRequired = 1000000;
                   
                   int usersRequired = numberOfEntriesRequired - numberOfUsers;
                   
                   if(usersRequired > 0) {
                             User[] users = new User[usersRequired];
                                                         
                             for(int i=0; i<usersRequired; i++) {
                                      users[i] = GenerateData.generateUser();
                             }
                                      
                             DatabaseInformation.dao.insertUser(users);

                             // reset new model
                             dataGrid.setModel(new SortingPagingListModel());
                   }
                   else
                   {
                             try {
                                      Messagebox.show("There are already 1,000,000 entries in the database!");
                             } catch (InterruptedException e) {
                                      e.printStackTrace();
                             }
                   }
          }

As you can see, we recreate the SortingPagingListModel object to the dataGrid when the data has been updated.

    public void onClick$btnDeletepage() {
        //delete page from the database
        SortingPagingListModel model = (SortingPagingListModel)dataGrid.getListModel();
        int act = dataGrid.getActivePage();
        int pgsz = dataGrid.getPageSize();
        for(int i= act * pgsz, j = i + pgsz; i < j; i++) {  
           model.remove(i);
        }
    }

In the example above, we introduce a method called remove to remove the item from the database. It is questionable why don't we remove the item outside the model? Yes, we can, but the data where cached in the model won't be up to date.

Configuration

As suggested in Performance Tips, we turn on render-on-demand (ROD) in this example. It is done by specifying the following in WEB-INF/zk.xml.

<library-property>
	<name>org.zkoss.zul.grid.rod</name>
	<value>true</value>
</library-property>
<library-property>
	<name>org.zkoss.zul.listbox.rod</name>
	<value>true</value>
</library-property>

Notice that ROD is available only in ZK EE. If you're using other editions, you could create an external paging component and then display only a page of data in the grid or listbox. Fore real example, please refer to Small Talk: Handling huge data using ZK.

Download

Please download the source here.

You can also download the WAR file here.




Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.