Java与Spring框架调用数据库存储过程与函数的完整指南

一、JDBC原生方式调用存储过程与函数

1.1 存储过程调用基础

在Java中通过JDBC调用存储过程需要以下步骤：

// 注册Oracle驱动示例
Class.forName("oracle.jdbc.driver.OracleDriver");

try (Connection conn = DriverManager.getConnection(url, user, password);
     CallableStatement cstmt = conn.prepareCall("{call EMPLOYEE_PKG.CALC_BONUS(?, ?, ?)}")) {
    
    // 设置输入参数
    cstmt.setInt(1, 2023);
    cstmt.setString(2, "SALES");
    
    // 注册输出参数
    cstmt.registerOutParameter(3, Types.NUMERIC);
    
    // 执行存储过程
    cstmt.execute();
    
    // 获取输出值
    BigDecimal bonus = cstmt.getBigDecimal(3);
    System.out.println("部门奖金总额：" + bonus);
    
} catch (SQLException e) {
    // 处理特定错误代码
    if(e.getErrorCode() == 20001) {
        System.err.println("无效的部门参数");
    } else {
        e.printStackTrace();
    }
}

1.2 函数调用实现

处理数据库函数的注意事项：

try (CallableStatement cstmt = conn.prepareCall("{? = call CALC_EMPLOYEE_SENIORITY(?)}")) {
    
    // 注册返回参数
    cstmt.registerOutParameter(1, Types.INTEGER);
    cstmt.setInt(2, 1001);
    
    cstmt.execute();
    
    int seniorityYears = cstmt.getInt(1);
    System.out.println("员工工龄：" + seniorityYears);
    
} catch (SQLException e) {
    // 处理ORA-01403等特殊错误
    if(e.getSQLState().equals("02000")) {
        System.err.println("员工记录不存在");
    }
}

1.3 高级结果集处理

处理多结果集和游标：

cstmt = conn.prepareCall("{call GET_EMPLOYEE_HIERARCHY(?)}");
cstmt.setInt(1, 1001);

boolean hasResults = cstmt.execute();
while(hasResults) {
    try (ResultSet rs = cstmt.getResultSet()) {
        while(rs.next()) {
            System.out.println("员工层级：" + rs.getInt("LEVEL") 
                + " - " + rs.getString("EMP_NAME"));
        }
    }
    hasResults = cstmt.getMoreResults();
}

二、Spring JDBC高级封装

2.1 SimpleJdbcCall深度应用

处理复杂返回值类型：

public class EmployeeBonusService {
    
    private SimpleJdbcCall bonusCalculationCall;
    
    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.bonusCalculationCall = new SimpleJdbcCall(dataSource)
            .withCatalogName("EMPLOYEE_PKG")
            .withProcedureName("CALC_BONUS")
            .declareParameters(
                new SqlParameter("YEAR", Types.NUMERIC),
                new SqlParameter("DEPT_CODE", Types.VARCHAR),
                new SqlOutParameter("TOTAL_BONUS", Types.NUMERIC),
                new SqlReturnResultSet("BONUS_DETAILS", 
                    new BeanPropertyRowMapper<>(BonusDetail.class))
            );
    }

    public BonusResult calculateBonus(int year, String deptCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("YEAR", year);
        params.put("DEPT_CODE", deptCode);
        
        Map<String, Object> results = bonusCalculationCall.execute(params);
        
        BonusResult result = new BonusResult();
        result.setTotalBonus((BigDecimal) results.get("TOTAL_BONUS"));
        result.setDetails((List<BonusDetail>) results.get("BONUS_DETAILS"));
        return result;
    }
}

2.2 元数据自动发现机制

自动参数映射配置：

SimpleJdbcCall call = new SimpleJdbcCall(dataSource)
    .withProcedureName("COMPLEX_CALCULATION")
    .withoutProcedureColumnMetaDataAccess()
    .useInParameterNames("input1", "input2")
    .declareParameters(
        new SqlOutParameter("output1", Types.VARCHAR),
        new SqlOutParameter("output2", Types.DATE)
    );

三、Spring Data JPA存储过程集成

3.1 实体类映射方案

使用Hibernate特有注解：

