关于Mybatis框架中的Mapper.xml中SQL的编写方法

我们在mybatis框架中写sql语句的时候，分为静态语句和动态语句。

 

静态语句

就是在java中直接写入的SQL语句，在达到程序设计的节点时就会进行SQL语句的查询。

动态语句

相比静态语句来说，mybatis中用得多的是动态语句，因为业务中传过来的值是不一定的，是动态变化的，所以动态SQL是有很多东西需要学习的。

以一个例子来讲，使用Mybatis 动态表名，查询数据，比如最近项目中使用到根据时间进行查询表的需求（即表名是根据时间变化的）

mapper.java 文件：

public Gpsinfo selectGpsByPlateNo(@Param("tableName")String tableName,@Param("plateNo")String plateNo);

mapper.xml 文件：

<select id="selectGpsByPlateNo"  parameterType="String" resultMap="BaseResultMap" statementType="STATEMENT">
       SELECT *FROM
               ${tableName}
        where plateNo='${plateNo}'
        order by createDate desc limit 1
</select>

serviceImpl.java文件：

@Override  
public Gpsinfo selectGpsByPlate(String plateNo) {  
      Gpsinfo gpsinfo = new Gpsinfo();  
      Date date=new Date();
      DateFormat format=new SimpleDateFormat("yyyyMMdd");
      String tableName="gpsinfo"+format.format(date);
      gpsinfo = myGpsMapper.selectGpsByPlateNo(tableName,plateNo);  
      return gpsinfo;  
}

表名根据时间进行拼接

这是一次别人代码优化过程中发现的问题，在功能优化后发现部分数据查出来了，问题就在于一条sql上的#和$。

下图为两条sql：

从图上可以看出 wwlr.LabelId in(${showLabels}) 和 wwlr.LabelId in(#{showLabels})，其中showLabels是传进来一个字符串类型的参数，参数的样子是这样的“4,44,514”，问题就出在这个参数传进来后#和$处理的方式是不一样的。

区别

	作用	应用场景	缺点
#{ }	相当于占位符，预编译处理，MyBatis在处理#{ }时，它会将sql中的#{ }替换为？，然后调用PreparedStatement的set方法来赋值，传入字符串后，会在值两边加上单引号，如值 “1,0”就会变成“ ‘1,0’ ”；	获取DAO中的参数数据,在映射文件的SQL语句中出现#{}表达式,底层会创建预编译的SQL；
${ }	字符串拼接与替换， MyBatis在处理${ }时,它会将sql中的${ }替换为变量的值，传入的数据不会在两边加上单引号。	获取配置文件数据,DAO接口中的参数信息,当$出现在映射文件的SQL语句中时创建的不是预编译的SQL,而是字符串的拼接,有可能会导致SQL注入问题.所以一般使用$接收dao参数时,这些参数一般是字段名,表名等,例如order by ${column}。	1.有时需要在${parameter}前后加上单引号； 2.导致sql注入，不利于系统的安全性。

关于#{}底层调用的PreparedStatement中的set方法：

这里涉及到一个知识点叫做SQL注入，就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。常见的有匿名登录（在登录框输入恶意的字符串）、借助异常获取数据库信息等。因此有些情况下能使用#{}达到要求的情况下就尽量不要使用${}了。

模糊查询

mybatis中的模糊查询有3种方式：

1.首先个人推荐使用CONCAT函数（concat，合并多个数组、字符串）：

public interface FoodMapper{
    @Select("select * from table where name like CONCAT('%',#{name},'%')")
    List<Food> fuzzySearchByName(String name);
}

对应的Controller类：

public class foodController{
    @Autowired
    FoodMapper foodMapper = new FoodMApper()

    @Component("/fuzzySearch")
    Public List<Food> fuzzySearch(String name){
        return fuzzySearchByName(name)
    }
}

2.#{}

public interface FoodMapper{
    @Select("select * from table where name like '%#{name,jdbcType=VARCHAR}%')")
    List<Food> fuzzySearchByName(String name);
}

3.${}

public interface FoodMapper{
    @Select("select * from table where name like '%${name}%')")
    List<Food> fuzzySearchByName(String name);
}

推荐使用前两种方法，因为前两种方法使用的是#{}，所以不会引起SQL注入，风险较小。

条件查询：

假设有以下业务场景，根据3个条件查找出符合的数据，但是这3个条件可以为任何值，空值也是被允许的，现在为了直接将空值过滤掉，只查找有具体条件内容的属性，可以像下面这样写：

<select id="getByParams" resultMap="BaseResultMap" parameterType="string">
        SELECT
        *
        from
            xxx_t
        where
            1 = 1
        <if test = "null != number ">
           and xx_number = #{xxNumber}
        </if>
        <if test = "null != age">
           and xx_age = #{xxAge}
        </if>
        <if test = "null != state">
           and xx_state = #{xxState}
        </if>
</select>

case并列判断：

<choose><when>跟<if>是有区别的，类似于java语法中case与if、else之间的关系。

使用‘if‘ ‘else ‘ 判断 when otherwise，when其实自带一个break，只要有一个条件符合，就自动break跳出。

<select id="getByParams" resultMap="BaseResultMap" parameterType="string">
        SELECT
        *
        from
            xxx_t
        <where>
            <choose>
            <when test = "1 == number">
                number = 100
            </when>
            <when test = "2 == number">
                number = 200
            </when>
            <when test = "3 == number">
                number = 300
            </when>
            </choose>
        </where>
</select>

也可以使用when otherwise。when的意思就是和java 代码中switch 的case一样,只能进一个判断。在所有的when条件都不满足的情况下就是进的otherwise。比如说我此时传了sum=5 那sql就是的aaa=bbb。