ssm后台条件查询分页
源码地址点击打开链接
1.dao层
OnuDetailedInfoMapper
package com.onu.dao.awave;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import com.onu.entity.awave.OnuDetailedInfo;
public interface OnuDetailedInfoMapper extends BaseDao<OnuDetailedInfo> {
//根据条件分页
List<OnuDetailedInfo> selectOnuInfo(Map<String, Object> map);
/**
* 根据条件查询总条数
* @param map
* @return
*/
int count(Map<String, Object> map);
}
2.mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.onu.dao.awave.OnuDetailedInfoMapper">
<!-- 根据条件分页查询光交箱 -->
<select id="selectOnuInfo" resultType="OnuDetailedInfo"
>
SELECT o.name,o.addr,o.uploadTime,o.longitude,r.region_name,o.latitude,o.onunumber,o.onuid,o.deviceid FROM onuinfo o,region r where o.areaid = r.id
<if test="id!=null and id!=0">
and (r.id=#{id} or r.pid=#{id} or r.region_level=#{id})
</if>
<if test="name!=null and name!=''">
and o.name like concat('%',#{name},'%')
</if>
<if test="onlineStatus != null ">
and o.onlineStatus=#{onlineStatus}
</if>
<if test="startTime != null and endTime!=null">
and o.uploadtime BETWEEN #{startTime} and #{endTime}
</if>
<if test="start!=null">
limit #{start},10
</if>
</select>
<!-- 查询条数 -->
<select id="count" resultType="int" >
SELECT count(*) FROM onuinfo o , region r where o.areaid = r.id
<if test="id!=null and id!=0">
and (r.id=#{id} or r.pid=#{id} or r.region_level=#{id})
</if>
<if test="name!=null and name!=''">
and o.name like concat('%',#{name},'%')
</if>
<if test="onlineStatus != null ">
and o.onlineStatus=#{onlineStatus}
</if>
<if test="startTime != null and endTime!=null">
and o.uploadtime between #{startTime} and #{endTime}
</if>
</select>
</mapper>
3.service层
package com.onu.service.awave;
import java.beans.IntrospectionException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.aspectj.weaver.patterns.PerSingleton;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.mysql.jdbc.PreparedStatement.ParseInfo;
import com.onu.common.util.ExcelBean;
import com.onu.common.util.ExcelUtils;
import com.onu.common.util.JqGridReturn;
import com.onu.common.util.JqReturnJson;
import com.onu.dao.awave.OnuDetailedInfoMapper;
import com.onu.dao.awave.RegionMapper;
import com.onu.entity.awave.OnuDetailedInfo;
import com.onu.entity.awave.Region;
import freemarker.core.ParseException;
/**
*
* @author penghui.li
* @param 光交箱service
*
*/
@Service
public class OnuDetailedInfoService {
@Autowired
OnuDetailedInfoMapper onuDetailedInfoMapper;
@Autowired
RegionMapper regionMapper;
public List<OnuDetailedInfo> selectOnu(){
return onuDetailedInfoMapper.selectOnu();
}
/**
* 根据条件查询
* @param onuNumber
* @param deviceId
* @param name
* @return
*/
public List<OnuDetailedInfo> selectByCondition(Map<String, Object> map){
return onuDetailedInfoMapper.selectByCondition(map);
}
/**
* 分页查询
* @return
*/
public JqGridReturn selectOnuInfo(Map<String, Object> map){
List<OnuDetailedInfo> list=onuDetailedInfoMapper.selectOnuInfo(map);
JqGridReturn jq=new JqGridReturn();
jq.setRows(list);
jq.setTotal(count(map));
jq.setTotolPage((jq.getTotal()/jq.getPageSize()+1));
return jq;
}
/**
* 查询总条数
* @param map
* @return
*/
public int count(Map<String, Object> map){
return onuDetailedInfoMapper.count(map);
}
}
4.controller
package com.onu.controller.awave;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import com.onu.common.util.JqGridReturn;
import com.onu.common.util.JqReturnJson;
import com.onu.entity.awave.OnuDetailedInfo;
import com.onu.service.awave.OnuDetailedInfoService;
/**
* 光交箱管理controller
* @author penghui.li
*
*/
@Controller
@RequestMapping("/awave")
public class OnuDetailedInfoController {
@Resource
private OnuDetailedInfoService onuDetailedInfoService;
@RequestMapping("/selectOnu")
@ResponseBody
public Object selectOnu()
{
return onuDetailedInfoService.selectOnu();
}
/**
* 根据条件分页查询
* @param start
* @param id
* @param name
* @param onlineStatus
* @param startTime
* @param endTime
* @return
*/
@RequestMapping("/selectOnuInfo")
@ResponseBody
public JqGridReturn selectOnuInfo(Integer start,Integer id,String name,String onlineStatus,String startTime,String endTime)
{
Map<String, Object> map = new HashMap<String, Object>();
if(name!=null&&!name.equals("")){
try {
String sname= new String(name.getBytes("ISO8859-1"), "UTF-8");
map.put("name", sname);
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(onlineStatus!=null&&!onlineStatus.equals("")){
String onlineStatus1;
try {
onlineStatus1 = new String(onlineStatus.getBytes("ISO8859-1"), "UTF-8");
map.put("onlineStatus", onlineStatus1);
System.out.println(onlineStatus1);
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(id!=null&&!id.equals("0")){
map.put("id", id);
}
if(startTime!=null){
map.put("startTime", startTime);
System.out.println(startTime);
}
if(endTime!=null){
map.put("endTime", endTime);
System.out.println(endTime);
}
if (start!=null) {
if (start<=0) {
start=1;
}
map.put("start", (start-1)*10);
}else{
map.put("start", 0);
}
JqGridReturn jq=new JqGridReturn();
jq=onuDetailedInfoService.selectOnuInfo(map);
return jq;
}
}
帮助类JqGridReturn
package com.onu.common.util;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.builder.ToStringBuilder;
/**
* @ClassName: JqGridReturn
* @Description: TODO(JQuery Grid返回对象)
* @author awave
*/
@SuppressWarnings("rawtypes")
public class JqGridReturn {
/**
* 总共条数
*/
private int total = 0;
/**
* 当前页数
*/
private int p = 1;
/**
* 内容数据
*/
private List<?> rows = new ArrayList();
/**
* 脚表头数�?
*/
private List<?> footer = new ArrayList();
private int totolPage;
private int pageSize=10;
public JqGridReturn() {
}
public JqGridReturn(int total, int p, List<?> rows, List<?> footer, int totolPage, int pageSize) {
this.total = total;
this.p = p;
this.rows = rows;
this.footer = footer;
this.totolPage = totolPage;
this.pageSize = pageSize;
}
public int getTotal() {
return total;
}
public void setTotal(int total) {
this.total = total;
}
public int getP() {
return p;
}
public void setP(int p) {
this.p = p;
}
public List<?> getRows() {
return rows;
}
public void setRows(List<?> rows) {
this.rows = rows;
}
public List<?> getFooter() {
return footer;
}
public void setFooter(List<?> footer) {
this.footer = footer;
}
public int getTotolPage() {
return totolPage;
}
public void setTotolPage(int totolPage) {
this.totolPage = totolPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
}