Springboot java oracle procedure calling with cursor

Question

I am a new in spring world. I have a question about what is the better way to call a procedure from oracle package and return a CURSOR to a Spring?

I can do that like in classical java using "Callable Statement", but I think that there are any better, more cleaner ways, how to do that?

For example,

a) We have a simple table PEOPLE_TAB, where are :

NAME: NEO, Mary SURNAME: ANDERSON, Smith SEX: M, W AGE: 20, 25 ROLL:TEST, TEST

b) Also, we have a package HOME_TEST_PKG with procedure

PROCEDURE show_people_data( i_name IN VARCHAR2, o_resp_set OUT SYS_REFCURSOR)

IS

BEGIN

dbms_output.put_line('Hello 1');

OPEN o_resp_set FOR SELECT name, surname, sex, age, roll from people where name=i_name;

dbms_output.put_line('Hello 2');

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('Hello  3');

OPEN o_resp_set FOR SELECT 'something wrong' as error from dual;

END show_people_data;

c) then, we have a sample java code, which works in Spring:

@RequestMapping(value = "/DBtest")
@ResponseBody
public Map DBtest() throws SQLException {

private String PROCEDURE_NAME = "{call test.home_test_pkg.show_people_data(?,?)}";

    JSONObject answer = new JSONObject();
    CallableStatement stmt = null;
    Connection conn = null;
    ResultSet rset = null;
    String testNameNeo="NEO"; --simple check input for procedure
    try {
        conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:XE", "testname", "testpass");
        stmt = conn.prepareCall(PROCEDURE_NAME);
        stmt.setString(1, testNameNeo);
        stmt.registerOutParameter(2, OracleTypes.CURSOR);
        stmt.execute();
        rset = (ResultSet) stmt.getObject(2);

        while (rset.next()) {
            String name = rset.getString(1);
            log.info(name);
            answer.put("name",rset.getObject(1).toString());
            answer.put("surname",rset.getObject(2).toString());
        }
    }catch (Exception a){
        log.error("Exception "+a);
    }finally {
        rset.close();
        stmt.close();
        conn.close();
    }
    return Collections.singletonMap("response", answer);
}

Solution 1

I think the simplest solution is to create a repository interface using @Procedure annotation like the example below.

@Repository
public interface MyRepository extends CrudRepository<MyEntity, Long> {

  @Procedure(name = "test.home_test_pkg.show_people_data")
  List<MyEntity> getPeopleData(@Param("my_param_in") String myParamIn);
}

Solution 2

Spring Boot integrates two technologies for working with relational databases:

1. JPA / Hibernate

2. JdbcTemplate

Both technologies can make use of Oracle cursors.

For JPA / Hibernate, there's a good example: How to call Oracle stored procedures and functions with JPA and Hibernate

For JdbcTemplate, have a look at: Spring - Returning REF Cursor from a SimpleJdbcCall

Comments

[Daniel]

Ringo Store! Thank you for the idea! I have tried both ways and I have found the SimpleJdbc is better way. For Other things I have tried JPA, also nice! thank you!!