参照网上的资料,在自己的项目中实际运用可行,记在这里,以防遗忘!
数据表结构
<resultMap type="**.**.**.OrgVO" id="orgMap">
<id property="orgId" column="ORG_ID" jdbcType="VARCHAR"/>
<result property="orgNo" column="ORG_NO" jdbcType="VARCHAR"/>
<result property="orgName" column="ORG_NAME" jdbcType="VARCHAR"/>
<result property="orgLevel" column="ORG_LEVEL" jdbcType="INTEGER"/>
<result property="parOrgId" column="PAR_ORG_ID" jdbcType="VARCHAR"/> <!-- 父机构ID -->
<result property="state" column="STATE" jdbcType="INTEGER"/>
</resultMap>
1、批量插入
<insert id="addOrgs" parameterType="java.util.List">
insert into DIM_SYS_ORG
(
ORG_ID,
ORG_NO,
ORG_NAME,
ORG_LEVEL,
PAR_ORG_ID,
STATE
)
<foreach collection="list" item="item" index="index" separator="union all">
(select
#{item.orgId,jdbcType=VARCHAR},
#{item.orgNo,jdbcType=VARCHAR},
#{item.orgName,jdbcType=VARCHAR},
#{item.orgLevel,jdbcType=INTEGER},
#{item.parOrgId,jdbcType=VARCHAR},
#{item.state,jdbcType=INTEGER}
from dual
)
</foreach>
</insert>
2、批量更新
<update id="updateOrgs" parameterType="java.util.List">
begin
<foreach collection="list" item="item" separator=";">
update
DIM_SYS_ORG
set
ORG_NO = #{item.orgNo,jdbcType=VARCHAR},
ORG_NAME =#{item.orgName,jdbcType=VARCHAR},
ORG_LEVEL = #{item.orgLevel,jdbcType=INTEGER},
PAR_ORG_ID = #{item.parOrgId,jdbcType=VARCHAR},
STATE = #{item.state,jdbcType=INTEGER}
where
ORG_ID = #{item.orgId,jdbcType=VARCHAR}
</foreach>
;end;
</update>
3、批量删除,与MySql等一致
<delete id="delOrgs" parameterType="java.util.List">
delete from DIM_SYS_ORG
where
ORG_ID
in
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
4、树查询
使用oracle提供的connect by结构化查询进行树查询,基本语法如下:
select ... from tablename
where 条件3
start with 条件1
connect by prior 条件2;
注:若prior缺省:则只能查询到符合条件的起始行,并不进行递归查询;
注:使用时需特别注意条件的关系,prior后面的条件顺序影响到查找方向
1、通过根节点遍历子节点,查询整个树,从树干到树叶
select * from DIM_SYS_ORG where 1=1 start with id=1 connect by prior ORG_ID= PAR_ORG_ID
2、通过子节点追溯根节点,从树叶到树干
select * from test where 1=1 start with id=1 connect by prior PAR_ORG_ID= ORG_ID
实际运用(查找对应id的机构及其子机构ID):
<select id="queryOrgIds" parameterType="java.util.List" resultType="java.lang.String">
select ORG_ID from DIM_SYS_ORG
start with
<foreach collection="list" item="item" index="index" separator="OR">
ORG_ID = #{item.orgId}
</foreach>
connect by prior
ORG_ID = PAR_ORG_ID
</select>
注:Mysql并没有提供树查询机制,若树的深度没有超过4,则可用left join来实现;其他情况就需要使用储存过程或程序代码来实现了!