mybatis动态sql和自定义结果集

新增

<insert id="addMaterialByComodity">
        insert INTO jsh_material
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id!=null">id,</if>
            <if test="name!=null">name,</if>
            <if test="standard!=null">standard,</if>
            <if test="model!=null">model,</if>
            <if test="remark!=null">remark,</if>
            <if test="color!=null">color,</if>
            <if test="unit!=null">unit,</if>
            <if test="weight!=null">weight,</if>
            <if test="expiryNum!=null">expiry_num,</if>
            <if test="enableSerialNumber!=null">enable_serial_number,</if>
            <if test="enableBatchNumber!=null">enable_batch_number,</if>
        </trim>
        <trim suffixOverrides="," prefix="values (" suffix=")">
            <if test="id!=null and id!=''">#{id},</if>
            <if test="name!=null and name!=''">#{name},</if>
            <if test="standard!=null and standard!=''">#{standard},</if>
            <if test="model!=null and model!=''">#{model},</if>
            <if test="remark!=null and remark!=''">#{remark},</if>
            <if test="color!=null and color!=''">#{color},</if>
            <if test="unit!=null and unit!=''">#{unit},</if>
            <if test="weight!=null and weight!=''">#{weight},</if>
            <if test="expiryNum!=null and expiryNum!=''">#{expiryNum},</if>
            <if test="enableSerialNumber!=null and enableSerialNumber!=''">#{enableSerialNumber},</if>
            <if test="enableBatchNumber!=null and enableBatchNumber!=''">#{enableBatchNumber},</if>
        </trim>
    </insert>

查询

    <select id="checkIsExist" resultType="java.lang.Integer">
        select count(1) from jsh_material m
        where ifnull(m.delete_flag,'0') !='1'
        <if test="name != null">
            and m.name = #{name}
        </if>
        <if test="model != null">
            and m.model = #{model}
        </if>
        <if test="color != null">
            and m.color = #{color}
        </if>
        <if test="standard != null">
            and m.standard = #{standard}
        </if>
        <if test="mfrs != null">
            and m.mfrs = #{mfrs}
        </if>
        <if test="otherField1 != null">
            and m.other_field1 = #{otherField1}
        </if>
        <if test="otherField2 != null">
            and m.other_field2 = #{otherField2}
        </if>
        <if test="otherField3 != null">
            and m.other_field3 = #{otherField3}
        </if>
        <if test="unit != null">
            and m.unit = #{unit}
        </if>
        <if test="unitId != null">
            and m.unit_id = #{unitId}
        </if>
        <if test="id != null">
            and m.id != #{id}
        </if>
    </select>

修改

    <update id="getUpdateItem">
        UPDATE jsh_depot_item
        <trim prefix="set" suffixOverrides="," suffix="WHERE id=#{id}">
            <if test="operNumber!=null and operNumber!=''">oper_number = #{operNumber},</if>
            <if test="unitPrice!=null and unitPrice!=''">unit_price=#{unitPrice},</if>
            <if test="allPrice!=null and allPrice!=''">all_price=#{allPrice},</if>
            <if test="taxRate!=null and taxRate!=''">tax_rate=#{taxRate},</if>
            <if test="taxLastMoney!=null and taxLastMoney!=''">tax_last_money=#{taxLastMoney},</if>
            <if test="remark!=null and remark!=''">remark=#{remark},</if>
        </trim>
    </update>

自定义结果集

    <resultMap id="DetailQList" type="com.xc.erp.model.JshDepotItem">
        <id property="id" column="id"/>
        <result column="MName" property="MName"/>
        <result column="MModel" property="MModel"/>
        <result column="MaterialUnitt" property="Company"/>
        <result column="MColor" property="MColor"/>
        <result column="MStandard" property="MStandard"/>
        <result column="MMfrs" property="MMfrs"/>
        <result column="MOtherField1" property="MOtherField1"/>
        <result column="MOtherField2" property="MOtherField2"/>
        <result column="MOtherField3" property="MOtherField3"/>
        <result column="enable_serial_number" property="enableSerialNumber"/>
        <result column="enable_batch_number" property="enableBatchNumber"/>
        <result column="DepotName" property="DepotName"/>
        <result column="AnotherDepotName" property="AnotherDepotName"/>
        <result column="barCode" property="barCode"/>
        <result column="purchase_decimal" property="purchaseDecimal"/>
    </resultMap>
    <select id="getDetailList" resultMap="DetailQList">
         select di.*,m.name MName,m.model MModel,m.unit MaterialUnitt,m.color MColor,m.standard MStandard,m.mfrs MMfrs,
        m.other_field1 MOtherField1,m.other_field2 MOtherField2,m.other_field3 MOtherField3,m.enable_serial_number, m.enable_batch_number,
        dp1.name DepotName,dp2.name AnotherDepotName, me.bar_code barCode, me.purchase_decimal
        from jsh_depot_item di
        left join jsh_material m on di.material_id=m.id  and ifnull(m.delete_flag,'0') !='1'
        left join jsh_material_extend me on me.id=di.material_extend_id  and ifnull(me.delete_Flag,'0') !='1'
        left join jsh_depot dp1 on di.depot_id=dp1.id and ifnull(dp1.delete_Flag,'0') !='1'
        left join jsh_depot dp2 on di.another_depot_id=dp2.id and ifnull(dp2.delete_Flag,'0') !='1'
        where di.header_id =#{headerId}
        and ifnull(di.delete_flag,'0') !='1'
        order by di.id asc
    </select>

