第一种 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)这里可查询集合,但是暂时还没有方法如何去处理分页。。。
|