Quarkus中配置P6Spy记录SQL查询结果的方法问询

问题 #

已在Quarkus项目中成功配置P6Spy，但当前仅能记录SQL语句及参数化后的SQL，无法记录SQL查询返回的结果数据。期望日志能输出类似JSON格式的查询结果，相关配置及日志信息如下：

依赖配置 #

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>1.3</version>
</dependency>

application.properties配置 #

quarkus.datasource.jdbc.driver=com.p6spy.engine.spy.P6SpyDriver

spy.properties配置 #

module.log=com.p6spy.engine.logging.P6LogFactory
realdriver=org.postgresql.Driver
deregisterdrivers=false
outagedetection=false
filter=false
autoflush=true
#excludecategories=info,debug,batch,statement,commit,rollback,outage
logfile=quarkus.log
reloadproperties=false
reloadpropertiesinterval=60
useprefix=false
appender=com.p6spy.engine.logging.appender.FileLogger
append=true
log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout
log4j.appender.STDOUT.layout.ConversionPattern=p6spy - %m%n
log4j.logger.p6spy=INFO,STDOUT
dateformat=yyyy-MM-dd hh:mm:ss a

当前quarkus.log日志内容 #

2022-12-28 03:37:06 PM|45|0|statement|select customer0_.id as id1_5_, customer0_.CREATE_TIME as create_t2_5_, customer0_.CREATE_TIME_UNIX as create_t3_5_, customer0_.UPDATE_TIME as update_t4_5_, customer0_.UPDATE_TIME_UNIX as update_t5_5_, customer0_.business_registration_number as business6_5_, customer0_.company as company7_5_, customer0_.country as country8_5_, customer0_.currency as currency9_5_, customer0_.description as descrip10_5_, customer0_.email as email11_5_, customer0_.mobile as mobile12_5_, customer0_.name as name13_5_, customer0_.status as status14_5_, customer0_.telephone as telepho15_5_ from customer customer0_ where customer0_.email=? limit ?|select customer0_.id as id1_5_, customer0_.CREATE_TIME as create_t2_5_, customer0_.CREATE_TIME_UNIX as create_t3_5_, customer0_.UPDATE_TIME as update_t4_5_, customer0_.UPDATE_TIME_UNIX as update_t5_5_, customer0_.business_registration_number as business6_5_, customer0_.company as company7_5_, customer0_.country as country8_5_, customer0_.currency as currency9_5_, customer0_.description as descrip10_5_, customer0_.email as email11_5_, customer0_.mobile as mobile12_5_, customer0_.name as name13_5_, customer0_.status as status14_5_, customer0_.telephone as telepho15_5_ from customer customer0_ where customer0_.email='hoatest14@gmail.com' limit 1
2022-12-28 03:37:06 PM|0|0|result|select customer0_.id as id1_5_, customer0_.CREATE_TIME as create_t2_5_, customer0_.CREATE_TIME_UNIX as create_t3_5_, customer0_.UPDATE_TIME as update_t4_5_, customer0_.UPDATE_TIME_UNIX as update_t5_5_, customer0_.business_registration_number as business6_5_, customer0_.company as company7_5_, customer0_.country as country8_5_, customer0_.currency as currency9_5_, customer0_.description as descrip10_5_, customer0_.email as email11_5_, customer0_.mobile as mobile12_5_, customer0_.name as name13_5_, customer0_.status as status14_5_, customer0_.telephone as telepho15_5_ from customer customer0_ where customer0_.email=? limit ?|select customer0_.id as id1_5_, customer0_.CREATE_TIME as create_t2_5_, customer0_.CREATE_TIME_UNIX as create_t3_5_, customer0_.UPDATE_TIME as update_t4_5_, customer0_.UPDATE_TIME_UNIX as update_t5_5_, customer0_.business_registration_number as business6_5_, customer0_.company as company7_5_, customer0_.country as country8_5_, customer0_.currency as currency9_5_, customer0_.description as descrip10_5_, customer0_.email as email11_5_, customer0_.mobile as mobile12_5_, customer0_.name as name13_5_, customer0_.status as status14_5_, customer0_.telephone as telepho15_5_ from customer customer0_ where customer0_.email='hoatest14@gmail.com' limit 1
2022-12-28 03:37:06 PM|52|0|commit||

