mybatis分页查询

分页查询

查询总数

Dao
//查询总条数
//
// select count(*) from student where ??动态sql #{id} #{name}—??
//

 int selectStudentsByCount(@Param("student")Student student);

分页查询

Dao
//当前页的数据:pageSize,pageIndex
//
// select * from student where ??动态sql #{student.id} #{student.name} ?? limit ?,?
//
//Student student, 查询条件
//int pageIndex, 页索引，当前页
//int pageSize 页大小:每页显示多少条数据
//limit (pageIndex-1)*pageSize,pageSize

List<Student> selectStudentsByPage(@Param("student") Student student,  @Param("pageIndex") int  pageIndex, @Param("pageSize")int pageSize);

sql不支持直接计算,需要在测试方法中算好

1.映射文件

<select id="selectStudentsByPage" resultType="Student">
		select * from	student
		<include refid="pagewhere" />
		<!--error-->
		<!-- limit (#{pageIndex}-1)*#{pageSize},#{pageSize} -->
		limit #{pageIndex},#{pageSize}
	</select>

	<sql id="pagewhere">
		<where>
			<if test="student.name!=null and student.name!=''">
				and name like '%' #{student.name} '%'
			</if>
			<if test="student.age>0">
				and age>#{student.age}
			</if>
		</where>
	</sql>
	
	<select id="selectStudentsByCount" resultType="int">
		select count(*) from	student
		<include refid="pagewhere" />
	</select>

2.测试方法

@Test
	public void testselectStudentsByPage() {
		Student student=new Student();
		student.setAge(10);
		//	条件,pageIndex，pageSize
		//System.out.println(studentDao.selectStudentsByPage(student,1,10));
		//		条件,(pageIndex-1)*pageSize，pageSize
		System.out.println(studentDao.selectStudentsByPage(student,(1-1)*10,10));
		System.out.println(studentDao.selectStudentsByCount(student));
	}

bind 标签支持计算

1.映射文件

<select id="selectStudentsByPage" resultType="Student">
		<bind name="mydata" value="(pageIndex-1)*pageSize" />
		select * from	student
		<include refid="pagewhere" />
		<!-- limit (#{pageIndex}-1)*#{pageSize},#{pageSize} -->
		limit #{mydata},#{pageSize}
	</select>

	<sql id="pagewhere">
		<where>
			<if test="student.name!=null and student.name!=''">
				and name like '%' #{student.name} '%'
			</if>
			<if test="student.age>0">
				and age>#{student.age}
			</if>
		</where>
	</sql>
	
	<select id="selectStudentsByCount" resultType="int">
		select count(*) from	student
		<include refid="pagewhere" />
	</select>

2.测试方法

@Test
	public void testselectStudentsByPage2() {
		Student student=new Student();
		student.setAge(10);
		System.out.println(studentDao.selectStudentsByPage(student,1,10));
		System.out.println(studentDao.selectStudentsByCount(student));
	}