如何使用JdbcTemplate实现分页查询？代码举例讲解

使用JdbcTemplate实现分页查询,可以通过以下步骤进行:

1. 添加Maven依赖:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

2. 定义数据源和JdbcTemplate:

@Bean
public DataSource dataSource() {
    return new DriverManagerDataSource(url, username, password);
}

@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
    return new JdbcTemplate(dataSource); 
}

3. 编写分页查询方法:

@Repository
public class UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Page<User> findByPage(int page, int size) {
        // 计算查询起始索引及总记录数
        int start = (page - 1) * size;
        int count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM user", Integer.class);

        // 获取当前页数据
        List<User> list = jdbcTemplate.query("SELECT * FROM user LIMIT ? OFFSET ?", 
            new Object[]{size, start}, 
            BeanPropertyRowMapper.newInstance(User.class));

        // 返回分页结果
        return new PageImpl<>(list, PageRequest.of(page, size), count);
    }
}

4. 在Service层调用:

@Service
public class UserService {
    @Autowired
    private UserDao userDao;

    public Page<User> listByPage(int page, int size) {
        return userDao.findByPage(page, size);
    }
}

5. 在Controller层接收请求并返回结果:

@RestController
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping("/users")
    public ResponseEntity<Page<User>> listByPage(@RequestParam int page, @RequestParam int size) {
        Page<User> pageResult = userService.listByPage(page, size);
        return new ResponseEntity<>(pageResult, HttpStatus.OK);
    } 
}

通过访问/users?page=1&size=3可以获取第一页3条数据。