mybatis中#{}和${}的详细解析

2024-12-12 技术教程

这篇文章主要讲解了mybatis中#{}和${}的详细解析，内容清晰明了，对此有兴趣的小伙伴可以学习一下，相信大家阅读完之后会有帮助。

1 #{}和${}的区别、及注入问题

(1) 区别：首先清楚一点，动态 SQL 是 mybatis 的强大特性之一，在 mapper 中定义的参数传到 xml 中之后，在查询之前 mybatis 会对其进行动态解析，#{} 和 ${} 在预编译中的处理是不一样的：例如：select * from t_user where userName = #{name}; #{}预编译：用一个占位符 ? 代替参数:select * from t_user where userName = ? #{}预编译：会将参数值一起进行编译：select * from t_user where userName = 'zhangsan'(2) 使用场景：一般情况首选#{}，因为这样能避免sql注入；如果需要传参动态表名、动态字段名时，需要使用${} 比如：select * from ${tableName} where id > #{id};(3) SQL注入问题：举个例子，如果使用${}出现的注入问题： select * from ${tableName}; 如果传参 t_user;delete from t_user，则预编译后的sql如下，将会导致系统不可用： select * from t_user;delete from t_user;(4) like 语句防注入：使用concat函数： select * from t_user where name like concat('%', #{name}, '%')

2 mybatis几种传参方式

非注解：(1)单参数：public User getUserByUuid(String uuid); <select id="getUserByUuid" resultMap="BaseResultMap" parameterType="Object"> SELECT * FROM t_user WHERE uuid = #{uuid}</select>(2)多参数public User getUserByNameAndPass(String name,String pass); <select id="getUserByNameAndPass" resultMap="BaseResultMap" parameterType="Object"> SELECT * FROM t_user WHERE t_name = #{0} and t_pass = #{1}</select>(3)Map参数public User getUserByMap(Map<String,Object> map);<select id="getUserByMap" resultMap="BaseResultMap" parameterType="java.util.Map"> SELECT * FROM t_user WHERE t_name = #{name} and t_pass = #{pass}</select>(4)实体对象参数public int updateUser(User user); <select id="updateUser" resultMap="BaseResultMap" parameterType="Object"> update t_user set t_name = #{name}, t_pass = #{pass} where uuid=#{uuid}</select>(4)List集合参数public int batchDelUser(List<String> uuidList);<delete id="batchDelUser" parameterType="java.util.List"> DELETE FROM t_user WHERE uuid IN <foreach collection="list" index="index" item="uuid" open="(" separator="," close=")"> #{uuid} </foreach></delete>注解：public List<User> getUserByTime(@Param("startTime")String startTime, @Param("endTime")String endTime);<select id="getUserByTime" resultMap="BaseResultMap" parameterType="Object"> SELECT * from t_user where createTime >= #{startTime} and createTime <= #{endTime}</select>

2 choose when otherwise

//JAVA 代码public List<Group> getUserRoleRelByUserUuid(@Param("groupUuid") String userUuid,@Param("roleList")List<String> roleUuidList);//SQLSELECT * from user_role where groupUuid=#{groupUuid} <choose> <when test="roleList!=null&&roleList.size()>0"> AND roleUuid IN <foreach collection="roleList" index="index" item="roleUuid" open="(" separator="," close=")"> #{roleUuid} </foreach> </when> <otherwise> AND roleUuid IN ('') </otherwise> </choose>

3 判断字符串相等

//JAVA 代码public int getOrderCountByParams(Map<String, Object> params);//SQL<select id="getOrderCountByParams" resultType="java.lang.Integer" parameterType="Object"> SELECT count(*) FROM itil_publish_order where 1=1 <if test="timeType == '1'.toString()" > AND create_time >= #{timeStr} </if> <if test="timeType == '2'.toString()" > AND end_time <= #{timeStr} </if> </select>或者<if test = 'timeType== "1"'> </if>

4 CONCAT函数实现模糊匹配

<select id="getMaxSerialCode" resultType="java.lang.String" parameterType="Object"> SELECT count(*) FROM itil_publish_order WHERE serial_code LIKE CONCAT('%',#{codeStr},'%') ORDER BY serial_code DESC LIMIT 1</select>

5 大于等于、小于等于

//JAVA代码public List<PublishOrder> getOrderCount(@Param("startTime") String startTime,@Param("startTime")List<String> startTime);//SQL<select id="getOrderCount" resultType="java.lang.String" parameterType="Object"> SELECT * FROM itil_publish_order WHERE createTime >= #{startTime} and <= #{startTime}</select>

看完上述内容，是不是对mybatis中#{}和${}的详细解析有进一步的了解，如果还想学习更多内容，欢迎关注亿速云行业资讯频道。