MyBatis中的${}和#{}

缺乏、安全感 2023-02-21 04:51 19阅读 0赞

\#\{\}：其作用相当于占位符，MyBatis会将里面的参数解析成一个字符串，比如，select \* from table where name = \#\{name\}，当传入的name为小李，则执行的SQL为select \* from table where name = '小李'。预编译，和JDBC的prepareStatement一样，无法进行SQL注入。

$\{\}：其作用是直接拼接参数的值到SQL语句中，而不对其加上单引号，比如，order by $\{argu\}，当传入的argu为age时，执行的SQL为order by age。

$\{\}、\#\{\}的使用举例：

示例一：获取用户在一段时间内某种设备的登录时间

后端Controller

@RequestMapping(value = "/getStatisticsData", method = RequestMethod.GET, produces = "application/json;charset=utf8")
    public List<SearchResult> getStatisticsData(Long startTime, Long endTime, String argu) {
        List<SearchResult> result = mapper.getSum(getTableName(), startTime, endTime, argu);
        return result;
    }

Mapper接口：

List<SearchResult> getSum(@Param("table") String table,
                            @Param("start") String startTime,
                            @Param("end") String endTime,
                            @Param("tpType") String argu);

Mapper.xml：

tpType可以是使用移动设备（平板/手机）登录的时间，也可以是使用电脑登录的时间

<select id="getSum" resultType="com.entity.SearchResult">
        select username as name, avg(${tpType}) as avgTime from ${table}
        <where>
            <if test=" start != null">
                <![CDATA[
                    and startTime >= #{start}
                ]]>
            </if>
            <if test=" end != null">
                <![CDATA[
                    and endTime <= #{end}
                ]]>
            </if>
        </where>
        group by username
    </select>

示例二：获取用户一段时间内每天（可能有几条数据）使用某种设备的登录时间

<select id="getPerSum" resultType="com.entity.SearchResult">
        select username as name, DATE_FORMAT(time, '%Y-%m-%d') as time2, avg(${tpType}) as avgTime from ${table}
        <where>
            <if test=" start != null">
                <![CDATA[
                    and startTime >= #{start}
                ]]>
            </if>
            <if test=" end != null">
                <![CDATA[
                    and endTime <= #{end}
                ]]>
            </if>
        </where>
        group by time2
    </select>

注意：如果某个分组不只一条数据，即时不使用avg等函数，得到的各个分组中依然只有一条数据，并且其字段是该分组所有数据中的一条的值。

示例三：带有过滤条件的查询

<select id="get" resultType="com.entity.SearchResult">
        select * from ${table}
        <include refid="filter">
        <include refid="orderByAsc">
    </select> 
    <sql id="filter">
        <where>
            <if test=" username != null">
                <![CDATA[
                    and username == #{username}
                ]]>
            </if>
            <if test=" start != null">
                <![CDATA[
                    and UNIX_TIMESTAMP(`time`) >= #{start}
                ]]>
            </if>
            <if test=" end != null">
                <![CDATA[
                    and UNIX_TIMESTAMP(`time`) <= #{end}
                ]]>
            </if>
        </where>
    </sql>
    <sql id="orderByAsc">
        order by `time` asc limit #{start}, #{count}
    </sql>

示例四：单条插入&批量插入

单条插入

<insert id="insert" parameterType="com.entity.SearchResult">
        insert into ${table}(
            username, `time`, line90, line95, line99
        )
        values(
            #{data.username}, #{data.time}, #{data.line90}, #{data.line95}, #{data.line99}
        )
    </insert>

批量插入

<insert id="insertBatch">
        insert into ${table}(
            username, `time`, line90, line95, line99
        )
        values
        <foreach item="data" collection="dataList" separator=",">
            (
            #{data.username}, #{data.time}, #{data.line90}, #{data.line95}, #{data.line99}
            )
        </foreach>
    </insert>

示例五：单条删除&批量删除

单条删除

<delete id="deleteById" parameterType="java.lang.Integer">
        delete from ${table} where id=#{id}
    </delete>

批量删除

<delete id="deleteBatch">
        delete from ${table} where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>