SQL映射文件

我是修沟的修狗
115 阅读8分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第2天，点击查看活动详情

大家都知道MyBatis 是用于访问数据库，通过 SQL 语句访问数据库时，就会涉及 SQL 的输入参数与输出参数（即 SQL 语句的执行结果）

那么对于不同类型的sql，我们应该怎么书写他的参数？

简单类型输入

  • 声明 在语法上，MyBatis 语句可以通过多种不同的方式接收任意数量的输入参数，但限于篇幅有限，这里只会展示参数为0，或者1的情况。这也是目前企业中最流行、最规范的方式。因此，如果遇到有多个输入参数的情况，就需要想办法将多个输入参数转化为唯一的一个输入参数，例如可以先将多个参数转化为数组对象，或者将多个参数封装到一个 JavaBean 对象中，之后再将这唯一的一个参数传给 SQL 映射文件。

那么在sql里面，如果需要获取我们的输入参数怎么做？

通过 #{参数} 获取参数值

parameterType 用于指定 SQL 输入参数的类型，SQL 语句可以通过 #{stuNo} 获取到该参数值，如下。

…
<mapper namespace="…">
    <select id=".." parameterType=".." resultType="..">
        SQL语句，如select * from student where stuNo=#{stuNo}
    </select>
</mapper>

实际上，“#{参数}” 就表示一个 “占位符”，相当于 JDBC 中 PreparedStatement 占位符 “ ？”。如果输入参数是简单类型（即 8 个基本类型或 String 类型），那么语法上 “参数” 的名字可以是任意的（如 #{studentNo}、#{abc} 等），但建议大家按照 “见名知意” 的变量命名原则，以驼峰命名法设置合理的参数名。“#{参数}” 还可以防止 SQL 注入，为传入 String 类型的参数值自动加上引号。

通过 ${value} 获取参数值

除了 “#{参数}” 以外，还可以使用 ${value} 来获取输入的参数值。${value} 的作用就是输出变量的值。务必要注意的是，在解析简单类型的参数值时，${value} 中的参数值 “value” 是固定写法，不能改成其他名字。并且 ${value} 不能防止 SQL 注入，有很大的安全隐患。

${value} 主要用于动态排序（Order By）。因为 ${value} 是把变量值原样输出，不会像 “#{参数}” 那样自动为字符串等类型的值加上引号。例如，如果需要按照 “学号” 对查询的结果集进行排序。

那么还有${},他和#{}有什么区别？

#{}方式传值是安全的，起占位符作用，为参数占位符 ?，即sql 预编译，能防止sql 注入

${}为字符串替换，即 sql 拼接，不能防止sql 注入

什么是sql注入？ 比如我们登录，需要同时密码。

   即正常：select * from info where name=? and password=??;

     注入：select * from info where name=? and passwd=?? Or 1=1;

这样的话，密码就不用正确也能登录，就有问题

功能#{参数}${value 参数}
防止 SQL 注入支持不支持
参数名（参数值是简单类型时）任意，如#{eNo}、#{abc}等必须是 value，即${value}
参数值会给 String 类型的参数值自动加引号将参数值原样输出，可以用来实现动态参数排序
获取级联属性支持支持

输入参数为String

SQL 映射文件 StudentMapper.xml 如下。

<!-- 根据姓名，查询学生 -->
<select id="queryStudentByName" parameterType="string" resultType="student">
    select * from student where stuName=#{stuName}
</select>

动态接口：IStudentMapper.java：

public interface IStudentMapper{
    …
    //按照“约定”编写的查询方法
    public abstract Student queryStudentByName(String stuName);
}

参数为int

<!-- 根据学号查询一个学生 --> 
<select id="queryStudentByNo" parameterType="int" resultType="student"> 
select * from student where stuNo=#{stuNo}
</select>

实体类型

当输入参数是简单类型时，${value} 的参数值必须是 “value”；但当输入参数是实体类对象时，${...} 的参数名只能是对象的属性名。类似的，当使用 #{...} 时，其参数名必须是对象的属性名。可见，${...} 和 #{...} 在接收实体类对象时，用法是相似的。不同的是 ${...} 会将参数值原样输出，而 #{...} 会根据参数类型，将参数值原样输出或给参数值加上引号。

SQL 代码片段：

