Tuesday, 17 June 2014

ZK MVVM List Box–Retrieve records from MYSQL with Hibernate and Spring integration

ZK Version : ZK 7.0.2
Project Name : zk7example2

In this Post, we will see how to connect MySQL via hibernate spring integration and display in ZK List Box using MVVM Pattern. We will see how to achieve the
following things in the ZK ListBox using MVVM.

a) Format the amount field and show the currency symbol.
b) Column sorting.
c) Showing date fields in preferred format using ZK Converter.
d) Showing phone numbers in preferred format using ZK Converter.
e) Use EL expression to call a method in a ViewModel.
f) Binding label value according to run time value.
g) Dynamic sclass based on value.
h) Dynamic Template.

Step 1:
Follow
this post, to create ZK 7 Maven Project.

Step 2:
Follow
this post, to connect MySQL Via Hibernate Spring Integration. After this step, the project structure should be like as follows;

image

Step 3:
Next we will create new table called “orders” in our zkexamples database. Here is the script. Please remember, in the step 2, we have already created the database in MySQL.
Here is the create table script and insert into :
/*
SQLyog Ultimate v11.3 (64 bit)
MySQL - 5.0.41-community-nt : Database - zkexamples
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`zkexamples` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `zkexamples`;

/*Table structure for table `orders` */

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
`ID` bigint(20) NOT NULL auto_increment,
`orderNumber` bigint(20) NOT NULL,
`customerFname` varchar(100) default NULL,
`customerLname` varchar(200) default NULL,
`customerGender` varchar(10) default NULL,
`customerAddress1` varchar(300) default NULL,
`customerCity` varchar(100) default NULL,
`customerState` varchar(10) default NULL,
`customerZip` varchar(20) default NULL,
`customerPhone` varchar(15) default NULL,
`orderTotal` decimal(12,2) default NULL,
`status` varchar(100) default NULL,
`orderDate` date default NULL,
`delivered` int(1) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `orders` */

insert into `orders`(`ID`,`orderNumber`,`customerFname`,`customerLname`,`customerGender`,`customerAddress1`,`customerCity`,`customerState`,`customerZip`,`customerPhone`,`orderTotal`,`status`,`orderDate`,`delivered`) values (1,100,'NELLIE','MUNIZ','Male','6649 N Blue Gum St','New Orleans','LA','70116','5046218927','1200.00','Pending','2014-06-18',0),(2,101,'LUCIA','MORALES','Female','73 State Road 434 E','Phoenix','AZ','85013\r\n','6022774385','9000.34','Closed','2014-04-15',1),(3,102,'OLIVERA','EGLIS','Female','69734 E Carrillo St','New York','MD','99708\r\n','2159079111','45.00','Pending','2014-04-17',0),(4,103,'TED','CABRERA','Male','1 State Route 27','Camarillo','TX\r\n','93012\r\n','2124029216\r\n','4500.00','Hold','2014-03-12',0),(5,104,'SUSAN','HUTSON','Female','394 Manchester Blvd','Abilene','OH\r\n','66204\r\n','4105204832','90.20','Shipped','2014-06-18',0),(6,105,'FRED','KIRKLAND','Male','95 Main Ave #2','Overland Park','NM\r\n','66204\r\n','7734465569\r\n','200.00','Transit','2014-06-18',0),(7,106,'JOSE','URDAMBIDELUS VALDES','Male','2759 Livingston Ave','Conroe','NJ\r\n','99708\r\n',NULL,'1600.00','Closed','2014-03-04',1),(8,107,'NOEL','FERNANDEZ IGLESIA','Male','17 Jersey Ave','Evanston','NJ\r\n','54481\r\n','9565376195','800.75','Cancelled','2014-05-01',0),(9,108,'JOSE M','REYES VELAZQUEZ','Male','2 W Grand Ave','Boise','NY\r\n','66218\r\n',NULL,'9032.00','Closed','2014-05-10',1),(10,109,'ROSA','FERNANDEZ','Female','80312 W 32nd St','Daytona Beach','LA\r\n','21601\r\n',NULL,'6382.00','Pending','2014-05-27',0),(11,110,'TIMOTHY','RUIZ BORRERO','Male','63 E Aurora Dr','Providence','NY\r\n','77301\r\n','8158282147','4562.00','Hold','2014-05-24',0),(12,111,'CARMEN','MARTINEZ','Male','51120 State Route 18','Saint Joseph','CA\r\n','93012\r\n',NULL,'763.00','Closed','2014-05-09',1),(13,112,'RICARDO','RODGERS','Male','1 Century Park E','Orlando','TX\r\n','78204\r\n','5124863817','674.00','Closed','2014-05-16',1),(14,113,'ANDREA','ALBURRY','Female','79 S Howell Ave','Conroe','KS\r\n','67410\r\n','9047754480','896.10','Hold','2014-05-22',0),(15,114,'RICHARD','FARRINGTON','Male','18 Coronado Ave #563','Denver','FL\r\n','97754\r\n','6089767199','623.00','Pending','2014-05-15',0),(16,115,'CARLOS','BEST','Male','38 Pleasant Hill Rd','Jersey City','AK\r\n','66204\r\n','7737754522','764.00','Closed','2014-05-15',1);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Step 4:
Next we will create our Hibernate entity class with annotation style for our orders table. In the package domain, create a class called Orders as shown

