说明:
PageHelper.startPage(pageNum,pageSize);只对该语句以后的第一个查询语句得到的数据进行分页。
springboot坏境
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-autoconfigure</artifactId>
<version>1.3.0</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
此方法会自动注入加载pagehelper插件
而不是原来的
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>{pagehelper.version}</version> </dependency>
因为上面的依赖缺少了
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-autoconfigure</artifactId> </dependency>
手动注入加载:
/**
* @author zyc
* @Description
* @date 2019/10/25
* @since v1.0.0
*/
@Configuration
@MapperScan(basePackages = TpDb.PACKAGE, sqlSessionFactoryRef = "testSqlSessionFactory")
public class TpDb {
protected static final String PACKAGE = "com.cy.test.dao.core.mysql.**.*";
private static final String MAPPER_LOCATION = "classpath:mapper/**/*.xml";
private Logger logger = LoggerFactory.getLogger(TpDb.class);
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driverClassName:'com.mysql.jdbc.Driver'}")
private String driverClassName;
@Value("${spring.datasource.initialSize:30}")
private int initialSize;
@Value("${spring.datasource.minIdle:30}")
private int minIdle;
@Value("${spring.datasource.maxActive:150}")
private int maxActive;
@Value("${spring.datasource.maxWait:60000}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis:600000}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis:300000}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery:'SELECT 1 FROM DUAL'}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle:false}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow:true}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn:true}")
private boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements:true}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize:20}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.filters:log4j}")
private String filters;
@Value("${spring.datasource.connectionInitSqls}")
private String connectionInitSqls;
@Value("${spring.datasource.connectionProperties}")
private String connectionProperties;
@Bean(name = "testDataSource")
@Primary
public DataSource fkDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClassName);
dataSource.setUrl(dbUrl);
dataSource.setUsername(username);
dataSource.setPassword(password);
//configuration
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
StringTokenizer tokenizer = new StringTokenizer(connectionInitSqls, ";");
//utf-8mb4
dataSource.setConnectionInitSqls(Collections.list(tokenizer));
try {
dataSource.setFilters(filters);
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
dataSource.setConnectionProperties(connectionProperties);
try {
dataSource.init();
} catch (SQLException e) {
logger.error("druid init error", e);
}
return dataSource;
}
@Bean(name = "testTransactionManager")
@Primary
public DataSourceTransactionManager fkTransactionManager() {
return new DataSourceTransactionManager(fkDataSource());
}
@Bean(name = "testSqlSessionFactory")
@Primary
public SqlSessionFactory fkSqlSessionFactory(@Qualifier("testDataSource") DataSource fkDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(fkDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(TpDb.MAPPER_LOCATION));
PageInterceptor pageInterceptor = new PageInterceptor();
Properties properties = new Properties();
properties.setProperty("reasonable", "false");
properties.setProperty("helperDialect", "mysql");
properties.setProperty("supportMethodsArguments", "true");
properties.setProperty("returnPageInfo", "check");
properties.setProperty("params", "count=countSql");
pageInterceptor.setProperties(properties);
// 添加插件
sessionFactory.setPlugins(new Interceptor[] {pageInterceptor});
return sessionFactory.getObject();
}
}
自定义数据源&加载分页加载
配置化:
mybatis-config.xml
<!--翻页插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="helperDialect" value="mysql"></property>
<property name="reasonable" value="true"></property>
</plugin>
</plugins>
示例:
public PageInfo<Order> findOrderList(int pageNum, int pageSize, OrderVO orderVO) {
//使用分页插件,核心代码就这一行
PageHelper.startPage(pageNum, pageSize);
List<Order> list = orderMapper.findOrderList(orderVO);
PageInfo<Order> pageInfo = new PageInfo<>(list);
return pageInfo;
}
参照:
https://github.com/pagehelper/pagehelper-spring-boot
https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md