MyBatis Mybatis cannot retrieve data from Oracle with data type blob, clob to HashMap?

I am trying to retrieve field from Oracle 11g using Mybatis and Spring boot but facing some problems. Here is what I have done: - Mapper Class

@Mapper
interface TestMapper {
    @Select("""
    <script>
        SELECT 
        COL1, COL2, COL3 FROM 
                ( 
                    SELECT T.*, rowNum as rowIndex
                    FROM 
                    (
                        SELECT *
                        FROM ${'$'}{param.table}
                    )T
                )
                WHERE rowIndex BETWEEN 0 AND 1
    </script>
    """ )
    fun find(@Param("param") param: HashMap<String, Any>) : HashMap<String, Any>
}

• Here is my table definition

CREATE TABLE YOURPC.TEST (
  COL1 VARCHAR2(100),
  COL2 NUMBER,
  COL3 CLOB,
  COL4 BLOB,
  COL5 TIMESTAMP,
  COL6 DATE
);

• Here the way I seed data:

BEGIN
  FOR l_counter IN 1..1000
  LOOP
INSERT INTO test values((select dbms_random.random from dual), (select dbms_random.random from dual), (select dbms_random.random from dual), (SELECT TO_CHAR( TRUNC( DBMS_RANDOM.VALUE( 0, 256*256*256 ) ), 'FM0XXXXX' )
FROM DUAL), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
  END LOOP;
END;

And when I am trying to execute the code, I got this error message:

There was an unexpected error (type=Internal Server Error, status=500).
Type definition error: [simple type, class oracle.jdbc.OracleConnection]; nested exception is com.fasterxml.jackson.databind.exc.InvalidDefinitionException: Direct self-reference leading to cycle (through reference chain: java.util.HashMap["COL3"]->oracle.sql.CLOB["physicalConnection"]->oracle.jdbc.driver.T4CConnection["wrapper"])

I have tried to extends BasedTypeHandler of ByteArray but debugger never reach there. Here is the code:

package com.gdce.xgen.handler

@Configuration
class UUIDTypeHandler : BaseTypeHandler<ByteArray?>() {
    @Throws(SQLException::class)
    override fun setNonNullParameter(ps: PreparedStatement, i: Int, parameter: ByteArray?, jdbcType: JdbcType?) {
        ps.setObject(i, parameter)
    }

    @Throws(SQLException::class)
    override fun getNullableResult(rs: ResultSet, columnName: String?): ByteArray {
        return rs.getObject<ByteArray>(columnName, ByteArray::class.java)
    }

    @Throws(SQLException::class)
    override fun getNullableResult(rs: ResultSet, columnIndex: Int): ByteArray {
        return rs.getObject<ByteArray>(columnIndex, ByteArray::class.java)
    }

    @Throws(SQLException::class)
    override fun getNullableResult(cs: CallableStatement, columnIndex: Int): ByteArray {
        return cs.getObject<ByteArray>(columnIndex, ByteArray::class.java)
    }
}

Here is my application.yml

mybatis:
  type-handlers-package: com.gdce.xgen.handler

Comment From: harawata

Hi @sereymongkol ,

It does not seem like a MyBatis issue because the exception is com.fasterxml.jackson.databind.exc.InvalidDefinitionException. If you believe this is a MyBatis issue, please provide a repro like these.

Comment From: jeanse7en

I already got this trouble before. MyBatis separated between selectByExample and selectByExampleWithBLOBs. You should use selectByExampleWithBLOBs incase you need to load the BLOBs

Comment From: harawata

No reply.