sql - mybatis的动态字段insert和IFNULL结合where使用实践

物料日志表,这些是可以重复物料名称(mingchengguige)的子数据(详细)--> <!--其中期初库存字段插入时,不用判断是否为空,值为写好的子sql,--> <!--fachuleixing根据正常发出(出库单),破损发出(破损单),维修发出(维修单)--> <!--qichukucun和jieyushuliang不需要判定为空与否,直接新增(首先进行了计算)--> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.tansuo365.test1.bean.WuliaoKucunRiZhi" useGeneratedKeys="true"> insert into wuliaokucunrizhi <trim prefix="(" suffix=")" suffixOverrides=","> <if test="mingchengguige != null"> mingchengguige, </if> qichukucun, <if test="shourushuliang != null"> shourushuliang, </if> <if test="fachushuliang != null"> fachushuliang, </if> <if test="tuihuishuliang != null"> tuihuishuliang, </if> jieyushuliang, <if test="jiagongyigong != null"> jiagongyigong, </if> <if test="danwei != null"> danwei, </if> <if test="wuliaobianma != null"> wuliaobianma, </if> <if test="dalei != null"> dalei, </if> <if test="rukukufang != null"> rukukufang, </if> <if test="xiangmuweihu != null"> xiangmuweihu, </if> <if test="fachuleixing != null"> fachuleixing, </if> <if test="createtime != null"> createtime, </if> <if test="updatetime != null"> updatetime, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="mingchengguige != null"> #{mingchengguige}, </if> IFNULL((select jie.jieyushuliang from (select jieyushuliang from wuliaokucunrizhi <where> <if test="mingchengguige != null and mingchengguige != ‘‘"> and mingchengguige = #{mingchengguige} </if> <if test="rukukufang != null and rukukufang != ‘‘ "> and rukukufang = #{rukukufang} </if> <if test="dalei != null and dalei != ‘‘ "> and dalei = #{dalei} </if> <if test="xiangmuweihu != null and xiangmuweihu != ‘‘ "> and xiangmuweihu = #{xiangmuweihu} </if> and createtime &lt; (select now()) </where> order by createtime desc limit 1) jie),0), <if test="shourushuliang != null"> #{shourushuliang}, </if> <if test="fachushuliang != null"> #{fachushuliang}, </if> <if test="tuihuishuliang != null"> #{tuihuishuliang}, </if> IFNULL((select jie.jieyushuliang from (select jieyushuliang from wuliaokucunrizhi where createtime &lt;(select now()) order by createtime desc limit 1) jie),0) +IFNULL(#{shourushuliang},0) -IFNULL(#{fachushuliang},0) +IFNULL(#{tuihuishuliang},0), <if test="jiagongyigong != null"> #{jiagongyigong}, </if> <if test="danwei != null"> #{danwei}, </if> <if test="wuliaobianma != null"> #{wuliaobianma}, </if> <if test="dalei != null"> #{dalei}, </if> <if test="rukukufang != null"> #{rukukufang}, </if> <if test="xiangmuweihu != null"> #{xiangmuweihu}, </if> <if test="fachuleixing != null"> #{fachuleixing}, </if> <if test="createtime != null"> #{createtime}, </if> <if test="updatetime != null"> #{updatetime}, </if> </trim> </insert>

 

注意其中的

 IFNULL((select jie.jieyushuliang from
            (select jieyushuliang from wuliaokucunrizhi
            <where>
                <if test="mingchengguige != null and mingchengguige != ‘‘">
                    and mingchengguige = #{mingchengguige}
                </if>
                <if test="rukukufang != null and rukukufang != ‘‘ ">
                    and rukukufang = #{rukukufang}
                </if>
                <if test="dalei != null and dalei != ‘‘ ">
                    and dalei = #{dalei}
                </if>
                <if test="xiangmuweihu != null and xiangmuweihu != ‘‘ ">
                    and xiangmuweihu = #{xiangmuweihu}
                </if>
               and createtime &lt; (select now())
            </where>
            order by createtime desc limit 1) jie),0),

这段代码是获取上个时间点的结余数量作为新数据的期初库存数量的

同时还有jieyushuliang

            jieyushuliang,

 

而这段代码是计算了当时的结余数量

                IFNULL((select jie.jieyushuliang from
                (select jieyushuliang from wuliaokucunrizhi where createtime <(select now())
                order by createtime desc limit 1) jie),0)
                +IFNULL(#{shourushuliang},0)
                 -IFNULL(#{fachushuliang},0)
                +IFNULL(#{tuihuishuliang},0),

 

在下面代码

    <insert id="insertSelective" keyColumn="id" keyProperty="id"
            parameterType="com.tansuo365.test1.bean.WuliaoKucunRiZhi" useGeneratedKeys="true">
        insert into wuliaokucunrizhi
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="mingchengguige != null">
                mingchengguige,
            </if>
            qichukucun,

这里没有进行qichukucun字段的判空,而直接会选定这个字段要进行数据插入

 

执行结果

==>  Preparing: insert into wuliaokucunrizhi ( mingchengguige, qichukucun, shourushuliang, jieyushuliang, jiagongyigong, danwei, wuliaobianma, dalei, rukukufang, xiangmuweihu ) values ( ?, IFNULL((select jie.jieyushuliang from (select jieyushuliang from wuliaokucunrizhi WHERE mingchengguige = ? and rukukufang = ? and dalei = ? and xiangmuweihu = ? and createtime < (select now()) order by createtime desc limit 1) jie),0), ?, IFNULL((select jie.jieyushuliang from (select jieyushuliang from wuliaokucunrizhi where createtime <(select now()) order by createtime desc limit 1) jie),0) +IFNULL(?,0) -IFNULL(?,0) +IFNULL(?,0), ?, ?, ?, ?, ?, ? ) 
==> Parameters: 甲板(String), 甲板(String), A(String), 甲类(String), 甲项目1(String), 3.0(Double), 3.0(Double), null, null, 甲供(String), 吨(String), jb1101(String), 甲类(String), A(String), 甲项目1(String)
<==    Updates: 1

 

注意在这里的数值在数据库中都设定了默认值0

在获取不到期初库存(比如该物品第一条),和其它的字段数值时,会默认填0,也利于了结余的计算

 

sql - mybatis的动态字段insert和IFNULL结合where使用实践

标签：img   rom   第一条   efi   代码   src   维修   O365   一个