mybatis物理分页的实现(转)
转：http://dove19900520.iyunv.com/blog/1724414
最近两周一直在学习mybatis，昨天经理让我将mybatis的物理分页整理一下，打成一个jar包供以后调用。结果昨天我整了一天，也没整出个1、2、3来。现在终于写出来了，先记录下来再说，哈哈。
下面是所有的代码：
Java代码
[*]packagecom.xxyd.mybatis.pojo;
[*]
[*]importjava.io.Serializable;
[*]/**
[*]*实体类
[*]*@authordove
[*]*
[*]*/
[*]publicclassTestEntityimplementsSerializable{
[*]
[*]privatestaticfinallongserialVersionUID=-5849200248418883686L;
[*]privateintid;
[*]privateStringname;
[*]privateintno;
[*]privateintsex;
[*]privateintage;
[*]privateStringcount;
[*]privateStringschool;
[*]privateintweight;
[*]privateintheight;
[*]privateStringhabbit;
[*]privateStringmemo;
[*]publicintgetId(){
[*]returnid;
[*]}
[*]publicvoidsetId(intid){
[*]this.id=id;
[*]}
[*]publicStringgetName(){
[*]returnname;
[*]}
[*]publicvoidsetName(Stringname){
[*]this.name=name;
[*]}
[*]publicintgetNo(){
[*]returnno;
[*]}
[*]publicvoidsetNo(intno){
[*]this.no=no;
[*]}
[*]publicintgetSex(){
[*]returnsex;
[*]}
[*]publicvoidsetSex(intsex){
[*]this.sex=sex;
[*]}
[*]publicintgetAge(){
[*]returnage;
[*]}
[*]publicvoidsetAge(intage){
[*]this.age=age;
[*]}
[*]publicStringgetCount(){
[*]returncount;
[*]}
[*]publicvoidsetCount(Stringcount){
[*]this.count=count;
[*]}
[*]publicStringgetSchool(){
[*]returnschool;
[*]}
[*]publicvoidsetSchool(Stringschool){
[*]this.school=school;
[*]}
[*]publicintgetWeight(){
[*]returnweight;
[*]}
[*]publicvoidsetWeight(intweight){
[*]this.weight=weight;
[*]}
[*]publicintgetHeight(){
[*]returnheight;
[*]}
[*]publicvoidsetHeight(intheight){
[*]this.height=height;
[*]}
[*]publicStringgetHabbit(){
[*]returnhabbit;
[*]}
[*]publicvoidsetHabbit(Stringhabbit){
[*]this.habbit=habbit;
[*]}
[*]publicStringgetMemo(){
[*]returnmemo;
[*]}
[*]publicvoidsetMemo(Stringmemo){
[*]this.memo=memo;
[*]}
[*]@Override
[*]publicStringtoString(){
[*]return"TestEntity[id="+id+",name="+name+",no="+no
[*]+",sex="+sex+",age="+age+",count="+count
[*]+",school="+school+",weight="+weight+",height="
[*]+height+",habbit="+habbit+",memo="+memo+"]";
[*]}
[*]
[*]}
2、DAO接口
Java代码
[*]packagecom.xxyd.mybatis.dao;
[*]
[*]importjava.util.List;
[*]
[*]importorg.apache.ibatis.annotations.Param;
[*]
[*]importcom.xxyd.mybatis.pojo.TestEntity;
[*]/**
[*]*dao接口
[*]*@authordove
[*]*
[*]*/
[*]publicinterfaceTestMapper{
[*]publicvoidcreateTestEntity(TestEntityentity);
[*]publicList<TestEntity>getTestEntityByPager(@Param("pageNo")intpageNo,@Param("pageSize")intpageSize);
[*]publicList<TestEntity>getListTestEntity();
[*]publicintgetTotalCount(@Param("pageNo")intpageNo,@Param("pageSize")intpageSize);
[*]publicvoidupdateTestEntity(TestEntityentity);
[*]publicvoiddeleteTestEntityById(@Param("id")intid);
[*]}
3、映射文件TestMapper.xml
Xml代码
[*]<?xmlversion="1.0"encoding="UTF-8"?>
[*]<!DOCTYPEmapperSYSTEM"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
[*]<mappernamespace="com.xxyd.mybatis.dao.TestMapper">
[*]
[*]<resultMaptype="com.xxyd.mybatis.pojo.TestEntity"id="test_test">
[*]<idproperty="id"column="id"javaType="int"jdbcType="INTEGER"/>
[*]<resultproperty="name"column="name"javaType="String"jdbcType="VARCHAR"/>
[*]<resultproperty="no"column="no"javaType="int"jdbcType="INTEGER"/>
[*]<resultproperty="sex"column="sex"javaType="int"jdbcType="INTEGER"/>
[*]<resultproperty="age"column="age"javaType="int"jdbcType="INTEGER"/>
[*]<resultproperty="count"column="count"javaType="String"jdbcType="VARCHAR"/>
[*]<resultproperty="school"column="school"javaType="String"jdbcType="VARCHAR"/>
[*]<resultproperty="weight"column="weight"javaType="int"jdbcType="INTEGER"/>
[*]<resultproperty="height"column="height"javaType="int"jdbcType="INTEGER"/>
[*]<resultproperty="habbit"column="habbit"javaType="String"jdbcType="VARCHAR"/>
[*]<resultproperty="memo"column="memo"javaType="String"jdbcType="VARCHAR"/>
[*]</resultMap>
[*]
[*]<insertid="createTestEntity"useGeneratedKeys="true"parameterType="com.xxyd.mybatis.pojo.TestEntity">
[*]insertintotest_test(name,no,sex,age,count,school,weight,height,habbit,memo)
[*]values(#{name},#{no},#{sex},#{age},#{count},#{school},#{weight},#{height},#{habbit},#{memo});
[*]</insert>
[*]
[*]<selectid="getTestEntityByPager"resultMap="test_test">
[*]selectid,name,no,sex,age,count,school,weight,height,habbit,memo
[*]fromtest_test
[*]limit#{pageNo,jdbcType=INTEGER},#{pageSize,jdbcType=INTEGER}
[*]</select>
[*]
[*]<selectid="getListTestEntity"resultMap="test_test">
[*]selectid,name,no,sex,age,count,school,weight,height,habbit,memo
[*]fromtest_test
[*]</select>
[*]
[*]<selectid="getTotalCount"resultType="int">
[*]selectcount(sub.id)from
[*](selecttest.idasidfromtest_testtest
[*]limit#{pageNo,jdbcType=INTEGER},#{pageSize,jdbcType=INTEGER})assub
[*]</select>
[*]
[*]<updateid="updateTestEntity"parameterType="com.xxyd.mybatis.pojo.TestEntity">
[*]updatetest_test
[*]<set>
[*]<iftest="name!=nullandname!=''">
[*]name=#{name,jdbcType=VARCHAR},
[*]</if>
[*]<iftest="no!=nullandno!=''">
[*]no=#{no,jdbcType=INTEGER},
[*]</if>
[*]<iftest="sex!=nullandsex!=''">
[*]sex=#{sex,jdbcType=INTEGER},
[*]</if>
[*]<iftest="age!=nullandage!=''">
[*]age=#{age,jdbcType=INTEGER},
[*]</if>
[*]<iftest="count!=nullandcount!=''">
[*]count=#{count,jdbcType=VARCHAR},
[*]</if>
[*]<iftest="school!=nullandschool!=''">
[*]school=#{school,jdbcType=VARCHAR},
[*]</if>
[*]<iftest="weight!=nullandweight!=''">
[*]weight=#{weight,jdbcType=INTEGER},
[*]</if>
[*]<iftest="height!=nullandheight!=''">
[*]height=#{height,jdbcType=INTEGER},
[*]</if>
[*]<iftest="habbit!=nullandhabbit!=''">
[*]habbit=#{habbit,jdbcType=VARCHAR},
[*]</if>
[*]<iftest="memo!=nullandmemo!=''">
[*]memo=#{memo,jdbcType=VARCHAR},
[*]</if>
[*]</set>
[*]whereid=#{id,jdbcType=INTEGER}
[*]</update>
[*]
[*]<deleteid="deleteTestEntityById"parameterType="int">
[*]deletefromtest_testwhereid=#{id}
[*]</delete>
[*]
[*]</mapper>
4、mybatis主配置文件mybatis-config.xml
Xml代码
[*]<?xmlversion="1.0"encoding="UTF-8"?>
[*]<!DOCTYPEconfigurationPUBLIC"-//mybatis.org//DTDConfig3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
[*]<configuration>
[*]<!--配置数据库方言目前只有mysql和oracle两种-->
[*]<properties>
[*]<propertyname="dialect"value="mysql"/>
[*]</properties>
[*]
[*]<!--配置mysql分页拦截器start-->
[*]<!--com.xxyd.mybatis.interceptor.PaginationInterceptor来自于jar包mybatis-pager-1.0.0.jar-->
[*]<plugins>
[*]<plugininterceptor="com.xxyd.mybatis.interceptor.PaginationInterceptor"></plugin>
[*]</plugins>
[*]
[*]<!--映射文件-->
[*]<mappers>
[*]<mapperresource="com/xxyd/mybatis/mapper/TestMapper.xml"/>
[*]</mappers>
[*]</configuration>
[*]
6、jdbc.properties文件
Xml代码
[*]driverClass=com.mysql.jdbc.Driver
[*]url=jdbc\:mysql\://127.0.0.1\:3306/student_manager
[*]username=root
[*]password=123456
7、spring配置文件部分配置
Xml代码
[*]<?xmlversion="1.0"encoding="UTF-8"?>
[*]<beansxmlns="http://www.springframework.org/schema/beans"
[*]xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:aop="http://www.springframework.org/schema/aop"
[*]xmlns:p="http://www.springframework.org/schema/p"xmlns:tx="http://www.springframework.org/schema/tx"
[*]xmlns:context="http://www.springframework.org/schema/context"
[*]xsi:schemaLocation="
[*]http://www.springframework.org/schema/beans
[*]http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
[*]http://www.springframework.org/schema/aop
[*]http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
[*]http://www.springframework.org/schema/tx
[*]http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
[*]http://www.springframework.org/schema/context
[*]http://www.springframework.org/schema/context/spring-context.xsd">
[*]
[*]<!--Properties文件读取配置，base的properties-->
[*]<context:property-placeholderlocation="classpath:jdbc.properties"/>
[*]
[*]<beanid="dataSource"
[*]class="org.springframework.jdbc.datasource.DriverManagerDataSource">
[*]<propertyname="driverClassName"value="${driverClass}"/>
[*]<propertyname="url"value="${url}"/>
[*]<propertyname="username"value="${username}"></property>
[*]<propertyname="password"value="${password}"></property>
[*]</bean>
[*]
[*]<beanid="transactionManager"
[*]class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
[*]<propertyname="dataSource"ref="dataSource"/>
[*]</bean>
[*]
[*]<beanid="sqlSessionFactory"class="org.mybatis.spring.SqlSessionFactoryBean">
[*]<propertyname="configLocation"value="classpath:mybatis-config.xml"/>
[*]<propertyname="dataSource"ref="dataSource"/>
[*]</bean>
[*]
[*]<!--测试用例start-->
[*]<beanid="TestMapperTest"class="com.xxyd.mybatis.test.TestMapperTest">
[*]<propertyname="sessionFactory"ref="sqlSessionFactory"/>
[*]</bean>
[*]<!--测试用例end-->
[*]
[*]
[*]<!--mapperbean-->
[*]<beanid="TestMapper"class="org.mybatis.spring.mapper.MapperFactoryBean">
[*]<propertyname="mapperInterface"value="com.xxyd.mybatis.dao.TestMapper"/>
[*]<propertyname="sqlSessionFactory"ref="sqlSessionFactory"/>
[*]</bean>
[*]
[*]</beans>
7、最后，测试类：
Java代码
[*]packagecom.xxyd.mybatis.test;
[*]
[*]importjava.util.List;
[*]
[*]importorg.apache.ibatis.session.RowBounds;
[*]importorg.apache.ibatis.session.SqlSession;
[*]importorg.apache.ibatis.session.SqlSessionFactory;
[*]importorg.apache.ibatis.session.SqlSessionFactoryBuilder;
[*]importorg.junit.Test;
[*]importorg.springframework.context.support.ClassPathXmlApplicationContext;
[*]
[*]importcom.xxyd.mybatis.pojo.TestEntity;
[*]
[*]publicclassTestMapperTest{
[*]
[*]//privateSqlSessionFactorysessionFactory;
[*]privatestaticSqlSessionFactoryBuilderbuilder;
[*]privatestaticSqlSessionFactorysessionFactory;
[*]static{
[*]builder=newSqlSessionFactoryBuilder();
[*]sessionFactory=builder.build(Thread.currentThread()
[*].getContextClassLoader()
[*].getResourceAsStream("mybatis-config.xml"));
[*]}
[*]/**
[*]*@paramargs
[*]*/
[*]publicstaticvoidmain(String[]args){
[*]ClassPathXmlApplicationContextctx=newClassPathXmlApplicationContext("applicationContext.xml");
[*]TestMapperTestTestMapperTest=(TestMapperTest)ctx.getBean("TestMapperTest");
[*]TestMapperTest.getList();
[*]
[*]}
[*]
[*]@Test
[*]publicvoidgetList(){
[*]SqlSessionsqlSession=sessionFactory.openSession();
[*]//TestEntityentity=newTestEntity();
[*]List<TestEntity>list=sqlSession.selectList("com.xxyd.mybatis.dao.TestMapper.getListTestEntity",TestEntity.class,newRowBounds(0,200));
[*]sqlSession.commit();
[*]sqlSession.close();
[*]for(TestEntitytestEntity:list){
[*]System.out.println(testEntity.toString());
[*]}
[*]}
[*]
[*]//publicSqlSessionFactorygetSessionFactory(){
[*]//returnsessionFactory;
[*]//}
[*]//publicvoidsetSessionFactory(SqlSessionFactorysessionFactory){
[*]//this.sessionFactory=sessionFactory;
[*]//}
[*]}
能够成功运行的前提是，你已经将mybatis+spring的运行环境搭建好了，并且将mybatis-pager-1.0.0.jar也放置到classpath下。之后，直接运行测试类就可以了。
注意1：mybatis-pager-1.0.0.jar该包就是浪费我昨天一整天也没整出来的分页jar包，里面目前只适用于mysql和oracle两种数据库使用，其他的数据库还在研究中。
注意2：List<TestEntity> list = sqlSession.selectList("com.xxyd.mybatis.dao.TestMapper.getListTestEntity", TestEntity.class, new RowBounds(0, 200));
第一个参数标准写法是TestMapper.xml的名称空间+select的对应id（建议sql映射文件中的sql语句结尾不要有分号）；第二个参数是：实体类，当然也可以使字符串，只有是Object类型的就可以，目前还没发现什么异常，或许我研究的较为浅显的原因；第三个参数则是需要分页的数据。
页:
[1]