ssm原生分页(oracle版本)

ssm分页(数据库使用的是oracle)

oracle

补充知识

用doc命令登录oracle数据库的命令

-- 第一步
sqlplus /nolog
-- 第二部
conn  username/password@ip地址:端口号/服务名
-- 说明:一般情况下,服务名我们都是用orcl,在虚拟机上安装oracle
-- 示例
conn shaoming/root@192.168.66.100/orcl

1.数据库脚本

create table demo(id number(10,0) , name varchar(50)); 

2.定义最后封装的vo对象,方便记忆,结构固定,名称叫PageInfo

public class PageInfo<T> implements Serializable {
	/**
		 * 
		 */
	private static final long serialVersionUID = 5932652753267663362L;
	private Integer pageIndex;// 当前页
	private Integer pageSize;// 每页记录数
	private Integer pageCount;// 总页数
	private Integer total;// 总记录数
	private Integer pageIndexRows;// 当前页有几条记录
	private List<T> tList;
	public Integer getPageIndex() {
		return pageIndex;
	}
	public void setPageIndex(Integer pageIndex) {
		this.pageIndex = pageIndex;
	}
	public Integer getPageSize() {
		return pageSize;
	}
	public void setPageSize(Integer pageSize) {
		this.pageSize = pageSize;
	}
	public Integer getPageCount() {
		return pageCount;
	}
	public void setPageCount(Integer pageCount) {
		this.pageCount = pageCount;
	}
	public Integer getTotal() {
		return total;
	}
	public void setTotal(Integer total) {
		this.total = total;
	}
	public Integer getPageIndexRows() {
		return pageIndexRows;
	}
	public void setPageIndexRows(Integer pageIndexRows) {
		this.pageIndexRows = pageIndexRows;
	}
	public List<T> gettList() {
		return tList;
	}
	public void settList(List<T> tList) {
		this.tList = tList;
	}
	@Override
	public String toString() {
		return "PageInfo [pageIndex=" + pageIndex + ", pageSize=" + pageSize + ", pageCount=" + pageCount + ", total="
				+ total + ", pageIndexRows=" + pageIndexRows + ", tList=" + tList + "]";
	}

}

3.dao层方法模板

/**
	 * 分页查询的demo
	 */
	/**
	 * 1.根据name模糊查询记录数
	 * @param name        sql查询条件,根据name进行模糊查询
	 * @retun             返回符合条件的记录数
	 * 最后赋值给pageInfo的total
	 */
	public int findByNameCount(@Param("name")String name);

	//2.传入pageSize每页的记录数,pageIndex当前页码值
	public List<Demo> findByNameByPageObject
	(@Param("name")String name,@Param("pageSize")Integer pageSize,
			@Param("pageIndex")Integer pageIndex);
    //模拟添加数据的方法
    @Insert("insert into demo values(#{id},#{name})")
    public int insertOne(Demo demo);

4.mapper.xml的模板

<select id="findByNameCount" resultType="int">
		select count(1) from demo
		<where>
			<if test="name!=null   and name!='' and  name!='all'">
				name like '%'||#{name}||'%'
			</if>
		</where>
	</select>

<select id="findByNameByPageObject"
		resultType="com.fcmap.ssm.domain.Demo">
		select * from(
		SELECT A.*, ROWNUM RN
		FROM (
		SELECT * FROM DEMO
		<where>
		<if test="name!=null and name!='' and name!='all'">
				and name like '%'||#{name}||'%'
			</if>
		</where>
		) A
		WHERE
		ROWNUM <![CDATA[ <= ]]>
		(#{pageIndex}*#{pageSize})
		)
		<where>
			RN <![CDATA[ >= ]]>
			((#{pageIndex}-1)*#{pageSize})+1
		</where>
	</select>

5.添加测试数据

@ContextConfiguration("classpath:/spring-mybatis.xml")
@RunWith(SpringJUnit4ClassRunner.class)
//这里可以声明一个事务管理 每个单元测试都进行事务回滚  无论成功与否  
@TransactionConfiguration(defaultRollback = true)
@Transactional
/**
	 * 模拟插入数据 95条数据
	 */
	@Rollback(false)//表示不会滚,成功添加数据
	@Test
	public void testInsertSomeTestInfo() {
		for (int i = 1; i < 96; i++) {
			demoDao.insertOne(new Demo(i, "demo的name" + i));
		}
	}

6.service进行分页处理的业务

@Service
public class DemoServiceImpl implements DemoService {
	@Autowired
	private DemoDao demoDao;

	@Override
	public PageInfo<Demo> findDemoPageInfo(String name, Integer pageSize, Integer pageIndex) {
		PageInfo<Demo> pageInfo = new PageInfo<Demo>();
		int totals = demoDao.findByNameCount(name);
		List<Demo> demoList = demoDao.findByNameByPageObject(name, pageSize, pageIndex);
		int pageCount = (totals + 1) / pageSize + 1;
		int pageIndexRows = pageSize;
		if (pageIndex < pageCount) {
			pageIndexRows = pageSize;
		} else if (pageIndex > pageCount) {
			pageIndexRows = 0;
		} else {
			pageIndexRows = totals % pageSize;
		}
		pageInfo.setTotal(totals);
		pageInfo.setPageSize(pageSize);
		pageInfo.setPageIndex(pageIndex);
		pageInfo.setPageIndexRows(pageIndexRows);
		pageInfo.setPageCount(pageCount);
		pageInfo.settList(demoList);
		return pageInfo;
	}

}

7.controller的书写

@Controller
@RequestMapping("/demo")
public class DemoController {
	@Autowired
	private DemoService demoService;

	@RequestMapping(value="/querypageinfo/{name}/{pageSize}/{pageIndex}",method=RequestMethod.GET)
	@ResponseBody
	public PageInfo<Demo> findPageInfo(@PathVariable("name") String name, @PathVariable("pageSize") Integer pageSize,
			@PathVariable("pageIndex") Integer pageIndex) {
		return demoService.findDemoPageInfo(name, pageSize, pageIndex);

	}
}

8.测试

(1)测试第一页,应该返回10条数据,pageIndexRows=pageSize=10

`http://localhost:8080/ssm_oracl/demo/querypageinfo/all/10/10

返回的json数据如下

{
pageIndex: 1,
pageSize: 10,
pageCount: 10,
total: 95,
pageIndexRows: 10,
tList: [
{
id: 1,
name: "demo的name1"
},
{
id: 2,
name: "demo的name2"
},
{
id: 3,
name: "demo的name3"
},
{
id: 4,
name: "demo的name4"
},
{
id: 5,
name: "demo的name5"
},
{
id: 6,
name: "demo的name6"
},
{
id: 7,
name: "demo的name7"
},
{
id: 8,
name: "demo的name8"
},
{
id: 9,
name: "demo的name9"
},
{
id: 10,
name: "demo的name10"
}
]
}

(2)测试第十页,应该返回5条数据,pageIndexRows!=pageSize , pageIndexRows=5

`http://localhost:8080/ssm_oracl/demo/querypageinfo/all/10/1

返回的json数据如下

{
pageIndex: 10,
pageSize: 10,
pageCount: 10,
total: 95,
pageIndexRows: 5,
tList: [
{
id: 91,
name: "demo的name91"
},
{
id: 92,
name: "demo的name92"
},
{
id: 93,
name: "demo的name93"
},
{
id: 94,
name: "demo的name94"
},
{
id: 95,
name: "demo的name95"
}
]
}