一個有點噁心的查詢+分頁

@Entity
public class Employee extends XXXObject {

//...

/**
	 * 最近復職日期
	 */
	private Date lastReinstatementDate;

/**
	 * 核薪資料者
	 */
	private Set<BaseCheckSalary> baseCheckSalary = new HashSet<BaseCheckSalary>();

@OneToMany(mappedBy = "employee", fetch = FetchType.LAZY)
	@Cascade(value = { org.hibernate.annotations.CascadeType.ALL,
			org.hibernate.annotations.CascadeType.DELETE_ORPHAN })
	public Set<BaseCheckSalary> getBaseCheckSalary() {
		return baseCheckSalary;
	}

	public void setBaseCheckSalary(Set<BaseCheckSalary> baseCheckSalary) {
		this.baseCheckSalary = baseCheckSalary;
	}

//...

}

 

@Entity
public class BaseCheckSalary extends XXXObject {

//...

private Employee employee;
	private Date startDate;

	@ManyToOne(fetch = FetchType.LAZY)
	public Employee getEmployee() {
		return employee;
	}

	public void setEmployee(Employee employee) {
		this.employee = employee;
	}

//...

}

  

@Name("baseCheckSalaryQuery")
@Scope(ScopeType.CONVERSATION)
public class BaseCheckSalaryQuery extends EntityQuery<Employee> {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	//未核薪員工查詢語句
	private static final String UNCHECKED_EMPLOYEE = "select e from Employee e where e not in " +
			"(select b.employee from BaseCheckSalary b) or " +
			"(e.lastReinstatementDate is not null and e.lastReinstatementDate > " +
			"(select max(b.startDate) from BaseCheckSalary b where b.employee = e ))";
	
	//已核薪員工查詢語句
	private static final String CHECKED_EMPLOYEE = "select e from Employee e where e not in " +
			"(select emp from Employee emp where emp not in (select b.employee from BaseCheckSalary b) or " +
			"(e.lastReinstatementDate is not null and emp.lastReinstatementDate > " +
			"(select max(b.startDate) from BaseCheckSalary b where b.employee = emp )))";
	
	//所有員工查詢語句
	private static final String ALL_EMPLOYEE = "from Employee e";
	
	public BaseCheckSalaryQuery() {
		super.setEjbql(UNCHECKED_EMPLOYEE);
		super.setOrder("e.employeeNumber");
		super.setMaxResults(20);
	}
	
	/**
	 * 查詢所有員工
	 * 
	 * @return
	 */
	public List<Employee> queryAllEmployee() {
		super.setEjbql(ALL_EMPLOYEE);
		return super.getResultList();
	}
	
	/**
	 * 查詢已核薪員工
	 * 
	 * @return
	 */
	public List<Employee> queryCheckedEmployee() {
		super.setEjbql(CHECKED_EMPLOYEE);
		return super.getResultList();
	}
	
	/**
	 * 查詢未核薪員工
	 * 
	 * @return
	 */
	public List<Employee> queryUnCheckedEmployee() {
		super.setEjbql(UNCHECKED_EMPLOYEE);
		return super.getResultList();
	}

}

 

@Name("baseCheckSalaryHome")
@Scope(ScopeType.CONVERSATION)
public class BaseCheckSalaryAction {

//...
	
	private static final Integer MAXRESULTS = 15;// 分頁查詢每頁顯示行數

@RequestParameter
	private Integer firstResult = 0;// 分頁查詢開始頁
	
	@In(required = false, create = true)
	@Out(required = false, scope = ScopeType.CONVERSATION)
	private ControlBean controlBean;// 注入控制類

	/**
	 * 根據核薪狀態查詢符合條件的員工列表
	 */
	@Factory("employeeList")
	public void getEmployeeList() {
		baseCheckSalaryQuery.setFirstResult(firstResult);
		baseCheckSalaryQuery.setMaxResults(MAXRESULTS);
		if (queryStatus.equals("false")) {
			employeeList = baseCheckSalaryQuery.queryUnCheckedEmployee();
		} else if (queryStatus.equals("true")) {
			employeeList = baseCheckSalaryQuery.queryCheckedEmployee();
		} else {
			employeeList = baseCheckSalaryQuery.queryAllEmployee();
		}
		controlBean.setPreviousExists(baseCheckSalaryQuery.isPreviousExists());
		controlBean.setNextExists(baseCheckSalaryQuery.isNextExists());
	}

//...

}

 

