Mybatis——主子表查询的Mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wz.project.proj.ProjMaterialsMain.mapper.ProjMaterialsMainMapper">
    
    <resultMap type="ProjMaterialsMain" id="ProjMaterialsMainResult">
        <result property="bNo"    column="b_no"    />
        <result property="bType"    column="b_type"    />
        <result property="bProductId"    column="b_product_id"    />
        <result property="bQuantity"    column="b_quantity"    />
        <result property="bPlaceOrderPeo"    column="b_place_order_peo"    />
        <result property="bSubmitTime"    column="b_submit_time"    />
        <result property="bStockId"    column="b_stock_id"    />
        <result property="bArrivalTime"    column="b_arrival_time"    />
        <result property="bIntegrationLeader"    column="b_integration_leader"    />
        <result property="bReviewer"    column="b_reviewer"    />
        <result property="bReviewTime"    column="b_review_time"    />
        <result property="bProj"    column="b_proj"    />
    </resultMap>

    <resultMap id="ProjMaterialsMainProjMaterialsBillResult" type="ProjMaterialsMain" extends="ProjMaterialsMainResult">
        <collection property="projMaterialsBillList" notNullColumn="b_no" javaType="java.util.List" resultMap="ProjMaterialsBillResult" />
    </resultMap>

    <resultMap type="ProjMaterialsBill" id="ProjMaterialsBillResult">
        <result property="bNo"    column="b_no"    />
        <result property="bProId"    column="b_pro_id"    />
        <result property="bType"    column="b_type"    />
        <result property="materialCode"    column="material_code"    />
        <result property="materialName"    column="material_name"    />
        <result property="materialModel"    column="material_model"    />
        <result property="quantity"    column="quantity"    />
        <result property="unit"    column="unit"    />
        <result property="total"    column="total"    />
        <result property="manufacturer"    column="manufacturer"    />
        <result property="arrivalState"    column="arrival_state"    />
        <result property="submitTime"    column="submit_time"    />
        <result property="arrivalTime"    column="arrival_time"    />
        <result property="placeOrderPeo"    column="place_order_peo"    />
        <result property="signedBy"    column="signed_by"    />
        <result property="remark"    column="remark"    />
        <result property="description"    column="description"    />
        <result property="pNo"    column="p_no"    />
    </resultMap>

    <sql id="selectProjMaterialsMainVo">
        select b_no, b_type, b_product_id,b_proj,b_quantity, b_place_order_peo, b_submit_time, b_stock_id, b_arrival_time, b_integration_leader, b_reviewer, b_review_time from proj_materials_main
    </sql>

    <select id="selectProjMaterialsMainList" parameterType="ProjMaterialsMain" resultMap="ProjMaterialsMainResult">
        <include refid="selectProjMaterialsMainVo"/>
        <where>  
            <if test="bNo != null "> and b_no = #{bNo}</if>
            <if test="bType != null  and bType != ''"> and b_type = #{bType}</if>
            <if test="bProductId != null  and bProductId != ''"> and b_product_id = #{bProductId}</if>
            <if test="bQuantity != null "> and b_quantity = #{bQuantity}</if>
            <if test="bPlaceOrderPeo != null  and bPlaceOrderPeo != ''"> and b_place_order_peo = #{bPlaceOrderPeo}</if>
            <if test="bSubmitTime != null  and bSubmitTime != ''"> and b_submit_time = #{bSubmitTime}</if>
            <if test="bStockId != null  and bStockId != ''"> and b_stock_id = #{bStockId}</if>
            <if test="bArrivalTime != null  and bArrivalTime != ''"> and b_arrival_time = #{bArrivalTime}</if>
            <if test="bIntegrationLeader != null  and bIntegrationLeader != ''"> and b_integration_leader = #{bIntegrationLeader}</if>
            <if test="bReviewer != null  and bReviewer != ''"> and b_reviewer = #{bReviewer}</if>
            <if test="bReviewTime != null  and bReviewTime != ''"> and b_review_time = #{bReviewTime}</if>
            <if test="bProj != null  and bProj != ''"> and b_proj = #{bProj}</if>
        </where>
    </select>
    
    <select id="selectProjMaterialsMainById" parameterType="Long" resultMap="ProjMaterialsMainProjMaterialsBillResult">
        select a.b_no, a.b_type, a.b_product_id, a.b_quantity, a.b_place_order_peo, a.b_submit_time, a.b_stock_id, a.b_arrival_time, a.b_integration_leader, a.b_reviewer, a.b_review_time,
            b.b_no, b.b_pro_id,a.b_proj, b.b_type, b.material_code, b.material_name, b.material_model, b.quantity, b.unit, b.total, b.manufacturer, b.arrival_state, b.submit_time, b.arrival_time, b.place_order_peo, b.signed_by, b.remark, b.description, b.p_no
        from proj_materials_main a
        left join proj_materials_bill b on b.p_no = a.b_no
        where a.b_no = #{bNo}
    </select>
        
    <insert id="insertProjMaterialsMain" parameterType="ProjMaterialsMain">
        insert into proj_materials_main
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bNo != null">b_no,</if>
            <if test="bType != null">b_type,</if>
            <if test="bProductId != null">b_product_id,</if>
            <if test="bQuantity != null">b_quantity,</if>
            <if test="bPlaceOrderPeo != null">b_place_order_peo,</if>
            <if test="bSubmitTime != null">b_submit_time,</if>
            <if test="bStockId != null">b_stock_id,</if>
            <if test="bArrivalTime != null">b_arrival_time,</if>
            <if test="bIntegrationLeader != null">b_integration_leader,</if>
            <if test="bReviewer != null">b_reviewer,</if>
            <if test="bReviewTime != null">b_review_time,</if>
            <if test="bProj != null">b_proj,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="bNo != null">#{bNo},</if>
            <if test="bType != null">#{bType},</if>
            <if test="bProductId != null">#{bProductId},</if>
            <if test="bQuantity != null">#{bQuantity},</if>
            <if test="bPlaceOrderPeo != null">#{bPlaceOrderPeo},</if>
            <if test="bSubmitTime != null">#{bSubmitTime},</if>
            <if test="bStockId != null">#{bStockId},</if>
            <if test="bArrivalTime != null">#{bArrivalTime},</if>
            <if test="bIntegrationLeader != null">#{bIntegrationLeader},</if>
            <if test="bReviewer != null">#{bReviewer},</if>
            <if test="bReviewTime != null">#{bReviewTime},</if>
            <if test="bProj != null">#{bProj},</if>
         </trim>
    </insert>

    <update id="updateProjMaterialsMain" parameterType="ProjMaterialsMain">
        update proj_materials_main
        <trim prefix="SET" suffixOverrides=",">
            <if test="bType != null">b_type = #{bType},</if>
            <if test="bProductId != null">b_product_id = #{bProductId},</if>
            <if test="bQuantity != null">b_quantity = #{bQuantity},</if>
            <if test="bPlaceOrderPeo != null">b_place_order_peo = #{bPlaceOrderPeo},</if>
            <if test="bSubmitTime != null">b_submit_time = #{bSubmitTime},</if>
            <if test="bStockId != null">b_stock_id = #{bStockId},</if>
            <if test="bArrivalTime != null">b_arrival_time = #{bArrivalTime},</if>
            <if test="bIntegrationLeader != null">b_integration_leader = #{bIntegrationLeader},</if>
            <if test="bReviewer != null">b_reviewer = #{bReviewer},</if>
            <if test="bReviewTime != null">b_review_time = #{bReviewTime},</if>
            <if test="bProj != null">b_proj = #{bProj},</if>
        </trim>
        where b_no = #{bNo}
    </update>

    <delete id="deleteProjMaterialsMainById" parameterType="Long">
        delete from proj_materials_main where b_no = #{bNo}
    </delete>

    <delete id="deleteProjMaterialsMainByIds" parameterType="String">
        delete from proj_materials_main where b_no in 
        <foreach item="bNo" collection="array" open="(" separator="," close=")">
            #{bNo}
        </foreach>
    </delete>
    
    <delete id="deleteProjMaterialsBillByPNos" parameterType="String">
        delete from proj_materials_bill where p_no in 
        <foreach item="pNo" collection="array" open="(" separator="," close=")">
            #{pNo}
        </foreach>
    </delete>

    <delete id="deleteProjMaterialsBillByPNo" parameterType="Long">
        delete from proj_materials_bill where p_no = #{pNo}
    </delete>

    <insert id="batchProjMaterialsBill">
        insert into proj_materials_bill( b_no, b_pro_id, b_type, material_code, material_name, material_model, quantity, unit, total, manufacturer, arrival_state, submit_time, arrival_time, place_order_peo, signed_by, remark, description, p_no) values
		<foreach item="item" index="index" collection="list" separator=",">
            ( #{item.bNo}, #{item.bProId}, #{item.bType}, #{item.materialCode}, #{item.materialName}, #{item.materialModel}, #{item.quantity}, #{item.unit}, #{item.total}, #{item.manufacturer}, #{item.arrivalState}, #{item.submitTime}, #{item.arrivalTime}, #{item.placeOrderPeo}, #{item.signedBy}, #{item.remark}, #{item.description}, #{item.pNo})
        </foreach>
    </insert>

  

</mapper>