CODEWITHSUNDEEP
javaoracle-databasestored-proceduresplsqlspring-jdbc
Apostolos Emmanouilidis
Apostolos Emmanouilidis

Reputation: 7197

How to call an Oracle PL-SQL procedure which has a custom defined complex Object type as input parameter from Java

In Oracle database, I have defined an Object type which has a nested Object type. Assuming, that I have a PL-SQL procedure which uses the parent Object type as input parameter, I'm looking for a performant way to call the procedure from Java code.

CREATE OR REPLACE TYPE STUDENT AS OBJECT ( 
     name         VARCHAR2(35),
     address      HOME_ADDRESS
);

CREATE OR REPLACE TYPE HOME_ADDRESS AS OBJECT ( 
     street    VARCHAR2(35)
);

CREATE OR REPLACE PROCEDURE TEST(
    studentObject IN STUDENT
)
IS
BEGIN
     ... do domething ...
END;

On Java side, I have the relevant POJOs holding data.

I have tried using Spring SimpleJdbcCall along with a StructMapper.

StructMapper<Demo> MAPPER = BeanPropertyStructMapper.newInstance(Student.class);
SqlParameter studentIn = new SqlParameter("studentObject", Types.STRUCT, "MY_SCHEMA.STUDENT");
SimpleJdbcCall sjc = new SimpleJdbcCall(datasource).withSchemaName("MY_SCHEMA")
      .withProcedureName("TEST")
      .withoutProcedureColumnMetaDataAccess()
      .useInParameterNames("studentObject")
      .declareParameters(studentIn);
final MapSqlParameterSource inputParameters = new MapSqlParameterSource().addValue("studentObject",
            new SqlStructValue<Student>(studentInstance, MAPPER, "MY_SCHEMA.STUDENT"));
final Map<String, Object> execute = sjc.execute(inputParameters);

The above example works when not using nested Object types. When using a nested Object type, a java.sql.SQLException: Fail to convert to internal representation exception is thrown. Besides that, spring-data-oracle and spring-data-jdbc-ext projects seems to not be maintained anymore and I would like to avoid using them. The above code is not my real code but an example for the question's purposes. My real Object type and the corresponding POJO in Java might be quite complex with many nested objects and arrays.

Upvotes: 3

Views: 2845

Answers (2)

Apostolos Emmanouilidis
Apostolos Emmanouilidis

Reputation: 7197

Eventually, I have come to the solution of using Java wrapper classes which extend my plain POJOs and implement the java.sql.SQLData interface. The implementation of readSQL method instructs on how to populate the object with data read from the database, while the writeSQL writes this object to the given SQL data stream.

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class StudentMapper extends Student implements SQLData {

    public StudentMapper(Student student) {
        setName(student.getName());
        setAddress(student.getAddress());
    }

    @Override
    public String getSQLTypeName() throws SQLException {
        return "MY_SCHEMA.STUDENT";
    }

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        setName(stream.readString());
        setAddress((Address) stream.readObject());
    }

    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeString(getName());
        final AddressMapper addressMapper = new AddressMapper(getAddress());
        stream.writeObject(addressMapper);
    }
}

final StudentMapper mapper = new StudentMapper(studentInstance);
CallableStatement callableStatement = ...
callableStatement.setObject("studentObject", mapper);
callableStatement.execute();

Upvotes: 3

mehnet ali
mehnet ali

Reputation: 133

you can use JPA 2.1 or upper for call stored procedure in Oracle DB.

for example: https://thoughts-on-java.org/call-stored-procedures-jpa/

Upvotes: -2

Related Questions