image
Here is the class members:

package com.example.domain;

import java.math.BigDecimal;
import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table(name = "orders")
@NamedQueries({ @NamedQuery(name = "Orders.getAllOrders", query = "SELECT ord FROM Orders as ord order by customerLname") })
public class Orders {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long ID;
private Long orderNumber;
private String customerFname;
private String customerLname;
private String customerGender;
private String customerAddress1;
private String customerCity;
private String customerState;
private String customerZip;
private String customerPhone;
private BigDecimal orderTotal;
private String status;
@Temporal(TemporalType.DATE)
private Date orderDate;
private Integer delivered;

public Long getID() {
return ID;
}

public void setID(Long iD) {
ID = iD;
}

public Long getOrderNumber() {
return orderNumber;
}

public void setOrderNumber(Long orderNumber) {
this.orderNumber = orderNumber;
}

public String getCustomerFname() {
return customerFname;
}

public void setCustomerFname(String customerFname) {
this.customerFname = customerFname;
}

public String getCustomerLname() {
return customerLname;
}

public void setCustomerLname(String customerLname) {
this.customerLname = customerLname;
}

public String getCustomerGender() {
return customerGender;
}

public void setCustomerGender(String customerGender) {
this.customerGender = customerGender;
}

public String getCustomerAddress1() {
return customerAddress1;
}

public void setCustomerAddress1(String customerAddress1) {
this.customerAddress1 = customerAddress1;
}

public String getCustomerCity() {
return customerCity;
}

public void setCustomerCity(String customerCity) {
this.customerCity = customerCity;
}

public String getCustomerState() {
return customerState;
}

public void setCustomerState(String customerState) {
this.customerState = customerState;
}

public String getCustomerZip() {
return customerZip;
}

public void setCustomerZip(String customerZip) {
this.customerZip = customerZip;
}

public String getCustomerPhone() {
return customerPhone;
}

public void setCustomerPhone(String customerPhone) {
this.customerPhone = customerPhone;
}

public BigDecimal getOrderTotal() {
return orderTotal;
}

public void setOrderTotal(BigDecimal orderTotal) {
this.orderTotal = orderTotal;
}

public String getStatus() {
return status;
}

public void setStatus(String status) {
this.status = status;
}

public Date getOrderDate() {
return orderDate;
}

public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}

public Integer getDelivered() {
return delivered;
}

public void setDelivered(Integer delivered) {
this.delivered = delivered;
}

}

