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
这个语法很好理解,就是递归语法,从什么节点依次去找。。
Mybatis 递归查询
select * from goods_category where status=0 AND parentid=#{pid}
ORDER BY displayorder,goodscateid
mybatis 接口:
List 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 catelist;
……
}
mybatsi 调用存储过程:
mybatis 接口:
void addDep(@Param("dep") Department department);
xml中写法:
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})
解释:
注意statementType调用表示这是一个存储过程,mode=IN表示这是输入参数,mode=OUT表示这是输出参数,调用成功之后,在service中获取department的id和result字段,就能拿到相应的调用结果了。