MYBATIS/Oracle out参数：结果映射集合不包含“TestEntity”的值

Question

我使用了Oracle过程的out参数。out参数仅返回一个对象，该对象具有Oracle类型的嵌套对象。out pareemter应该是java bean TestEntity的精确映射，但mybatis说结果不包含TestEntity

请帮忙

Java bean：

@Data
public class BaseEntity {
    private Long id;
    private String name;
    private String description;
}

@Data
public class TestEntity {
    private String name;
    private String description;
    private BaseEntity client;
}

@Mapper
public interface TestMapper {
    List<TestEntity> getAll(@Param("outParam") TestEntity outParam);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.ssc.gss.mapper.TestMapper" >

    <resultMap id="TestEntity" type="org.ssc.gss.entity.TestEntity">
        <result property="name" column="NAME" />
        <result property="description" column="DESCRIPTION" />
        <result property="client" column="CLIENT" typeHandler="org.ssc.gss.utils.OracleBaseEntityHandler" />
    </resultMap>

    <select id="getAll"  statementType="CALLABLE"  >
        call TEST_GET_BASEDETAILS(#{c1, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet,  resultMap="TestEntity"})
    </select>
</mapper>

 CREATE TABLE CLIENTS (
      ID NUMBER ,
      NAME VARCHAR2(40) unique,
      DESCRIPTION VARCHAR2(200),
          CONSTRAINT CLIENT_PK PRIMARY KEY (ID) USING INDEX  ENABLE
      )
  /
 CREATE TABLE DELIVERABLES (
                                ID NUMBER ,
                                NAME varchar2(30),
                                CLIENTID NUMBER,
                                DESCRIPTION VARCHAR2(200)
  )
  /


 CREATE TYPE BASEENTITYTYPE IS OBJECT
  (
  ID NUMBER,
  NAME VARCHAR2(30),
  DESCRIPTION VARCHAR2(200)
  );
  /

 CREATE OR REPLACE FUNCTION get_base_details(
    i_p_id IN number,
    i_p_type IN VARCHAR2
  ) return BASEENTITYTYPE
  IS
   o_detail BASEENTITYTYPE;
  BEGIN
   o_detail :=  BASEENTITYTYPE(i_p_id, null, null);
  BEGIN
  CASE
    WHEN UPPER(i_p_type) = 'CLIENT'
      THEN
  SELECT NAME, DESCRIPTION INTO o_detail.NAME, o_detail.DESCRIPTION FROM CLIENTS where ID = o_detail.id;
  END case;
  END;
  return o_detail;
  END;
  /


 CREATE OR REPLACE PROCEDURE TEST_GET_BASEDETAILS(c1 out sys_refcursor)
  AS
  BEGIN
    OPEN c1 for
   SELECT d.name, d.DESCRIPTION, get_base_details(d.clientID,'CLIENT')  client  FROM deliverables d;
  END;
  /

 INSERT INTO CLIENTS(ID, NAME, description) values (1, 'A','AAA');
 INSERT INTO CLIENTS(ID, NAME, description) values (2, 'B','bbb');
 Insert into deliverables values(1,'D1',1, 'CCC');

SQL> var cur refcursor
SQL> exec TEST_GET_BASEDETAILS(:cur)

PL/SQL procedure successfully completed.

SQL> print cur

NAME|DESCRIPTION|CLIENT(ID, NAME, DESCRIPTION)
D1|CCC|BASEENTITYTYPE(1, 'A', 'AAA')

    @Test
    void getAll() {
        TestEntity d = new TestEntity();
        try {
            testMapper.getAll(d);
        } catch (Exception e)
        {
            logger.info(e.getClass().getName());
            e.printStackTrace();
        };
    }    

org.mybatis.spring.mybatisSystemException：嵌套异常为org.apache.ibatis.exceptions.persistenceException：

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
at com.sun.proxy.$Proxy116.selectList(Unknown Source)

Answer 1

这要归功于@ave，他是Mybatis的大师...所以-Java beans,xml mapper,Java mapper etc=>请参见上面所指出的一个更正：from：

        call TEST_GET_BASEDETAILS(#{c1, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet,  resultMap="TestEntity"})

致：

        call TEST_GET_BASEDETAILS(#{c1, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet,  resultMap=TestEntity})

和其他考虑：

null

@Test
    void getAll() {
        TestEntity d = new TestEntity();
        try {
            testMapper.getAll(d);
        } catch (Exception e)
        {
            logger.info(e.getClass().getName());
            e.printStackTrace();
        };
    }   

@Test
    void getAll() {
         Map<String, ?> outParam = new HashMap<>();
        try {
            testMapper.getAll(outParam );
        } catch (Exception e)
        {
            logger.info(e.getClass().getName());
            e.printStackTrace();
        };
    }   

#{c1

#{outParam.c1, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet,  resultMap=TestEntity})

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.ssc.gss.entity.BaseEntity;
import java.sql.*;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import oracle.jdbc.OracleConnection;

public class OracleBaseEntityHandler extends BaseTypeHandler<BaseEntity> {
    final Logger logger = LoggerFactory.getLogger(getClass());


    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                    BaseEntity parameter, JdbcType jdbcType) throws SQLException {
        OracleConnection oconn =(OracleConnection) ps.getConnection();

        Object[] o = new Object[3]; // this "3" is because the BaseEntity java bean has 3 fields
        o[0] = parameter.getId();
        o[1] = parameter.getName();
        o[2] = parameter.getDescription();
        Struct struct = oconn.createStruct("BASEENTITYTYPE",o);
        ps.setObject(i, struct);

    }


    @Override
    public BaseEntity getNullableResult(ResultSet rs, String columnName) throws SQLException {
          Struct struct =(java.sql.Struct) rs.getObject(columnName);
          return extractObject(struct);
    }

    @Override
    public BaseEntity getNullableResult(ResultSet rs, int columnIndex) throws SQLException {

        Struct struct =(java.sql.Struct) rs.getObject(columnIndex);

        return extractObject(struct);
    }

    @Override
    public BaseEntity getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {

        Struct struct =(java.sql.Struct) cs.getObject(columnIndex);

        return extractObject(struct);
    }

    protected BaseEntity extractObject(Struct o) throws SQLException {
        if (o == null) {
            return null;
        }
        BaseEntity b = new BaseEntity();
        Object[] attrs = o.getAttributes();
        b.setId(Long.parseLong(attrs[0].toString()));
        b.setName(attrs[1].toString());
        b.setDescription(attrs[2].toString());
        return b;
    }
}