springboot项目中实现一个sql层的查询分页(使用RowBounds 和PageHelper 方式)
时间: 2024-03-17 14:42:20 浏览: 19
在Spring Boot项目中,可以使用MyBatis作为ORM框架来实现在SQL语句的分页。MyBatis提供了RowBounds类来进行分页查询,也可以使用PageHelper插件来进行分页查询。下面是实现分页查询的步骤:
1. 在MyBatis的mapper文件中编写SQL语句,例如:
```
<select id="getUserList" resultMap="userResultMap">
select * from user
</select>
```
2. 在Spring Boot项目中配置MyBatis和PageHelper插件,例如:
```
@Configuration
@MapperScan("com.example.demo.mapper")
public class MyBatisConfig {
@Bean
public SqlSessionFactoryBean sqlSessionFactoryBean() throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource());
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
sessionFactory.setPlugins(new Interceptor[]{pageHelper()});
return sessionFactory;
}
@Bean
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/test");
dataSource.setUsername("root");
dataSource.setPassword("root");
return dataSource;
}
@Bean
public PageHelper pageHelper() {
PageHelper pageHelper = new PageHelper();
Properties properties = new Properties();
properties.setProperty("dialect", "mysql");
properties.setProperty("reasonable", "true");
properties.setProperty("pageSizeZero", "true");
pageHelper.setProperties(properties);
return pageHelper;
}
}
```
3. 在Java代码中调用mapper方法进行分页查询,使用RowBounds方式,例如:
```
@Autowired
private UserMapper userMapper;
public List<User> getUserListByRowBounds(String name, Integer startIndex, Integer pageSize) {
RowBounds rowBounds = new RowBounds(startIndex, pageSize);
return userMapper.getUserList(name, rowBounds);
}
```
或者使用PageHelper方式,例如:
```
@Autowired
private UserMapper userMapper;
public PageInfo<User> getUserListByPageHelper(String name, Integer pageNum, Integer pageSize) {
PageHelper.startPage(pageNum, pageSize);
List<User> userList = userMapper.getUserList(name);
return new PageInfo<>(userList);
}
```
其中,startIndex和pageSize是分页查询的参数,使用RowBounds方式需要在mapper中使用if标签进行判断,如果有值则使用limit关键字进行分页查询;使用PageHelper方式则直接调用PageHelper.startPage方法进行分页查询,返回的结果需要使用PageInfo进行包装。