Please Note, we have also used named queries to retrieve the records from the orders table.

Named queries are compiled when Session Factory is instantiated (so, essentially, when your application starts up).
The obvious advantage, therefore, is that all your named queries are validated at that time rather than failing upon execution. The other advantage is that they're
easy to maintain - certainly for complex queries.

Step 5:
Now let us modify our index.zul and its view model to display the orders records in the ZK List view using MVVM Pattern.


<zk>
<window apply="org.zkoss.bind.BindComposer"
viewModel="@id('vm')@init('zk7example2.MyViewModel')">
<listbox selectedItem="@bind(vm.selectedOrder)"
model="@bind(vm.ordersList)">
<listhead sizable="true">
<listheader label="order No" />
<listheader label="FName" />
<listheader label="LName" />
<listheader label="Gender" />
<listheader label="Phone" />
<listheader label="Status" />
<listheader label="Order Total" />
<listheader label="Order Date" />
<listheader label="Delivered" />
</listhead>
<template name="model" var="p1">
<listitem>
<listcell label="@load(p1.orderNumber)" />
<listcell label="@load(p1.customerFname)" />
<listcell label="@load(p1.customerLname)" />
<listcell label="@load(p1.customerGender)" />
<listcell label="@load(p1.customerPhone)" />
<listcell label="@load(p1.status)" />
<listcell label="@load(p1.orderTotal)" />
<listcell label="@load(p1.orderDate)" />
<listcell label="@load(p1.delivered)" />
</listitem>
</template>
</listbox>
</window>
</zk>

View Modal

package zk7example2;

import java.util.List;

import org.zkoss.bind.annotation.Init;
import org.zkoss.zk.ui.select.annotation.WireVariable;
import org.zkoss.zkplus.spring.SpringUtil;

import com.example.business.service.CRUDService;
import com.example.domain.Orders;

public class MyViewModel {

@WireVariable
private CRUDService crudService;
private List<Orders> ordersList = null;
private Orders selectedOrder;



public Orders getSelectedOrder() {
return selectedOrder;
}

public void setSelectedOrder(Orders selectedOrder) {
this.selectedOrder = selectedOrder;
}

public List<Orders> getOrdersList() {
return ordersList;
}

public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}

@Init
public void init() {
crudService = (CRUDService) SpringUtil.getBean("CRUDService");
ordersList = crudService.GetListByNamedQuery("Orders.getAllOrders");
}

}

Here is the output:
image
1. We are using MVVM as design pattern.
2. And also, to display the values, we are using MVVM Data binding concept.
3. For event handling, we are using MVVM Command Binding.
4. We are using ZK List box to show the record from the DB.
5. We will display the records using MVVM View Modal

Step 6:
Next we will see how to format the amount field and show the currency symbol. In the index.zul, add the taglib in the top as shown here.

<?taglib uri="http://www.zkoss.org/dsp/web/core" prefix="c"?>

And change the List header and List cell as follows
<listheader label="Order Total"  align="right"/>
<listcell   label="@load(c:formatNumber(p1.orderTotal, '$#,###.00'))" />


b) Next we will see how to add sorting capability for our listing.
Regarding the case-sensitivity for listbox auto-sorting,
<listheader sort="auto" /> is case-insensitive.
<listheader sort="auto(name)" /> is case-SENSITIVE.
<listheader sort="auto(LOWER(name))" /> is case-insensitive.

Here is the modified zul file after adding sort and format feature.


