Spring+HikariCP连接Oracle报错SQLSTATE(08003)：连接已关闭排查

问题场景 #

在Spring项目中使用HikariCP作为Oracle数据库连接池，调用StudentService.enrollStudents方法时，先执行studentRepository.performBackgroundVerification（该方法获取包装连接后unwrap为OracleConnection，操作后关闭两个连接），随后调用studentRepository.enrollStudents时抛出错误：

Connection oracle.jdbc.driver.T4CConnection@73e0fb8c marked as broken because of SQLSTATE(08003), ErrorCode(17008)
java.sql.SQLRecoverableException: Closed Connection

用户疑问：每次调用jdbcTemplate.getDataSource().getConnection()应该从连接池获取新连接，为什么前一个方法关闭连接会导致后续报错？

相关代码配置如下：

@Service
public class StudentService {

    @Autowired
    private StudentRepository studentRepository;
    
    public void enrollStudents(List<Student> students) {
    
        //Calling 1st method (verification)
        studentRepository.performBackgroundVerification(students);
        
        //Calling 2nd method (enroll)
        studentRepository.enrollStudents(students);
    }
}

@Repository
public class StudentRepository {

    public void performBackgroundVerification(List<Student> students) throws Exception {
    
        Connection wrappedConnection = null;
        OracleConnection oracleConnection = null;
        
        try {
            
            //Get Wrapped Connection
            wrappedConnection = jdbcTemplate.getDataSource().getConnection();
                
            //Get Oracle Connection
            oracleConnection = wrappedConnection.unwrap(OracleConnection.class);
            
            //Create Oracle Object(typ_student_obj")
            Struct[] studentStruct = new Struct[students.size()];
            for(int i=0; i<students.size(); i++) {
                studentStruct[i] = oracleConnection.createStruct("TYP_STUDENT_OBJ", new Object[] {
                    students.get(i).getFirstName(), Date.valueOf(students.get(i).getDateOfBirth()), students.get(i).getAddress(), students.get(i).getAge()
                });
            }
            
            //Create Oracle Array(typ_student_tbl)
            Array studentStructArray = oracleConnection.createOracleArray("TYP_STUDENT_TBL", studentStruct);
            
            //Call Stored Procedure
            Map<String, Object> resultSet = new SimpleJdbcCall(jdbcTemplate)
                    .withSchemaName(schemaName)
                    .withCatalogName(PackageConstants.PKG_STUDENT)
                    .withProcedureName(StoredProcedureConstants.SP_INSERT_STUDENTS)
                    .declareParameters(new SqlParameter[] {
                        new SqlParameter("p_student_data", OracleTypes.ARRAY),
                        new SqlOutParameter("p_status", OracleTypes.VARCHAR),
                    })
                    .execute();
            
            if(resultSet.get("p_status") != null) {
                String verificationStatus = String.valueOf(resultSet.get("p_status"));
            }   
        }
        finally {
        
            if(wrappedConnection != null && !wrappedConnection.isClosed()) {
                wrappedConnection.close();
                logger.info("WRAPPED CONNECTION IS CLOSED ? : "+wrappedConnection.isClosed());
            }
            
            if(oracleConnection != null && !oracleConnection.isClosed()) {
                oracleConnection.close();
                logger.info("ORACLE CONNECTIONS IS CLOSED ? : "+oracleConnection.isClosed());
            }
        }       
    }
    
    public void enrollStudents(List<Student> students) throws Exception {
    
        Connection wrappedConnection = null;
        OracleConnection oracleConnection = null;
        
        try {
            
            //Get Wrapped Connection
            wrappedConnection = jdbcTemplate.getDataSource().getConnection();
                
            //Get Oracle Connection
            oracleConnection = wrappedConnection.unwrap(OracleConnection.class);
            
            //Create Oracle Object(typ_student_obj")
            
            //Create Oracle Array(typ_student_tbl)
            
        }   
        finally {
        
            if(wrappedConnection != null && !wrappedConnection.isClosed()) {
                wrappedConnection.close();
                logger.info("WRAPPED CONNECTION IS CLOSED ? : "+wrappedConnection.isClosed());
            }
            
            if(oracleConnection != null && !oracleConnection.isClosed()) {
                oracleConnection.close();
                logger.info("ORACLE CONNECTIONS IS CLOSED ? : "+oracleConnection.isClosed());
            }
        }
    }
}

spring:
  application:
    name: test-jobs
  profiles:
    active: local
  datasource:
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 60000
      idle-timeout: 600000  #default is 600000 i.e 10 minutes
      max-lifetime: 1800000 #default is 1800000 i.e 30 minutes
      pool-name: testPool

错误原因 #

核心问题在于同时关闭了Hikari的包装连接（HikariProxyConnection）和unwrap后的原生OracleConnection：

• HikariCP的包装连接是对原生Oracle连接的代理，unwrap(OracleConnection.class)返回的是连接池内部持有的物理连接实例，而非副本
• 手动关闭oracleConnection会直接销毁底层物理连接，而非将其归还连接池
• 当后续方法从连接池获取连接时，HikariCP可能会分配这个已被物理关闭的连接，从而触发"Closed Connection"错误
• 后续调用wrappedConnection.close()是无效操作，因为底层物理连接已经被关闭

另外，performBackgroundVerification中使用SimpleJdbcCall.execute()时，没有指定使用当前获取的连接，会额外从连接池拿新连接，造成不必要的资源占用，但不是本次报错的直接原因。

解决方案 #

1. 仅关闭包装连接，禁止手动关闭原生Oracle连接 #

HikariCP的代理连接在调用close()时，会自动将原生连接归还连接池，无需手动关闭unwrap后的OracleConnection。修改finally块：

finally {
    if(wrappedConnection != null && !wrappedConnection.isClosed()) {
        wrappedConnection.close(); // 仅关闭包装连接，连接池负责归还原生连接
        logger.info("WRAPPED CONNECTION IS CLOSED ? : "+wrappedConnection.isClosed());
    }
    // 移除对oracleConnection的关闭逻辑
}

2. 优化SimpleJdbcCall的连接使用（可选） #

如果希望SimpleJdbcCall复用当前获取的连接，而非重新从池里获取，可以指定连接参数：

Map<String, Object> resultSet = new SimpleJdbcCall(jdbcTemplate)
        .withSchemaName(schemaName)
        .withCatalogName(PackageConstants.PKG_STUDENT)
        .withProcedureName(StoredProcedureConstants.SP_INSERT_STUDENTS)
        .declareParameters(new SqlParameter[] {
            new SqlParameter("p_student_data", OracleTypes.ARRAY),
            new SqlOutParameter("p_status", OracleTypes.VARCHAR),
        })
        // 指定使用当前获取的包装连接
        .execute(wrappedConnection, new MapSqlParameterSource()
                .addValue("p_student_data", studentStructArray, OracleTypes.ARRAY));

3. 采用Spring自动连接管理（最佳实践） #

避免手动获取和关闭连接，利用Spring的内置机制让JdbcTemplate自动管理连接，彻底规避连接错误：

// 使用JdbcTemplate的execute方法，自动管理连接生命周期
jdbcTemplate.execute((Connection con) -> {
    OracleConnection oracleCon = con.unwrap(OracleConnection.class);
    // 执行Oracle特定操作（创建Struct、Array等）
    return null;
});

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