Spring JPAクエリーのいくつかの方法とページングの処理


第1種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>

 
 
第2の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)その他は第1種と同様であり,実体クラスには@NamedNativeQueriesなどの注釈は不要である.ページ読み込みはitem[0]、item[1].ただし、複雑なsqlには適していないようです.たとえば、count(id)のカラムを使用すると、いくつかのテーブルが直接複雑なフィールドでクエリーを表示することはできません.研究を待つ.
 
 
3番目の注釈オブジェクトクエリー(戻りはエンティティオブジェクトのセット)
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)ここでは集合を問い合わせることができますが、ページングをどのように処理するかはまだ方法がありません...