<?taglib uri="http://www.zkoss.org/dsp/web/core" prefix="c"?>
<zk>
<window apply="org.zkoss.bind.BindComposer"
viewModel="@id('vm')@init('zk7example2.MyViewModel')">
<listbox selectedItem="@bind(vm.selectedOrder)"
model="@bind(vm.ordersList)">
<listhead sizable="true">
<listheader label="order No" sort="auto(orderNumber)" />
<listheader label="FName" sort="auto(LOWER(customerFname))" />
<listheader label="LName" sort="auto(LOWER(customerLname))" />
<listheader label="Gender" sort="auto(customerGender)" />
<listheader label="Phone" sort="auto(customerPhone)" />
<listheader label="Status" sort="auto(status)" />
<listheader label="Order Total" align="right"
sort="auto(orderTotal)" />
<listheader label="Order Date" align="center"
sort="auto(orderDate)" />
<listheader label="Delivered"
sort="auto(delivered)" />
</listhead>
<template name="model" var="p1">
<listitem>
<listcell label="@load(p1.orderNumber)" />
<listcell label="@load(p1.customerFname)" />
<listcell label="@load(p1.customerLname)" />
<listcell label="@load(p1.customerGender)" />
<listcell label="@load(p1.customerPhone)" />
<listcell label="@load(p1.status)" />
<listcell
label="@load(c:formatNumber(p1.orderTotal, '$#,###.00'))" />
<listcell label="@load(p1.orderDate)" />
<listcell label="@load(p1.delivered)" />
</listitem>
</template>
</listbox>
</window>
</zk>


Step 7:
Next we will see how to format the date and phone number field using ZK Converter. For more information about converters, please look here
. As said
in ZK Documentation, we will use built in converter for date and custom converter for Phone.

Change the display of date in the zul as follows
<listcell   label="@load(p1.orderDate) @converter('formatedDate', format='MM/dd/yyyy')" />

In the zk7example2 package, create a class called MyPhoneConverter which will implement ZK Converter interface as shown here.

package zk7example2;

import org.zkoss.bind.BindContext;
import org.zkoss.bind.Converter;
import org.zkoss.zk.ui.Component;

@SuppressWarnings("rawtypes")
public class MyPhoneConverter implements Converter {

/**
* The method coerceToUi() is invoked when loading ViewModel's property to
* component and its return type should correspond to bound component
* attribute's value[1]. The coerceToBean() is invoked when saving. If you
* only need to one way conversion, you can leave unused method empty.
*/

@Override
public Object coerceToBean(Object val, Component arg1, BindContext arg2) {
return val;
}

@Override
public Object coerceToUi(Object val, Component arg1, BindContext arg2) {

if (val == null)
return null;
String value = (String) val;
value= value.trim();
if (value.length() != 10)
return val;
value = "(" + value.substring(0, 3) + ") " + value.substring(3, 6)
+ "-" + value.substring(6, 10);
return value;
}
}

Now let us use this converter in our zul file as follows
<listcell     label="@load(p1.customerPhone) @converter('zk7example2.MyPhoneConverter')" />

Step 8:
Next we will see how to EL expression to call a method in a ViewModel and  Binding label value according to run time value.

Instead of showing both first name and lastname, let us show as one field by joining first name and last name. This will be done by
calling a method in View Model and also , instead of showing 1 or 0 for delivered field, we will show Yes or No .

In the view Model, write the following method

public String getCustomerName(String firstName, String lastName) {
        return firstName + " " + lastName;
    }

And change the zul file as follows

<listheader label="Customer Name"  sort="auto(LOWER(customerFname))" />

<listcell  label="@load(vm.getCustomerName(p1.customerFname, p1.customerLname))" />

Next we will show Yes or No for delivered field.
<listcell label="@bind(p1.delivered eq 1?'Yes':'No')"/>

Here is the modified zul file


