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.sqlCREATE 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 ProjectDependencies 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
|