DB2 使用 LIMIT 和 OFFSET

DB2使用LIMIT和OFFSET进行数据分页查询操作

sql
SELECT *
FROM employee
ORDER BY emp_id
LIMIT 10 OFFSET 20;

java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ProductDao {
    private static final String URL = "jdbc:db2://localhost:50000/mydb";
    private static final String USERNAME = "username";
    private static final String PASSWORD = "password";
    public List getProductList(int currentPage) {
        List productList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            String sql = "SELECT * FROM product ORDER BY id LIMIT ? OFFSET ?";
            preparedStatement = connection.prepareStatement(sql);
            int limit = 10; // 每页显示的记录数量
            int offset = (currentPage - 1) * limit; // 偏移量
            preparedStatement.setInt(1, limit);
            preparedStatement.setInt(2, offset);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                double price = resultSet.getDouble("price");
                Product product = new Product(id, name, price);
                productList.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return productList;
    }
}