SpringBoot调用Oracle包存储过程返回CURSOR最优方案咨询

嘿，作为Spring刚入门的同学，我完全懂你不想再写繁琐的CallableStatement的心情！针对你说的调用Oracle包中存储过程并返回CURSOR的场景，下面给你分享两种Spring生态里更简洁优雅的实现方式，比传统Java方式省心太多：

Spring自带的SimpleJdbcCall已经帮我们封装了所有底层JDBC的繁琐操作，直接就能映射CURSOR到实体类，代码简洁可读性还强。

步骤1：定义实体类 #

先写一个和PEOPLE_TAB字段对应的实体类，建议用Lombok的@Data注解省去getter/setter的冗余代码：

import lombok.Data;

@Data
public class People {
    private String name;
    private String surname;
    private String sex;
    private Integer age;
    private String roll;
}

步骤2：用SimpleJdbcCall实现DAO层 #

直接注入数据源，配置好Oracle包名和存储过程名，再指定CURSOR到实体的映射逻辑即可：

import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;

@Repository
public class PeopleDao {
    private final SimpleJdbcCall showPeopleCall;

    // 构造注入数据源，初始化SimpleJdbcCall
    public PeopleDao(DataSource dataSource) {
        this.showPeopleCall = new SimpleJdbcCall(dataSource)
                .withCatalogName("HOME_TEST_PKG") // 指定Oracle包名
                .withProcedureName("show_...") // 你的存储过程名称
                // 配置CURSOR结果集到实体类的映射
                .returningResultSet("OUT_CURSOR", (rs, rowNum) -> {
                    People people = new People();
                    people.setName(rs.getString("NAME"));
                    people.setSurname(rs.getString("SURNAME"));
                    people.setSex(rs.getString("SEX"));
                    people.setAge(rs.getInt("AGE"));
                    people.setRoll(rs.getString("ROLL"));
                    return people;
                });
    }

    public List<People> getPeopleList() {
        // 如果存储过程有入参，这里用MapSqlParameterSource传入即可，无参就传空对象
        Map<String, Object> result = showPeopleCall.execute(new MapSqlParameterSource());
        return (List<People>) result.get("OUT_CURSOR");
    }
}

优势 #

不用手动注册参数、遍历结果集，Spring全帮你搞定，原生组件无额外依赖，代码清爽易维护。

如果你的项目已经在用MyBatis，那用它来调用Oracle存储过程会更省心，XML配置直观，结果映射还能复用。

步骤1：定义Mapper接口 #

import org.apache.ibatis.annotations.Mapper;
import java.util.List;

@Mapper
public interface PeopleMapper {
    List<People> callShowPeopleProcedure();
}

步骤2：编写Mapper XML配置 #

直接配置存储过程调用语句和结果映射：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yourpackage.PeopleMapper">
    <!-- 调用Oracle包存储过程 -->
    <select id="callShowPeopleProcedure" statementType="CALLABLE">
        {call HOME_TEST_PKG.show_...(#{outCursor, mode=OUT, jdbcType=CURSOR, resultMap=PeopleResultMap})}
    </select>

    <!-- 结果集到实体类的映射 -->
    <resultMap id="PeopleResultMap" type="com.yourpackage.People">
        <result column="NAME" property="name"/>
        <result column="SURNAME" property="surname"/>
        <result column="SEX" property="sex"/>
        <result column="AGE" property="age"/>
        <result column="ROLL" property="roll"/>
    </resultMap>
</mapper>

步骤3：业务层调用 #

直接注入Mapper接口就能用：

import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class PeopleService {
    private final PeopleMapper peopleMapper;

    public PeopleService(PeopleMapper peopleMapper) {
        this.peopleMapper = peopleMapper;
    }

    public List<People> getPeople() {
        return peopleMapper.callShowPeopleProcedure();
    }
}

优势 #

代码量极少，XML配置一目了然，结果映射可复用，适合已经接入MyBatis的项目，后期维护成本极低。

• 确保项目中引入了正确的Oracle驱动（比如ojdbc8）
• 如果存储过程有入参，SimpleJdbcCall可以通过MapSqlParameterSource.addValue()传入，MyBatis则在接口方法加@Param注解，XML中对应#{paramName}即可
• 实体类用Lombok的@Data能大幅减少冗余代码，让结构更清晰

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