1.两个表对应的实体
public class Subarea implements Serializable{
private String id;
private String decidedzoneId;
private String regionId;
private String addresskey;
private String startnum;
private String endnum;
private String single;
private String position;
//封装和区域表的联查结果
private Region region;
...
...
...
}
public class Region implements Serializable{
private String id;
private String province;
private String city;
private String district;
private String postcode;
private String shortcode;
private String citycode;
...
...
...
}
1.Controller
/**
* 分页查询
* @return
*/
@RequestMapping("/SubAreaController/list.action")
@ResponseBody
public PageBean list(Subarea subarea,@RequestParam(defaultValue = "1") Integer page,
@RequestParam(defaultValue = "10")Integer rows) throws Exception {
PageBean pageBean = new PageBean();
pageBean.setCurrentPage(page);
pageBean.setPageSize(rows);
if(subarea.getRegion() == null){
subAreaService.list(pageBean);
}else{
subAreaService.selectByConditions(pageBean,subarea);
}
return pageBean;
}
2.service层
/**
* 带条件的分页查询
* @param pageBean
* @param subarea
* @throws Exception
*/
@Override
public void selectByConditions(PageBean pageBean, Subarea subarea) throws Exception {
PageHelper.startPage(pageBean.getCurrentPage(),pageBean.getPageSize());
List<Subarea> subareas = subareaMapper.selectByConditions(subarea);
PageInfo<Subarea> subareaPageInfo = new PageInfo<>(subareas);
pageBean.setTotal(subareaPageInfo.getTotal());
pageBean.setRows(subareaPageInfo.getList());
}
3.Mapper接口
List<Subarea> selectByConditions(Subarea subarea);
4.Mapper.xml文件
<resultMap id="selectByConditionsResult" type="com.jujung.bos.pojo.Subarea">
<id column="id" property="id"/>
<result column="regionId" property="regionId"/>
<result column="addresskey" property="addresskey"/>
<result column="startnum" property="startnum"/>
<result column="endnum" property="endnum"/>
<result column="single" property="single"/>
<result column="position" property="position"/>
<association property="region" javaType="com.jujung.bos.pojo.Region">
<!--
column:指关联对象数据库中字段名
property:指关联对象在实体中属性名
-->
<id column="region_id" property="id"/>
<result column="province" property="province"/>
<result column="city" property="city"/>
<result column="district" property="district"/>
</association>
</resultMap>
<select id="selectByConditions" parameterType="com.jujung.bos.pojo.Subarea" resultMap="selectByConditionsResult">
SELECT t1.id,t1.region_id,t1.addresskey,t1.startnum,t1.endnum,t1.single,t1.position,t2.province,t2.city,t2.district
FROM bc_subarea t1 INNER JOIN bc_region t2
ON t1.region_id = t2.id
<where>
<if test="region.province != null">
AND t2.province like '%${region.province}%'
</if>
<if test="region.city != null">
AND t2.city like '%${region.city}%'
</if>
<if test="region.district != null">
AND t2.district like '%${region.district}%'
</if>
<if test="addresskey != null">
AND t1.addresskey like '%${addresskey}%'
</if>
</where>
</select>