mybatis物理分页的实现
下面是所有的代码:
1.java类:
1.package com.xxyd.mybatis.pojo; 2. 3.import java.io.Serializable; 4./** 5. * 实体类 6. * @author dove 7. * 8. */ 9.public class TestEntity implements Serializable{ 10. 11. private static final long serialVersionUID = -5849200248418883686L; 12. private int id ; 13. private String name; 14. private int no; 15. private int sex; 16. private int age; 17. private String count; 18. private String school; 19. private int weight; 20. private int height; 21. private String habbit; 22. private String memo; 23. public int getId() { 24. return id; 25. } 26. public void setId(int id) { 27. this.id = id; 28. } 29. public String getName() { 30. return name; 31. } 32. public void setName(String name) { 33. this.name = name; 34. } 35. public int getNo() { 36. return no; 37. } 38. public void setNo(int no) { 39. this.no = no; 40. } 41. public int getSex() { 42. return sex; 43. } 44. public void setSex(int sex) { 45. this.sex = sex; 46. } 47. public int getAge() { 48. return age; 49. } 50. public void setAge(int age) { 51. this.age = age; 52. } 53. public String getCount() { 54. return count; 55. } 56. public void setCount(String count) { 57. this.count = count; 58. } 59. public String getSchool() { 60. return school; 61. } 62. public void setSchool(String school) { 63. this.school = school; 64. } 65. public int getWeight() { 66. return weight; 67. } 68. public void setWeight(int weight) { 69. this.weight = weight; 70. } 71. public int getHeight() { 72. return height; 73. } 74. public void setHeight(int height) { 75. this.height = height; 76. } 77. public String getHabbit() { 78. return habbit; 79. } 80. public void setHabbit(String habbit) { 81. this.habbit = habbit; 82. } 83. public String getMemo() { 84. return memo; 85. } 86. public void setMemo(String memo) { 87. this.memo = memo; 88. } 89. @Override 90. public String toString() { 91. return "TestEntity [id=" + id + ", name=" + name + ", no=" + no 92. + ", sex=" + sex + ", age=" + age + ", count=" + count 93. + ", school=" + school + ", weight=" + weight + ", height=" 94. + height + ", habbit=" + habbit + ", memo=" + memo + "]"; 95. } 96. 97.}
2、DAO接口
1.package com.xxyd.mybatis.dao; 2. 3.import java.util.List; 4. 5.import org.apache.ibatis.annotations.Param; 6. 7.import com.xxyd.mybatis.pojo.TestEntity; 8./** 9. * dao接口 10. * @author dove 11. * 12. */ 13.public interface TestMapper { 14. public void createTestEntity(TestEntity entity); 15. public List<TestEntity> getTestEntityByPager(@Param("pageNo")int pageNo,@Param("pageSize") int pageSize); 16. public List<TestEntity> getListTestEntity(); 17. public int getTotalCount(@Param("pageNo")int pageNo,@Param("pageSize") int pageSize); 18. public void updateTestEntity(TestEntity entity); 19. public void deleteTestEntityById(@Param("id") int id); 20.}
3、映射文件TestMapper.xml
1.<?xml version="1.0" encoding="UTF-8"?> 2.<!DOCTYPE mapper SYSTEM "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3.<mapper namespace="com.xxyd.mybatis.dao.TestMapper"> 4. 5. <resultMap type="com.xxyd.mybatis.pojo.TestEntity" id="test_test"> 6. <id property="id" column="id" javaType="int" jdbcType="INTEGER"/> 7. <result property="name" column="name" javaType="String" jdbcType="VARCHAR"/> 8. <result property="no" column="no" javaType="int" jdbcType="INTEGER"/> 9. <result property="sex" column="sex" javaType="int" jdbcType="INTEGER"/> 10. <result property="age" column="age" javaType="int" jdbcType="INTEGER"/> 11. <result property="count" column="count" javaType="String" jdbcType="VARCHAR"/> 12. <result property="school" column="school" javaType="String" jdbcType="VARCHAR"/> 13. <result property="weight" column="weight" javaType="int" jdbcType="INTEGER"/> 14. <result property="height" column="height" javaType="int" jdbcType="INTEGER"/> 15. <result property="habbit" column="habbit" javaType="String" jdbcType="VARCHAR"/> 16. <result property="memo" column="memo" javaType="String" jdbcType="VARCHAR"/> 17. </resultMap> 18. 19. <insert id="createTestEntity" useGeneratedKeys="true" parameterType="com.xxyd.mybatis.pojo.TestEntity"> 20. insert into test_test(name,no,sex, age,count,school,weight,height,habbit,memo) 21. values(#{name},#{no},#{sex},#{age},#{count},#{school},#{weight},#{height},#{habbit},#{memo}); 22. </insert> 23. 24. <select id="getTestEntityByPager" resultMap="test_test"> 25. select id,name,no,sex, age,count,school,weight,height,habbit,memo 26. from test_test 27. limit #{pageNo, jdbcType=INTEGER} , #{pageSize, jdbcType=INTEGER} 28. </select> 29. 30. <select id="getListTestEntity" resultMap="test_test"> 31. select id,name,no,sex, age,count,school,weight,height,habbit,memo 32. from test_test 33. </select> 34. 35. <select id="getTotalCount" resultType="int"> 36. select count(sub.id) from 37. (select test.id as id from test_test test 38. limit #{pageNo, jdbcType=INTEGER} , #{pageSize, jdbcType=INTEGER}) as sub 39. </select> 40. 41. <update id="updateTestEntity" parameterType="com.xxyd.mybatis.pojo.TestEntity"> 42. update test_test 43. <set> 44. <if test="name != null and name != ''"> 45. name = #{name , jdbcType=VARCHAR}, 46. </if> 47. <if test="no != null and no != ''"> 48. no = #{no , jdbcType=INTEGER}, 49. </if> 50. <if test="sex != null and sex != ''"> 51. sex = #{sex , jdbcType=INTEGER}, 52. </if> 53. <if test="age != null and age != ''"> 54. age = #{age , jdbcType=INTEGER}, 55. </if> 56. <if test="count != null and count != ''"> 57. count = #{count , jdbcType=VARCHAR}, 58. </if> 59. <if test="school != null and school != ''"> 60. school = #{school , jdbcType=VARCHAR}, 61. </if> 62. <if test="weight != null and weight != ''"> 63. weight = #{weight , jdbcType=INTEGER}, 64. </if> 65. <if test="height != null and height != ''"> 66. height = #{height , jdbcType=INTEGER}, 67. </if> 68. <if test="habbit != null and habbit != ''"> 69. habbit = #{habbit , jdbcType=VARCHAR}, 70. </if> 71. <if test="memo != null and memo != ''"> 72. memo = #{memo , jdbcType=VARCHAR}, 73. </if> 74. </set> 75. where id = #{id ,jdbcType=INTEGER} 76. </update> 77. 78. <delete id="deleteTestEntityById" parameterType="int"> 79. delete from test_test where id = #{id} 80. </delete> 81. 82.</mapper>
4、mybatis主配置文件mybatis-config.xml
1.<?xml version="1.0" encoding="UTF-8" ?> 2.<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3.<configuration> 4. <!-- 配置数据库方言 目前只有mysql和oracle两种--> 5. <properties> 6. <property name="dialect" value="mysql"/> 7. </properties> 8. 9. <!-- 配置mysql分页拦截器 start --> 10. <!-- com.xxyd.mybatis.interceptor.PaginationInterceptor 来自于jar包mybatis-pager-1.0.0.jar --> 11. <plugins> 12. <plugin interceptor="com.xxyd.mybatis.interceptor.PaginationInterceptor"></plugin> 13. </plugins> 14. 15. <!-- 映射文件 --> 16. <mappers> 17. <mapper resource="com/xxyd/mybatis/mapper/TestMapper.xml" /> 18. </mappers> 19.</configuration>
5、spring配置文件部分配置
1.<?xml version="1.0" encoding="UTF-8"?> 2.<beans xmlns="http://www.springframework.org/schema/beans" 3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" 4. xmlns:p="http://www.springframework.org/schema/p" xmlns:tx="http://www.springframework.org/schema/tx" 5. xmlns:context="http://www.springframework.org/schema/context" 6. xsi:schemaLocation=" 7. http://www.springframework.org/schema/beans 8. http://www.springframework.org/schema/beans/spring-beans-2.5.xsd 9. http://www.springframework.org/schema/aop 10. http://www.springframework.org/schema/aop/spring-aop-2.5.xsd 11. http://www.springframework.org/schema/tx 12. http://www.springframework.org/schema/tx/spring-tx-2.5.xsd 13. http://www.springframework.org/schema/context 14. http://www.springframework.org/schema/context/spring-context.xsd"> 15. 16. <!-- Properties文件读取配置,base的properties --> 17. <context:property-placeholder location="classpath:jdbc.properties" /> 18. 19. <bean id="dataSource" 20. class="org.springframework.jdbc.datasource.DriverManagerDataSource"> 21. <property name="driverClassName" value="${driverClass}" /> 22. <property name="url" value="${url}" /> 23. <property name="username" value="${username}"></property> 24. <property name="password" value="${password}"></property> 25. </bean> 26. 27. <bean id="transactionManager" 28. class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> 29. <property name="dataSource" ref="dataSource" /> 30. </bean> 31. 32. <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 33. <property name="configLocation" value="classpath:mybatis-config.xml" /> 34. <property name="dataSource" ref="dataSource" /> 35. </bean> 36. 37. <!-- 测试用例start --> 38. <bean id="TestMapperTest" class="com.xxyd.mybatis.test.TestMapperTest"> 39. <property name="sessionFactory" ref="sqlSessionFactory"/> 40. </bean> 41. <!-- 测试用例end --> 42. 43. 44. <!-- mapper bean --> 45. <bean id="TestMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> 46. <property name="mapperInterface" value="com.xxyd.mybatis.dao.TestMapper" /> 47. <property name="sqlSessionFactory" ref="sqlSessionFactory" /> 48. </bean> 49. 50.</beans>
6、最后,测试类:
1.package com.xxyd.mybatis.test; 2. 3.import java.util.List; 4. 5.import org.apache.ibatis.session.RowBounds; 6.import org.apache.ibatis.session.SqlSession; 7.import org.apache.ibatis.session.SqlSessionFactory; 8.import org.apache.ibatis.session.SqlSessionFactoryBuilder; 9.import org.junit.Test; 10.import org.springframework.context.support.ClassPathXmlApplicationContext; 11. 12.import com.xxyd.mybatis.pojo.TestEntity; 13. 14.public class TestMapperTest { 15. 16. //private SqlSessionFactory sessionFactory; 17. private static SqlSessionFactoryBuilder builder; 18. private static SqlSessionFactory sessionFactory; 19. static { 20. builder = new SqlSessionFactoryBuilder(); 21. sessionFactory = builder.build(Thread.currentThread() 22. .getContextClassLoader() 23. .getResourceAsStream("mybatis-config.xml")); 24. } 25. /** 26. * @param args 27. */ 28. public static void main(String[] args) { 29. ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); 30. TestMapperTest TestMapperTest = (TestMapperTest)ctx.getBean("TestMapperTest"); 31. TestMapperTest.getList(); 32. 33. } 34. 35. @Test 36. public void getList(){ 37. SqlSession sqlSession = sessionFactory.openSession(); 38. //TestEntity entity = new TestEntity(); 39. List<TestEntity> list = sqlSession.selectList("com.xxyd.mybatis.dao.TestMapper.getListTestEntity", TestEntity.class, new RowBounds(0, 200)); 40. sqlSession.commit(); 41. sqlSession.close(); 42. for (TestEntity testEntity : list) { 43. System.out.println(testEntity.toString()); 44. } 45. } 46. 47. //public SqlSessionFactory getSessionFactory() { 48. // return sessionFactory; 49. //} 50. //public void setSessionFactory(SqlSessionFactory sessionFactory) { 51. // this.sessionFactory = sessionFactory; 52. //} 53.}
能够成功运行的前提是,你已经将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类型的就可以,目前还没发现什么异常,或许我研究的较为浅显的原因;第三个参数则是需要分页的数据。
划船不用桨、杨帆不等风、一生全靠浪