…
<!-- 根据实体类的属性，查询学生信息，并分别通过${...}和#{...}解析属性 -->
<select id="queryStudentsByNameAndAge" parameterType="student" resultType="student">
    select * from student where stuName like '%${stuName}%' and stuAge = #{stuAge}
</select>
…

即：输入的是Student对象

输入hashmap

在 SQL 映射文件 StudentMapper.xml 添加如下代码：

…
<!-- 测试传入HashMap类型 -->
<select id="queryStudentsWithHashMap" parameterType="HashMap" resultType="student">
    select * from student where stuName like '%${stuName}%' and stuAge = #{stuAge}
</select>
…

编写动态代理接口 IStudentMapper.java：

public interface IStudentMapper{
    Student queryStudentsWithHashMap(Map<String, Object> map);
}

输出和输入类似，不过是resultType变换

比如：StudentMapper.xml 中添加如下代码：

…
<!-- 测试输出简单类型 -->
<select id="queryStudentsCount" resultType="int">
    select count(*) from student
</select>
…

在 SQL 映射文件中，指定输出类型为 HashMap，并给每个字段都起上别名，SQL 映射文件 StudentMapper.xml 中代码片段如下：

<select id="queryStudentOutByHashMap"   parameterType="int" resultType="HashMap">
    select stuNo "no",stuName "name",stuAge "age" from student where stuNo = #{stuNo}
</select>

一个返回实体类对象类型的例子——根据学号查询一个学生。 SQL 映射文件 StudentMapper.xml：

…
<mapper namespace="包名.类名">
    <select id="getStudentByNo" parameterType="int" resultType="student">
        select stuNo,stuName,stuAge,graName from student where stuNo=#{stuNo}
    </select>
</mapper>

根据已有知识，此例中 SQL 语句使用的字段名必须和 resultType 指定的实体类中的属性名一致，例如 stuNo、stuName 等必须既是数据表的字段名，又同时是 Student 类的属性名，否则就会产生异常。

有没有办法让字段名与属性名不一致时，程序也能正常运行呢？

  1. 在 SQL 语句中定义别名。

用别名来表示实体类的属性名，即 select 字段名 “属性名” from 数据表，其中 “属性名” 可以是字段名的别名，如下所示：

select no "stuNo",name "stuName" ,age "stuAge", gName "graName" from student
  1. 使用 <resultMap> 元素指定字段和属性的对应关系。

<!-- 根据学号，查询一个学生 -->
<select id="queryStudentByNo" parameterType="int" resultMap ="studentResultMap">
   。。
</select>

<!-- 配置resultMap，用来指定字段和属性的对应关系 -->
<resultMap type="student" id="studentResultMap">
    <!-- 数据表主键no对应于属性stuNo -->
    <id column="no" property="stuNo"/>
    <!-- 字段name对应于属性stuName -->
    <result column="name" property="stuName"/>
    <result column="age" property="stuAge"/>
    <result column="gName" property="graName"/>
</resultMap>

对应关系如下：

  • “<select> 元素中，resultMap 的属性值” 对应于 “<resultMap> 元素的 id 属性值”；
  • “<resultMap> 元素中，子元素 <id> 或 <result> 的 column 值、property 值” 分别对应于 “表的字段名和对象的属性名”。其中，<result> 元素用来指定普通字段，<id> 元素用来指定主键字段。

mybatis实现动态sql

前面内容： 根据不同条件拼接 SQL 语句是件很繁琐的事，需要非常谨慎。

MyBatis 提供了 <if>、<where>、<foreach> 等标签来实现 SQL 语句的动态拼接

  1. <if> 标签

<select id="testQueryStudentByNoWithOGNL" parameterType="student" resultType="student">
       select stuNo,stuName,stuAge,graName from student where 1=1
    <if test="graName != null and graName !='' ">
          and graName like '%${graName}%'
    </if>
    <if test="stuAge != null and stuAge !='' ">
             and stuAge = #{stuAge}
    </if>
</select>

上述 SQL 代码表示，只有当传入的 Student 对象的 graName 属性不为空时，才会拼接 SQL 语句 and graName like '%${graName}%'，而如果 graName 属性为空就不会再拼接；stuAge 同理。

2．<where> 标签

