Spring/JPA操作PostgreSQL报错时如何记录表与列名？

一、PostgreSQL端配置 #

通过调整PostgreSQL的日志参数，让错误日志自动包含表和列的上下文信息，且不会产生冗余日志：

• 修改postgresql.conf文件中的参数：
  • 设置log_error_verbosity = verbose，开启后PostgreSQL在输出错误时会附带更多细节，包括涉及的表、列以及具体的超长值。
  • 保持log_statement = 'none'（默认值），避免记录所有执行的SQL语句，只在错误发生时输出关键信息。
• 修改完成后重启PostgreSQL服务生效。

二、Spring/Hibernate端配置（无需全局开启DEBUG） #

1. 自定义Hibernate异常转换器 #

扩展Hibernate的默认异常转换器，仅针对字段超长错误（SQL状态码22001）提取并记录详细信息：

import org.hibernate.exception.StandardSQLExceptionConverter;
import org.hibernate.exception.spi.SQLExceptionConverter;
import org.hibernate.exception.spi.ViolatedConstraintNameExtracter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.SQLException;

public class DetailedSQLExceptionConverter extends StandardSQLExceptionConverter {
    private static final Logger logger = LoggerFactory.getLogger(DetailedSQLExceptionConverter.class);

    public DetailedSQLExceptionConverter(ViolatedConstraintNameExtracter extracter) {
        super(extracter);
    }

    @Override
    public org.hibernate.JDBCException convert(SQLException sqlException, String message, String sql) {
        org.hibernate.JDBCException jdbcException = super.convert(sqlException, message, sql);
        // 匹配PostgreSQL字符串超长错误的SQL状态码
        if ("22001".equals(sqlException.getSQLState())) {
            logger.error("字段超长错误详情: 执行SQL={}, 错误信息={}", sql, sqlException.getMessage());
        }
        return jdbcException;
    }
}

在Spring配置类中注册该转换器：

import org.hibernate.cfg.AvailableSettings;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import javax.persistence.EntityManagerFactory;
import java.util.Properties;

@Configuration
public class HibernateConfig {
    @Bean
    public EntityManagerFactory entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        // 配置数据源、实体扫描路径等原有设置...
        
        Properties hibernateProps = new Properties();
        hibernateProps.put(AvailableSettings.SQL_EXCEPTION_CONVERTER, "com.yourpackage.DetailedSQLExceptionConverter");
        em.setJpaProperties(hibernateProps);
        
        em.afterPropertiesSet();
        return em.getObject();
    }
}

2. Spring AOP拦截持久化异常 #

通过AOP拦截JPA的保存方法，仅在DataIntegrityViolationException发生时解析并记录错误详情：

import org.aspectj.lang.annotation.AfterThrowing;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.stereotype.Component;
import java.sql.SQLException;

@Aspect
@Component
public class PersistenceExceptionAspect {
    private static final Logger logger = LoggerFactory.getLogger(PersistenceExceptionAspect.class);

    @AfterThrowing(pointcut = "execution(* org.springframework.data.jpa.repository.JpaRepository+.save*(..))", throwing = "ex")
    public void handlePersistenceException(DataIntegrityViolationException ex) {
        Throwable rootCause = ex.getRootCause();
        if (rootCause instanceof SQLException) {
            SQLException sqlEx = (SQLException) rootCause;
            if ("22001".equals(sqlEx.getSQLState())) {
                logger.error("持久化失败：字段超长，错误详情: {}", sqlEx.getMessage());
            }
        }
    }
}

三、验证效果 #

配置完成后触发错误，日志中会出现包含表、列、超长值的详细信息，示例如下：

ERROR: value too long for type character varying(5)
Detail: Value: "abcdef" is too long for type character varying(5).
Where: SQL statement "INSERT INTO user_info (nickname, age) VALUES ($1, $2)"

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