@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
        name = "Employee.calculateYearlyBonus",
        procedureName = "EMP_PKG.CALC_YEARLY_BONUS",
        parameters = {
            @StoredProcedureParameter(
                name = "p_year", 
                type = Integer.class, 
                mode = ParameterMode.IN),
            @StoredProcedureParameter(
                name = "o_total_bonus",
                type = Double.class,
                mode = ParameterMode.OUT)
        },
        resultClasses = BonusSummary.class
    )
})
@Entity
public class Employee {
    // 实体类字段定义
}

3.2 动态存储过程调用

运行时参数绑定：

@Repository
public class EmployeeRepositoryImpl implements CustomEmployeeRepository {

    @PersistenceContext
    private EntityManager em;

    @Override
    public BonusSummary executeDynamicProcedure(int year, String region) {
        StoredProcedureQuery query = em.createStoredProcedureQuery("DYNAMIC_BONUS_CALC");
        query.registerStoredProcedureParameter("p_year", Integer.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("p_region", String.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("o_total", Double.class, ParameterMode.OUT);
        
        query.setParameter("p_year", year);
        query.setParameter("p_region", region);
        
        query.execute();
        
        return new BonusSummary(
            (Double) query.getOutputParameterValue("o_total"),
            year,
            region
        );
    }
}

四、性能优化实践

4.1 批处理调用优化

Oracle批量执行示例：

int batchSize = 50;
try (Connection conn = dataSource.getConnection();
     CallableStatement cstmt = conn.prepareCall("{call UPDATE_EMPLOYEE_STATUS(?)}")) {
    
    conn.setAutoCommit(false);
    
    for (int i = 0; i < employeeIds.size(); i++) {
        cstmt.setInt(1, employeeIds.get(i));
        cstmt.addBatch();
        
        if ((i + 1) % batchSize == 0) {
            cstmt.executeBatch();
            conn.commit();
        }
    }
    
    // 处理剩余记录
    int[] remaining = cstmt.executeBatch();
    conn.commit();
    
} catch (BatchUpdateException e) {
    // 处理批量异常
    handleFailedBatch(e.getUpdateCounts());
}

4.2 连接池配置建议

HikariCP优化配置示例：

# application.properties
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.connectionTimeout=2000
spring.datasource.hikari.maxLifetime=1800000
spring.datasource.hikari.leakDetectionThreshold=5000
spring.datasource.hikari.poolName=MainPool

五、异常处理模式

5.1 自定义异常转换器

Spring JDBC异常处理：

@ControllerAdvice
public class JdbcExceptionHandler {

    @ExceptionHandler(SQLException.class)
    public ResponseEntity<ErrorResponse> handleJdbcExceptions(SQLException ex) {
        String errorCode = ex.getSQLState();
        
        ErrorResponse response = new ErrorResponse();
        response.setTimestamp(LocalDateTime.now());
        response.setErrorCode("DB-" + errorCode);
        
        switch (errorCode) {
            case "22003":
                response.setMessage("数值超出范围");
                return new ResponseEntity<>(response, HttpStatus.BAD_REQUEST);
            case "23505":
                response.setMessage("数据唯一性冲突");
                return new ResponseEntity<>(response, HttpStatus.CONFLICT);
            default:
                response.setMessage("数据库操作异常");
                return new ResponseEntity<>(response, 
                    HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }
}

六、安全最佳实践

6.1 SQL注入防护

安全参数处理示例：

public void unsafeMethod(String userInput) {
    // 危险做法
    String sql = "{call SEARCH_EMPLOYEES('" + userInput + "')}";
    
    // 安全做法
    CallableStatement cstmt = conn.prepareCall("{call SEARCH_EMPLOYEES(?)}");
    cstmt.setString(1, userInput);
}

七、监控与调试

7.1 日志跟踪配置

开启JDBC日志记录：

<!-- logback.xml配置 -->
<logger name="org.springframework.jdbc.core.JdbcTemplate" level="DEBUG"/>
<logger name="org.springframework.jdbc.core.StatementCreatorUtils" level="TRACE"/>
<logger name="org.hibernate.SQL" level="DEBUG"/>
<logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="TRACE"/>

7.2 执行计划分析

Oracle执行计划获取：

EXPLAIN PLAN FOR
BEGIN
  YOUR_PROCEDURE_NAME(param1, param2);
END;
/

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);