Java SQL Server 分页实现

在Java中连接SQL Server数据库并实现分页查询,通常有以下几种方法：

使用OFFSET-FETCH语法（SQL Server 2012及以上版本）

这是最现代、最推荐的方法,性能较好。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SQLServerPagination {
    private static final String DB_URL = "jdbc:sqlserver://localhost:1433;databaseName=YourDB";
    private static final String USER = "username";
    private static final String PASS = "password";
    public static void main(String[] args) {
        int pageSize = 10;
        int pageNumber = 2; // 第二页
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            String sql = "SELECT * FROM YourTable ORDER BY id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, (pageNumber - 1) * pageSize);
                pstmt.setInt(2, pageSize);
                try (ResultSet rs = pstmt.executeQuery()) {
                    while (rs.next()) {
                        // 处理结果集
                        System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

使用ROW_NUMBER()窗口函数（适用于SQL Server 2005及以上版本）

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SQLServerPaginationWithRowNumber {
    private static final String DB_URL = "jdbc:sqlserver://localhost:1433;databaseName=YourDB";
    private static final String USER = "username";
    private static final String PASS = "password";
    public static void main(String[] args) {
        int pageSize = 10;
        int pageNumber = 2; // 第二页
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            String sql = "SELECT * FROM ( " +
                         "   SELECT *, ROW_NUMBER() OVER (ORDER BY id) as row_num " +
                         "   FROM YourTable " +
                         ") AS temp_table " +
                         "WHERE row_num BETWEEN ? AND ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                int start = (pageNumber - 1) * pageSize + 1;
                int end = pageNumber * pageSize;
                pstmt.setInt(1, start);
                pstmt.setInt(2, end);
                try (ResultSet rs = pstmt.executeQuery()) {
                    while (rs.next()) {
                        // 处理结果集
                        System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

使用存储过程实现分页

对于复杂查询或频繁使用的分页,可以考虑使用存储过程：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SQLServerPaginationWithStoredProcedure {
    private static final String DB_URL = "jdbc:sqlserver://localhost:1433;databaseName=YourDB";
    private static final String USER = "username";
    private static final String PASS = "password";
    public static void main(String[] args) {
        int pageSize = 10;
        int pageNumber = 2; // 第二页
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            String sql = "{call sp_GetPagedData(?, ?)}";
            try (CallableStatement cstmt = conn.prepareCall(sql)) {
                cstmt.setInt(1, pageNumber);
                cstmt.setInt(2, pageSize);
                try (ResultSet rs = cstmt.executeQuery()) {
                    while (rs.next()) {
                        // 处理结果集
                        System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

对应的存储过程示例（SQL Server）：

CREATE PROCEDURE sp_GetPagedData
    @PageNumber INT,
    @PageSize INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM YourTable
    ORDER BY id
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END

使用JPA/Hibernate分页

如果你使用JPA或Hibernate,可以这样实现分页：

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;
public class PaginationExample {
    @PersistenceContext
    private EntityManager entityManager;
    public List<YourEntity> getPagedData(int pageNumber, int pageSize) {
        Query query = entityManager.createQuery("SELECT e FROM YourEntity e ORDER BY e.id");
        query.setFirstResult((pageNumber - 1) * pageSize);
        query.setMaxResults(pageSize);
        return query.getResultList();
    }
}

性能优化建议

1. 确保排序列（如id）有索引
2. 对于大数据集，考虑使用WITH (NOLOCK)提示减少锁争用（但要小心脏读）
3. 避免在OFFSET中使用计算表达式，直接传递计算后的值
4. 考虑使用WHERE条件配合分页，减少数据扫描量

方法中，OFFSET-FETCH语法是最简洁高效的，如果你的SQL Server版本支持（2012及以上）,建议优先使用这种方法。