Spring JPA 查询的几种方式并处理分页

第一种 NamedQuery（返回方式为列模式[原生态sql的复杂查询]）

1)dao层处理查询并分页

@SuppressWarnings("unchecked")
	public PageResult<T> getList(Integer currentPage){
		PageResult<T> pageResult = new PageResult<T>();
			int pageSize = Constant.DEFAULT_PAGE_SIZE;
			int start = (currentPage - 1) * pageSize;
			Query query = getEntityManager().createNamedQuery("ReturnTrainAppyUser");
			int total = query.getResultList().size();
			// 判断分页
			if (start < total && pageSize > 0) {
				query.setFirstResult(start);
				query.setMaxResults(pageSize);
				pageResult.setFirst(start);
				pageResult.setPageSize(pageSize);
			}
			
			pageResult.setTotalCount(total);
			pageResult.setPageResultList(query.getResultList());
		return pageResult;
	}

 2）控制层代码

@RequestMapping("/applyList")
	public String applyList(HttpServletRequest request,
			HttpServletResponse response, Model model) throws Exception {
		Integer currentPage = 1;
		Integer pageNum = getIntParameter(request, "pageNum");
		if (pageNum != null) {
			currentPage = getIntParameter(request, "pageNum");
		}
		PageResult<TrainApply> a = trainApplyService.findContentResult(currentPage);
		addPageResultModel2(a, currentPage, model);
		return "common/train/admin/applyList";
	}

  处理分页参数

 

	protected <E extends VO> void addPageResultModel2(PageResult<E> pct,Integer currentPage, Model model) {
		model.addAttribute("totalCount", pct.getTotalCount());
		model.addAttribute("numPerPage", Constant.DEFAULT_PAGE_SIZE);
		model.addAttribute("pageNum", currentPage);
		model.addAttribute("pageNumShown", pct.getPageCount(pct.getTotalCount(), Constant.DEFAULT_PAGE_SIZE));
		model.addAttribute("currentPage", currentPage);
		model.addAttribute("itemList", pct.getPageResultList());
	}

 3）实体类

@NamedNativeQueries
(
    {
       @NamedNativeQuery(
           name="ReturnTrainAppyUser",
           query=" select a.id as apply_id,b.id as plan_id,b.title as plan_title,(select count(c.id) from train_apply_user c where c.APPLY_ID=a.ID) as 'apply_user_num',a.company as 'apply_company' from train_apply a inner join train_plan b on b.ID=a.PLAN_ID",
           resultSetMapping="ReturnTrainAppyUser"),
}
)
@SqlResultSetMappings(
{
    @SqlResultSetMapping
    (
       name="ReturnTrainAppyUser",
       entities={},
       columns=
       {
    	   @ColumnResult(name="apply_id"),
           @ColumnResult(name="plan_id"),
           @ColumnResult(name="plan_title"),
           @ColumnResult(name="apply_user_num"),
           @ColumnResult(name="apply_company")
       }
    )
})
@Entity
@Table(name = "train_apply")
public class TrainApply extends VO {
	private static final long serialVersionUID = -6530604520661376764L;
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;// ID
	private Long planId;//计划ID
	private String orgName;//单位名称
	private String roomType;//客房类型
	private String roomNumber;//客房数
	private String invoiceType;//发票类型
	private String status;//状态
	
	
	
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public Long getPlanId() {
		return planId;
	}
	public void setPlanId(Long planId) {
		this.planId = planId;
	}
	public String getOrgName() {
		return orgName;
	}
	public void setOrgName(String orgName) {
		this.orgName = orgName;
	}
	public String getRoomType() {
		return roomType;
	}
	public void setRoomType(String roomType) {
		this.roomType = roomType;
	}
	public String getRoomNumber() {
		return roomNumber;
	}
	public void setRoomNumber(String roomNumber) {
		this.roomNumber = roomNumber;
	}
	public String getInvoiceType() {
		return invoiceType;
	}
	public void setInvoiceType(String invoiceType) {
		this.invoiceType = invoiceType;
	}
	public String getStatus() {
		return status;
	}
	public void setStatus(String status) {
		this.status = status;
	}
	
}

 4）页面处理

