java:
实体类:
/**
* 用于分页
*/
public class TribePageVo {
private Long tid;
private String name;
private String introduce;
private Integer memberCount;
private String imgUrl;
private Byte isSign;
private String createNickName;
private String createUserId;
public String getCreateUserId() {
return createUserId;
}
public void setCreateUserId(String createUserId) {
this.createUserId = createUserId;
}
public String getCreateNickName() {
return createNickName;
}
public void setCreateNickName(String createNickName) {
this.createNickName = createNickName;
}
public String getImgUrl() {
return imgUrl;
}
public void setImgUrl(String imgUrl) {
this.imgUrl = imgUrl;
}
private Byte relation=(byte)0;
public Long getTid() {
return tid;
}
public void setTid(Long tid) {
this.tid = tid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getIntroduce() {
return introduce;
}
public void setIntroduce(String introduce) {
this.introduce = introduce;
}
public Integer getMemberCount() {
return memberCount;
}
public void setMemberCount(Integer memberCount) {
this.memberCount = memberCount;
}
public Byte getRelation(){
return relation;
}
public void setRelation(Byte relation){
this.relation=relation;
}
public Byte getIsSign() {
return isSign;
}
public void setIsSign(Byte isSign) {
this.isSign = isSign;
}
@Override
public String toString() {
return "TribePageVo{" +
"tid=" + tid +
", name='" + name + '\'' +
", introduce='" + introduce + '\'' +
", memberCount=" + memberCount +
", relation=" + relation +
", imgUrl=" + imgUrl +
'}';
}
}
DAO:
Map<String, Object> resultMap = new HashMap<>();
// 1.初始化查询结果接收参数
Page<TribePageVo> tribePageVoList = new Page<TribePageVo>();// 2.设置查询参数
Map<String, Object> tribePageParams = new HashMap<String, Object>();
tribePageParams.put("cityId", cityId);
tribePageParams.put("typeId", typeId);
tribePageParams.put("orderType", orderType);
tribePageParams.put("uid", userId);
// 3.校验页码
page = checkPage(page);
tribePageParams.put("page", page);
PageHelper.startPage(page , 20);
tribePageVoList = (Page<TribePageVo>) tribeModelMapper.selectTribeList1(tribePageParams);
resultMap.put("tribePage", tribePageVoList);
public String tribeIndex(
HttpServletRequest request,
Model model,
Integer page,
String orderType,
@CookieValue(value = "cityName", defaultValue = "全国") String cityName,
@CookieValue(value = "cityId", defaultValue = "1") String cityId) {
。。。。。。。。。。。。。
model.addAllAttributes(resultMap);
return resultMap;
}
配置mapping;
1.union必须每列一致且顺序一致;
2.加一个select tt.* from ( ...) 和 select ttt.* from ()是为了让tt.*查出的内容始终在前面,不管按照什么排序;
3.如果是单独分页不需要前面分配固定的内容,则只需要union前后其中一个sql就可以。
<!--部落首页的分页vo-->
<resultMap id="TribePageVo" type="poseidon.web.muses.db.tribe.vo.TribePageVo">
<id column="tid" property="tid" jdbcType="BIGINT"/>
<result column="introduce" property="introduce" jdbcType="VARCHAR"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="memberCount" property="memberCount" javaType="int"/>
<result column="isSign" property="isSign" jdbcType="TINYINT"/>
</resultMap>
<select id="selectTribeList1" resultMap="TribePageVo" resultType="list">
select tt.* from ( select
t.tid AS tid,
t.name AS name,
t.introduce AS introduce,
t.createUserId as createUserId,
(select c.cityEn from city c where c.id = #{cityId}) AS imgUrl,
(select u.nickName from user u where u.uid = t.createUserId) as createNickName,
(select count(*) from tribejoin tj where (tj.tid = t.tid)) as memberCount,
(select max(tp.createDate) from tribepost tp where (tp.tid = t.tid)) as hot
<if test="uid != null">
,(case when t.createUserId=#{uid} then 2 else 0 end + case when (select count(*) from tribejoin tj where tj.tid= t.tid and tj.uid = #{uid} and tj.examine = 1) = 0 then 0 else 1 end) as relation
</if>
from (recommenddata rd left join tribe t on rd.objId = t.tid) left join recommendtype rt on rd.typeid=rt.typeid
where rt.cityid = #{cityId} and rt.typeid = #{typeId}) tt
UNION
select ttt.* from ( select
t.tid AS tid,
t.name AS name,
t.introduce as introduce,
t.createUserId as createUserId,
(select c.cityEn from city c where c.id = #{cityId}) AS imgUrl,
(select u.nickName from user u where u.uid = t.createUserId) as createNickName,
(select count(*) from tribejoin tj where (tj.tid = t.tid)) as memberCount,
(select max(tp.createDate) from tribepost tp where tp.tid = t.tid) as hot
<if test="uid != null">
,(case when t.createUserId=#{uid} then 2 else 0 end + case when (select count(*) from tribejoin tj where tj.tid= t.tid and tj.uid = #{uid} and tj.examine = 1) = 0 then 0 else 1 end) as relation
</if>
from tribe t
where t.status = 1 and t.examine = 1 and not exists(select 1 from recommenddata rd,recommendtype rt where rt.typeid = rd.typeid and rd.objid = t.tid and rt.cityid= #{cityId} and rt.typeid = #{typeId})
order by memberCount desc) ttt
</select>
前台:
<div class="hot-activity-area self-driving-area tribal-topics-area-two o-hidden">
<ul>
<% for(tribe in tribePage){ %>
<li class="">
<div class="item">
<div class="pic o-hidden posr">
<img src="../style0/images/ad/picture_17.png">
<input type="button" class="input-join posa br5 bg-deep-orange btn-hover cp white yahei" value="+加入">
</div>
<div class="info o-hidden bg-white">
<h4 class="font18 black normal">${tribe.createNickName}</h4>
<p class="font12 o-hidden black">${tribe.introduce!''}</p>
<div class="ind o-hidden">
<div class="icon fl o-hidden"><img src="../style0/images/ad/picture_23.png"></div>
<div class="name fl o-hidden orange font14">${tribe.name!''}</div>
</div>
</div>
</div>
</li>
<% }%>
</ul>
</div>
<!--分页区-->
<div class="page-area text-center">
<div class="fix"></div>
<div id="main_Page"></div>
</div>
<script type="text/javascript">
/*分页*/
$(document).ready(function(){
laypage({
cont: $('#main_Page'),
pages: ${tribePageCount},
curr: ${tribePageCurrent},
urlPrefix:"/tribe/tribe.html?orderType=${orderType! 'memberCount'}&page="
});
});
</script >