分页 模糊查询「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。

一、所有的web项目都会用到分页显示和模糊查询，对于有些人不知道该怎么写

二、今天我用springMVC 和 myBatis 写的分页和模糊分享给大家，不喜勿喷

三、数据库是mysql

四、其实写分页就是新建一个分页的类，定义 页码  每页数量  共几页  当前页数  总数量

五、判断多少页，获取总数量除以每页显示的数量，有余数+1页

六、sql语句就是用 limit  显示的数量，把从多少条开始，到显示几条传到sql语句上

七、目前每页的显示的数量是定义好的，等过两天有时间把自己可以选择显示页数的代码写出来

八、源码下载地址  点击打开链接

用户实体类

public class User {
	
	private int id;
	private String name;
	private String sex;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
}

分页的实体类

public class Page {
	/**
	 * 页码
	 */
	private int pageNum;
	/**
	 * 每页显示的行数
	 */
	private int pageRows;
	/**
	 * 总行数
	 */
	private int totalRows;
	/**
	 * 总页数
	 */
	private int totalPages;
	/**
	 * 起始行号
	 */
	private int beginRownum;
	/**
	 * 结束行号
	 */
	private int endRownum;

	public Page(int pageNum, int pageRows, int totalRows) {
		this.pageNum = pageNum;
		this.pageRows = pageRows;
		this.totalRows = totalRows;
		// 计算总页数:总行数%每页行数==0?总行数/每页行数:总行数/每页行数+1
		this.totalPages = totalRows % pageRows == 0 ? totalRows / pageRows : (totalRows / pageRows) + 1;
		beginRownum = (pageNum - 1) * pageRows;
		endRownum = pageNum * pageRows;
	}

	public int getPageNum() {
		return pageNum;
	}

	public void setPageNum(int pageNum) {
		this.pageNum = pageNum;
	}

	public int getPageRows() {
		return pageRows;
	}

	public void setPageRows(int pageRows) {
		this.pageRows = pageRows;
	}

	public int getTotalRows() {
		return totalRows;
	}

	public void setTotalRows(int totalRows) {
		this.totalRows = totalRows;
	}

	public int getTotalPages() {
		return totalPages;
	}

	public void setTotalPages(int totalPages) {
		this.totalPages = totalPages;
	}

	public int getBeginRownum() {
		return beginRownum;
	}

	public void setBeginRownum(int beginRownum) {
		this.beginRownum = beginRownum;
	}

	public int getEndRownum() {
		return endRownum;
	}

	public void setEndRownum(int endRownum) {
		this.endRownum = endRownum;
	}
	
}

mapper  和  映射文件

public interface UserMapper {
	
	public List<User> selectAll(Map<String,Object> map);
	
	//查询总行数
	public int selTotalRows(Map<String,Object> map);
}

<mapper namespace="com.mapper.UserMapper">

	<select id="selectAll" resultType="com.entity.User">
		select * from user 
		<where>
			<if test="name != null and name != ''">
				and name like #{name}
			</if>
			<if test="sex != null and sex != ''">
				and sex = #{sex}
			</if>
		</where>
		limit #{page.beginRownum},5
	</select>
	
	<!-- 查询总行数 -->
	<select id="selTotalRows" resultType="int">
		select count(id) from user
		<where>
			<if test="name != null and name != ''">
				and name like #{name}
			</if>
			<if test="sex != null and sex != ''">
				and sex = #{sex}
			</if>
		</where>
	</select>

</mapper>

控制层

public class UserController {

		@Autowired
		private UserService userService;

		@RequestMapping("/showAll.action")
		public String selAll(HttpServletRequest request){
			Map<String,Object> map = new HashMap<String, Object>();	
			map.put("name", null);
			map.put("sex", null);
			Page page = new Page(1,5,userService.selTotalRows(map));
			map.put("page", page);
			List<User> userList = userService.selectAll(map);
			request.setAttribute("userList", userList);
			request.setAttribute("page", page);
			return "two.jsp";
		}
		
		
		//查询所有用户（带模糊查询、分页）
		@RequestMapping("/showPage.action")
		public String selAllLikePage(int pageNum,String name,String sex,HttpServletRequest request){
			Map<String,Object> map = new HashMap<String, Object>();
			if("".equals(name)){
				map.put("name", null);
			}else{
				map.put("name", "%"+name+"%");
			}
			if("".equals(sex)){
				map.put("sex", null);
			}else{
				map.put("sex", sex);
			}
			Page page = new Page(pageNum,5,userService.selTotalRows(map));
			map.put("page", page);
			List<User> userList = userService.selectAll(map);
			request.setAttribute("userList",userList);
			request.setAttribute("page", page);
			request.setAttribute("name", name);
			request.setAttribute("sex", sex);
			return "two.jsp";
		}
}

jsp页面

<body>
	<div>
		<form action="showPage.action?pageNum=1" method="post">
			<span>
				姓名：<input name="name" value="${name }" />   
				性别：<input name="sex" value="${sex }" />  
				<input type="submit" value="查询" /> 
			</span>
		</form>
	</div>
	<br><br>
	<div>
		<table width="500" border="1" cellspacing="0" cellpadding="0">
			<tr>
				<th width="100px">序号</th>
				<th width="200px">姓名</th>
				<th width="200px">性别</th>
			</tr>
			<c:forEach items="${userList }" var="user">
				<tr>
					<td>${user.id }</td>
					<td>${user.name }</td>
					<td>${user.sex }</td>
				</tr>
			</c:forEach>
			<tr>
				<td class="scott" colspan="20" style="text-align: center;">
					 <c:if test="${page.pageNum > 1 }">
                       	<a href="showPage.action?name=${name }&sex=${sex }&pageNum=1">首页</a>
						<a href="showPage.action?name=${name }&sex=${sex }&pageNum=${page.pageNum - 1 }">上一页</a>
					</c:if>
					<c:forEach begin="1" end="${page.totalPages}" step="1" var="num">
						<c:if test="${page.pageNum == num }">${num }</c:if>
						<c:if test="${page.pageNum != num }">
							<a href="showPage.action?name=${name }&sex${sex }&pageNum=${num }">${num }</a>
						</c:if>
					</c:forEach>
					<c:if test="${page.pageNum < page.totalPages }">
						<a href="showPage.action?name=${name }&sex=${sex }&pageNum=${page.pageNum + 1 }">下一页</a>
						<a href="showPage.action?name=${name }&sex=${sex }&pageNum=${page.totalPages }">尾页</a>
					</c:if>
					总${page.pageNum}/${page.totalPages }页
				</td>
			</tr>
		</table>
		
	</div>
</body>