mapper文件相关-小记

1. java代码编写sql语句而不需要进入…Mapper.xml文件里编写

即：MyBatis 注解使用动态SQL

在注解上不能直接使用动态的SQL，需要在其前后加入 <script>。举个栗子：

@Select("<script> " +
"SELECT id, name, email,password " +
"FROM user " +
" <where> " +
" <if test=\"email != null\">id=#{email}</if> " +
" <if test=\"name != null\"> AND name=#{name}</if> " +
" </where> " +
" </script> ")

同时 对于LIKE 也是不能够直接使用的 可以借助concat函数实现。e.g.：

@Select("SELECT name from user WHERE email LIKE concat(#{prefix},'%') limit 5")



2. java代码中取绝对值：Math.abs(-3.5)=3.5

mapper.xml文件中(oracle数据库)：直接用函数abs()即取数值的绝对值




3. mapper文件中定义变量与定义映射关系：

定义变量<sql>—>引用变量：<include refid="head_count_sql" />

<sql id="head_count_sql">
join f55n431d fd on f4311.pddcto = fd.lxdcto3
join f4301 on f4311.pddoco = f4301.phdoco and f4311.pddcto = f4301.phdcto and f4311.pdkcoo = f4301.phkcoo
join f4101 on f4311.pditm = f4101.imitm
join (select * from pa_display_rule where display_classify = 'BRANCH' and work_code = #{work_code}) pb on trim(f4311.pdpodc02) = pb.display_code
left join (select * from pa_display_rule where display_classify = 'STANDARD' and work_code = #{work_code}) ps on trim(pdpdp1) = ps.display_code
left join (select * from pa_display_rule where display_classify = 'CUSTOM' and work_code = #{work_code}) pc on trim(pdpdp3) = pc.display_code
left join (select order_number,order_type,order_company from pa_purchaseorder_paycondition group by order_number,order_type,order_company ) pqu
on f4311.pddoco = pqu.order_number and f4311.pddcto = pqu.order_type and f4311.pdkcoo = pqu.order_company
where (ps.display_code is not null or pc.display_code is not null)
</sql>


<select id="findPayBranchHead" parameterType="java.util.Map" resultType="com.harmontronics.erp.po.OrderTotalPO">
select code,sum(total) total from (
select pb.display_code code,count(pddoco) total from f4311
<include refid="head_count_sql" />
and pdnxtr = '220' and pqu.order_number is null
<if test="hd_type == 'H'.toString() ">
group by f4311.pddoco,f4311.pddcto,f4311.pdkcoo,pb.display_code
</if>
<if test="hd_type == 'D'.toString() ">
group by pb.display_code
</if>
) group by code
</select>


定义映射关系<resultMap>—>引用：resultMap=”…(id名)” />

<resultMap id="BaseResultMap" type="com.mmall.pojo.Shipping">
<constructor>
<idArg column="id" javaType="java.lang.Integer" jdbcType="INTEGER" />
<arg column="user_id" javaType="java.lang.Integer" jdbcType="INTEGER" />
<arg column="receiver_name" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="receiver_phone" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="receiver_mobile" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="receiver_province" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="receiver_city" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="receiver_district" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="receiver_address" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="receiver_zip" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="create_time" javaType="java.util.Date" jdbcType="TIMESTAMP" />
<arg column="update_time" javaType="java.util.Date" jdbcType="TIMESTAMP" />
</constructor>
</resultMap>

<sql id="Base_Column_List">
id, user_id, receiver_name, receiver_phone, receiver_mobile, receiver_province, receiver_city,
receiver_district, receiver_address, receiver_zip, create_time, update_time
</sql>

<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from mmall_shipping
where id = #{id,jdbcType=INTEGER}
</select>



4. mapper文件中：row_number() over()用法举例：

<select id="findFixPriceInfoByCode" parameterType="java.util.List" resultType="com.harmontronics.erp.po.MaterialPO">
select * from
(
select imlitm,imdsc1,imuom1,mfr_part_number,imatprn,imprp1,imprp3,
provider_code,provider_name,promise_date,num_segpoints,unit_price,currency_code,
row_number()over(partition by imlitm order by unit_price) [AS] colnum from
(
select trim(B.imlitm) imlitm,trim(imdsc1) imdsc1,trim(imuom1) imuom1,
(trim(imdsc2) || (case when trim(imsrtx) = '@' then '' else to_char(trim(imsrtx)) end )) mfr_part_number,
trim(imatprn) imatprn,trim(imprp1) imprp1,trim(imprp3) imprp3,
provider_code,trim(abalph) provider_name,promise_date,num_segpoints,unit_price,currency_code
from PA_MATERIAL_DEAL A join f0101 on A.provider_code = trim(f0101.aban8)
join f4101 B on A.imlitm = trim(B.imlitm)
where abat1 in ('V','O','C') and abac28 != '01'
and (A.imlitm,A.currency_code) in
<foreach collection="portalNoCalApplyOrderVOList" item="item" index="index" separator="union" open="(" close=")">
(
select
#{item.imlitm},
#{item.currency_code}
from dual
)
</foreach>
)
)
where colnum = 1
</select>