mybatis物理分页的实现(转)

转：http://dove19900520.iteye.com/blog/1724414

最近两周一直在学习mybatis，昨天经理让我将mybatis的物理分页整理一下，打成一个jar包供以后调用。结果昨天我整了一天，也没整出个1、2、3来。现在终于写出来了，先记录下来再说，哈哈。

下面是所有的代码：

package com.xxyd.mybatis.pojo;

import java.io.Serializable;
/**
 * 实体类
 * @author dove
 *
 */
public class TestEntity implements Serializable{

    private static final long serialVersionUID = -5849200248418883686L;
    private int id ;
    private String name;
    private int no;
    private int sex;
    private int age;
    private String count;
    private String school;
    private int weight;
    private int height;
    private String habbit;
    private String memo;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getNo() {
        return no;
    }
    public void setNo(int no) {
        this.no = no;
    }
    public int getSex() {
        return sex;
    }
    public void setSex(int sex) {
        this.sex = sex;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getCount() {
        return count;
    }
    public void setCount(String count) {
        this.count = count;
    }
    public String getSchool() {
        return school;
    }
    public void setSchool(String school) {
        this.school = school;
    }
    public int getWeight() {
        return weight;
    }
    public void setWeight(int weight) {
        this.weight = weight;
    }
    public int getHeight() {
        return height;
    }
    public void setHeight(int height) {
        this.height = height;
    }
    public String getHabbit() {
        return habbit;
    }
    public void setHabbit(String habbit) {
        this.habbit = habbit;
    }
    public String getMemo() {
        return memo;
    }
    public void setMemo(String memo) {
        this.memo = memo;
    }
    @Override
    public String toString() {
        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接口

package com.xxyd.mybatis.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.xxyd.mybatis.pojo.TestEntity;
/**
 * dao接口
 * @author dove
 *
 */
public interface TestMapper {
    public void createTestEntity(TestEntity entity);
    public List<TestEntity> getTestEntityByPager(@Param("pageNo")int pageNo,@Param("pageSize") int pageSize);
    public List<TestEntity> getListTestEntity();
    public int getTotalCount(@Param("pageNo")int pageNo,@Param("pageSize") int pageSize);
    public void updateTestEntity(TestEntity entity);
    public void deleteTestEntityById(@Param("id") int id);
}

3、映射文件TestMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper SYSTEM "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xxyd.mybatis.dao.TestMapper">

    <resultMap type="com.xxyd.mybatis.pojo.TestEntity" id="test_test">
        <id property="id" column="id" javaType="int" jdbcType="INTEGER"/>
        <result property="name" column="name" javaType="String" jdbcType="VARCHAR"/>
        <result property="no" column="no" javaType="int" jdbcType="INTEGER"/>
        <result property="sex" column="sex" javaType="int" jdbcType="INTEGER"/>
        <result property="age" column="age" javaType="int" jdbcType="INTEGER"/>
        <result property="count" column="count" javaType="String" jdbcType="VARCHAR"/>
        <result property="school" column="school" javaType="String" jdbcType="VARCHAR"/>
        <result property="weight" column="weight" javaType="int" jdbcType="INTEGER"/>
        <result property="height" column="height" javaType="int" jdbcType="INTEGER"/>
        <result property="habbit" column="habbit" javaType="String" jdbcType="VARCHAR"/>
        <result property="memo" column="memo" javaType="String" jdbcType="VARCHAR"/>
    </resultMap>

    <insert id="createTestEntity" useGeneratedKeys="true" parameterType="com.xxyd.mybatis.pojo.TestEntity">
        insert into test_test(name,no,sex, age,count,school,weight,height,habbit,memo)
        values(#{name},#{no},#{sex},#{age},#{count},#{school},#{weight},#{height},#{habbit},#{memo});
    </insert>

    <select id="getTestEntityByPager" resultMap="test_test">
        select id,name,no,sex, age,count,school,weight,height,habbit,memo
            from test_test
            limit #{pageNo, jdbcType=INTEGER} , #{pageSize, jdbcType=INTEGER}
    </select>

    <select id="getListTestEntity" resultMap="test_test">
        select id,name,no,sex, age,count,school,weight,height,habbit,memo
            from test_test
    </select>

    <select id="getTotalCount" resultType="int">
        select count(sub.id) from
            (select test.id as id from test_test test
                limit #{pageNo, jdbcType=INTEGER} , #{pageSize, jdbcType=INTEGER}) as sub
    </select>

    <update id="updateTestEntity" parameterType="com.xxyd.mybatis.pojo.TestEntity">
        update test_test
        <set>
            <if test="name != null and name != ''">
                name = #{name , jdbcType=VARCHAR},
            </if>
            <if test="no != null and no != ''">
                no = #{no , jdbcType=INTEGER},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex , jdbcType=INTEGER},
            </if>
            <if test="age != null and age != ''">
                age = #{age , jdbcType=INTEGER},
            </if>
            <if test="count != null and count != ''">
                count = #{count , jdbcType=VARCHAR},
            </if>
            <if test="school != null and school != ''">
                school = #{school , jdbcType=VARCHAR},
            </if>
            <if test="weight != null and weight != ''">
                weight = #{weight , jdbcType=INTEGER},
            </if>
            <if test="height != null and height != ''">
                height = #{height , jdbcType=INTEGER},
            </if>
            <if test="habbit != null and habbit != ''">
                habbit = #{habbit , jdbcType=VARCHAR},
            </if>
            <if test="memo != null and memo != ''">
                memo = #{memo , jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id ,jdbcType=INTEGER}
    </update>

    <delete id="deleteTestEntityById" parameterType="int">
        delete from test_test where id = #{id}
    </delete>

</mapper>

4、mybatis主配置文件mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 配置数据库方言  目前只有mysql和oracle两种-->
    <properties>
        <property name="dialect" value="mysql"/>
    </properties>

    <!-- 配置mysql分页拦截器 start -->
    <!-- com.xxyd.mybatis.interceptor.PaginationInterceptor 来自于jar包mybatis-pager-1.0.0.jar -->
    <plugins>
        <plugin interceptor="com.xxyd.mybatis.interceptor.PaginationInterceptor"></plugin>
    </plugins>

    <!-- 映射文件 -->
    <mappers>
        <mapper resource="com/xxyd/mybatis/mapper/TestMapper.xml" />
    </mappers>
</configuration>

6、jdbc.properties文件

driverClass=com.mysql.jdbc.Driver
url=jdbc\:mysql\://127.0.0.1\:3306/student_manager
username=root
password=123456

7、spring配置文件部分配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="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-placeholder location="classpath:jdbc.properties" />

    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${driverClass}" />
        <property name="url" value="${url}" />
        <property name="username" value="${username}"></property>
        <property name="password" value="${password}"></property>
    </bean>

    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="configLocation" value="classpath:mybatis-config.xml" />
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- 测试用例start -->
    <bean id="TestMapperTest" class="com.xxyd.mybatis.test.TestMapperTest">
        <property name="sessionFactory" ref="sqlSessionFactory"/>
    </bean>
    <!-- 测试用例end -->


    <!-- mapper bean -->
    <bean id="TestMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value="com.xxyd.mybatis.dao.TestMapper" />
        <property name="sqlSessionFactory" ref="sqlSessionFactory" />
    </bean>

</beans>

 7、最后，测试类： 

package com.xxyd.mybatis.test;

import java.util.List;

import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.xxyd.mybatis.pojo.TestEntity;

public class TestMapperTest {

    //private SqlSessionFactory sessionFactory;
    private static SqlSessionFactoryBuilder builder;
    private static SqlSessionFactory sessionFactory;
    static {
        builder = new SqlSessionFactoryBuilder();
        sessionFactory = builder.build(Thread.currentThread()
                .getContextClassLoader()
                .getResourceAsStream("mybatis-config.xml"));
    }
    /**
     * @param args
     */
    public static void main(String[] args) {
        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        TestMapperTest TestMapperTest = (TestMapperTest)ctx.getBean("TestMapperTest");
        TestMapperTest.getList();

    }

    @Test
    public void getList(){
        SqlSession sqlSession = sessionFactory.openSession();
        //TestEntity entity = new TestEntity();
        List<TestEntity> list = sqlSession.selectList("com.xxyd.mybatis.dao.TestMapper.getListTestEntity", TestEntity.class, new RowBounds(0, 200));
        sqlSession.commit();
        sqlSession.close();
        for (TestEntity testEntity : list) {
            System.out.println(testEntity.toString());
        }
    }

    //public SqlSessionFactory getSessionFactory() {
    //  return sessionFactory;
    //}
    //public void setSessionFactory(SqlSessionFactory sessionFactory) {
    //  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类型的就可以，目前还没发现什么异常，或许我研究的较为浅显的原因；第三个参数则是需要分页的数据。

• mybatis-pager-1.0.0.jar (11.2 KB)
• 下载次数: 6

• MybatisSpringPager.zip (7.2 MB)
• 下载次数: 3