Mybatis get clob field

Question

We want to get a clob field in a annotation based call to a oracle function. This function return a CLOB field Oracle. We have this code:

@Select("{ #{result,  javaType=java.lang.String ,jdbcType = CLOB, mode=OUT } = call pkg_exportacion_datos.f_recupera_dgp_pac( "
        + "#{cipAuto, jdbcType=VARCHAR, mode=IN},#{idExplotacion, jdbcType=NUMERIC, mode=IN} )}")
    @Options(statementType = StatementType.CALLABLE)
@Results(
        value = {
          @Result(
              property = "result",
              column = "result",
              javaType = String.class)
        })
String getListadoDGPPaciente(String cipAuto,long idExplotacion,String result);

We can debug in ClobTypeHandler on method private String toString(Clob clob) throws SQLException and inside this method field CLOB has a valid value, but this value not return inside variable result. Inside variable resul set a null value.

Can anybody help me?

Thanks.

Answer 1

The method should look as follows:

@Update({
  "{#{result,javaType=java.lang.String,jdbcType=CLOB,mode=OUT}",
  "= call pkg_exportacion_datos.f_recupera_dgp_pac(",
    "#{cipAuto,jdbcType=VARCHAR,mode=IN},",
    "#{idExplotacion,jdbcType=NUMERIC,mode=IN}",
  ")}"
})
@Options(statementType = StatementType.CALLABLE)
void getListadoDGPPaciente(ParamBean param);

Note that the result is returned as an OUT parameter, not the method return value.
So, you should use @Update or @Insert instead of @Select.
And to receive the OUT parameter, the method parameter must be a Map or a bean like the following.

public class ParamBean {
  private String cipAuto;
  private long idExplotacion;
  private String result;
  // getters / setters
}

The code that calls the method would look as follows.

ParamBean param = new ParamBean();
param.setCipAuto("foo");
param.setIdExplotacion(123L);
mapper.getListadoDGPPaciente(param);
return param.getResult(); // returns the CLOB as a String