Oralce 递归sql
一、查询所有子节点
SELECT *
FROM district
START WITH NAME ='平昌县'
CONNECT BY PRIOR parent_id=ID
二、查询所有父节点
SELECT *
FROM district
START WITH NAME ='平昌县'
CONNECT BY PRIOR parent_id=ID
这个语法很好理解,就是递归语法,从什么节点依次去找。。
引用文献:https://www.cnblogs.com/Soprano/p/10659127.html
Mybatis 递归查询
<resultMap id="getSelf" type="net.chunxiao.vo.GoodsCategoryVo">
<id column="goodscateid" property="goodscateid"></id>
<result column="name" property="name"></result>
<collection property="catelist" select="getCategory" column="goodscateid"></collection>
<!--查到的cid作为下次的pid-->
</resultMap>
<select id="getCategory" resultMap="getSelf">
select * from goods_category where status=0 AND parentid=#{pid}
ORDER BY displayorder,goodscateid
mybatis 接口:
List<GoodsCategoryVo> getCategory(Integer pid);
实体类:
public class GoodsCategoryVo {
private Integer goodscateid;
private String name;
private Integer parentid;
private String description;
private Integer displayorder;
private Double commissionrate;
private Integer enabled;
private List<GoodsCategoryVo> catelist;
……
}
参考文献:https://blog.csdn.net/janet796/article/details/79500349
mybatsi 调用存储过程:
mybatis 接口:
void addDep(@Param("dep") Department department);
xml中写法:
<select id="addDep" statementType="CALLABLE">
call addDep(#{dep.name,mode=IN,jdbcType=VARCHAR},#{dep.parentId,mode=IN,jdbcType=INTEGER},#{dep.enabled,mode=IN,jdbcType=BOOLEAN},#{dep.result,mode=OUT,jdbcType=INTEGER},#{dep.id,mode=OUT,jdbcType=BIGINT})
</select>
解释:
注意statementType调用表示这是一个存储过程,mode=IN表示这是输入参数,mode=OUT表示这是输出参数,调用成功之后,在service中获取department的id和result字段,就能拿到相应的调用结果了。