Mybatis动态SQL(详细介绍)

自述:此篇文章主要记录了本人学习Mybatis的日常,在CSDN上发表,一是为了方便今后回顾,二也是想分享给有需要的人。

看完此文章主要实现的功能有:

1.动态查询

2.批量新增

3.批量删除

4.动态修改

目录

1.什么是动态SQL

2.动态SQL的作用

3.动态SQL的常用标签

4.数据库And实体类

5.if

6.where

7.choose

8.foreach

1.实现批量新增(List集合)

2.实现批量删除(数组) 

9.set

1.实现动态修改

10.trim

1.属性及作用 

2.优化代码


1.什么是动态SQL

MyBatis中的动态SQL是一种可以根据不同条件生成不同SQL语句的技术。它允许我们在映射文件中编写灵活的SQL语句,以便根据参数的不同情况来动态生成SQL语句。这种灵活性使得我们能够根据应用程序的需求来构建动态的查询语句。

2.动态SQL的作用

动态SQL是根据不同条件和需求,动态生成SQL语句的一种技术。它的作用主要有以下几点:

  1. 条件灵活:使用动态SQL可以根据不同的条件生成不同的SQL语句,使得查询、更新或删除数据时能够根据具体情况进行灵活的处理。

  2. 查询优化:有时候在编写静态SQL语句时难以预料到查询条件的变化,而使用动态SQL可以根据运行时的条件动态调整查询语句,从而更好地适应实际情况,提高查询性能。

  3. 动态表名和字段名:有时候需要根据不同的场景来操作不同的表或字段,这时候就可以利用动态SQL来动态构建表名和字段名,实现灵活性和扩展性。

  4. 防止SQL注入:通过使用参数化查询或者绑定变量的方式来构建动态SQL,可以有效防止SQL注入攻击,提升系统的安全性。

3.动态SQL的常用标签

动态SQL的常用标签及作用
常用标签作用
if根据指定的条件判断是否包含某部分SQL代码,使得SQL语句在运行时更具灵活性。
where生成动态的WHERE子句,只有满足条件时才包含WHERE子句,避免不必要的WHERE关键字。
choose根据不同的条件选择执行不同的SQL片段,实现类似于switch-case语句的功能。
foreach对集合进行循环,并在SQL语句中使用循环的结果,可以用于动态构建IN或VALUES子句。
set生成动态的SET子句,只有满足条件时才包含SET子句,用于动态更新表中的字段。
trim对SQL语句进行修剪和重组,去掉多余的AND或OR等,以便根据不同的条件动态生成合适的SQL语句。

4.数据库And实体类

