mybatis物理分页的实现

dove19900520

浏览: 599482 次
性别:
来自: 北京

最近访客更多访客>>

xieaiguo

zhf1206

loginboot

tianxin_008883

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

mybatis

mybatis ibatis 逻辑分页物理分页 spring+mybatis

最近两周一直在学习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)
下载次数: 745

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

分享到：

Spring整合MyBatis错误：Mapped Statement ... | mybatis的selectKey作用

2012-11-13 10:34
浏览 29755
评论(10)
分类:编程语言
查看更多

10 楼 rxcss66 2014-02-18

另外 public int getTotalCount(@Param("pageNo")int pageNo,@Param("pageSize") int pageSize);这个方法没啥用吧。
分页返回的是数据集合，想要获取数量直接获取集合的长度就可以了，没必要再查一次数据库。
改成一个返回符合条件的总数的方法即可。

9 楼 rxcss66 2014-02-18

试验了一下，基本找到问题了。
public List<TestEntity> getTestEntityByPager(@Param("pageNo")int pageNo,@Param("pageSize") int pageSize);这里面的pageNo其实不是我们理解的页码，是（页面-1）*pageSize。
实际的理解应该参数1是从第几条开始，比方说(1,5)就是从第1条记录往后取5条，取到的是2 3 4 5 6。
所以参数名叫recordNo看起来比较明了。

8 楼 rxcss66 2014-02-18

我引用了之后返回的数据集合里面只有一条数据。。。。不知道是哪里错了。

7 楼 dove19900520 2013-02-26

dove19900520 写道

sd6292766 写道

PaginationInterceptor 是实现了MYBATIS的Interceptor接口吧，能否贴出是怎么做的？我自己也在写基于Mybatis的分页。你这个是基于MYSQL的。如果是ORACLE的，RowBound部分参数怎么去配置？Limit..offset方言与Oracle里面Rownum是有区别的。

感觉MYBATIS的过滤器层比较难掌握主要是

你想要的是下面这段吗：
@Intercepts({@org.apache.ibatis.plugin.Signature(type=StatementHandler.class, method="prepare", args={java.sql.Connection.class})})
public class PaginationInterceptor
implements Interceptor
{
private static final Log log = LogFactory.getLog(PaginationInterceptor.class);

public Object intercept(Invocation invocation) throws Throwable
{
    StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
    BoundSql boundSql = statementHandler.getBoundSql();
    MetaObject metaStatementHandler = MetaObject.forObject(statementHandler);
    RowBounds rowBounds = (RowBounds)metaStatementHandler.getValue("delegate.rowBounds");
    if ((rowBounds == null) || (rowBounds == RowBounds.DEFAULT)) {
      return invocation.proceed();
    }
    Configuration configuration = (Configuration)metaStatementHandler.getValue("delegate.configuration");
    Dialect.Type databaseType = null;
    try {
      databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect").toUpperCase());
    }
    catch (Exception localException) {
    }
    if (databaseType == null) {
      throw new RuntimeException("the value of the dialect property in configuration.xml is not defined : " + configuration.getVariables().getProperty("dialect"));
    }
    Dialect dialect = null;
    switch ($SWITCH_TABLE$com$xxyd$mybatis$dialect$Dialect$Type()[databaseType.ordinal()])
    {
    case 1:
      dialect = new MySql5Dialect();
      break;
    case 2:
      dialect = new OracleDialect();
    }

    String originalSql = (String)metaStatementHandler.getValue("delegate.boundSql.sql");
    metaStatementHandler.setValue("delegate.boundSql.sql", dialect.getLimitString(originalSql, rowBounds.getOffset(), rowBounds.getLimit()));
    metaStatementHandler.setValue("delegate.rowBounds.offset", Integer.valueOf(0));
    metaStatementHandler.setValue("delegate.rowBounds.limit", Integer.valueOf(2147483647));
    if (log.isDebugEnabled()) {
      log.debug("生成分页SQL : " + boundSql.getSql());
    }
    return invocation.proceed();
}

public Object plugin(Object target)
{
    return Plugin.wrap(target, this);
}

public void setProperties(Properties properties)
{
}
}

呵呵，不好意思啊，我也是刚接触MyBatis不久，这个是借鉴别人写的东西整理出来的，有些地方我也不是很懂的，谢谢。

6 楼 sd6292766 2013-02-25

dove19900520 写道

sd6292766 写道

感觉MYBATIS的过滤器层比较难掌握主要是

谢谢，我有个疑问，rowBounds == RowBounds.DEFAULT为什么加上这个判断。是担心开发人员在传参时候，写成无参构造吗

5 楼 dove19900520 2013-02-25

sd6292766 写道

感觉MYBATIS的过滤器层比较难掌握主要是

4 楼 sd6292766 2013-02-23

sd6292766 写道

感觉MYBATIS的过滤器层比较难掌握主要是

3 楼 sd6292766 2013-02-23

2 楼 dove19900520 2013-02-18

sunney2010 写道

非常好用，我想问一下获取总记录数是否还要一个方法呢？如果能一起返回可以是说完美！

你的建议非常好，有时间的话一定去实现的，谢谢

1 楼 sunney2010 2013-02-02

非常好用，我想问一下获取总记录数是否还要一个方法呢？如果能一起返回可以是说完美！

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论