1 #{}和${}的區(qū)別、及注入問題
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
(1) 區(qū)別： 首先清楚一點，動態(tài) SQL 是 mybatis 的強大特性之一，在 mapper 中定義的參數(shù)傳到 xml 中之后，在查詢之前 mybatis 會對其進行動態(tài)解析，#{} 和 ${} 在預(yù)編譯中的處理是不一樣的： 例如：select * from t_user where userName = #{name}; #{}預(yù)編譯：用一個占位符 ? 代替參數(shù):select * from t_user where userName = ? #{}預(yù)編譯：會將參數(shù)值一起進行編譯：select * from t_user where userName = 'zhangsan'(2) 使用場景： 一般情況首選#{}，因為這樣能避免sql注入；如果需要傳參 動態(tài)表名、動態(tài)字段名時，需要使用${} 比如：select * from ${tableName} where id > #{id};(3) SQL注入問題： 舉個例子，如果使用${}出現(xiàn)的注入問題： select * from ${tableName}; 如果傳參 t_user;delete from t_user，則預(yù)編譯后的sql如下，將會導(dǎo)致系統(tǒng)不可用： select * from t_user;delete from t_user;(4) like 語句防注入： 使用concat函數(shù)： 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)單參數(shù)：public User getUserByUuid(String uuid); <select id="getUserByUuid" resultMap="BaseResultMap" parameterType="Object"> SELECT * FROM t_user WHERE uuid = #{uuid}</select>(2)多參數(shù)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參數(shù)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)實體對象參數(shù)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集合參數(shù)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函數(shù)實現(xiàn) 模糊匹配
|
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> |
到此這篇關(guān)于mybatis #{}和${}的區(qū)別、傳參、基本語法的文章就介紹到這了,更多相關(guān)MyBatis中${}和#{}傳參的區(qū)別內(nèi)容請搜索服務(wù)器之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持服務(wù)器之家！
原文鏈接：https://blog.csdn.net/wutongyuWxc/article/details/84584239
