mysql 内置函数动态sql mysql的动态sql

转载

云端行者 2023-09-02 22:19:40

文章标签 mysql 内置函数动态sql MyBatis 动态SQL sql List 文章分类 MySQL 数据库

为什么要有动态SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。

动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

MyBatis为我们提供了以下四种运算符：

• if:判断

• choose (when, otherwise):分支选择；带了break的swtich-case

如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个

• trim 字符串截取(where(封装查询条件), set(封装修改条件))

• foreach 遍历集合

2.if的示例：

（1）使用了where标签

<!-- 查询员工，要求，携带了哪个字段查询条件就带上这个字段的值 -->
<!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
<selectid="getEmpsByConditionIf"resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee
<!-- where （在这里我们用了MyBatis的where标签 ，因为当我们不带id而带了lastName时会在前面多出来一个and，
而where标签能也只能解决前面多出来一个and的问题,where只会去掉第一个多出来的and或者or。-->
<where>
<!-- 
        test：判断表达式（OGNL）
        OGNL参照PPT或者官方文档。
        c:if test
        从参数中取值进行判断
        遇见特殊符号(比如此处不应出现&&和"")应该去写转义字符：（"是"的转义字符,&是&的转义字符，
        这里用"" 取代""（当然也可以用两个单引号''）,用&& 取代&&(当然也可以用and)）
-->
<iftest="id!=null">
        id=#{id}
</if>
<iftest="lastName!=null&&lastName!=""">
        and last_name like #{lastName}
</if>
<iftest="email!=null and email.trim()!=""">
        and email=#{email}
</if>
<!-- ognl会进行字符串与数字的转换判断 "0"==0 -->
<iftest="gender==0 or gender==1">
        and gender=#{gender}
</if>
</where>
</select>

（2）使用了set标签

<updateid="updateEmp">
        <!-- set标签的使用在这里我们用了MyBatis的set标签 ，因为我们在大多数语句后面会多出来一个逗号以便接下来的sql语句拼接，
                而这个多出来的逗号会导致sql语句出错，用set标签可以去除掉后面多出来的一个逗号。-->
        update tbl_employee
<set>
<iftest="lastName!=null">
        last_name=#{lastName},
</if>
<iftest="email!=null">
        email=#{email},
</if>
<iftest="gender!=null">
        gender=#{gender}
</if>
</set>
        where id=#{id}
</update>

trim的示例：

（1）

<selectid="getEmpsByConditionTrim"resultType="com.atguigu.mybatis.bean.Employee">
select* from tbl_employee
<!-- 
        后面多出的and或者or where标签不能解决
        prefix="":前缀：trim标签体中是整个字符串拼串 后的结果。
        prefix给拼串后的整个字符串加一个前缀
        prefixOverrides="":
        前缀覆盖： 去掉整个字符串前面多余的字符
        suffix="":后缀
        suffix给拼串后的整个字符串加一个后缀
        suffixOverrides=""
        后缀覆盖：去掉整个字符串后面多余的字符
-->
<!-- 自定义字符串的截取规则 -->
<trimprefix="where"suffixOverrides="and">
<iftest="id!=null">
        id=#{id} and
</if>
<iftest="lastName!=null&&lastName!=""">
        last_name like #{lastName} and
</if>
<iftest="email!=null and email.trim()!=""">
        email=#{email} and
</if>
<!-- ognl会进行字符串与数字的转换判断 "0"==0 -->
<iftest="gender==0 or gender==1">
        gender=#{gender}
</if>
</trim>
</select>

（2）

<updateid="updateEmp">
        update tbl_employee
<trimprefix="set"suffixOverrides=",">
<iftest="lastName!=null">
        last_name=#{lastName},
</if>
<iftest="email!=null">
        email=#{email},
</if>
<iftest="gender!=null">
        gender=#{gender}
</if>
</trim>
        where id=#{id}
</update>


choose的示例：
<selectid="getEmpsByConditionChoose"resultType="com.atguigu.mybatis.bean.Employee">
        select* from tbl_employee
<where>
                <!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 -->
<choose>
        <whentest="id!=null">
                id=#{id}
        </when>
        <whentest="lastName!=null">
                last_name like #{lastName}
        </when>
        <whentest="email!=null">
                email = #{email}
        </when>
        <otherwise>
                gender = 0
        </otherwise>
</choose>
</where>
</select>

foreach的示例：

现在我们在EmployeeMapperDynamicSQL.class接口里定义一个这样的方法

publicList<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);

在其映射文件EmployeeMapperDynamicSQL.xml中

