Spring - Returning REF Cursor from a SimpleJdbcCall

This example shows how to use SimpleJdbcCall to call a database procedure which returns a REF Cursor.

We are going to use Oracle database as DataSource in this example. If you do not have Oracle database server installed, you can follow this guide to download, install the database (express edition) and getting started with Oracle SQL Developer.

Example

Creating the database Procedure returning REF Cursor in Oracle

src/main/resources/oracle_objects.sql

CREATE TABLE EMPLOYEE(
ID NUMBER(19),
NAME VARCHAR(255),
ROLE VARCHAR(255),
PRIMARY KEY (ID)
);

CREATE SEQUENCE seq_EMPLOYEE
 MINVALUE 1
 START WITH 1
            INCREMENT BY 1
            CACHE 10;

insert into EMPLOYEE values (seq_EMPLOYEE.nextval, 'Jane', 'Developer');
insert into EMPLOYEE values (seq_EMPLOYEE.nextval, 'Mike', 'Manager');
insert into EMPLOYEE values (seq_EMPLOYEE.nextval, 'Rose', 'Manager');

CREATE PROCEDURE get_managers (manager_cursor IN OUT SYS_REFCURSOR)
 IS
 BEGIN
open manager_cursor for
 SELECT * FROM EMPLOYEE where role = 'Manager';
END;

Copy and paste the above statements to Oracle SQL Developer and execute (green arrow icon or Ctrl+ENTER key) and commit it (green check icon):

Using SimpleJdbcCall

@Repository
public class EmployeeDaoImpl implements EmployeeDao {
  @Autowired
  private DataSource dataSource;
  private JdbcTemplate jdbcTemplate;
  private SimpleJdbcCall simpleJdbcCall;

  @PostConstruct
  private void postConstruct() {
      jdbcTemplate = new JdbcTemplate(dataSource);
      simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
              .withProcedureName("get_managers")
              .returningResultSet("manager_cursor",
                      BeanPropertyRowMapper.newInstance(Employee.class));
  }
    .............
  @Override
  public List<Employee> getAllManagers() {
      Map<String, Object> result =
              simpleJdbcCall.execute(new HashMap<String, Object>(0));
      System.out.println("result: " + result);
      return (List) result.get("manager_cursor");
  }
}

@Component
public class EmployeeClientBean {

  @Autowired
  EmployeeDao employeeDao;

  public void run() {
      List<Employee> persons = employeeDao.loadAll();
      System.out.println("Loaded employees: "+persons);

      List<Employee> allManagers = employeeDao.getAllManagers();
      System.out.println("All Managers: "+allManagers);
  }
}

Java Config

@Configuration
@ComponentScan
public class AppConfig {

  @Bean
  public DataSource dataSource() {
      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName(oracle.jdbc.driver.OracleDriver.class.getName());
      ds.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
      ds.setUsername("system");
      ds.setPassword("1234");
      return ds;
  }

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      context.getBean(EmployeeClientBean.class).run();
  }
}

Loaded employees: [Employee{id=1, name='Jane', role='Developer'}, Employee{id=2, name='Mike', role='Manager'}, Employee{id=3, name='Rose', role='Manager'}]
result: {manager_cursor=[Employee{id=2, name='Mike', role='Manager'}, Employee{id=3, name='Rose', role='Manager'}]}
All Managers: [Employee{id=2, name='Mike', role='Manager'}, Employee{id=3, name='Rose', role='Manager'}]

Example Project

Dependencies and Technologies Used:

• spring-context 4.2.3.RELEASE: Spring Context.
• spring-jdbc 4.2.3.RELEASE: Spring JDBC.
• ora-jdbc 7: POM was created from install:install-file.
• JDK 1.8
• Maven 3.3.9