目錄

Mybatis中Like模糊查詢?nèi)N處理方式

1.通過(guò)單引號(hào)拼接+${}

1）mapper接口

2）Mapper.xml

3）測(cè)試代碼

4) 測(cè)試結(jié)果

2.通過(guò)concat()函數(shù)拼接(個(gè)人推薦使用這種)

1）mapper接口

2）Mapper.xml

3）測(cè)試代碼

4) 測(cè)試結(jié)果

3.通過(guò)"%"#{}"%"?

1）mapper接口

2）Mapper.xml

3）測(cè)試代碼

4) 測(cè)試結(jié)果

附加

1.User實(shí)體

2.LikeMapper類

3.LikeMapperTest代碼

4.LikeMapper.xml文件

5.表結(jié)構(gòu)?

---

Mybatis中Like模糊查詢?nèi)N處理方式

1.通過(guò)單引號(hào)拼接+${}

????????這種方法使用了字符串替換的方式來(lái)進(jìn)行模糊查詢。但是這種方式存在SQL注入的風(fēng)險(xiǎn)，因?yàn)?code>"${name}"會(huì)直接將變量值插入到SQL語(yǔ)句中，如果輸入沒(méi)有經(jīng)過(guò)適當(dāng)?shù)倪^(guò)濾，則可能會(huì)導(dǎo)致安全問(wèn)題。

注:在XML文件中不建議使用'%${name}%'的方式，而是應(yīng)該使用concat()或者%' + #{name} + '% '來(lái)避免SQL注入。?

1）mapper接口

/*** 通過(guò)單引號(hào)拼接+${}*/
public List<User> getLikeBySingleQuote(String name);

2）Mapper.xml

<!--單引號(hào)拼接+${}-->
<select id="getLikeBySingleQuote" resultType="org.xiji.enty.User">select * from user where username like '%${name}%'
</select>

3）測(cè)試代碼

/*** 通過(guò)單引號(hào)拼接+${}*   '%${}%'*/
@Test
public void testGetLikeBySingleQuote(){String name = "xiji";List<User> likeBySingleQuote = likeMapper.getLikeBySingleQuote(name);System.out.println(likeBySingleQuote.toString());
}

4) 測(cè)試結(jié)果

2.通過(guò)concat()函數(shù)拼接(個(gè)人推薦使用這種)

????????使用數(shù)據(jù)庫(kù)的concat()函數(shù)可以避免SQL注入的問(wèn)題，并且是跨平臺(tái)的（MySQL, PostgreSQL等支持concat()或類似函數(shù)）。

1）mapper接口

/*** 通過(guò)ConCat函數(shù)拼接**/
public List<User> getLikeByConCat(String name);

2）Mapper.xml

<!--concat函數(shù)拼接-->
<select id="getLikeByConCat" resultType="org.xiji.enty.User">select * from user where username like concat('%',#{name},'%')
</select>

3）測(cè)試代碼

/*** 通過(guò)concat函數(shù)拼接*   concat('%',#{name},'%')*/
@Test
public void testGetLikeByConCat(){String name = "xiji";List<User> likeByConCat = likeMapper.getLikeByConCat(name);System.out.println(likeByConCat.toString());
}

4) 測(cè)試結(jié)果

3.通過(guò)"%"#{}"%"?

????????這種方式也是安全的，并且簡(jiǎn)潔。它使用了MyBatis的預(yù)編譯功能，自動(dòng)對(duì)參數(shù)進(jìn)行轉(zhuǎn)義，防止SQL注入攻擊。

注：雖然使用'%'#{name}'%'看起來(lái)簡(jiǎn)潔，但是在某些情況下，如果name包含特殊字符，可能需要進(jìn)一步的處理來(lái)保證安全性和正確性。因此，推薦使用concat()函數(shù)來(lái)構(gòu)建LIKE語(yǔ)句。

1）mapper接口

/*** 通過(guò) “%”#{}“%” 拼接*/
public List<User> getLikeByPercent(String name);

2）Mapper.xml

<!-- "%"#{}"%"  -->
<select id="getLikeByPercent" resultType="org.xiji.enty.User">select  * from user where username like "%"#{name}"%"
</select>

3）測(cè)試代碼

/***  通過(guò)通過(guò) "%"#{}"%" 拼接*   like '%#{name}%'*/@Test
public void testGetLikeByPercent(){String name = "xiji";List<User> likeByPercent = likeMapper.getLikeByPercent(name);System.out.println(likeByPercent.toString());
}

4) 測(cè)試結(jié)果

附加

1.User實(shí)體

package org.xiji.enty;public class User {private  int id;private String username;private String password;private String userInfo;public User() {}public User(int id, String username, String password, String userInfo) {this.id = id;this.username = username;this.password = password;this.userInfo = userInfo;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getUserInfo() {return userInfo;}public void setUserInfo(String userInfo) {this.userInfo = userInfo;}@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + '\'' +", password='" + password + '\'' +", userInfo='" + userInfo + '\'' +'}';}
}

2.LikeMapper類

package org.xiji.mapper;import org.apache.ibatis.annotations.Mapper;
import org.xiji.enty.User;import java.util.List;/*** 模糊查詢的三種方式*/
@Mapper
public interface LikeMapper {/*** 通過(guò)單引號(hào)拼接+${}*/public List<User> getLikeBySingleQuote(String name);/*** 通過(guò)ConCat函數(shù)拼接**/public List<User> getLikeByConCat(String name);/*** 通過(guò) “%”#{}“%” 拼接*/public List<User> getLikeByPercent(String name);}

3.LikeMapperTest代碼

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;
import org.xiji.enty.User;
import org.xiji.mapper.LikeMapper;import java.util.List;@SpringJUnitConfig(locations = {"classpath:springConfig.xml"})
public class LikeMapperTest {@Autowiredprivate LikeMapper likeMapper;/*** 通過(guò)單引號(hào)拼接+${}*   '%${}%'*/@Testpublic void testGetLikeBySingleQuote(){String name = "xiji";List<User> likeBySingleQuote = likeMapper.getLikeBySingleQuote(name);System.out.println(likeBySingleQuote.toString());}/*** 通過(guò)concat函數(shù)拼接*   concat('%',#{name},'%')*/@Testpublic void testGetLikeByConCat(){String name = "xiji";List<User> likeByConCat = likeMapper.getLikeByConCat(name);System.out.println(likeByConCat.toString());}/***  通過(guò)通過(guò) “%”#{}“%” 拼接*   like '%#{name}%'*/@Testpublic void testGetLikeByPercent(){String name = "xiji";List<User> likeByPercent = likeMapper.getLikeByPercent(name);System.out.println(likeByPercent.toString());}}

4.LikeMapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.xiji.mapper.LikeMapper"><!--模糊查詢的三種方式--><!--單引號(hào)拼接+${}--><select id="getLikeBySingleQuote" resultType="org.xiji.enty.User">select * from user where username like '%${name}%'</select><!--concat函數(shù)拼接--><select id="getLikeByConCat" resultType="org.xiji.enty.User">select * from user where username like concat('%',#{name},'%')</select><!-- ”%“#{}“%”  --><select id="getLikeByPercent" resultType="org.xiji.enty.User">select  * from user where username like "%"#{name}"%"</select>
</mapper>

5.表結(jié)構(gòu)?

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (`id` int NOT NULL AUTO_INCREMENT COMMENT '用戶id',`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用戶名字',`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用戶密碼',`userInfo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用戶信息',PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;