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>

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值