springboot + mybatis配置分页插件

一：使用pagehelper配置分页插件

1：首先配置springboot +mybatis框架  参考：http://www.cnblogs.com/liyafei/p/7911549.html

2：创建配置类MybatisConfig，对分页插件进行配置。将mybatis-config.xml移动到classpath路径下。

　　

package com.liyafei.util.pagehelper;

import java.util.Properties;

import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

import com.github.pagehelper.PageHelper;

@Configuration   //添加这个注解相当于配置文件
@EnableTransactionManagement
public class MyBatisConfig implements TransactionManagementConfigurer {
       //MybatisConfig将会映射到classpath下的mybaits-config.xml,功能和xml下面配置类似
    @Autowired
    DataSource dataSource;

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean() {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //分页插件
        PageHelper pageHelper = new PageHelper();
        Properties props = new Properties();
        props.setProperty("reasonable", "true");
        props.setProperty("supportMethodsArguments", "true");
        props.setProperty("returnPageInfo", "check");
        props.setProperty("params", "count=countSql");
        pageHelper.setProperties(props);
        //添加插件
        bean.setPlugins(new Interceptor[]{pageHelper});
        try {
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            bean.setConfigLocation(resolver.getResource("classpath:mybatis-config.xml"));
            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    @Override
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        return new DataSourceTransactionManager(dataSource);
    }
}

3：在controller中使用分页插件pagehelper,分页插件只能配置一个，不能多用。调用PageHelper.startPage之后下一行（只作用于这一行）的查询结果将会自动调用分页插件
　　

    @RequestMapping("/query/{page}/{pageSize}")
    public PageInfo query(@PathVariable Integer page, @PathVariable Integer pageSize) {
        if(page!= null && pageSize!= null){
            System.out.println("page"+page+"pageSize"+pageSize);
            PageHelper.startPage(page, pageSize);
        }
        List<User> userList = userService.getUserList();
        
        for(User user:userList){
            System.out.print(user.getId());
            System.out.println(user.getUsername());
            
        }
        
        return new PageInfo(userList);
    }

4：测试成功

　　

二：使用mybatis自带的RowBounds分页参数

　　1：首先向UserMapper.java中添加一个查询函数，RowBounds作为参数

　　

	public List<User> findByRowBounds(RowBounds rowBounds);

    2：在UserMapper.xml配置查询函数的sql语句，如红色部分

　　

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.liyafei.dao.mapper.UserMapper" >
    <resultMap id="BaseResultMap" type="com.liyafei.dao.pojo.User" >  //id是resultMapd的标示，type代表使用哪个类作为映射的类
        <id column="id" property="id" jdbcType="INTEGER" />  //id代表resultMap的主键，result代表其属性
        <result column="username" property="username" jdbcType="VARCHAR" />   //column代表sql的列名，property代表POJO的属性，jdbcType代表sql的类型
        <result column="age" property="age" jdbcType="INTEGER" />
        <result column="ctm" property="ctm" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List" >
        id, username, age, ctm
    </sql>

　　　<!-- resultMap是上面定义的映射集，使用BaseResultMap作为映射规则，不是结果类型-->
    <select id="getUserList" resultMap="BaseResultMap"  >
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user
    </select>
    
    <select id="findByRowBounds" resultMap="BaseResultMap">   
        SELECT 
        <include refid="Base_Column_List" />
        FROM tb_user
    </select>

    <select id="getUserById" parameterType="java.lang.Integer" resultMap="BaseResultMap" >
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user
        WHERE id = #{id}
    </select>

    <insert id="add" parameterType="com.liyafei.dao.pojo.User" >
        INSERT INTO
        tb_user
        (username,age,ctm)
        VALUES
        (#{username}, #{age}, now())
    </insert>

    <update id="update" parameterType="java.util.Map" >
        UPDATE
        tb_user
        SET
        username = #{user.username},age = #{user.age}
        WHERE
        id = #{id}
    </update>

    <delete id="delete" parameterType="java.lang.Integer" >
        DELETE FROM
        tb_user
        WHERE
        id = #{id}
    </delete>
</mapper>

3：创建查询参数，在UserController中调用查询函数，并将查询产数作为查询函数的参数。

    @RequestMapping("/rowquery/{page}/{pageSize}")
    public PageInfo findByRowBounds(@PathVariable Integer page,@PathVariable Integer pageSize){
        RowBounds rowBounds=new RowBounds(page,pageSize);
        List<User> userList = userMapper.findByRowBounds(rowBounds); return new PageInfo(userList);
    }

4：分页查询成功