Health Care Ideas: June 2014

Monday, 30 June 2014

CentOS Commands

To Check CentOS Version via command Line

cat /etc/redhat-release



Connect with MySQL via command Line

mysql -uroot -p123456




To Create folder
mkdir foo

Sunday, 29 June 2014

MySQL dropping index is very slow

I found a good post in server fault on how we can speed up drop index in big table in MySQL. Here is the scripts.

CREATE TABLE patient_new LIKE patient;
ALTER TABLE patient_new DROP INDEX post_text;
ALTER TABLE patient_new DISABLE KEYS;
INSERT INTO patient_new SELECT * FROM patient;
ALTER TABLE patient_new ENABLE KEYS;
ALTER TABLE patient RENAME patient_old;
ALTER TABLE patient_new RENAME patient;
DROP TABLE patient_old;

Friday, 27 June 2014

MySQL Trigger Before Insert example

Here is an small example how to create trigger in MySQL after inserting record into table.
This examples how to generate random password in the MySQL and update the user name in the Before insert trigger.

Step 1 :
Let us create a small table with three fields such as ID, loginName and password. Here is the script for the same;

DROP TABLE IF EXISTS `appusers`;

CREATE TABLE `appusers` (
  `ID` bigint(20) NOT NULL auto_increment,
  `loginName` varchar(20) default NULL,
  `password` varchar(100) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step 2 :
Let us create a stored procedure which will return random password using MySQL RAND,ROUND and MOD function.

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `getPassword`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getPassword`(OUT _password VARCHAR(7))
BEGIN
	DECLARE COUNT INT DEFAULT 0;
	DECLARE alphanum INT;
	DECLARE randomCharacter CHAR(1);
	DECLARE PASSWORD VARCHAR(10) DEFAULT "";
	
	WHILE COUNT<7 DO
		SET COUNT=COUNT+1;
		SELECT ROUND(RAND()*10) INTO alphanum;

		IF alphanum<5 THEN
			#Generate a random digit
			SELECT CHAR(48+MOD(ROUND(RAND()*100),10)) INTO randomCharacter;
		ELSE
			#Generate a random digit
			SELECT CHAR(65+MOD(ROUND(RAND()*100),26)) INTO randomCharacter;
		END IF;
		#CONCAT function concatenates two or more strings and returns result
		SELECT CONCAT(PASSWORD,randomCharacter) INTO PASSWORD;
	END WHILE;
	SET _password=PASSWORD;
    END$$

DELIMITER ;

Step 3 :
Here is trigger

DELIMITER $$


DROP TRIGGER /*!50032 IF EXISTS */ `updatepassword`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `updatepassword` BEFORE INSERT ON `appusers` 

    FOR EACH ROW BEGIN
	 DECLARE varPassword VARCHAR(100);
	 CALL getPassword(varPassword);
	 SET NEW.password = varPassword;
    END;
$$

DELIMITER ;

Now you can test by inserting record as follows:
INSERT INTO appusers(loginName) VALUES('John1');

Sunday, 22 June 2014

ZK MVVM List Box Export to Excel

In this post, we will see how to export records from the DB to Excel format.

ZK Version : ZK 7.0.2
Project Name : zk7example3

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;

Step 3:
Follow this post, to connect MySQL via Hibernate Spring integration and display the records in ZK List View Component . After this step, the output
will be as follows

Step 4:

Next we will see how to take a list of JAVA beans and generate an Excel spread sheet using the Apache Commons POI library, with help from the
Apache Commons BeanUtils library. First let us create our Columns class which basically defines the properties of the column.

Let us add Apache dependencies in our POM File. Add the following dependencies;

	<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.9</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.9</version>
		</dependency>

		<dependency>
			<groupId>commons-beanutils</groupId>
			<artifactId>commons-beanutils</artifactId>
			<version>1.8.3</version>
		</dependency>

In the package zk7example3, create a class called ExcelColumns.java and a enum called FormatType

package zk7example3;

public enum FormatType {
	TEXT, INTEGER, FLOAT, DATE, MONEY, PERCENTAGE
}

ExcelColumns.Java

package zk7example3;

import org.apache.poi.xssf.usermodel.XSSFFont;

public class ExcelColumns {

	private String m_method;
	private String m_header;
	private FormatType m_type;
	private XSSFFont m_font;
	private Short m_color;
	private int m_width;

	public ExcelColumns(String method, String header, FormatType type,
			XSSFFont font, Short color, int width) {
		this.m_method = method;
		this.m_header = header;
		this.m_type = type;
		this.m_font = font;
		this.m_color = color;
		this.m_width = width;
	}

	public ExcelColumns(String method, String header, FormatType type,
			XSSFFont font) {
		this(method, header, type, font, null, 3000);
	}

	public ExcelColumns(String method, String header, FormatType type,
			Short color) {
		this(method, header, type, null, color, 3000);
	}

	public ExcelColumns(String method, String header, FormatType type) {
		this(method, header, type, null, null, 3000);
	}

	public ExcelColumns(String method, String header, FormatType type, int width) {
		this(method, header, type, null, null, width);
	}

	public String getMethod() {
		return m_method;
	}

	public void setMethod(String method) {
		this.m_method = method;
	}

	public String getHeader() {
		return m_header;
	}

	public void setHeader(String header) {
		this.m_header = header;
	}

	public FormatType getType() {
		return m_type;
	}

	public void setType(FormatType type) {
		this.m_type = type;
	}

	public XSSFFont getFont() {
		return m_font;
	}

	public void setFont(XSSFFont m_font) {
		this.m_font = m_font;
	}

	public Short getColor() {
		return m_color;
	}

	public void setColor(Short m_color) {
		this.m_color = m_color;
	}

	public int getWidth() {
		if (this.m_width == 0)
			return 3000;
		else
			return m_width;
	}

	public void setWidth(int m_width) {
		this.m_width = m_width;
	}

}

Step 5:
Next we will create our generic class which converts list of beans to excel. In the package zk7example3, create a class called BeanToExcel

package zk7example3;

import java.io.File;
import java.io.FileOutputStream;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.Date;
import java.util.Calendar;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.zkoss.zul.Filedownload;

public class BeanToExcel {

	protected List<?> dataList = null;
	protected List<ExcelColumns> excelColumns = null;
	protected String dataSheetName;
	private XSSFSheet sheet;
	private XSSFWorkbook workbook;

	public List<?> getDataList() {
		return dataList;
	}

	public void setDataList(List<?> dataList) {
		this.dataList = dataList;
	}

	public List<ExcelColumns> getExcelColumns() {
		return excelColumns;
	}

	public void setExcelColumns(List<ExcelColumns> excelColumns) {
		this.excelColumns = excelColumns;
	}

	public String getDataSheetName() {
		return dataSheetName;
	}

	public void setDataSheetName(String dataSheetName) {
		this.dataSheetName = dataSheetName;
	}

	public void exportToExcel() {

		try {

			// Blank workbook
			this.workbook = new XSSFWorkbook();
			// Create a blank sheet
			this.sheet = this.workbook.createSheet(this.dataSheetName);

			int numCols = this.excelColumns.size();
			int currentRow = 0;

			// Create the report header at row 0
			Row excelHeader = sheet.createRow(currentRow);
			Cell dataCell;
			// Loop over all the column beans and populate the report headers
			for (int i = 0; i < numCols; i++) {
				dataCell = excelHeader.createCell(i);
				dataCell.setCellStyle(getHeaderCellStyle(workbook));
				dataCell.setCellValue(excelColumns.get(i).getHeader());
				this.sheet.setColumnWidth(i, excelColumns.get(i).getWidth());
			}

			currentRow++;
			Row dataRow;
			for (int i = 0; i < this.dataList.size(); i++) {
				// create a row in the spreadsheet
				dataRow = sheet.createRow(currentRow++);
				// get the bean for the current row
				// get the bean for the current row
				Object bean = dataList.get(i);
				for (int y = 0; y < numCols; y++) {
					Object value = PropertyUtils.getProperty(bean, excelColumns
							.get(y).getMethod());
					writeCell(dataRow, y, value, excelColumns.get(y).getType(),
							excelColumns.get(y).getColor(), excelColumns.get(y)
									.getFont());
				}
			}

		} catch (IllegalAccessException e) {
			System.out.println(e);
		} catch (InvocationTargetException e) {
			System.out.println(e);
		} catch (NoSuchMethodException e) {
			System.out.println(e);
		}

		Calendar now = Calendar.getInstance();
		int day = now.get(Calendar.DAY_OF_MONTH);
		int hour = now.get(Calendar.HOUR_OF_DAY);
		int minute = now.get(Calendar.MINUTE);
		int second = now.get(Calendar.SECOND);
		String fileName = this.dataSheetName + "_" + day + "_" + hour + "_"
				+ minute + "_" + second + "_";

		try {
			// Write the workbook in file system
			File temp = File.createTempFile(fileName, ".xlsx");
			FileOutputStream out = new FileOutputStream(temp);
			workbook.write(out);
			out.close();
			Filedownload.save(temp, null);
		} catch (Exception e) {
			System.out.println(e);

		}
	}

	private void writeCell(Row dataRow, int col, Object value,
			FormatType formatType, Short bgColor, XSSFFont font) {

		Cell dataCell;
		dataCell = dataRow.createCell(col);
		if (value == null) {
			return;
		}
		switch (formatType) {
		case TEXT:
			dataCell.setCellValue(value.toString());
			break;
		case DATE:
			dataCell.setCellStyle(getDateFormatStyle(this.workbook));
			dataCell.setCellValue((Date) value);
			break;
		case FLOAT:
			break;
		case INTEGER:
			dataCell.setCellStyle(getIntegerCellStyle(this.workbook));
			dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
			dataCell.setCellValue(((Number) value).intValue());
			break;
		case MONEY:
			dataCell.setCellStyle(getNumberCellStyle(this.workbook));
			dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
			dataCell.setCellValue(((BigDecimal) value).doubleValue());
			break;
		case PERCENTAGE:
			break;
		default:
			break;
		}

	}

	public static void createExcelHeader(XSSFWorkbook workbook,
			XSSFSheet excelSheet, String titles[]) {

		Row excelHeader = excelSheet.createRow(0);
		Cell dataCell;
		int count = 0;
		for (String caption : titles) {
			dataCell = excelHeader.createCell(count++);
			dataCell.setCellStyle(getHeaderCellStyle(workbook));
			dataCell.setCellValue(caption);
		}
	}

	public static XSSFCellStyle getHeaderCellStyle(XSSFWorkbook workbook) {
		XSSFCellStyle titleStyle = workbook.createCellStyle();

		titleStyle.setFillPattern(XSSFCellStyle.FINE_DOTS);
		titleStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		titleStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
		titleStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
		titleStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
		titleStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
		titleStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);

		Font headerFont = workbook.createFont();
		headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headerFont.setFontHeightInPoints((short) 10);
		titleStyle.setFont(headerFont);

		return titleStyle;
	}

	private XSSFCellStyle getDateFormatStyle(XSSFWorkbook workbook) {
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFDataFormat df = workbook.createDataFormat();
		style.setDataFormat(df.getFormat("mm/dd/yyyy"));
		return style;
	}

	private XSSFCellStyle getNumberCellStyle(XSSFWorkbook workbook) {
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFDataFormat format = workbook.createDataFormat();
		style.setDataFormat(format.getFormat("0.00"));
		return style;
	}

	private XSSFCellStyle getIntegerCellStyle(XSSFWorkbook workbook) {
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFDataFormat format = workbook.createDataFormat();
		style.setDataFormat(format.getFormat("#,##0"));
		return style;
	}
}

Step 6:
Next we will see how we can use our generic class to generate excel from List of beans. In the index.zul , at the top, add button as follows

<?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('zk7example3.MyViewModel')">
		<button label="Export to Excel"
			onClick="@command('onExcelExport')">
		</button>
		<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('zk7example3.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>

Now let us add the method “onExcelExport” in our viewmodel.

package zk7example3;

import java.util.ArrayList;
import java.util.List;

import org.zkoss.bind.annotation.Command;
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");
	}

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

	@Command
	public void onExcelExport() {
		List<ExcelColumns> excelColumns = new ArrayList<ExcelColumns>();
		excelColumns.add(new ExcelColumns("orderNumber", "Order No",
				FormatType.INTEGER));
		excelColumns.add(new ExcelColumns("orderDate", "Order Date",
				FormatType.DATE));

		excelColumns.add(new ExcelColumns("customerFname", "First Name",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerLname", "Last Name",
				FormatType.TEXT,5000));
		excelColumns.add(new ExcelColumns("customerGender", "Gender",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerAddress1", "Address1",
				FormatType.TEXT,5000));
		excelColumns.add(new ExcelColumns("customerCity", "City",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerState", "State",
				FormatType.TEXT,2000));
		excelColumns.add(new ExcelColumns("customerZip", "Zip",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerPhone", "Phone",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("orderTotal", "Order Total",
				FormatType.MONEY));

		BeanToExcel beanToExcel = new BeanToExcel();
		beanToExcel.setExcelColumns(excelColumns);
		beanToExcel.setDataSheetName("orders");
		beanToExcel.setDataList(ordersList);
		beanToExcel.exportToExcel();
	}

}

Thats all. Now you can run and click the button to export the list box records into excel.

You can download the source here.

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;

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

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:

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”

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

You can download the source here

Saturday, 14 June 2014

ZK and Hibernate Configuration for MySQL Database

ZK Version : ZK 7.0.2

In this post, we will see how to setup the XML configuration file to connect MySQL via Hibernate ORM.

Step 1:
Follow my earlier post to create ZK 7 Maven Project.

Step 2:

Double click the pom.xml file to add dependencies for Hibernate and MySQL

a) hibernate-core 4.3.5.Final
b) mysql-connector-java
c) Apache DBCP. For more information, please check here

And also we are going to Manage Hibernate session via Spring. For More information on Spring integration with hibernate, please read this article. So we will
add Spring ORM Dependencies as well.
Here is the complete POM.File after adding all necessary dependencies.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>ZK7Example1</groupId>
	<artifactId>ZK7Example1</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<properties>
		<zk.version>6.5.2</zk.version>
		<commons-io>1.3.1</commons-io>
		<spring.version>4.0.0.RELEASE</spring.version>
		<maven.build.timestamp.format>yyyy-MM-dd</maven.build.timestamp.format>
		<packname>-${project.version}-FL-${maven.build.timestamp}</packname>
	</properties>
	<packaging>war</packaging>
	<name>The ZK7Example1 Project</name>
	<description>The ZK7Example1 Project</description>
	<licenses>
		<license>
			<name>GNU LESSER GENERAL PUBLIC LICENSE, Version 3</name>
			<url>http://www.gnu.org/licenses/lgpl.html</url>
			<distribution>repo</distribution>
		</license>
	</licenses>
	<repositories>
		<repository>
			<id>ZK CE</id>
			<name>ZK CE Repository</name>
			<url>http://mavensync.zkoss.org/maven2</url>
		</repository>
		<repository>
			<id>ZK EVAL</id>
			<name>ZK Evaluation Repository</name>
			<url>http://mavensync.zkoss.org/eval</url>
		</repository>
	</repositories>
	<pluginRepositories>
		<pluginRepository>
			<id>zkmaven</id>
			<name>ZK Maven Plugin Repository</name>
			<url>http://mavensync.zkoss.org/maven2/</url>
		</pluginRepository>
	</pluginRepositories>
	<dependencies>
		<dependency>
			<groupId>org.zkoss.zk</groupId>
			<artifactId>zkbind</artifactId>
			<version>${zk.version}</version>
		</dependency>
		<dependency>
			<groupId>org.zkoss.zk</groupId>
			<artifactId>zul</artifactId>
			<version>${zk.version}</version>
		</dependency>
		<dependency>
			<groupId>org.zkoss.zk</groupId>
			<artifactId>zkplus</artifactId>
			<version>${zk.version}</version>
		</dependency>
		<dependency>
			<groupId>org.zkoss.zk</groupId>
			<artifactId>zhtml</artifactId>
			<version>${zk.version}</version>
		</dependency>

		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>${commons-io}</version>
		</dependency>

		<!-- Hibernate -->

		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>
			<version>4.3.5.Final</version>
		</dependency>

		<!-- MySQL database driver -->

		<dependency> <!-- MySQL database driver -->
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.24</version>
		</dependency>

		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.1</version>
		</dependency>

		<dependency> <!-- Apache BasicDataSource -->
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.2.2</version>
		</dependency>

		<dependency>
			<groupId>c3p0</groupId>
			<artifactId>c3p0</artifactId>
			<version>0.9.1.2</version>
		</dependency>


		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>servlet-api</artifactId>
			<version>2.5</version>
		</dependency>


		<!-- Spring framework -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context-support</artifactId>
			<version>${spring.version}</version>
		</dependency>


		<dependency> <!-- Used for Hibernate4 LocalSessionFactoryBean -->
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
			<version>${spring.version}</version>
		</dependency>


		<!-- ZK 5 breeze theme <dependency> <groupId>org.zkoss.theme</groupId> 
			<artifactId>breeze</artifactId> <version>${zk.version}</version> <optional>true</optional> 
			</dependency> -->
	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
		<plugins>
			<!-- Run with Jetty -->
			<plugin>
				<groupId>org.mortbay.jetty</groupId>
				<artifactId>maven-jetty-plugin</artifactId>
				<version>6.1.10</version>
				<configuration>
					<scanIntervalSeconds>5</scanIntervalSeconds>
					<stopKey>foo</stopKey>
					<stopPort>9999</stopPort>
				</configuration>
				<executions>
					<execution>
						<id>start-jetty</id>
						<phase>pre-integration-test</phase>
						<goals>
							<goal>run</goal>
						</goals>
						<configuration>
							<scanIntervalSeconds>0</scanIntervalSeconds>
							<daemon>true</daemon>
						</configuration>
					</execution>
					<execution>
						<id>stop-jetty</id>
						<phase>post-integration-test</phase>
						<goals>
							<goal>stop</goal>
						</goals>
					</execution>
				</executions>
			</plugin>
			<!-- Compile java -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.3.2</version>
				<configuration>
					<source>1.6</source>
					<target>1.6</target>
				</configuration>
			</plugin>
			<!-- Build war -->
			<plugin>
				<artifactId>maven-war-plugin</artifactId>
				<groupId>org.apache.maven.plugins</groupId>
				<version>2.1.1</version>
			</plugin>
			<!-- Pack zips -->
			<plugin>
				<artifactId>maven-assembly-plugin</artifactId>
				<version>2.2</version>
				<executions>
					<execution>
						<id>webapp</id>
						<phase>package</phase>
						<goals>
							<goal>single</goal>
						</goals>
						<configuration>
							<finalName>ZK7Example1${packname}</finalName>
							<appendAssemblyId>false</appendAssemblyId>
							<descriptors>
								<descriptor>src/main/assembly/webapp.xml</descriptor>
							</descriptors>
						</configuration>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>
</project>

Step 3:
Now let us setup our Spring XML Configuration. In our Project, location file web.xml (webapp->Web-inf) and add the following: Here is the complete web.xml after adding.

<?xml version="1.0" encoding="UTF-8"?>

<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

	<description><![CDATA[My ZK Application]]></description>
	<display-name>ZK7Example1</display-name>



	<filter>
		<filter-name>hibernateFilter</filter-name>
		<filter-class>org.springframework.orm.hibernate4.support.OpenSessionInViewFilter</filter-class>
		<init-param>
			<param-name>sessionFactoryBeanName</param-name>
			<param-value>sessionFactory</param-value>
		</init-param>
	</filter>

	<filter-mapping>
		<filter-name>hibernateFilter</filter-name>
		<url-pattern>/*</url-pattern>
		<dispatcher>REQUEST</dispatcher>
		<dispatcher>FORWARD</dispatcher>
	</filter-mapping>


	<!-- Spring can be easily integrated into any Java-based web framework. 
		All you need to do is to declare the ContextLoaderListener in your web.xml 
		and use a contextConfigLocation <context-param> to set which context files 
		to load. If you don't specify the contextConfigLocation context parameter, 
		the ContextLoaderListener will look for a /WEB-INF/applicationContext.xml 
		file to load. Once the context files are loaded, Spring creates a WebApplicationContext 
		object based on the bean definitions and puts it into the ServletContext. -->

	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>
		            classpath:applicationContext.xml
		            classpath:applicationContext_hib.xml
		            classpath:springOrmContext.xml
         </param-value>
	</context-param>
	<!-- Loads the Spring web application context -->
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>



	<!-- ====================================================== -->
	<!-- SPRING REQUEST LISTENER -->
	<!-- ====================================================== -->
	<listener>
		<display-name>Spring Request Context Listener</display-name>
		<listener-class>org.springframework.web.context.request.RequestContextListener</listener-class>
	</listener>



	<!-- //// -->
	<!-- ZK -->
	<listener>
		<description>ZK listener for session cleanup</description>
		<listener-class>org.zkoss.zk.ui.http.HttpSessionListener</listener-class>
	</listener>
	<servlet>
		<description>ZK loader for ZUML pages</description>
		<servlet-name>zkLoader</servlet-name>
		<servlet-class>org.zkoss.zk.ui.http.DHtmlLayoutServlet</servlet-class>

		<!-- Must. Specifies URI of the update engine (DHtmlUpdateServlet). It 
			must be the same as <url-pattern> for the update engine. -->
		<init-param>
			<param-name>update-uri</param-name>
			<param-value>/zkau</param-value>
		</init-param>
		<!-- Optional. Specifies whether to compress the output of the ZK loader. 
			It speeds up the transmission over slow Internet. However, if you configure 
			a filter to post-processing the output, you might have to disable it. Default: 
			true <init-param> <param-name>compress</param-name> <param-value>true</param-value> 
			</init-param> -->
		<!-- [Optional] Specifies the default log level: OFF, ERROR, WARNING, INFO, 
			DEBUG and FINER. If not specified, the system default is used. <init-param> 
			<param-name>log-level</param-name> <param-value>OFF</param-value> </init-param> -->
		<load-on-startup>1</load-on-startup><!-- Must -->
	</servlet>
	<servlet-mapping>
		<servlet-name>zkLoader</servlet-name>
		<url-pattern>*.zul</url-pattern>
	</servlet-mapping>
	<servlet-mapping>
		<servlet-name>zkLoader</servlet-name>
		<url-pattern>*.zhtml</url-pattern>
	</servlet-mapping>
	<!-- [Optional] Uncomment it if you want to use richlets. <servlet-mapping> 
		<servlet-name>zkLoader</servlet-name> <url-pattern>/zk/*</url-pattern> </servlet-mapping> -->
	<servlet>
		<description>The asynchronous update engine for ZK</description>
		<servlet-name>auEngine</servlet-name>
		<servlet-class>org.zkoss.zk.au.http.DHtmlUpdateServlet</servlet-class>

		<!-- [Optional] Specifies whether to compress the output of the ZK loader. 
			It speeds up the transmission over slow Internet. However, if your server 
			will do the compression, you might have to disable it. Default: true <init-param> 
			<param-name>compress</param-name> <param-value>true</param-value> </init-param> -->
		<!-- [Optional] Specifies the AU extension for particular prefix. <init-param> 
			<param-name>extension0</param-name> <param-value>/upload=com.my.MyUploader</param-value> 
			</init-param> -->
	</servlet>
	<servlet-mapping>
		<servlet-name>auEngine</servlet-name>
		<url-pattern>/zkau/*</url-pattern>
	</servlet-mapping>

	<!-- [Optional] Uncomment if you want to use the ZK filter to post process 
		the HTML output generated by other technology, such as JSP and velocity. 
		<filter> <filter-name>zkFilter</filter-name> <filter-class>org.zkoss.zk.ui.http.DHtmlLayoutFilter</filter-class> 
		<init-param> <param-name>extension</param-name> <param-value>html</param-value> 
		</init-param> <init-param> <param-name>compress</param-name> <param-value>true</param-value> 
		</init-param> </filter> <filter-mapping> <filter-name>zkFilter</filter-name> 
		<url-pattern>your URI pattern</url-pattern> </filter-mapping> -->
	<!-- //// -->

	<!-- ///////////// -->
	<!-- DSP (optional) Uncomment it if you want to use DSP However, it is turned 
		on since zksandbox uses DSP to generate CSS. <servlet> <servlet-name>dspLoader</servlet-name> 
		<servlet-class>org.zkoss.web.servlet.dsp.InterpreterServlet</servlet-class> 
		<init-param> <param-name>class-resource</param-name> <param-value>true</param-value> 
		</init-param> </servlet> <servlet-mapping> <servlet-name>dspLoader</servlet-name> 
		<url-pattern>*.dsp</url-pattern> </servlet-mapping> -->

	<!-- /////////// -->
	<!-- [Optional] Session timeout -->
	<session-config>
		<session-timeout>60</session-timeout>
	</session-config>

	<!-- [Optional] MIME mapping -->
	<mime-mapping>
		<extension>doc</extension>
		<mime-type>application/vnd.ms-word</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>gif</extension>
		<mime-type>image/gif</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>htm</extension>
		<mime-type>text/html</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>html</extension>
		<mime-type>text/html</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>jpeg</extension>
		<mime-type>image/jpeg</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>jpg</extension>
		<mime-type>image/jpeg</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>js</extension>
		<mime-type>text/javascript</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>pdf</extension>
		<mime-type>application/pdf</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>png</extension>
		<mime-type>image/png</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>txt</extension>
		<mime-type>text/plain</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>xls</extension>
		<mime-type>application/vnd.ms-excel</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>xml</extension>
		<mime-type>text/xml</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>zhtml</extension>
		<mime-type>text/html</mime-type>
	</mime-mapping>
	<mime-mapping>
		<extension>zul</extension>
		<mime-type>text/html</mime-type>
	</mime-mapping>

	<welcome-file-list>
		<welcome-file>index.zul</welcome-file>
		<welcome-file>index.zhtml</welcome-file>
		<welcome-file>index.html</welcome-file>
		<welcome-file>index.htm</welcome-file>
	</welcome-file-list>
</web-app>

Please note in the above file, we have specified three files to include the configuration via XML Files. The files are applicationContext.xml, applicationContext_hib.xml and springOrmContext.xml. Next step is to create all those files.
Step 4:
We will create a folder called resource and we will create all the three XML Configuration files. In the Eclipse Navigator , right click on the Project->Main folder and select New –> Folder and enter the folder name as “resource”. Now right click on Project and select Properties. In the Properties window, select Java Build Path
Select “Source Tab” and Click “Add Folder” and check the resource folder.

Step 5:
Next we will create applicationContext.xml in the resource folder. Select resource folder and right click, Select New –> Other –> XML File

Enter the file name as “applicationContext.xml”. Copy the below into newly created XML File

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:p="http://www.springframework.org/schema/p" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="
           http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-3.0.xsd
           http://www.springframework.org/schema/tx
                   http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

	<!-- ====================================================== -->
	<!-- Define the property placeholder configurer -->
	<!-- ====================================================== -->
	<bean id="propertyConfigurer"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="location" value="classpath:jdbc.properties" />
	</bean>

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="${jdbc.driverClassName}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
	</bean>

	
</beans>

Please Note that, in the above file, we have included one more file called jdbc.properties. This is the file where we are going to say what is the MySQL database name,u
user name and password. We will create this file later. So basically this XML Configuration file used to configure our data source (i,e) MySQL in our example.

Step 6:
Next on the resource folder, we will create the another XML Configuration file (applicationContext_hib.xml) for Hibernate Properties. Here is the content of the
applicationContext_hib.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:p="http://www.springframework.org/schema/p" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="
           http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-3.0.xsd
           http://www.springframework.org/schema/tx
                   http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">



	<!-- Define the datasource; define the session factory, we need specify 
		the packagesToScan property, this property will scan all entity annotated 
		class. define the context component-scan basepackage, this will scan all 
		@Repository annotated Class -->

	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="packagesToScan" value="com.example.domain" />

		<property name="hibernateProperties">
			<props>
				<prop key="configurationClass">org.hibernate.cfg.AnnotationConfiguration</prop>
				<prop key="hibernate.show_sql">true</prop>
				<prop key="hibernate.format_sql">true</prop>
				<prop key="hibernate.use_sql_comments">true</prop>
				<prop key="hibernate.zeroDateTimeBehavior">convertToNull</prop>
				<prop key="hibernate.connection.zeroDateTimeBehavior">convertToNull</prop>
				<prop key="hibernate.hbm2ddl.auto">validate</prop>
			</props>
		</property>
	</bean>
</beans>

Please Note that, in the above file, we have used packagesToScan property of the SessionFactory to dynamically pick up entity classes and set up the sessionFactory.
You can see that, we are going to create all our entity classes in the package com.example.domain.

Step 7:
Next step to create Spring – Hibernate integration XML Configuration file springOrmContext.xml.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:p="http://www.springframework.org/schema/p" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="
           http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-3.0.xsd
           http://www.springframework.org/schema/tx
                   http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

	<bean id="transactionManager"
		class="org.springframework.orm.hibernate4.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>
	

	<!-- Container Configuration: The IOC container configuration xml file is 
		shown below,The container has the <context:component-scan> element and <context:annotation-config/> 
		<context:annotation-config/> used to intimate the beans of this IOC container 
		are annotation supported. By pass the base path of the beans as the value 
		of the base-package attribute of context:component-scan element, we can detect 
		the beans and registering their bean definitions automatically without lots 
		of overhead. The value of base-package attribute is fully qualified package 
		name of the bean classes. We can pass more than one package names by comma 
		separated -->

	<context:annotation-config />
	<context:component-scan base-package="com.example.business" />

	<tx:annotation-driven transaction-manager="transactionManager" />
	<!-- <tx:annotation-driven transaction-manager="transactionManager2" /> -->

	<!-- This will ensure that hibernate or jpa exceptions are automatically 
		translated into Spring's generic DataAccessException hierarchy for those 
		classes annotated with Repository -->

	<bean
		class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />

	<bean id="CRUDService" class="com.example.business.service.CRUDServiceImpl" />
</beans>

Normally you declare all the beans or components in XML bean configuration file, so that Spring container can detect and register your beans or components.
Actually, Spring is able to auto scan, detect and instantiate your beans from pre-defined project package, no more tedious beans declaration in in XML file. So here
we said to auto detect in com.example.business package.
Step 8:
Now let us create database called “zkexamples” and table called “member”. Here is the table structure.

/*
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 `member` */

DROP TABLE IF EXISTS `member`;

CREATE TABLE `member` (
  `ID` bigint(20) NOT NULL auto_increment,
  `code` varchar(50) default NULL,
  `lastName` varchar(100) default NULL,
  `firstName` varchar(100) default NULL,
  `gender` varchar(10) default NULL,
  `DOB` date default NULL,
  `mobileNo` varchar(20) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!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 */;

Insert some records. here is the insert into script

INSERT INTO member(CODE,lastname,firstName, gender,DOB,mobileNo) VALUES('PJ4990015','Javier','Perez','Male','1965-12-12','3112123123')
INSERT INTO member(CODE,lastname,firstName, gender,DOB,mobileNo) VALUES('FS41100045','Simmons','Foster','Male','1967-12-12','5434343333')
INSERT INTO member(CODE,lastname,firstName, gender,DOB,mobileNo) VALUES('DH41111395','HanaMeal','David','Female','1956-03-22','4234343433')
INSERT INTO member(CODE,lastname,firstName, gender,DOB,mobileNo) VALUES('MP41121203','PATRICK','MICHEL','Male','1989-09-09','7234343466')
INSERT INTO member(CODE,lastname,firstName, gender,DOB,mobileNo) VALUES('DJ41290900','Jody','Dianna','Female','2012-12-12','3432123213')

Step 9:
Now let us create our entity class for the table member. As we said earlier, we will create this class in the package called “com.example.domain”. Create this package and class Member as shown here.

package com.example.domain;

import java.util.Date;

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

@Entity
@Table(name = "member")
public class Member {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private long ID;
	private String code;
	private String firstName;
	private String lastName;
	private String gender;
	@Temporal(TemporalType.DATE)
	private Date DOB;
	private String mobileNo;

	public long getID() {
		return ID;
	}

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

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	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 getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public Date getDOB() {
		return DOB;
	}

	public void setDOB(Date dOB) {
		DOB = dOB;
	}

	public String getMobileNo() {
		return mobileNo;
	}

	public void setMobileNo(String mobileNo) {
		this.mobileNo = mobileNo;
	}

}

Step 10:
Next we will create our DAO Layer. Select the java folder and right click, Select New –> Other->Package and enter the package name as com.example.business.dao. After package is created, select the package dao and right click, select New-> Other->Interface and enter the interface name as “CRUDDao”

package com.example.business.dao;

import java.io.Serializable;
import java.util.List;

public interface CRUDDao {
	<T> List<T> getAll(Class<T> klass);

	<T> void Save(T klass);

	<T> T findByPrimaryKey(Class<T> klass, Serializable id);

	<T> T GetUniqueEntityByNamedQuery(String query, Object... params);

	<T> List<T> GetListByNamedQuery(String query, Object... params);

	<T> void delete(T klass);

	<T> Long getQueryCount(String query, Object... params);
}

Step 11:
Now let us create the DAO Class which implement the above interface. Select the package dao and right click, select New-> Other->class and enter the class name as "CRUDDaoImpl” as shown

package com.example.business.dao;

import java.io.Serializable;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Repository;

@Repository
public class CRUDDaoImpl implements CRUDDao {

	@Autowired
	SessionFactory sessionFactory;

	@SuppressWarnings("unchecked")
	public <T> List<T> getAll(Class<T> klass) {
		return getCurrentSession().createQuery("from " + klass.getName())
				.list();
	}

	protected final Session getCurrentSession() {
		return sessionFactory.getCurrentSession();
	}

	public <T> void Save(T klass) throws DataAccessException {
		getCurrentSession().saveOrUpdate(klass);
	}

	public <T> void delete(T klass) throws DataAccessException {
		getCurrentSession().delete(klass);

	}

	/**
	 * Retrieve an object that was previously persisted to the database using
	 * the indicated id as primary key
	 */
	@SuppressWarnings("unchecked")
	public <T> T findByPrimaryKey(Class<T> klass, Serializable id) {
		return (T) getCurrentSession().get(klass, id);
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> GetListByNamedQuery(String query, Object... params) {
		Query q = getCurrentSession().getNamedQuery(query);

		int i = 1;
		String arg = "arg";
		if (params != null) {
			for (Object o : params) {
				if (o != null) {
					q.setParameter(arg + i, o);
					i++;
				}
			}
		}

		List<T> list = (List<T>) q.list();
		return list;
	}

	@SuppressWarnings("unchecked")
	public <T> T GetUniqueEntityByNamedQuery(String query, Object... params) {

		Query q = getCurrentSession().getNamedQuery(query);

		int i = 1;
		String arg = "arg";
		for (Object o : params) {
			q.setParameter(arg + i, o);
			i++;
		}

		List<T> results = q.list();

		T foundentity = null;
		if (!results.isEmpty()) {
			// ignores multiple results
			foundentity = results.get(0);
		}
		return foundentity;
	}

	public <T> Long getQueryCount(String query, Object... params) {

		Query q = getCurrentSession().getNamedQuery(query);
		int i = 1;
		String arg = "arg";
		Long count = (long) 0;

		if (params != null) {
			for (Object o : params) {
				if (o != null) {
					q.setParameter(arg + i, o);
					i++;
				}
			}
		}
		count = (Long) q.uniqueResult();
		return count;
	}
}

Hibernate 4 provides a feature called "contextual Sessions", where Hibernate itself manages one current Session per transaction. This is roughly equivalent to Spring's synchronization of one Hibernate Session per transaction.The main advantage of this DAO style is that it depends on Hibernate API only; no import of any Spring class is required.
For more information, Please refer the following links
1. The Persistence Layer with Spring 3.1 and Hibernate
2. Spring Integration with Hibernate
3. Implementing DAOs based on plain Hibernate 3 API
Step 12:
Next we will create our Service Layer. Select the java folder and right click, Select New –> Other->Package and enter the package name as com.example.business.service. After package is created, select the package service and right click, select New-> Other->Interface and enter the interface name as “CRUDService”

package com.example.business.service;

import java.io.Serializable;
import java.util.List;

public interface CRUDService {
	<T> List<T> getAll(Class<T> klass);

	<T> void Save(T klass);

	<T> T findByPrimaryKey(Class<T> klass, Serializable id);

	<T> void delete(T klass);

	public <T> T GetUniqueEntityByNamedQuery(String query, Object... params);

	<T> List<T> GetListByNamedQuery(String query, Object... params);

	<T> Long getQueryCount(String query, Object... params);
}

Step 13:
Now let us create the Service Class which implement the above interface. Select the package service and right click, select New-> Other->class and enter the class name as "CRUDServiceImpl” as shown

package com.example.business.service;

import java.io.Serializable;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.example.business.dao.CRUDDao;

@Service
public class CRUDServiceImpl implements CRUDService {

	@Autowired
	private CRUDDao CRUDDao;

	@Transactional(readOnly = true)
	public <T> List<T> getAll(Class<T> klass) {
		return CRUDDao.getAll(klass);
	}

	@Transactional
	public <T> void Save(T klass) throws DataAccessException {
		CRUDDao.Save(klass);
	}

	@Transactional
	public <T> void delete(T klass) throws DataAccessException {
		CRUDDao.delete(klass);
	}

	@Transactional
	public <T> T GetUniqueEntityByNamedQuery(String query, Object... params) {
		return CRUDDao.GetUniqueEntityByNamedQuery(query, params);
	}

	@Transactional
	public <T> List<T> GetListByNamedQuery(String query, Object... params) {
		return CRUDDao.GetListByNamedQuery(query, params);
	}

	@Override
	@Transactional(readOnly = true)
	public <T> Long getQueryCount(String query, Object... params) {
		return CRUDDao.getQueryCount(query, params);
	}

	@Override
	@Transactional(readOnly = true)
	public <T> T findByPrimaryKey(Class<T> klass, Serializable id) {
		 return CRUDDao.findByPrimaryKey(klass, id);
	}

}

Step 14:
Next step is to create our jdbc.properties where we will define the MySQL connectivity information. In the resource folder, create a file called jdbc.properties as shown.

Here is the content of that file

#################################################################
# MYSQL                         								#
#################################################################
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/zkexamples?zeroDateTimeBehavior=convertToNull
jdbc.username=root
jdbc.password=1234
hibernate.dialect=org.hibernate.dialect.MySQLDialect 
 
#################################################################
# Hibernate Query Debug output in the console                   #
#################################################################
hibernate.show_sql=false
hibernate.format_sql=true

Step 15

It is time to test all our stuffs. Before running the application, make sure the following items.
1. In the Eclipse markers window, make sure there are no error message.
2. DAO Layer classes should be created in package com.example.business.dao;
3. Service Layer Classes should be created in package com.example.business.service.
4. Database Entity classes should be created in package com.example.domain;

Here is the Overall Project Structure.

When creating ZK Maven Project, by default index.zul and corresponding VM Class MyViewModel.java will be created as shown here

Double click MyViewModel and replace with the following content. Here we are just retrieving the all the records from the member table and
showing the total number of records in ZK Messagebox.

package ZK7Example1;

import java.util.List;

import org.zkoss.bind.annotation.Command;
import org.zkoss.bind.annotation.Init;
import org.zkoss.bind.annotation.NotifyChange;
import org.zkoss.zk.ui.select.annotation.WireVariable;
import org.zkoss.zkplus.spring.SpringUtil;
import org.zkoss.zul.Messagebox;

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

public class MyViewModel {

	@WireVariable
	protected CRUDService crudService;
	
	private int count;

	@Init
	public void init() {
		count = 100;
	}

	@Command
	@NotifyChange("count")
	public void cmd() {
		crudService = (CRUDService) SpringUtil.getBean("CRUDService");
		List<Member> allReordsInDB=null;
		allReordsInDB = crudService.getAll(Member.class);
		Messagebox.show("Total Records : " + allReordsInDB.size());
		++count;
	}

	public int getCount() {
		return count;
	}
}

Now select the project and run. On clicking of the button, you should see the following output

You can download the source here