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:分页查询成功
本博客为非营利性个人原创,除部分有明确署名的作品外,所刊登的所有作品的著作权均为本人所拥有,本人保留所有法定权利。违者必究