<!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids); -->
<selectid="getEmpsByConditionForeach"resultType="com.atguigu.mybatis.bean.Employee">
select *from tbl_employee
<!--
        collection：指定要遍历的集合：
        list类型的参数会特殊处理封装在map中，map的key就叫list
        item：将当前遍历出的元素赋值给指定的变量
        separator:每个元素之间的分隔符
        open：遍历出所有结果拼接一个开始的字符
        close:遍历出所有结果拼接一个结束的字符
        index:索引。遍历list的时候是index就是索引，item就是当前值
        遍历map的时候index表示的就是map的key，item就是map的值
        #{变量名}就能取出变量的值也就是当前遍历出的元素
-->
<foreachcollection="ids"item="item_id"separator=","
open="where id in("close=")">
        #{item_id}
</foreach>
</select>

测试文件中

List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1,2));

for(Employee emp : list) {

System.out.println(emp);

}

结果

足以说明foreach的作用（在sql语句中通过foreach修改in里面的参数个数）

抽取可重用的sql片段（学习下个内容用到）

<!--
        抽取可重用的sql片段。方便后面引用
        1、sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用
        2、include来引用已经抽取的sql：
        3、include还可以自定义一些property，sql标签内部就能使用自定义的属性
             include-property：取值的正确方式${prop},
            #{不能使用这种方式}
-->
<sqlid="insertColumn">
<iftest="_databaseId=='oracle'">
        employee_id,last_name,email
</if>
<iftest="_databaseId=='mysql'">
        last_name,email,gender,d_id
</if>
</sql>
<includerefid="insertColumn"></include>配合使用

有关于include的子标签property的使用

<includerefid="insertColumn">
        <propertyname="testColumn"value="abc"/>
</include>
<sqlid="insertColumn">
<iftest="_databaseId=='oracle'">
        employee_id,last_name,email,${testColumn}
</if>
<iftest="_databaseId=='mysql'">
        last_name,email,gender,d_id
</if>
</sql>

MySQL批量插入的两种方式

（1）sql--include--foreach--insert（四个标签，首选）

<!-- 批量保存 -->
<!--public void addEmps(@Param("emps")List<Employee> emps); -->
<!--MySQL下批量保存：可以foreach遍历 mysql支持values(),(),()语法-->
<insertid="addEmps">
        insert into tbl_employee(
<includerefid="insertColumn"></include>
        )
        values
<foreachcollection="emps"item="emp"separator=",">
        (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>

（2）foreach--insert

<!-- 这种方式需要数据库连接属性allowMultiQueries=true；

这种分号分隔多个sql可以用于其他的批量操作（删除，修改） -->

首先你得在数据库配置文件（一般为dbconfig.properties）中的jdbc.url这里设置为

jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true

<insertid="addEmps">
<foreachcollection="emps"item="emp"separator=";">
        insert into tbl_employee(last_name,email,gender,d_id)
        values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>

_parameter和_databaseId内置参数和bind绑定

_parameter相当于传入进来的参数整体，用于判断传入的整个参数是否为空或形式是否正确

_databaseId在mybatis的xml配置文件中配置了databaseIdProvider标签后，可以用来判断当前

使用的数据库是什么数据库，进而执行不同的命令

<!-- 两个内置参数：
不只是方法传递过来的参数可以被用来判断，取值。。。
mybatis默认还有两个内置参数：
_parameter:代表整个参数
单个参数：_parameter就是这个参数
多个参数：参数会被封装为一个map；_parameter就是代表这个map
_databaseId:如果配置了databaseIdProvider标签。
_databaseId就是代表当前数据库的别名oracle
-->
<!--public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<selectid="getEmpsTestInnerParameter"resultType="com.atguigu.mybatis.bean.Employee">
<!-- bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
<bindname="_lastName"value="'%'+lastName+'%'"/>
<iftest="_databaseId=='mysql'">
        select * from tbl_employee
<iftest="_parameter!=null">
        where last_name like #{lastName}
</if>
</if>
<iftest="_databaseId=='oracle'">
        select * from employees
<iftest="_parameter!=null">
        where last_name like #{_parameter.lastName}
</if>
</if>
</select>

bind标签在传入参数不太方便的情况下（如模糊查询，用户不会输入%）将原来的参数改变

一般使用不多，因为我们可以在使用前将参数改变，如果使用bind的话，需求改变时需要专门过来更改

<!--public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<selectid="getEmpsTestInnerParameter"resultType="com.atguigu.mybatis.bean.Employee">
<!-- bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
<bindname="_lastName"value="'%'+lastName+'%'"/>
<iftest="_databaseId=='mysql'">
        select * from tbl_employee
<iftest="_parameter!=null">
        where last_name like #{lastName}
</if>
</if>
<iftest="_databaseId=='oracle'">
        select * from employees
<iftest="_parameter!=null">
        where last_name like #{_parameter.lastName}
</if>
</if>
</select>

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。