<?taglib uri="http://www.zkoss.org/dsp/web/core" prefix="c"?>
<zk>
<window apply="org.zkoss.bind.BindComposer"
viewModel="@id('vm')@init('zk7example2.MyViewModel')">
<listbox selectedItem="@bind(vm.selectedOrder)"
model="@bind(vm.ordersList)">
<listhead sizable="true">
<listheader label="order No" sort="auto(orderNumber)" />
<listheader label="Customer Name"
sort="auto(LOWER(customerFname))" />
<listheader label="Gender" sort="auto(customerGender)" />
<listheader label="Phone" sort="auto(customerPhone)" />
<listheader label="Status" sort="auto(status)" />
<listheader label="Order Total" align="right"
sort="auto(orderTotal)" />
<listheader label="Order Date" align="center"
sort="auto(orderDate)" />
<listheader label="Delivered" sort="auto(delivered)" />
</listhead>
<template name="model" var="p1">
<listitem>
<listcell label="@load(p1.orderNumber)" />
<listcell
label="@load(vm.getCustomerName(p1.customerFname, p1.customerLname))" />
<listcell label="@load(p1.customerGender)" />
<listcell
label="@load(p1.customerPhone) @converter('zk7example2.MyPhoneConverter')" />
<listcell label="@load(p1.status)" />
<listcell
label="@load(c:formatNumber(p1.orderTotal, '$#,###.00'))" />
<listcell
label="@load(p1.orderDate) @converter('formatedDate', format='MM/dd/yyyy')" />
<listcell label="@bind(p1.delivered eq 1?'Yes':'No')"/>
</listitem>
</template>
</listbox>
</window>
</zk>


Step 9:
Next we will show different icon for Customer gender Male and Female. In order to do that, let us create a folder called images under
webapp folder and put the male.png  and female.png. Then create a folder called css and create a css file namely “style.css”
image

Here is the style.css file content

.Male {
width: 25px;
background-image: url('../images/male.png');
background-repeat: no-repeat;
border: 0 none;
}

.Female {
width: 25px;
background-image: url('../images/female.png');
background-repeat: no-repeat;
border: 0 none;
}

Next step we need to refer this style.css in our index.zul file. Add the following line
<style src="/css/style.css" />

Now let us show the Male.png for gender Male and Female.png for gender female. Here is the modified zul file

<?taglib uri="http://www.zkoss.org/dsp/web/core" prefix="c"?>
<zk>
<style src="/css/style.css" />
<window apply="org.zkoss.bind.BindComposer"
viewModel="@id('vm')@init('zk7example2.MyViewModel')">
<listbox selectedItem="@bind(vm.selectedOrder)"
model="@bind(vm.ordersList)">
<listhead sizable="true">
<listheader label="order No" sort="auto(orderNumber)" />
<listheader label="Customer Name"
sort="auto(LOWER(customerFname))" />
<listheader label="Gender" sort="auto(customerGender)" />
<listheader label="Phone" sort="auto(customerPhone)" />
<listheader label="Status" sort="auto(status)" />
<listheader label="Order Total" align="right"
sort="auto(orderTotal)" />
<listheader label="Order Date" align="center"
sort="auto(orderDate)" />
<listheader label="Delivered" sort="auto(delivered)" />
</listhead>
<template name="model" var="p1">
<listitem>
<listcell label="@load(p1.orderNumber)" />

<listcell
label="@load(vm.getCustomerName(p1.customerFname, p1.customerLname))" />
<listcell>
<hbox spacing="20px">
<image sclass="@bind(p1.customerGender)" />
<label value="@load(p1.customerGender)" />
</hbox>
</listcell>
<listcell
label="@load(p1.customerPhone) @converter('zk7example2.MyPhoneConverter')" />
<listcell label="@load(p1.status)" />
<listcell
label="@load(c:formatNumber(p1.orderTotal, '$#,###.00'))" />
<listcell
label="@load(p1.orderDate) @converter('formatedDate', format='MM/dd/yyyy')" />
<listcell
label="@bind(p1.delivered eq 1?'Yes':'No')" />
</listitem>
</template>
</listbox>
</window>
</zk>

Here is the output
image



You can download the source here

3 comments:

  1. Hello Sir,
    the example is too complicat for me, could u give a simple code without using hibernate and spring framework.
    Thanx a lot !

    ReplyDelete
  2. Nice to find your blog with valuable information.

    ReplyDelete