Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support of nested cursors #566

Open
dsulimchuk opened this issue Jan 30, 2016 · 6 comments · May be fixed by #3388
Open

Support of nested cursors #566

dsulimchuk opened this issue Jan 30, 2016 · 6 comments · May be fixed by #3388
Labels
enhancement Improve a feature or add a new feature

Comments

@dsulimchuk
Copy link

dsulimchuk commented Jan 30, 2016

Hello!
Can myBatis support oracle nested cursors ? https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions005.htm

for example i have next select statement:

<select id="tt" statementType="PREPARED" resultType="hashmap">
SELECT 1 id
                ,CURSOR (SELECT LEVEL
                    FROM dual
                    CONNECT BY LEVEL &gt;= 5) coll
        FROM dual
</select>

and take a result = {COLL=oracle.jdbc.driver.OracleResultSetImpl@54c75937, ID=1}

please add support of evaluating nested cursors

@albertKrafter
Copy link

Hi! Any news about this request? @dsulimchuk did you find any alternative?

@harawata
Copy link
Member

Could somebody show me how this works in JDBC?
I mean...

String sql = "select 1 id, cursor(select level from dual connect by level >= 5) coll from dual";
try (Connection con = new JdbcConnection("oracle18").getConnection();
    PreparedStatement stmt = con.prepareStatement(sql);
    ResultSet rs = stmt.executeQuery()) {
  while (rs.next()) {
    int id = rs.getInt("id");
    // How to get the 'coll' value?
  }
}

@albertKrafter
Copy link

...
while (rs.next()) {
    int id = rs.getInt("id");
    // How to get the 'coll' value?
    ResultSet nestedRs = (ResultSet)rs.getObject("coll");
  }
...

@harawata
Copy link
Member

Okay. Then you may be able to write a type handler as a workaround.

<resultMap type="map" id="rm">
  <id column="id" property="id" javaType="int" />
  <result column="coll" property="coll" typeHandler="test.CursorTypeHandler" />
</resultMap>

<select id="selectNestedCursor" resultMap="rm">
  select 1 id,
  cursor(select level from dual connect by level >= 5) coll from
  dual
</select>
public class CursorTypeHandler extends BaseTypeHandler<Map<String, Object>>{

  @Override
  public Map<String, Object> getNullableResult(ResultSet rs, String columnName) throws SQLException {
    Map<String, Object> result = new HashMap<>();
    try (ResultSet nestedRs = rs.getObject(columnName, ResultSet.class)){
      ResultSetMetaData rsmd = nestedRs.getMetaData();
      for (int i = 0; i < rsmd.getColumnCount(); i++) {
        String key = rsmd.getColumnName(i + 1);
        Object value = rs.getObject(i + 1);
        result.put(key, value);
      }
    }
    return result;
  }

It might be nice if we could reuse an existing result map.

@harawata harawata added enhancement Improve a feature or add a new feature and removed waiting for feedback labels Nov 21, 2019
@albertKrafter
Copy link

This is the problem. A TypeHandler cannot access the mappers, so it cannot invoke a properly configured ResultSetHandler on "value".

@albertKrafter
Copy link

albertKrafter commented Nov 21, 2019

It should be best if the "/mapper/resultMap/result" element could contain nested resultmaps linked to columns, e. g.:
<result column="MY_NESTED_CURSOR" property="listOfMyNestedThings" jdbcType="CURSOR" javaType="java.util.List" resultMap="MyNestedThingResult" />
where "listOfMyNestedThings" is of type "List<MyNestedThing>" and is a property of the object which "owns" the main "resultMap".

harawata added a commit to harawata/mybatis-3 that referenced this issue Jan 5, 2025
By specifying a special name NESTED_CURSOR to `resultSet` attribute,

Should fix mybatis#566
@harawata harawata linked a pull request Jan 5, 2025 that will close this issue
harawata added a commit to harawata/mybatis-3 that referenced this issue Jan 7, 2025
This probably worked in earlier versions, so these tests are to ensure backward compatibility.

mybatis#566 (comment)
harawata added a commit to harawata/mybatis-3 that referenced this issue Jan 7, 2025
This probably worked in earlier versions, so these tests are to ensure backward compatibility.

mybatis#566 (comment)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement Improve a feature or add a new feature
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants