Mybatis的动态拼接条件

最新推荐文章于 2025-05-16 18:18:14 发布

原创最新推荐文章于 2025-05-16 18:18:14 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍MyBatis中实现复杂条件查询的方法及批量插入数据的技术细节，包括使用if、foreach等标签来构建动态SQL，适用于需要灵活查询与高效数据录入的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

官网的例子永远是最好的，切记切记！！
拼接条件

    <sql id="select_asset_where">
        <if test="accountType != null and accountType.size != 0" >
            and 
            <foreach collection="accountType" item="param" separator="OR" open="(" close=")">
                a.account_type = #{param}
            </foreach>
        </if>
    </sql>1
2
3
4
5
6
7
8

条件查询

<select id="selectAssetByCondition"
                parameterType="com.zemcho.controller.asset.dto.AssetConditionDto"   resultMap="AssetCondtitionResultMap">
        SELECT reg_code, asset_name, asset_type, metering_units, use_info, 
                expect_end_date, regist_man, regist_date, account_type, fee_item, 
                finance_bill_date, user, user_account, keeper, checker, 
                buyer, school_addr, account_book, acquire_way, asset_use_way, 
                write_off_date, asset_status_1, store_place, orginal_value, net_value, 
                number_value
        FROM tb_asset_regist_d a
        <if test="assetDepInfo != null" >
            , cfg_asset_dep_info b
        </if>
        <if test="assetTypeInfo != null" >
            , cfg_asset_type_info c
        </if>
        <where>
            <include refid="select_asset_where"></include>
        </where>
</select>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

批量插入

宁波好的整形医院http://www.iyestar.com/
宁波最好的整形医院http://www.iyestar.com/

 <!-- 批量插入 -->
    <!-- 批量插入生成的兑换码 -->
    <insert id ="insertBulk" parameterType="java.util.List" >
        <selectKey resultType ="java.lang.Integer" keyProperty= "id"
                order= "AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey >
        insert into `tb_basic_treatment_d`
        (<include refid="Base_Column_List" />,LOAD_TIME)
        values
        <foreach collection ="list" item="item" index= "index" separator =",">
            (
            #{item.name},
            #{item.teacherNumber},
            #{item.idNumber},
            #{item.year},
            #{item.annualWageIncomeYuan},
            #{item.fiveInsuranceAGold},
            #{item.loadTime}
            )
        </foreach >
    </insert >1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

普通查询

 <select id="selectByReaderNum" parameterType="string" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from tb_library_borrower_d
    where reader_id = #{num,jdbcType=VARCHAR} limit 1
  </select>1
2
3
4
5
6