1 #{}和${}的區別、及注入問題
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
(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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
非注解：(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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
//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 判斷字符串相等
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
//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函數實現 模糊匹配
|
1
2
3
4
5
6
|
<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 大于等于、小于等于
|
1
2
3
4
5
6
7
|
//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 #{}和${}的區別、傳參、基本語法的文章就介紹到這了,更多相關MyBatis中${}和#{}傳參的區別內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://blog.csdn.net/wutongyuWxc/article/details/84584239