package com.xc.erp.model;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

import io.swagger.annotations.ApiModel;
import lombok.Data;

/**
 * 单据子表
 *
 * @TableName jsh_depot_item
 */
@TableName(value = "jsh_depot_item")
@ApiModel(value = "单据子表", description = "单据子表")
@Data
public class JshDepotItem implements Serializable {
    /**
     * 主键
     */
    @TableId(type = IdType.AUTO)
    private Long id;

    /**
     * 表头Id
     */
    private Long headerId;

    /**
     * 商品Id
     */
    private Long materialId;

    /**
     * 商品扩展id
     */
    private Long materialExtendId;

    /**
     * 商品计量单位
     */
    private String materialUnit;

    /**
     * 多属性
     */
    private String sku;

    /**
     * 数量
     */
    private BigDecimal operNumber;

    /**
     * 基础数量，如kg、瓶
     */
    private BigDecimal basicNumber;

    /**
     * 单价
     */
    private BigDecimal unitPrice;

    /**
     * 采购单价
     */
    private BigDecimal purchaseUnitPrice;

    /**
     * 含税单价
     */
    private BigDecimal taxUnitPrice;

    /**
     * 金额
     */
    private BigDecimal allPrice;

    /**
     * 备注
     */
    private String remark;

    /**
     * 仓库ID
     */
    private Long depotId;

    /**
     * 调拨时，对方仓库Id
     */
    private Long anotherDepotId;

    /**
     * 税率
     */
    private BigDecimal taxRate;

    /**
     * 税额
     */
    private BigDecimal taxMoney;

    /**
     * 价税合计
     */
    private BigDecimal taxLastMoney;

    /**
     * 商品类型
     */
    private String materialType;

    /**
     * 序列号列表
     */
    private String snList;

    /**
     * 批号
     */
    private String batchNumber;

    /**
     * 有效日期
     */
    private Date expirationDate;

    /**
     * 关联明细id
     */
    private Long linkId;

    /**
     * 租户id
     */
    private Long tenantId;

    /**
     * 删除标记，0未删除，1删除
     */
    private String deleteFlag;
    /**
     * 名称
     */
    @TableField(exist = false)
    private String MName;
    /**
     * 型号
     */
    @TableField(exist = false)
    private String MModel;
    /**
     * 单位-单个
     */
    @TableField(exist = false)
    private String Company;


    /**
     * 颜色
     */
    @TableField(exist = false)
    private String MColor;
    /**
     * 规格
     */
    @TableField(exist = false)
    private String MStandard;

    /**
     * 制造商
     */
    @TableField(exist = false)
    private String MMfrs;
    /**
     * 自定义1
     */
    @TableField(exist = false)
    private String MOtherField1;
    /**
     * 自定义2
     */
    @TableField(exist = false)
    private String MOtherField2;
    /**
     * 自定义3
     */
    @TableField(exist = false)
    private String MOtherField3;

    /**
     * 是否开启序列号
     */
    @TableField(exist = false)
    private String enableSerialNumber;
    /**
     * 是否开启批号
     */
    @TableField(exist = false)
    private String enableBatchNumber;
    /**
     * 仓库名称
     */
    @TableField(exist = false)
    private String DepotName;

    /**
     * 仓库名称2
     */
    @TableField(exist = false)
    private String AnotherDepotName;
    /**
     * 商品条码
     */
    @TableField(exist = false)
    private String barCode;
    /**
     * 采购价价格
     */
    @TableField(exist = false)
    private double purchaseDecimal;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;