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类型的就可以，目前还没发现什么异常，或许我研究的较为浅显的原因；第三个参数则是需要分页的数据。