<select id="testQueryStudentByNoWithOGNL" parameterType="student" resultType="student">
    select stuNo,stuName,stuAge,graName from student
    <where>
        <if test="graName != null and graName !='' ">
            and graName like '%${graName}%'
        </if>
        <if test="stuAge != null and stuAge !='' ">
            and stuAge = #{stuAge}
        </if>
    </where>
</select>

使用 <where> 标签替代了 SQL 中的 where 关键字，并且 <where> 标签可以根据情况自动处理 <if> 部分生成的 SQL 最终会导致语法错误的 “and”。如上例，假设两个 <if> 均成立，生成的最终 SQL 是 “select stuNo,stuName,stuAge,graName from student where and graName like '%${graName}%' and stuAge = #{stuAge}”，为保证语法正确，执行时会自动删掉第一个 “and”。

3．<foreach> 标签

我们已经知道，SQL 映射文件通过 parameterType 来指定输入的参数类型。如果输入参数是简单类型或一般对象类型（除集合和数组外），可以直接指定，如 parameterType="int"、parameterType="student" 等；但如果输入参数是集合或数组类型，就需要使用 <foreach> 标签来完成输入参数的处理。

例如，要想实现以下 SQL：

select * from student where stuNo in(第一个学号, 第二个学号, 第三个学号)

就必须将包含学号的数组或集合传入 SQL 并替代  “第一个学号, 第二个学号, 第三个学号”  等占位符。

传入数组或集合的四种方式。

  • 第一种：将集合或数组以对象属性的形式传入。

先在类中定义一个集合（或数组）类型的属性，然后将该属性传入 SQL 映射文件中。例如，新建一个年级 Grade 类，该类包含一个 List 集合类型的属性 stuNos，存放着该年级中所有学生的学号，Grade.java 部分代码片段如下：


public class Grade{
    //年级中所有学生的学号
    private List<Integer> stuNos ;

    public List<Integer> getStuNos() {
        return this.stuNos;
    }

    public void setStuNos(List<Integer> stuNos) {
        this.stuNos = stuNos;
    }
}

将 Grade 对象中 List 类型的属性 stuNos 传入 SQL 映射文件 StudentMapper.xml 中，代码如下所示：

<!-- 注意grade是对应类的别名 -->
<select id="queryStudentWithForeach" parameterType="grade" resultType="student">
    select * from student
    
    <where>
        <if test="stuNos != null and stuNos.size > 0 " >
                 <!-- 使用foreach标签，迭代取出Grade对象中stuNos集合属性中的每个元素 -->
               <foreach collection="stuNos" open=" stuNo in (" close=")" item="stuNo" separator=",">
                 #{stuNo}
            </foreach>
        </if>
    </where>
</select>

通过 parameterType 传入 Grade 对象，该对象包含了集合类型的属性 stuNos（假定 stuNos 集合属性包含 31、32、33 三个元素）。之后通过 <foreach> 处理 stuNos 集合中的数据，拼接出完整的 SQL 语句 select * from student where stuNo in(31,32,33)。

以此 SQL 映射文件为例，具体的拼接过程如下：

首先是主体的 SQL 语句：

select * from student

然后通过 <where> 标签拼接 where 关键字：

select * from student where

之后，如果传入的 Grade 对象不为空，就会进行 <foreach> 中的 SQL 拼接：

a. 先拼接 open 属性中的 SQL：