期望日志输出格式 #

{
    "createTime": "2022-09-27 08:27:42:705",
    "createTimeUnix": 1664292462705,
    "id": 3787,
    "updateTime": "2022-09-27 08:27:42:705",
    "updateTimeUnix": 1664292462705,
    "company": "IMIP",
    "country": "VietNam",
    "currency": "KRW",
    "description": "",
    "email": "hoatest14@gmail.com",
    "name": "Tong Thi Hoa 14",
    "status": "Active"
}

如何配置P6Spy使其记录SQL查询结果？

解决方案 #

P6Spy原生的P6LogFactory不支持直接记录查询结果并输出JSON格式，需要自定义日志模块实现需求，步骤如下：

1. 升级P6Spy版本 #

当前1.3版本较老旧，建议升级到最新稳定版（如3.9.1），提升兼容性与扩展能力：

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.9.1</version>
    <scope>runtime</scope>
</dependency>

2. 自定义结果日志模块 #

创建自定义LogFactory实现类，继承P6LogFactory并重写结果集日志逻辑，实现JSON格式输出：

import com.p6spy.engine.logging.P6LogFactory;
import com.p6spy.engine.logging.P6ResultSetLogger;
import org.json.JSONArray;
import org.json.JSONObject;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import io.quarkus.logging.Log;

public class CustomP6LogFactory extends P6LogFactory {
    @Override
    public P6ResultSetLogger getResultSetLogger() {
        return new CustomP6ResultSetLogger();
    }

    private static class CustomP6ResultSetLogger extends P6ResultSetLogger {
        @Override
        public void resultSetOpened(ResultSet resultSet) {
            try {
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                JSONArray resultArray = new JSONArray();

                while (resultSet.next()) {
                    JSONObject row = new JSONObject();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        String camelCaseName = convertToCamelCase(columnName);
                        Object value = resultSet.getObject(i);
                        row.put(camelCaseName, value);
                    }
                    resultArray.put(row);
                }

                // 输出JSON格式结果到Quarkus日志
                Log.info("SQL查询结果:\n" + resultArray.toString(4));

            } catch (SQLException e) {
                Log.error("记录查询结果失败", e);
            }
        }

        // 将下划线命名列转为小驼峰格式
        private String convertToCamelCase(String columnName) {
            if (columnName == null || columnName.isEmpty()) return columnName;
            String[] parts = columnName.split("_");
            StringBuilder sb = new StringBuilder(parts[0].toLowerCase());
            for (int i = 1; i < parts.length; i++) {
                sb.append(Character.toUpperCase(parts[i].charAt(0)));
                sb.append(parts[i].substring(1).toLowerCase());
            }
            return sb.toString();
        }
    }
}

3. 修改spy.properties配置 #

替换日志模块为自定义实现，确保配置正确：

# 使用自定义日志工厂
module.log=com.yourpackage.CustomP6LogFactory
realdriver=org.postgresql.Driver
deregisterdrivers=false
outagedetection=false
filter=false
autoflush=true
# 确保result类别未被排除，保留结果日志开关
excludecategories=info,debug,batch,statement,commit,rollback,outage
logfile=quarkus.log
reloadproperties=false
reloadpropertiesinterval=60
useprefix=false
appender=com.p6spy.engine.logging.appender.FileLogger
append=true
dateformat=yyyy-MM-dd hh:mm:ss a

注意事项 #

• 自定义日志会遍历整个结果集，大数据量查询可能影响性能，建议仅在开发/测试环境启用。
• 确保自定义类的包名与spy.properties中module.log配置路径一致。
• 若使用JPA/Hibernate，Quarkus默认配置即可让P6Spy拦截JDBC调用，无需额外调整。

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