<table width="100%" cellspacing="0" cellpadding="0">
			<thead>
				<tr class="Train_Resultlist-item">
					<th width="50">NO.</th>
					<th>培训计划</th>
					<th>报名人数</th>
					<th>操作</th>
				</tr>
			</thead>
			<tbody>
				<c:forEach var="item" items="${itemList}" varStatus="s">
				<tr>
					<td>${s.index + 1}</td>
					<td>${item[2]}</td>
					<td>${item[3]}</td>
					<td>
					<div  class="applylist_operate">
					    <a class="icon icon_train_edit" href="<c:url value='/train/admin/applyEdit/${item[0]}.htm'/>"></a>
						<a class="icon icon_train_delete" href="<c:url value='/train/admin/applyDelete'/>/${item[0]}.htm"  title="确定要删除吗?删除后数据将不可恢复"></a>
					</div>	
					</td>
				</tr>
				</c:forEach>
				<!-- 分页start -->
				<tr>
					<td colspan="4">
							<div class="green-black">
								共${totalCount}条数据.
								<a title="首页" href="<c:url value='/train/admin/applyList.htm?pageNum=1'/>">首页</a> 
								<c:if test="${currentPage le 1}" var="syy">
								<a title="上一页" href="#">上一页</a>
								</c:if>
								<c:if test="${!syy}">
								<a title="上一页" href="<c:url value='/train/admin/applyList.htm?pageNum=${currentPage-1}'/>">上一页</a>
								</c:if>
								<c:forEach var="pageNo" begin="1" end="${pageNumShown}">
										<a href="<c:url value='/train/admin/applyList.htm?pageNum=${pageNo}'/>">
										<c:if test="${currentPage eq pageNo}" var="rsFy">
											<strong>${pageNo}</strong>
										</c:if>
										<c:if test="${!rsFy}">
											${pageNo}
										</c:if>
										</a>
								</c:forEach>
								<c:if test="${currentPage ge pageNumShown}" var="xyy">
									<a title="下一页" href="#">下一页</a>
								</c:if>
								<c:if test="${!xyy}">
									<a title="下一页" href="<c:url value='/train/admin/applyList.htm?pageNum=${currentPage+1}'/>">下一页</a>
								</c:if>
								<a title="尾页" href="<c:url value='/train/admin/applyList.htm?pageNum=${pageNumShown}'/>">尾页</a>
							</div>
					</td>
				</tr>
				<!-- 分页end -->							
			</tbody>
		</table>

 

 

第二种createNativeQuery（返回方式为实体对象集合）

1）dao层代码

@SuppressWarnings("unchecked")
	public PageResult<T> getList(Integer currentPage){
		int pageSize = Constant.DEFAULT_PAGE_SIZE;
		int start = (currentPage - 1) * pageSize;
		String sql="select a.* "
				+" from train_apply a inner join train_plan b on b.ID=a.PLAN_ID";
		PageResult<T> pageResult = new PageResult<T>();
		
		Query query = getEntityManager().createNativeQuery(sql.toString(),TrainApply.class);
		int total = query.getResultList().size();
		// 判断分页
		if (start < total && pageSize > 0) {
			query.setFirstResult(start);
			query.setMaxResults(pageSize);
			pageResult.setFirst(start);
			pageResult.setPageSize(pageSize);
		}
		pageResult.setTotalCount(total);
		pageResult.setPageResultList(query.getResultList());
		return pageResult;
	}

 2）其他与第一种类似，实体类不需要注释@NamedNativeQueries等。页面读取为item.planId类似方法获取数据，而不是item[0],item[1]。。。只是对于复杂的sql好像不太适合，比如说要count(id)某一列，目前用这种方式还不能实现几个表直接复杂字段展现查询。待研究。

 

 

第三种 注解对象查询（返回方式为实体对象集合）

1）dao层

@Query("select new TrainApplyVo(a.id,count(c.id) as applyUserNum,a.orgName as applyUserCompany)"
			+" from TrainApply a,TrainPlan b,TrainApplyUser c where b.id=a.planId and a.id=c.applyId and b.id = ?1 group by b.id")
	public List<TrainApply> getTrainApplyListByPlanId(Long planId);

 2）这里可查询集合，但是暂时还没有方法如何去处理分页。。。