select * from student where stuNo in(

b. 再循环拼接 collection 属性所代表的集合：用 item 的属性值代表每次遍历时的别名，并在 <foreach> 下用 #{别名} 来接收每次的迭代值，再将每次迭代值之间用 separator 的属性值隔开。

select * from student where stuNo in( 31,32,33

c. 最后拼接 close 中的结尾符 “)”：

select * from student where stuNo in(31,32,33)

<foreach> 元素中各属性如下表所示。

属性含义
collection需要遍历的集合类型的属性名
item集合中每个元素进行迭代时的别名
index指定一个名字，用于表示在迭代过程中，每次迭代到的位置
open循环遍历前附加的 SQL 语句
separator在每次进行迭代之间，以什么符号作为分隔符
close循环遍历后附加的 SQL 语句

至此，就从数据库里查出了学号为 31、32、33 三位学生的信息，并封装到了 students 集合对象里。

  • 第二种：传入 List 类型的集合。

此种方式与 “将集合或数组以对象属性的形式传入” 的方法基本相同，只是需要将 parameterType 指定为 List，并且必须用参数值 “list” 来接收传来的 List 集合，在 SQL 映射文件 StudentMapper.xml 添加如下代码：

<select id="queryStudentWithForeachAndList" parameterType="java.util.List" resultType="student">
      select * from student
      <where>
      
          <if test="list !=null and list.size > 0" >
              <foreach collection="list" open=" stuNo in(" close=")" item="stuNo" separator=",">
                    #{stuNo}
              </foreach>
          </if>
          
      </where>
</select>
  • 第三种：传入简单类型的数组。

传入数组与传入 List 集合的方法基本相同，只是需要将 parameterType 指定为数组类型，并且必须用参数值 “array” 来接收传来的数组。下面以传入 int[] 数组为例进行讲解。

在 SQL 映射文件 StudentMapper.xml 中添加如下代码片段：

<select id="queryStudentWithForeachAndArray" parameterType="int[]" resultType="student">
    select * from student
    <where>
        <if test="array !=null and array.length>0" >
            <foreach collection="array" open="  stuNo in(" close=")" item="stuNo" separator=",">
                #{stuNo}
            </foreach>
        </if>
    </where>
</select>
  • 第四种：传入对象数组。

传入对象数组与 “传入简单类型的数组” 的方法基本相同，只是需要将 parameterType 的值固定写成 Object[]，并且可以通过 OGNL 获取迭代对象的属性，如 #{student.stuNo}。下面以传入 Student[] 数组为例进行讲解。

在 SQL 映射文件 StudentMapper.xml 映射文件中添加如下代码：

<select id="queryStudentWithForeachAndObjectArray" parameterType="Object[]" resultType="student">
    select * from student
    <where>
        <if test="array !=null and array.length>0" >
            <foreach collection="array" open=" stuNo in(" close=")" item="student" separator=",">
                    #{student.stuNo}
            </foreach>
        </if>
    </where>
</select>
  1. SQL 片段

为了实现复用的目的，可以通过 “方法” 将重复的 Java 代码提取出来，通过 “存储过程” 将重复的 SQL 语句提取出来；同样，在 MyBatis 中也可以使用 “SQL 片段” 将 SQL 映射文件中的重复代码提取出来。

例如有以下 SQL 映射文件：

<select id="testQueryStudentByNoWithOGNL" parameterType="student" resultType="student">
    select stuNo,stuName,stuAge,graName from student
    <where>
        <if test="graName != null and graName !='' ">
            and graName like '%${graName}%'
        </if>
        <if test="stuAge != null and stuAge !='' ">
            and stuAge = #{stuAge}
        </if>

    </where>
</select>

可以将 <where> 中的 if 判断提取出来，然后在需要使用的地方使用 <include> 导入，如下。

<!-- 提取的SQL片段 -->
<sql id="queryWithGranameAndAge">

    <if test="graName != null and graName !='' ">
        and graName like '%${graName}%'
    </if>
    
    <if test="stuAge != null and stuAge !='' ">
        and stuAge = #{stuAge}
    </if>
</sql>

<select id="testQueryStudentByNoWithOGNL" parameterType="student" resultType="student">
    select stuNo,stuName,stuAge,graName from student
    <where>
           <!--导入SQL片段 -->
        <include refid="queryWithGranameAndAge"/>
    </where>
</select>

即使用 <sql> 将代码提取出来，然后在需要使用的地方用 <include> 导入。其中 <include> 元素的 refid 属性指向需要导入 <sql> 标签的 id 值。

说明：如果 <include> 导入的是其他 SQL 映射文件中的 SQL 片段，则需要在引用时加上 namespace，如下是在 SQL 映射文件 B 中引入 SQL 映射文件 A 中 SQL 片段的伪代码。

SQL 映射文件 A：

<mapper namespace="namespaceA">
    <sql id="fragment">
       ...
</sql>
</mapper>

SQL 映射文件 B：

<select ...>
    select ... from ...
    <where>
           <!--导入SQL映射文件A中的SQL片段 -->
        <include refid="namespaceA.fragment"/>
    </where>
</select>
avatar
文章
阅读
粉丝