Spring Boot应用中如何查询非实体的PostgreSQL pg_stat_statements表？

嗨，这个问题我之前在做PostgreSQL性能监控的时候也碰到过！pg_stat_statements作为PostgreSQL的系统统计表，确实没法像自定义业务表那样直接用JPA Repository映射，但有几种非常实用的实现方式，我给你一一拆解：

方法一：用JdbcTemplate执行原生SQL（最直接） #

这是最省心的方式，直接通过原生SQL查询系统表，再把结果映射到自定义DTO里。

步骤1：先启用pg_stat_statements扩展 #

默认PostgreSQL没开这个扩展，得先搞定：

-- 执行SQL创建扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 修改postgresql.conf，添加以下配置后重启数据库
shared_preload_libraries = 'pg_stat_statements'

步骤2：定义DTO接收结果 #

创建一个和pg_stat_statements字段对应的DTO（按需选择字段，不用全写）：

public class PgStatStatementDTO {
    private Long queryid;
    private String query;
    private Long calls;
    private Double totalTime;

    // 构造器、getter、setter按需实现
    public PgStatStatementDTO(Long queryid, String query, Long calls, Double totalTime) {
        this.queryid = queryid;
        this.query = query;
        this.calls = calls;
        this.totalTime = totalTime;
    }
}

步骤3：用JdbcTemplate查询 #

在Service里注入JdbcTemplate，执行原生SQL并映射结果：

@Service
public class DbStatsService {
    private final JdbcTemplate jdbcTemplate;

    // 构造注入JdbcTemplate
    public DbStatsService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<PgStatStatementDTO> getTopTimeConsumingQueries() {
        String sql = """
            SELECT queryid, query, calls, total_time 
            FROM pg_stat_statements 
            ORDER BY total_time DESC 
            LIMIT 10;
        """;
        
        return jdbcTemplate.query(sql, (rs, rowNum) -> 
            new PgStatStatementDTO(
                rs.getLong("queryid"),
                rs.getString("query"),
                rs.getLong("calls"),
                rs.getDouble("total_time")
            )
        );
    }
}

方法二：用JPA原生查询映射到DTO #

如果你更习惯JPA的写法，可以通过@NamedNativeQuery和@SqlResultSetMapping来实现，不用创建实体类。

步骤1：给DTO添加映射注解 #

@SqlResultSetMapping(
    name = "PgStatStatementMapping",
    classes = @ConstructorResult(
        targetClass = PgStatStatementDTO.class,
        columns = {
            @ColumnResult(name = "queryid", type = Long.class),
            @ColumnResult(name = "query", type = String.class),
            @ColumnResult(name = "calls", type = Long.class),
            @ColumnResult(name = "total_time", type = Double.class)
        }
    )
)
@NamedNativeQuery(
    name = "PgStatStatementDTO.getStats",
    query = "SELECT queryid, query, calls, total_time FROM pg_stat_statements ORDER BY calls DESC LIMIT 10",
    resultSetMapping = "PgStatStatementMapping"
)
public class PgStatStatementDTO {
    // 必须和映射字段顺序一致的构造器
    public PgStatStatementDTO(Long queryid, String query, Long calls, Double totalTime) {
        this.queryid = queryid;
        this.query = query;
        this.calls = calls;
        this.totalTime = totalTime;
    }

    // getter方法
}

步骤2：用EntityManager执行查询 #

@Service
public class DbStatsService {
    private final EntityManager entityManager;

    public DbStatsService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<PgStatStatementDTO> getMostCalledQueries() {
        return entityManager.createNamedQuery("PgStatStatementDTO.getStats", PgStatStatementDTO.class)
                .getResultList();
    }
}

方法三：Spring Data JDBC原生查询 #

如果你用Spring Data JDBC，可以直接在Repository接口里写原生SQL，返回DTO：

public interface PgStatStatementRepository extends Repository<PgStatStatementDTO, Long> {
    @Query(value = """
        SELECT queryid, query, calls, total_time 
        FROM pg_stat_statements 
        WHERE calls > 100 
        ORDER BY mean_time DESC
    """, nativeQuery = true)
    List<PgStatStatementDTO> getFrequentSlowQueries();
}

额外注意点 #

• 确保数据库用户有pg_stat_statements的查询权限：GRANT SELECT ON pg_stat_statements TO your_db_user;
• 系统表的字段可能因PostgreSQL版本略有差异，建议先查\d pg_stat_statements确认字段名。

内容的提问来源于stack exchange，提问作者user3225948