这个项目用的是oracle数据库,所以我们使用oracle的分页方法。
oracle的分页查询语法:
例如:查询第20到30条数据
普通的分页语句:
select page_table.* from ( select sysuser.*,rownum page_number from sysuser where rownum<=30 )page_table where page_table.page_number>20
查询结果:
但是上面的sql语句不好扩展,接下来写一个容易扩展的sql语句:
select page_table2.*
from (
select page_table1.*,rownum page_num from (
//
select * from sysuser
//可以在//...//之间添加任何查询语句
)page_table1 where rownum<=30
) page_table2
where page_table2.page_num>20
查询的结果是一样的。
但是上面的写法,普遍性更加好,不管你要查询什么东西要实现分页的话都是可以直接用的。可以直接在// //之间添加任何查询语句,查询得到结构都是可以直接按照你的要求进行
实现分页的。这就是统一的做法。
接下来就是根据上面的sql语句来写dao和Service和Action。
如下:
修改SysuserMapperCustom.xml中findSysuserList添加分页支持。
Oracle分页需要起始和结束下标,需要根据当前页码、每页显示数量、总条数(符合查询条件记录数)
使用PageQuery.java计算起始和结束的下标:
PageQuery.java代码:
package yycg.base.pojo.vo; import javax.swing.event.ListSelectionEvent; /** * 鍒嗛〉鏌ヨ鍙傛暟绫� * @author miaoruntu * */ public class PageQuery { public static final int PageQuery_pageSize_common = 30; // 褰撳墠椤电爜 private int PageQuery_currPage; // 鎬婚〉鏁� private int PageQuery_Psize; // 鎬昏褰曟暟 private int PageQuery_infoCount; // 姣忛〉鏄剧ず涓暟 private int PageQuery_pageSize = PageQuery_pageSize_common; // 寮�鍧愭爣 private int PageQuery_start = 0; // 缁撴潫鍧愭爣 private int PageQuery_end = 30; public static final String PageQuery_classname = "pagequery"; /** * 灏嗗垎甯冨弬鏁颁紶鍏ュ鐞嗭紝鏈�粓璁$畻鍑哄綋鍓嶉〉鐮丳ageQuery_currPage锛屽紑濮嬪潗鏍嘝ageQuery_star锛岀粨鏉熷潗鏍嘝ageQuery_end锛屾�椤垫暟PageQuery_Psize * @param infoCount 璁板綍鎬绘暟 * @param pageSize 姣忛〉鏄剧ず涓暟 * @param currPage 褰撳墠椤电爜 */ public void setPageParams(int infoCount, int pageSize, int currPage) { this.PageQuery_infoCount = infoCount; this.PageQuery_pageSize = pageSize; this.PageQuery_currPage = currPage; float Psize_l = infoCount / (float) (this.PageQuery_pageSize); if (PageQuery_currPage < 2) { PageQuery_currPage = 1; PageQuery_start = 0; } else if (PageQuery_currPage > Psize_l) { if(Psize_l==0){ PageQuery_currPage=1; }else{ PageQuery_currPage = (int) Math.ceil(Psize_l); } PageQuery_start = (PageQuery_currPage - 1) * this.PageQuery_pageSize; } else { PageQuery_start = (PageQuery_currPage - 1) * this.PageQuery_pageSize; } PageQuery_Psize = (int) Math.ceil(Psize_l); this.PageQuery_end = PageQuery_currPage*this.PageQuery_pageSize; } public int getPageQuery_currPage() { return PageQuery_currPage; } public void setPageQuery_currPage(int pageQuery_currPage) { PageQuery_currPage = pageQuery_currPage; } public int getPageQuery_Psize() { return PageQuery_Psize; } public void setPageQuery_Psize(int pageQuery_Psize) { PageQuery_Psize = pageQuery_Psize; } public int getPageQuery_infoCount() { return PageQuery_infoCount; } public void setPageQuery_infoCount(int pageQuery_infoCount) { PageQuery_infoCount = pageQuery_infoCount; } public int getPageQuery_pageSize() { return PageQuery_pageSize; } public void setPageQuery_pageSize(int pageQuery_pageSize) { PageQuery_pageSize = pageQuery_pageSize; } public int getPageQuery_start() { return PageQuery_start; } public void setPageQuery_start(int pageQuery_start) { PageQuery_start = pageQuery_start; } public PageQuery getPageQuery() { return this; } public int getPageQuery_end() { return PageQuery_end; } }
使用方法:
New构造pageQuery对象,调用setPageParams方法,传入总条数(所以写一个sql函数来查询总的条数“<select id="findSysuserCount" ”)、每页显示数量、当前页码,该对象中的属性PageQuery_start和PageQuery_end计算出了起始和结束的下标。
将PageQuery对象传入mybatis,将此对象作为包装对象的属性。
package yycg.base.pojo.vo; //包装类,用于页面向action传递参数,将数据传递到mybatis.在Mapper.xml中的sql语句中就是 //把这个类当做输入类的。 //把SysuserCustom包进去 public class SysuserQueryVo { private SysuserCustom sysuserCustom; private PageQuery pageQuery; public PageQuery getPageQuery() { return pageQuery; } public void setPageQuery(PageQuery pageQuery) { this.pageQuery = pageQuery; } public SysuserCustom getSysuserCustom() { return sysuserCustom; } public void setSysuserCustom(SysuserCustom sysuserCustom) { this.sysuserCustom = sysuserCustom; } }
Oracle分页sql:
分页mapper.xml
分页头和分页的尾。
<!-- 用户查询 --> <select id="findSysuserList" parameterType="yycg.base.pojo.vo.SysuserQueryVo" resultType="yycg.base.pojo.vo.SysuserCustom"> <!-- 分页头 --> <if test="pageQuery!=null"> select page_2.* from (select page_1.*, rownum page_num from ( </if> select * from ( select SYSUSER.id, SYSUSER.userid, SYSUSER.username, SYSUSER.groupid, sysuser.USERSTATE, SYSUSER.sysid, decode(SYSUSER.Groupid, '1', (select mc from userjd where id = sysuser.sysid), '2', (select mc from userjd where id = sysuser.sysid), '3', (select mc from useryy where id = sysuser.sysid), '4', (select mc from usergys where id = sysuser.sysid) ) sysmc from SYSUSER )sysuser <where> <include refid="query_sysuser_where" /> </where> <!-- 分页尾 --> <if test="pageQuery!=null"> ) page_1 <![CDATA[ where rownum <= ${pageQuery.PageQuery_end}) page_2 where page_2.page_num >= ${pageQuery.PageQuery_start} ]]> </if> </select>
总条数:符合条件记录数
在mapper.xml中创建一个select,返回总条数
<!-- 查询列表的总记录数 --> <select id="findSysuserCount" parameterType="yycg.base.pojo.vo.SysuserQueryVo" resultType="int"> select count(*) from ( select SYSUSER.id, SYSUSER.userid, SYSUSER.username, SYSUSER.groupid, sysuser.USERSTATE, SYSUSER.sysid, decode(SYSUSER.Groupid, '1', (select mc from userjd where id = sysuser.sysid), '2', (select mc from userjd where id = sysuser.sysid), '3', (select mc from useryy where id = sysuser.sysid), '4', (select mc from usergys where id = sysuser.sysid) ) sysmc from SYSUSER )sysuser <where> <include refid="query_sysuser_where" /> </where> </select>
上面的查询语句最后都是要写入到SysuserMapper.xml中来使用的。
SysuerMapperCustom.java代码:
package yycg.base.dao.mapper; import java.util.List; import yycg.base.pojo.vo.SysuserCustom; import yycg.base.pojo.vo.SysuserQueryVo; public interface SysuserMapperCustom { //查询用户列表 public List<SysuserCustom> findSysuserList(SysuserQueryVo sysuserQueryVo) throws Exception; public int findSysuserCount(SysuserQueryVo sysuserQueryVo) throws Exception; }
SysuserMapper.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.xml要遵循的规则是:***MapperCustom.xml,当然还要根据这个名字自定义接口 --> <mapper namespace="yycg.base.dao.mapper.SysuserMapperCustom" > <!-- 开始写用户的查询,这里有很多的规范,好好学 输入参数parameterType统一采用包装类,命名规则是 --> <!-- sql片段 --> <sql id="query_sysuser_where"> <if test="sysuserCustom!=null"> <!-- 用户的id --> <if test="sysuserCustom.userid!=null and sysuserCustom.userid!=''"> and sysuser.userid = #{sysuserCustom.userid} </if> <!-- 用户的名字--> <if test="sysuserCustom.username!=null and sysuserCustom.username!=''"> and sysuser.username = #{sysuserCustom.username} </if> <!-- 用户的工作单位--> <if test="sysuserCustom.sysmc!=null and sysuserCustom.sysmc!=''"> and sysuser.sysmc like '%${sysuserCustom.sysmc}%' </if> <!-- 用户的类型 --> <if test="sysuserCustom.groupid!=null and sysuserCustom.groupid!=''"> and sysuser.groupid =#{sysuserCustom.groupid} </if> </if> </sql> <select id="findSysuserList" parameterType="yycg.base.pojo.vo.SysuserQueryVo" resultType="yycg.base.pojo.vo.SysuserCustom"> <if test="pageQuery!=null"> select pagae_table2.* from(select page_table1.*,rownum page_number from( </if> select * from(select SYSUSER.ID,SYSUSER.USERID,SYSUSER.USERNAME,SYSUSER.GROUPID,SYSUSER.SYSID,SYSUSER.USERSTATE,DECODE(SYSUSER.Groupid, '1', (select mc from userjd where id=sysuser.sysid), '2', (select mc from userjd where id=sysuser.sysid), '3', (select mc from useryy where id=sysuser.sysid), '4', (select mc from usergys where id=sysuser.sysid) )sysmc from SYSUSER )sysuser <where> <include refid="query_sysuser_where"></include> </where> <if test="pageQuery!=null"> ) page_table1 <![CDATA[ where rownum<=${pageQuery.PageQuery_end}) pagae_table2 where page_number>${pageQuery.PageQuery_start} ]]> </if> </select> <!-- 在mapper.xml中创建一个select ,返回总条数 --> <select id="findSysuserCount" parameterType="yycg.base.pojo.vo.SysuserQueryVo" resultType="int"> select count(*) from(select SYSUSER.ID,SYSUSER.USERID,SYSUSER.USERNAME,SYSUSER.GROUPID,SYSUSER.SYSID,SYSUSER.USERSTATE,DECODE(SYSUSER.Groupid, '1', (select mc from userjd where id=sysuser.sysid), '2', (select mc from userjd where id=sysuser.sysid), '3', (select mc from useryy where id=sysuser.sysid), '4', (select mc from usergys where id=sysuser.sysid) )sysmc from SYSUSER )sysuser <where> <include refid="query_sysuser_where"></include> </where> </select> </mapper>
1.1.1 service接口
service实现类:
package yycg.bae.service.impl; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import yycg.base.dao.mapper.SysuserMapperCustom; import yycg.base.pojo.vo.SysuserCustom; import yycg.base.pojo.vo.SysuserQueryVo; import yycg.base.service.UserService; public class userServiceimpl implements UserService{ @Autowired SysuserMapperCustom sysuserMapperCustom; //查询数据库中的记录。 @Override public List<SysuserCustom> findSysuserCustom(SysuserQueryVo sysuserQueryVo) throws Exception { return sysuserMapperCustom.findSysuserList(sysuserQueryVo); } //查询数据库中有几条记录的 @Override public int findSysuserCount(SysuserQueryVo sysuserQueryVo) throws Exception { return sysuserMapperCustom.findSysuserCount(sysuserQueryVo); } }
1.1.2 action
调用service执行分页查询,准备PageQuery对象数据(包括起始和结束下标)。
先调用service查询出总数。
Datagrid加载数据列表时,固定向action方法传两个分页参数:
当前页码和每页显示个数
Action分页查询代码:
package yycg.base.action; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; import yycg.base.pojo.vo.PageQuery; import yycg.base.pojo.vo.SysuserCustom; import yycg.base.pojo.vo.SysuserQueryVo; import yycg.base.process.result.DataGridResultInfo; import yycg.base.service.UserService; /* * 用户点击网页上的某个链接来到这里,然后执行这里的方法,从数据库中查到我想要的数据后返回到 *Model中,然后model里面的数据会传给显示的页面,当跳转到显示页面后,把model里面的数据显示出来 *完成任务。 */ @Controller //根访问目录 @RequestMapping("/user") public class UserAction { @Autowired private UserService userService; //子访问目录.用户查询页面 @RequestMapping("/queryuser") public String queryUser(Model model)throws Exception{ return "/base/user/queryuser"; } //用户查询页面的结果集 //最终DataGridResultInfo通过@ResponseBody将java对象转换成json对象。 //这里的形参包装类会一直从Action传到Service然后传到Mapper @RequestMapping("/queryuser_result") public @ResponseBody DataGridResultInfo queryUser_result(SysuserQueryVo sysuserQueryVo,int page,int rows)throws Exception { //这些都是根据EASYUI的规则来的 DataGridResultInfo dataGridResultInfo=new DataGridResultInfo(); int total=userService.findSysuserCount(sysuserQueryVo); //查询总的条数 PageQuery pageQuery=new PageQuery(); pageQuery.setPageParams(total, rows, page);//要使用PageQuery ,就要传入这些参数。 sysuserQueryVo.setPageQuery(pageQuery);//把PageQuery传入到包装类中。 //把查到的信息填充到DataGridResultInfo中,然后把DataGridResult转换成json。当外面的链接 //访问这个函数时,返回json数据。 List<SysuserCustom> list=userService.findSysuserCustom(sysuserQueryVo); dataGridResultInfo.setRows(list); dataGridResultInfo.setTotal(total); return dataGridResultInfo; } }
运行结果:OK.