<rich:dataTable
					id="empList" value="#{employeeList}" var="bcsl" width="90%"
					onRowMouseOver="this.style.backgroundColor='#666666'"
					onRowMouseOut="this.style.backgroundColor='#{a4jSkin.tableBackgroundColor}'">
					<a4j:support event="onRowClick"
						action="#{baseCheckSalaryHome.getBaseCheckSalaryDetail}"
						reRender="empInfo,rsb,otherItemtable">
					</a4j:support>
					<h:column>
						<f:facet name="header">
							<h:outputText value="員工工號" />
						</f:facet>
						<h:outputText value="#{bcsl.employeeNumber}" />
					</h:column>
					<h:column>
						<f:facet name="header">
							<h:outputText value="員工姓名" />
						</f:facet>
						<h:outputText value="#{bcsl.name}" />
					</h:column>
					<h:column>
						<f:facet name="header">
							<h:outputText value="核薪樣板" />
						</f:facet>
						<h:outputText
							value="#{baseCheckSalaryHome.getSalaryModelName(bcsl)}" />
					</h:column>
                    <f:facet name="footer">
									<h:panelGrid columns="4" >
							<f:subview id="s_firstPage_y"
								rendered="#{controlBean.previousExists}">
								<a4j:commandLink action="#{baseCheckSalaryHome.getEmployeeList}"
									value="&lt;&lt;第一頁" rendered="#{controlBean.previousExists}"
									reRender="empList">
									<f:param name="firstResult" value="0" />
								</a4j:commandLink>
							</f:subview>
							<f:subview id="s_firstPage_n"
								rendered="#{!controlBean.previousExists}">
								<h:outputText value="&lt;&lt;第一頁" />
							</f:subview>
							
							<f:subview id="s_previousPage_y"
								rendered="#{controlBean.previousExists}">
								<a4j:commandLink action="#{baseCheckSalaryHome.getEmployeeList}"
									value="&lt; 上一頁" rendered="#{controlBean.previousExists}"
									reRender="empList">
									<f:param name="firstResult"
										value="#{baseCheckSalaryQuery.previousFirstResult}" />
								</a4j:commandLink>
							</f:subview>
							<f:subview id="s_previousPage_n"
								rendered="#{!controlBean.previousExists}">
								<h:outputText value="&lt; 上一頁" />
							</f:subview>


							<f:subview id="s_nextPage_y" rendered="#{controlBean.nextExists}">
								<a4j:commandLink action="#{baseCheckSalaryHome.getEmployeeList}"
									value="下一頁&gt;" rendered="#{controlBean.nextExists}"
									reRender="empList">
									<f:param name="firstResult"
										value="#{baseCheckSalaryQuery.nextFirstResult}" />
								</a4j:commandLink>
							</f:subview>
							<f:subview id="s_nextPage_n"
								rendered="#{!controlBean.nextExists}">
								<h:outputText value="下一頁&gt;" />
							</f:subview>
							
							<f:subview id="s_lastPage_y" rendered="#{controlBean.nextExists}">
								<a4j:commandLink action="#{baseCheckSalaryHome.getEmployeeList}"
									value="最后頁&gt;&gt;" rendered="#{controlBean.nextExists}"
									reRender="empList">
									<f:param name="firstResult"
										value="#{baseCheckSalaryQuery.lastFirstResult}" />
								</a4j:commandLink>
							</f:subview>
							<f:subview id="s_lastPage_n"
								rendered="#{!controlBean.nextExists}">
								<h:outputText value="最后頁&gt;&gt;" />
							</f:subview>
						</h:panelGrid>	
					</f:facet>
				</rich:dataTable>