需求期望使用mybatis的级连查询得到树形结构数据返回, 代码如下:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DocumentTypeTreeNode {
@ApiModelProperty("类型id")
@JSONField(name = "value")
private Long id;
@ApiModelProperty("类型名称")
@JSONField(name = "label")
private String title;
@ApiModelProperty("子类型")
private List<DocumentTypeTreeNode> children;
}
<resultMap id="treeNode" type="com.zvan.doc.entity.DocumentTypeTreeNode">
<id column="id" property="id"/>
<result column="title" property="title"/>
<collection column="id" property="children" select="selectTree"/>
</resultMap>
<select id="selectTree" resultMap="treeNode">
select *
from source_document_classification
<where>
<if test="id == null">
parent_id is null
</if>
<if test="id != null and id != null">
parent_id = #{id}
</if>
</where>
</select>
结果接口调用出现问题:
Error querying database. Cause: java.sql.SQLException: Operation not allowed after ResultSet closed
错误提示不能在ResultSet被关闭后继续操作, 说明这次级连查询出现在同一个statement中,并且一次查询的resultSet被关闭后还期望对其进行操作。
那找问题所在的思路就很清晰了: 我们的statement被复用了, 有印象的小伙伴可能记得mybatis中存在三种Executor:
- SimpleExecutor
- ReuseExecutor
- BatchExecutor
Reuse和Batch都有复用连接的功能, 问题可能就出现在这儿, 题主将项目配置中的REUSE改为了BATCH, 问题果然得到了解决, 本着打破沙锅问到底的精神,我们看看是为什么。
以下为ReuseExecutor部分源码:
@Override
public <E> List<E> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
Configuration configuration = ms.getConfiguration();
StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, resultHandler, boundSql);
Statement stmt = prepareStatement(handler, ms.getStatementLog());
return handler.query(stmt, resultHandler);
}
以下为BatchExecutor部分源码
@Override
public <E> List<E> doQuery(MappedStatement ms, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql)
throws SQLException {
Statement stmt = null;
try {
flushStatements();
Configuration configuration = ms.getConfiguration();
StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameterObject, rowBounds, resultHandler, boundSql);
Connection connection = getConnection(ms.getStatementLog());
stmt = handler.prepare(connection, transaction.getTimeout());
handler.parameterize(stmt);
return handler.query(stmt, resultHandler);
} finally {
closeStatement(stmt);
}
}
他们的区别很简单: 他们都尝试对同一sql进行复用,不过不同的是: Reuse复用的是Statement, 而Batch复用的是Connection。 因此在Reuse情况下,会发生以上错误,而Batch中不会出现。