Mybatis sql if判读条件数字 Mybatis 动态sql if 判读条件等于一个数字的案例
゛Smlie。 人气:0在Mybatis中 mapper中
boolean updateRegisterCompanyFlag(@Param(value = "companyId") String companyId,
@Param(value = "flag") String flag);
传入的flag类型为String,但在mapper.XML中进行判断是下意识地以为判断的值要加上引号
<if test=" '4' == flag "> , LAST_CHECK_TIME = sysdate </if>
但是这样写,传入flag=“4”后,这个if语句一直不成立s
ql没有拼接。后来网上查了一番后将 test 中的 4 的引号去除才成功。
<if test=" 4 == flag "> , LAST_CHECK_TIME = sysdate </if>
记录一下,以防忘记。。。
补充知识:Mybatis中xml的有关if test以及==的用法
Mybatis中,通常都要在mapper.xml中写复杂的sql语句,结合页面的查询筛选条件,需要在sql语句中加入
<if test=""></if>等的判断。而这test 后面的字段也是来自jsp或者html页面,但是不是随便书写的。
与页面都要一一对应,除此之外,还需要在实体类中有定义。
另外,mybatis中 <if test=>等于的条件怎么写?
注意 仅仅需要将双引号和单引号的位置换一下即可!!!
<sql id="queryData"> select '集团' src_dbct_cd, substr(stats_mo,1,4) year, substr(stats_mo,5,6) month, sum(all_zb_cars) all_zb_cars,sum(all_kb_cars) all_kb_cars,sum(all_wd_cars) all_wd_cars, sum(all_zb_cars+all_kb_cars+all_wd_cars) all_cars_total, sum(round(all_zb_exp/10000,2)) all_zb_exp,sum(round(all_kb_exp/10000,2)) all_kb_exp,sum(round(all_wd_exp/10000,2)) all_wd_exp, sum(round(all_zb_exp/10000+all_kb_exp/10000+all_wd_exp/10000,2)) all_exp_total, sum(zb_cars) zb_cars,sum(kb_cars) kb_cars, sum(zb_cars+kb_cars) cars_total, sum(round(zb_net_wgt/10000000,2)) zb_net_wgt,sum(round(kb_net_wgt/10000000,2)) kb_net_wgt, sum(round(ifnull(zb_net_wgt/10000000,0)+ifnull(kb_net_wgt/10000000,0),2)) wgt_total, sum(round(zb_exp/10000,2)) zb_exp,sum(round(kb_exp/10000,2)) kb_exp, sum(round(zb_exp/10000+kb_exp/10000,2)) exp_total, sum(zb_lod_mnt) zb_lod_mnt,sum(kb_lod_mnt) kb_lod_mnt, sum(ifnull(zb_lod_mnt,0)+ifnull(kb_lod_mnt,0)) lod_mnt_total, sum(zb_wait_mnt) zb_wait_mnt,sum(kb_wait_mnt) kb_wait_mnt, sum(ifnull(zb_wait_mnt,0)+ifnull(kb_wait_mnt,0)) wait_mnt_total, concat(round(sum(zb_dlyd_cars/zb_cars)*100,2),'%') zb_dlyd_pt, concat(round(sum(kb_dlyd_cars/kb_cars)*100,2),'%') kb_dlyd_pt, concat(round(sum(ifnull(zb_dlyd_cars/zb_cars,0)+ifnull(kb_dlyd_cars/kb_cars,0))*100,2),'%') dlyd_pt_total, concat(round(sum(zb_frqc_dlyd_cars/zb_cars)*100,2),'%') zb_frqc_dlyd, concat(round(sum(kb_frqc_dlyd_cars/kb_cars)*100,2),'%') kb_frqc_dlyd, concat(round(sum(ifnull(zb_frqc_dlyd_cars/zb_cars,0)+ifnull(kb_frqc_dlyd_cars/kb_cars,0))*100,2),'%') frqc_dlyd_total, sum(zb_dlyd_mnt) zb_dlyd_mnt,sum(kb_dlyd_mnt) kb_dlyd_mnt, sum(ifnull(zb_dlyd_mnt,0)+ifnull(kb_dlyd_mnt,0)) dlyd_mnt_total, round(ifnull(sum(zb_sys_mlg)/sum(zb_stip_mnt),0),2) zb_stip_speed, round(ifnull(sum(kb_sys_mlg)/sum(kb_stip_mnt),0),2) kb_stip_speed, (round(ifnull(sum(zb_sys_mlg)/sum(zb_stip_mnt),0)+ifnull(sum(kb_sys_mlg)/sum(kb_stip_mnt),0),2)) stip_speed_total, round(ifnull(sum(zb_est_mlg)/sum(zb_actl_mnt),0),2) zb_actl_speed, round(ifnull(sum(kb_est_mlg)/sum(kb_actl_mnt),0),2) kb_actl_speed, (round(ifnull(sum(zb_est_mlg)/sum(zb_actl_mnt),0)+ifnull(sum(kb_est_mlg)/sum(kb_actl_mnt),0),2)) actl_speed_total, sum(round(zb_pnlt/10000,2)) zb_pnlt,sum(round(kb_pnlt/10000,2)) kb_pnlt, sum(ifnull(round(zb_pnlt/10000,2),0)+ifnull(round(kb_pnlt/10000,2),0)) pnlt_total, sum(zb_uld_wait_mnt) zb_uld_wait_mnt,sum(kb_uld_wait_mnt) kb_uld_wait_mnt,sum(wd_uld_wait_mnt) wd_uld_wait_mnt, round((ifnull(sum(zb_uld_wait_mnt),0)+ifnull(sum(kb_uld_wait_mnt),0)+ifnull(sum(wd_uld_wait_mnt),0))/ (ifnull(sum(all_zb_cars),0)+ifnull(sum(all_kb_cars),0)+ifnull(sum(all_wd_cars),0)),2) uld_wait_mnt_total, sum(zb_uld_mnt) zb_uld_mnt,sum(kb_uld_mnt) kb_uld_mnt,sum(wd_uld_mnt) wd_uld_mnt, sum(ifnull(zb_uld_mnt,0)+ifnull(kb_uld_mnt,0)+ifnull(wd_uld_mnt,0)) uld_mnt_total, concat(round(sum(zb_brk_cars/zb_mlg/10000)*100,2),'%') zb_brk_cars, concat(round(sum(kb_brk_cars/kb_mlg/10000)*100,2),'%') kb_brk_cars, concat(round(sum(wd_brk_cars/wd_mlg/10000)*100,2),'%') wd_brk_cars, concat((round(sum(ifnull(zb_brk_cars/zb_mlg/10000,0)+ifnull(kb_brk_cars/kb_mlg/10000,0)+ifnull(wd_brk_cars/wd_mlg/10000,0))*100,2)),'%') brk_cars_total, concat(round(sum(zb_accdt_cars/zb_mlg/10000)*100,2),'%') zb_accdt_cars, concat(round(sum(kb_accdt_cars/kb_mlg/10000)*100,2),'%') kb_accdt_cars, concat(round(sum(wd_accdt_cars/wd_mlg/10000)*100,2),'%') wd_accdt_cars, concat((round(sum(ifnull(zb_accdt_cars/zb_mlg/10000,0)+ifnull(kb_accdt_cars/kb_mlg/10000,0)+ifnull(wd_accdt_cars/wd_mlg/10000,0))*100,2)),'%') accdt_cars_total, concat(round(sum(actl_tms/stip_tms)*100,2),'%') tms_total from bdrpt.kd_car_trans_mont_stats where 1=1 <if test="stats_mo != null and stats_mo != ''" > and concat(SUBSTR(stats_mo,1,4),'-',SUBSTR(stats_mo,5,6))=#{stats_mo} </if> <if test='SRC_DBCT_CD == "2"'> and src_dbct_cd = '分拨' </if> GROUP BY substr(stats_mo,5,6) UNION select l.location_name src_dbct_cd, substr(stats_mo,1,4) year, substr(stats_mo,5,6) month, all_zb_cars,all_kb_cars,all_wd_cars, (all_zb_cars+all_kb_cars+all_wd_cars) all_cars_total, round(all_zb_exp/10000,2) all_zb_exp,round(all_kb_exp/10000,2) all_kb_exp,round(all_wd_exp/10000,2) all_wd_exp, round(all_zb_exp/10000+all_kb_exp/10000+all_wd_exp/10000,2) all_exp_total, zb_cars,kb_cars, (zb_cars+kb_cars) cars_total, round(zb_net_wgt/10000000,2) zb_net_wgt,round(kb_net_wgt/10000000,2) kb_net_wgt, round(ifnull(zb_net_wgt/10000000,0)+ifnull(kb_net_wgt/10000000,0),2) wgt_total, round(zb_exp/10000,2) zb_exp,round(kb_exp/10000,2) kb_exp, round(zb_exp/10000+kb_exp/10000,2) exp_total, zb_lod_mnt,kb_lod_mnt, (ifnull(zb_lod_mnt,0)+ifnull(kb_lod_mnt,0)) lod_mnt_total, zb_wait_mnt,kb_wait_mnt, (ifnull(zb_wait_mnt,0)+ifnull(kb_wait_mnt,0)) wait_mnt_total, concat(round(zb_dlyd_cars/zb_cars*100,2),'%') zb_dlyd_pt, concat(round(kb_dlyd_cars/kb_cars*100,2),'%') kb_dlyd_pt, concat(round((ifnull(zb_dlyd_cars/zb_cars,0)+ifnull(kb_dlyd_cars/kb_cars,0))*100,2),'%') dlyd_pt_total, concat(round(zb_frqc_dlyd_cars/zb_cars*100,2),'%') zb_frqc_dlyd, concat(round(kb_frqc_dlyd_cars/kb_cars*100,2),'%') kb_frqc_dlyd, concat(round((ifnull(zb_frqc_dlyd_cars/zb_cars,0)+ifnull(kb_frqc_dlyd_cars/kb_cars,0))*100,2),'%') frqc_dlyd_total, zb_dlyd_mnt,kb_dlyd_mnt, (ifnull(zb_dlyd_mnt,0)+ifnull(kb_dlyd_mnt,0)) dlyd_mnt_total, round(ifnull(sum(zb_sys_mlg)/sum(zb_stip_mnt),0),2) zb_stip_speed, round(ifnull(sum(kb_sys_mlg)/sum(kb_stip_mnt),0),2) kb_stip_speed, (round(ifnull(sum(zb_sys_mlg)/sum(zb_stip_mnt),0)+ifnull(sum(kb_sys_mlg)/sum(kb_stip_mnt),0),2)) stip_speed_total, round(ifnull(sum(zb_est_mlg)/sum(zb_actl_mnt),0),2) zb_actl_speed, round(ifnull(sum(kb_est_mlg)/sum(kb_actl_mnt),0),2) kb_actl_speed, (round(ifnull(sum(zb_est_mlg)/sum(zb_actl_mnt),0)+ifnull(sum(kb_est_mlg)/sum(kb_actl_mnt),0),2)) actl_speed_total, round(zb_pnlt/10000,2) zb_pnlt,round(kb_pnlt/10000,2) kb_pnlt, (ifnull(round(zb_pnlt/10000,2),0)+ifnull(round(kb_pnlt/10000,2),0)) pnlt_total, zb_uld_wait_mnt,kb_uld_wait_mnt,wd_uld_wait_mnt, round((ifnull(sum(zb_uld_wait_mnt),0)+ifnull(sum(kb_uld_wait_mnt),0)+ifnull(sum(wd_uld_wait_mnt),0))/ (ifnull(sum(all_zb_cars),0)+ifnull(sum(all_kb_cars),0)+ifnull(sum(all_wd_cars),0)),2) uld_wait_mnt_total, zb_uld_mnt,kb_uld_mnt,wd_uld_mnt, (ifnull(zb_uld_mnt,0)+ifnull(kb_uld_mnt,0)+ifnull(wd_uld_mnt,0)) uld_mnt_total, concat(round(zb_brk_cars/zb_mlg/10000*100,2),'%') zb_brk_cars, concat(round(kb_brk_cars/kb_mlg/10000*100,2),'%') kb_brk_cars, concat(round(wd_brk_cars/wd_mlg/10000*100,2),'%') wd_brk_cars, concat((round((ifnull(zb_brk_cars/zb_mlg/10000,0)+ifnull(kb_brk_cars/kb_mlg/10000,0)+ifnull(wd_brk_cars/wd_mlg/10000,0))*100,2)),'%') brk_cars_total, concat(round(zb_accdt_cars/zb_mlg/10000*100,2),'%') zb_accdt_cars, concat(round(kb_accdt_cars/kb_mlg/10000*100,2),'%') kb_accdt_cars, concat(round(wd_accdt_cars/wd_mlg/10000*100,2),'%') wd_accdt_cars, concat((round((ifnull(zb_accdt_cars/zb_mlg/10000,0)+ifnull(kb_accdt_cars/kb_mlg/10000,0)+ifnull(wd_accdt_cars/wd_mlg/10000,0))*100,2)),'%') accdt_cars_total, concat(round(actl_tms/stip_tms*100,2),'%') tms_total from bdrpt.kd_car_trans_mont_stats t LEFT JOIN bdkdbase.location l on t.src_dbct_cd = l.location_xid where 1=1 <if test="stats_mo != null and stats_mo != ''" > and concat(SUBSTR(stats_mo,1,4),'-',SUBSTR(stats_mo,5,6))=#{stats_mo} </if> <if test='SRC_DBCT_CD == "1"'> and src_dbct_cd = '集团' </if> <if test="SRC_DBCT_NM != null and SRC_DBCT_NM != ''"> and src_dbct_cd = #{SRC_DBCT_NM} </if> GROUP BY src_dbct_cd,substr(stats_mo,5,6) </sql> <!-- 查询数据 --> <select id="pageQuery" resultType="map"> <include refid="queryData"></include> </select>
以上这篇Mybatis 动态sql if 判读条件等于一个数字的案例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。
加载全部内容