CREATE TABLE `t_supplier` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `supCode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商编码',
  `supName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商名称',
  `supDesc` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商详细描述',
  `supContact` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '供应商联系人',
  `supPhone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '联系电话',
  `supAddress` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '地址',
  `supFax` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '传真',
  `createdUserId` bigint(20) DEFAULT NULL COMMENT '创建者(userId)',
  `createdTime` datetime DEFAULT NULL COMMENT '创建时间',
  `updatedUserId` bigint(20) DEFAULT NULL COMMENT '更新时间',
  `updatedTime` datetime DEFAULT NULL COMMENT '更新者(userId)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of t_supplier
-- ----------------------------
INSERT INTO `t_supplier` VALUES ('1', 'BJ_GYS001', '北京三木堂商贸有限公司', '长期合作伙伴,主营产品:茅台、五粮液、郎酒、酒鬼酒、泸州老窖、赖茅酒、法国红酒等', '张国强', '13566667777', '北京市丰台区育芳园北路', '010-58858787', '1', '2013-03-21 16:52:07', null, null);
INSERT INTO `t_supplier` VALUES ('3', 'GZ_GYS001', '深圳市泰香米业有限公司', '初次合作伙伴,主营产品:良记金轮米,龙轮香米等', '郑程瀚', '13402013312', '广东省深圳市福田区深南大道6006华丰大厦', '0755-67776212', '1', '2014-03-21 16:56:07', null, null);
INSERT INTO `t_supplier` VALUES ('4', 'GZ_GYS002', '深圳市喜来客商贸有限公司', '长期合作伙伴,主营产品:坚果炒货.果脯蜜饯.天然花茶.营养豆豆.特色美食.进口食品.海味零食.肉脯肉', '林妮', '18599897645', '广东省深圳市福龙工业区B2栋3楼西', '0755-67772341', '1', '2013-03-22 16:52:07', null, null);
INSERT INTO `t_supplier` VALUES ('5', 'JS_GYS001', '兴化佳美调味品厂', '长期合作伙伴,主营产品:天然香辛料、鸡精、复合调味料', '徐国洋', '13754444221', '江苏省兴化市林湖工业区', '0523-21299098', '1', '2015-11-22 16:52:07', null, null);
INSERT INTO `t_supplier` VALUES ('6', 'BJ_GYS002', '北京纳福尔食用油有限公司', '长期合作伙伴,主营产品:山茶油、大豆油、花生油、橄榄油等', '马莺', '13422235678', '北京市朝阳区珠江帝景1号楼', '010-588634233', '1', '2012-03-21 17:52:07', null, null);
INSERT INTO `t_supplier` VALUES ('7', 'BJ_GYS003', '北京国粮食用油有限公司', '初次合作伙伴,主营产品:花生油、大豆油、小磨油等', '王驰', '13344441135', '北京大兴青云店开发区', '010-588134111', '1', '2016-04-13 00:00:00', null, null);
INSERT INTO `t_supplier` VALUES ('8', 'ZJ_GYS001', '慈溪市广和绿色食品厂', '长期合作伙伴,主营产品:豆瓣酱、黄豆酱、甜面酱,辣椒,大蒜等农产品', '薛圣丹', '18099953223', '浙江省宁波市慈溪周巷小安村', '0574-34449090', '1', '2013-11-21 06:02:07', null, null);
INSERT INTO `t_supplier` VALUES ('9', 'GX_GYS001', '优百商贸有限公司', '长期合作伙伴,主营产品:日化产品', '李立国', '13323566543', '广西南宁市秀厢大道42-1号', '0771-98861134', '1', '2013-03-21 19:52:07', null, null);
INSERT INTO `t_supplier` VALUES ('10', 'JS_GYS002', '南京火头军信息技术有限公司', '长期合作伙伴,主营产品:不锈钢厨具等', '陈女士', '13098992113', '江苏省南京市浦口区浦口大道1号新城总部大厦A座903室', '025-86223345', '1', '2013-03-25 16:52:07', null, null);
INSERT INTO `t_supplier` VALUES ('11', 'GZ_GYS003', '广州市白云区美星五金制品厂', '长期合作伙伴,主营产品:海绵床垫、坐垫、靠垫、海绵枕头、头枕等', '梁天', '13562276775', '广州市白云区钟落潭镇福龙路20号', '020-85542231', '1', '2016-12-21 06:12:17', null, null);
INSERT INTO `t_supplier` VALUES ('12', 'BJ_GYS004', '北京隆盛日化科技', '长期合作伙伴,主营产品:日化环保清洗剂,家居洗涤专卖、洗涤用品网、墙体除霉剂、墙面霉菌清除剂等', '孙欣', '13689865678', '北京市大兴区旧宫', '010-35576786', '1', '2014-11-21 12:51:11', null, null);
INSERT INTO `t_supplier` VALUES ('13', 'SD_GYS001', '山东豪克华光联合发展有限公司', '长期合作伙伴,主营产品:洗衣皂、洗衣粉、洗衣液、洗洁精、消杀类、香皂等', '吴洪转', '13245468787', '山东济阳济北工业区仁和街21号', '0531-53362445', '1', '2015-01-28 10:52:07', null, null);
INSERT INTO `t_supplier` VALUES ('14', 'JS_GYS003', '无锡喜源坤商行', '长期合作伙伴,主营产品:日化品批销', '周一清', '18567674532', '江苏无锡盛岸西路', '0510-32274422', '1', '2016-04-23 11:11:11', null, null);
INSERT INTO `t_supplier` VALUES ('15', 'ZJ_GYS002', '乐摆日用品厂', '长期合作伙伴,主营产品:各种中、高档塑料杯,塑料乐扣水杯(密封杯)、保鲜杯(保鲜盒)、广告杯、礼品杯', '王世杰', '13212331567', '浙江省金华市义乌市义东路', '0579-34452321', '1', '2016-08-22 10:01:30', null, null);
public class Supplier {
        private int id; // 供应商ID

        private String supCode; // 供应商编码

        private String supName; // 供应商名称

        private String supDesc; // 供应商描述

        private String supContact; // 供应商联系人

        private String supPhone; // 供应商电话

        private String supAddress; // 供应商地址

        private String supFax; // 供应商传真

        private int createdUserId; // 创建用户ID

        private Date createdTime; // 创建时间

        private int updatedUserId; // 更新用户ID

        private Date updatedTime; // 更新时间

        // 省略getter和setter方法

}

5.if

我们经常会遇到如上图所示的多条件查询,将多条件查询的结果展示在下方的数据列表中。我们先使用

if 标签:条件判断

test 属性:逻辑表达式完成条件查询
<select id="getSuppliersAll" resultType="pojo.Supplier" parameterType="Map">
    select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier 
        where
        <if test="supCode!=null and supCode!=''">
                and supCode like #{supCode}
        </if>
        <if test="supName!=null and supName!=''">
                and supName like #{supName}
        </if>
</select>
如上的这种 SQL 语句就会根据传递的参数值进行动态的拼接。如果此时supCode和supName有值那么就会值拼接这两个条件。
执行结果如下:

但是它也存在问题,如果此时给的参数值是:
/*String supCode="GZ";
  supCode="%"+supCode+"%";
  map.put("supCode",supCode);*/
  String  supName="深圳";
  supName="%"+supName+"%";
  map.put("supName",supName);

拼接的SQL语句变成了

而上面的语句中 where 关键后直接跟 and 关键字,这就是一条错误的SQL语句。这个就可以使用 where1=1或者where标签了

<select id="getSuppliersAll" resultType="pojo.Supplier" parameterType="Map">
    select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier 
        where 1=1
        <if test="supCode!=null and supCode!=''">
                and supCode like #{supCode}
        </if>
        <if test="supName!=null and supName!=''">
                and supName like #{supName}
        </if>
</select>

使用where1=1可以执行成功 

6.where

作用:
        替换where关键字
        会动态的去掉第一个条件前的 and
        如果所有的参数没有值则不加where关键字
<select id="getSuppliersAll" resultType="pojo.Supplier" parameterType="Map">
    select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier
    <where>
       <if test="supCode!=null and supCode!=''">
          and supCode like #{supCode}
       </if>
       <if test="supName!=null and supName!=''">
          and supName like #{supName}
       </if>
    </where>
</select>

使用wehre标签也可以实现 

注意:需要给每个条件前都加上 and 关键字。 

7.choose

条件分支:

        when:用于定义条件成立时执行的代码块。它包含一个 test 属性,用于指定该条件分支的判断条件

        otherwise:用于定义默认的代码块,当所有的 <when> 条件都不成立时,将行 <otherwise> 中定义的代码块

<select id="getSuppliersAll" resultType="pojo.Supplier">
   select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier
   <where>
       <choose>  <!--相当于Switch-->
           <when test="supCode!=null and supCode!=''">  <!--相当于case-->
                and supCode like #{supCode}
           </when>
           <when test="supName!=null and supName!=''">  <!--相当于case-->
                and supName like #{supName}
           </when>
       </choose>
   </where>
</select>

8.foreach

用来迭代任何可迭代的对象(如数组,集合)。
        collection 属性:
                mybatis会将数组参数,封装为一个Map集合。
                默认:array = 数组
                使用@Param注解改变map集合的默认key的名称
        item 属性:本次迭代获取到的元素。
        separator 属性:集合项迭代之间的分隔符。 foreach 标签不会错误地添加多余的分隔符。也就是最后一次迭代不会加分隔符。
        open 属性:该属性值是在拼接SQL语句之前拼接的语句,只会拼接一次
        close 属性:该属性值是在拼接SQL语句拼接后拼接的语句,只会拼接一次

1.实现批量新增(List集合)

  
         1.接口定义方法
    /**
     * 批量新增
     */
    int insertManySupplier(@Param("Supplier") List<Supplier> Supplier);

        2.xml文件中编写SQL

    <!--批量新增-->
    <insert id="insertManySupplier">
        insert into t_supplier(supCode,supName) values
        <foreach collection="Supplier" item="supplier" separator=",">
            (#{supplier.supCode},#{supplier.supName})
        </foreach>
    </insert>

        3. 测试类中调用方法

/**
     * 批量新增
     */
    @Test
    public void insertManySupplier() throws Exception {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = factory.openSession();
        List<Supplier> supplierList = new ArrayList<Supplier>();
        Supplier supplier = new Supplier();
        supplier.setSupCode("CD_9969");
        supplier.setSupName("小王");
        Supplier supplier1 = new Supplier();
        supplier1.setSupCode("CD_9970");
        supplier1.setSupName("小红");
        Supplier supplier2 = new Supplier();
        supplier2.setSupCode("CD_9971");
        supplier2.setSupName("小强");
        supplierList.add(supplier);
        supplierList.add(supplier1);
        supplierList.add(supplier2);
        int i = sqlSession.getMapper(supplierMapper.class).insertManySupplier(supplierList);
        System.out.println("新增了---->"+i);
        sqlSession.commit();
    }

        4.运行结果 

2.实现批量删除(数组) 

        1.接口定义方法

    /**
     * 批量删除
     */
    int deleteManySupplier(@Param("ids") int [] ids);

        2.xml文件中编写SQL

    <!--批量删除-->
    <delete id="deleteManySupplier">
        delete from t_supplier where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

        3. 测试类中调用方法

    /**
     * 批量删除
     */
    @Test
    public void deleteManySupplier() throws Exception{
        int ids[]={25,26,27};
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = factory.openSession();
        int i = sqlSession.getMapper(supplierMapper.class).deleteManySupplier(ids);
        System.out.println("删除了---->"+i);
        sqlSession.commit();
    }

        4.运行结果 

9.set

1.实现动态修改

如果用户在进行数据修改时,注意一 点,如果哪个输入框没有输入内容,我们是将表中数据对应字段值替换为空白还是保留字段之前的值?答案肯定是保留之前的数据。

接下来我们就具体实现

        1.接口定义方法

    /**
     * 动态修改
     */
    int UpdateManySupplier(Supplier Supplier);

上述方法参数 Supplier就是封装了需要修改的数据,而id肯定是有数据的,这也是和添加方法的区别。 

        2.xml文件中编写SQL

    <!--动态修改-->
    <update id="UpdateManySupplier">
        update t_supplier
        <set>
            <if test="supCode!=null and supCode!=''">
                supCode=#{supCode},
            </if>
            <if test="supName!=null and supName!=''">
                supName=#{supName},
            </if>
            <if test="supPhone!=null and supPhone!=''">
                supPhone=#{supPhone},
            </if>
        </set>
        where id=#{id}
    </update>

set 标签可以用于动态包含需要更新的列,忽略其它不更新的列。  

        3. 测试类中调用方法

    /**
     * 动态修改
     */
    @Test
    public void UpdateManySupplier() throws Exception{
        Supplier supplier = new Supplier();
        supplier.setSupCode("DQ_6989");
        supplier.setSupName("地球有限公司");
        supplier.setId(17);
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = factory.openSession();
        int i = sqlSession.getMapper(supplierMapper.class).UpdateManySupplier(supplier);
        System.out.println("修改了---"+i);
        sqlSession.commit();
    }

        4.运行结果 

从结果中SQL语句可以看出,只修改了 supCode,supName字段值,因为我们给的数据中只给Supplier 实体对象的 supCode,supName属性设置值了。这就是 set 标签的作用。

10.trim

trim 标签允许你在模板引擎或XML处理器中对字符串进行修剪操作,包括去除空白字符、去除指定的前缀和后缀,以及根据条件进行修剪。它提供了一种方便和灵活的方式来处理和清理字符串数据。

1.属性及作用 

trim标签的属性及作用
属性作用
prefix指定一个字符串前缀,它将被添加到修剪后的字符串的开头。通常用于添加特定的字符或标记。
suffix指定一个字符串后缀,它将被添加到修剪后的字符串的结尾。常用于添加特定的字符或标记。
prefixOverrides指定一个字符串前缀,当修剪后的字符串以该前缀开头时,该前缀将被移除。这在处理具有特定前缀的文本时非常有用。
suffixOverrides

指定一个字符串后缀,在修剪后的字符串以该后缀结尾时,该后缀将被移除。这个处理具有特定后缀的文本时非常有用。

2.优化代码

        动态查询

    <!--动态查询-->
    <select id="getSuppliersAll" resultType="pojo.Supplier">
        select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier
        <trim prefix="where" prefixOverrides="and|or" suffix=" LIMIT #{index},#{pageSize}">
            <if test="supCode!=null and supCode!=''">
                and supCode like #{supCode}
            </if>
            <if test="supName!=null and supName!=''">
                and supName like #{supName}
            </if>
            <if test="supPhone!=null and supPhone!=''">
                and supPhone like #{supPhone}
            </if>
        </trim>
    </select>

        动态修改

 <update id="UpdateManySupplier">
        update t_supplier
        <trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
            <if test="supCode!=null and supCode!=''">
                supCode=#{supCode},
            </if>
            <if test="supName!=null and supName!=''">
                supName=#{supName},
            </if>
            <if test="supPhone!=null and supPhone!=''">
                supPhone=#{supPhone},
            </if>
        </trim>
    </update>

需要注意的是,prefixsuffix 属性是可选的,可以根据需要选择性地添加前缀和后缀。而 prefixOverridessuffixOverrides 属性则提供了更加灵活的方式来根据条件进行字符串修剪操作

MyBatis动态SQL是指在SQL语句中根据条件动态地拼接SQL字符串的能力。这个特性可以帮助开发者避免手动拼接SQL字符串时容易出现的错误,比如忘记空格或逗号。动态SQL可以通过使用OGNL表达式进行解析来实现。OGNL是一种表达式语言,可以在MyBatis中使用来创建动态SQL。如果需要在动态SQL中使用除了OGNL表达式以外的变量,可以使用bind标签来创建这些变量。例如,在一个select语句中,可以使用bind标签创建一个名为"pattern"的变量,其值为"'%' + _parameter.getTitle() + '%'",然后在SQL语句中使用#{pattern}来引用这个变量。另外,MyBatis还提供了一些动态标签,比如if标签,可以根据条件来动态地生成SQL语句的一部分。例如,在一个select语句中,可以使用if标签来判断name是否为空,如果不为空,则在SQL语句中添加一个条件。总之,MyBatis动态SQL功能可以帮助开发者更灵活地生成SQL语句,根据不同的条件动态地拼接SQL字符串。 #### 引用[.reference_title] - *1* [Mybatis动态sql](https://blog.csdn.net/weixin_52394141/article/details/125862192)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* *3* [mybatis动态sql(超详细)](https://blog.csdn.net/xjszsd/article/details/121924231)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值