employeequerycommand.java

一个自娱自乐的demo 开发环境 apache-tomcat-6.0.16 Mysql 5.1.11 Jdk 1.6 文件结构如下 --MyGame -----MyGam

/**
 * 2008/02/15
 * 
 * @author 何 貝
 */
package com.hb.base.services.organization.imp.queryCommand;

import com.hb.base.view.common.ResultsSize;
import com.hb.base.view.domain.EmployeeInfo;
import com.hb.core.query.queryCommandImp.hibernate3.AbstractNativeSQLQueryCommand;
import com.hb.core.util.StringUtil;

/**
 * 员工信息动态查询命令
 */
@SuppressWarnings("unchecked")
public class EmployeeQueryCommand extends AbstractNativeSQLQueryCommand {
	/** 记录集和尺寸标记 */
	private boolean resultsSize = false;
	public static final String FILTER_VALUE = "filterValue";
	public static final String ALL_DISPLAY = "999";
	private String filterCondition;
	private String filterValue;
	
	public EmployeeQueryCommand() {
		
	}
	
	public EmployeeQueryCommand(String filterCondition, String filterValue) {
		this.filterCondition = filterCondition;
		this.filterValue = filterValue;
	}
	
	public void getResultsSize() {
		resultsSize = true;
	}
	
	public void createQuerySql() {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT ")
			  .append("		DISTINCT ");
		
		if (resultsSize) {// 取得记录集和尺寸
			sqlBuf.append("count(emp.EMPLOYEE_ID) AS resultsSize");
		} else {
			sqlBuf.append("		emp.EMPLOYEE_ID AS employeeID, ")
			  	  .append("		per.NAME AS employeeName, ")
			  	  .append("		per.AGE AS employeeAge, ")
			  	  .append("		per.SEX AS employeeSex, ")
			  	  .append("		dep.DEPARTMENT_NAME AS departmentName, ")
			  	  .append("		jobInfo.JOB_INFO_NAME AS jobName, ")
			  	  .append("		employ.START_DATE AS employmentStartDate, ")
			  	  .append("		employ.END_DATE AS employmentEndDate ");
		}
			  
		sqlBuf.append(" FROM ")
			  .append("		SYS_EMPLOYEE emp,")
			  .append("		SYS_EMPLOYMENT employ,")
			  .append("		SYS_PERSON per,")
			  .append("		SYS_JOB_INFO jobInfo,")
			  .append("		SYS_DEPARTMENT dep,")
			  .append("		SYS_JOB job")
			  .append(" WHERE")
			  .append("		emp.EMPLOYEE_ID = employ.EMPLOYEE_ID ")
			  .append("	AND employ.PERSON_ID = per.PERSON_ID ")
			  .append("	AND job.EMPLOYEE_ID = emp.EMPLOYEE_ID ")
			  .append("	AND job.JOB_ID = jobInfo.JOB_ID ")
			  .append("	AND job.DEPARTMENT_ID = dep.DEPARTMENT_ID ");
		
		setQueryParam(sqlBuf);
		
		if (!resultsSize) {
			sqlBuf.append("	ORDER BY ")
			      .append("		employeeID ASC, ")
			      .append("		job.NO ASC ");
		}
			  
		this.querySql = sqlBuf.toString();
	}

	/**
	 * 追加查询条件
	 * @param sqlBuf 查询用SQL
	 */
	private void setQueryParam(StringBuffer sqlBuf) {
		// 包涵查询参数
		if (!StringUtil.isEmpty(filterCondition) && !filterCondition.equals(ALL_DISPLAY)) {
			if (filterCondition.equals("0")) {// 员工编号
				sqlBuf.append(" AND emp.EMPLOYEE_ID like :filterValue ");
				setQueryParam(FILTER_VALUE, "%" + filterValue + "%");

			} else if (filterCondition.equals("1")) {// 员工名称
				sqlBuf.append(" AND per.NAME like :filterValue ");
				setQueryParam(FILTER_VALUE, "%" + filterValue + "%");

			} else if (filterCondition.equals("2")) {// 年龄
				sqlBuf.append(" AND per.AGE = :filterValue ");
				setQueryParam(FILTER_VALUE, filterValue);

			} else if (filterCondition.equals("3")) {// 性别
				sqlBuf.append(" AND per.SEX = :filterValue ");
				setQueryParam(FILTER_VALUE, filterValue);

			} else if (filterCondition.equals("4")) {// 所属部门
				sqlBuf.append(" AND dep.DEPARTMENT_NAME like :filterValue ");
				setQueryParam(FILTER_VALUE, "%" + filterValue + "%");

			} else if (filterCondition.equals("5")) {// 担任职位
				sqlBuf.append(" AND jobInfo.JOB_INFO_NAME like :filterValue ");
				setQueryParam(FILTER_VALUE, "%" + filterValue + "%");

			} else if (filterCondition.equals("6")) {// 合同开始日期
				sqlBuf.append(" AND employ.START_DATE >= :filterValue ");
				setQueryParam(FILTER_VALUE, filterValue);

			} else if (filterCondition.equals("7")) {// 合同结束日期
				sqlBuf.append(" AND employ.END_DATE <= :filterValue ");
				setQueryParam(FILTER_VALUE, filterValue);
			}
		}
	}
	
	public void createDomainClass() {
		if (resultsSize) {// 取得记录集和尺寸
			setDomainClass(ResultsSize.class);
		} else {
			setDomainClass(EmployeeInfo.class);
		}
	}

	public void createQueryItemMap() {
		if (resultsSize) {
			addQueryItemMap("resultsSize", new org.hibernate.type.IntegerType());
		}else {
			addQueryItemMap("employeeID", new org.hibernate.type.StringType());
			addQueryItemMap("employeeName", new org.hibernate.type.StringType());
			addQueryItemMap("employeeAge", new org.hibernate.type.StringType());
			addQueryItemMap("employeeSex", new org.hibernate.type.IntegerType());
			addQueryItemMap("departmentName", new org.hibernate.type.StringType());
			addQueryItemMap("jobName", new org.hibernate.type.StringType());
			addQueryItemMap("employmentStartDate", new org.hibernate.type.StringType());
			addQueryItemMap("employmentEndDate", new org.hibernate.type.StringType());
		}
	}
}