Mybatis映射文件SQL语句模糊查询，#和$的区别和注意事项

心已赠人 2021-12-11 08:23 310阅读 0赞

# Mybatis映射文件SQL语句模糊查询 #

## 1. “%”\#\{value\}"%" 在参数中不需要添加 %\_ 推荐使用 ##

## 2. ‘%$\{value\}%’ 在参数中不需要添加 %\_ ##

## 3. \#\{abc\} 在参数中添加 %\_ ##

# \#和$的区别和注意事项 #

### $\{\}直接拼接 ，不会转换类型 ###

如果是简单类型，必须写${value}
    如果是pojo类型，${属性名}（注意：需要转换类型）

### \#\{\}相当于占位符，可以自动的转换类型 可以防止SQL注入 ###

如果是简单类型，可以随便写${随便写}
    如果是pojo类型，${属性名}（注意：可以自动转换类型）

# 一、\# “%”\#\{value\}"%" #

## 1.UserMapper.xml ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70]

<select id="findByUsername" parameterType="java.lang.String" resultType="com.william.domain.User">
            select * from user where username like "%"#{value}"%"
        </select>

## 2.TestMybatis ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 1]

/**
         * 模糊查询
         * 通过用户名字模糊查询
         */
        @Test
        public void findByUsername(){
            InputStream inputStream = Resources.class.getClassLoader().getResourceAsStream("Mybatis-configuration.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            List<User> selectList = sqlSession.selectList("com.william.mapper.UserMapper.findByUsername","a");
            for (Object o : selectList) {
                System.out.println(o);
            }
            sqlSession.close();
        }

## 3.测试结果 ##

### 执行的SQL语句 ###

Preparing: select * from user where username like "%"?"%" 
    Parameters: a(String)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 2]

# 二、$ ‘%$\{value\}%’ #

## 1.UserMapper.xml ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 3]

<select id="findByUsername" parameterType="java.lang.String" resultType="com.william.domain.User">
            select * from user where username like '%${value}%'
        </select>

## 2.TestMybatis ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 4]

## 3.测试结果 ##

### 执行的SQL语句 ###

Preparing: select * from user where username like '%a%' 
    Parameters:

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 5]

# 三、在测试类参数中添加 #

## 1.UserMapper.xml ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 6]

<select id="findByUsername" parameterType="java.lang.String" resultType="com.william.domain.User">
            select * from user where username like  #{abc}
        </select>

## 2.TestMybatis ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 7]

/**
         * 模糊查询
         * 通过用户名字模糊查询
         */
        @Test
        public void findByUsername(){
            InputStream inputStream = Resources.class.getClassLoader().getResourceAsStream("Mybatis-configuration.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            List<User> selectList = sqlSession.selectList("com.william.mapper.UserMapper.findByUsername","%a%");
            for (Object o : selectList) {
                System.out.println(o);
            }
            sqlSession.close();
        }

## 3.测试结果 ##

### 执行的SQL语句 ###

Preparing: select * from user where username like ? 
     Parameters: %a%(String)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 8]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70]: /images/20211211/cad5413f0dc648b5b1363a1f6aaf3b85.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 1]: /images/20211211/cd54c4ea3d734d80bb5dd4287b60c45c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 2]: /images/20211211/e91b992ba6f044758249f6bd76613333.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 3]: /images/20211211/2057ecec827b481caebfc77472ecfc3e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 4]: /images/20211211/952548693f6649859110a74d4d83f16a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 5]: /images/20211211/30807c0896b24aef9148682239865a61.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 6]: /images/20211211/1e65cc9585e6440f938e0f5cf4791f5e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 7]: /images/20211211/946828b20dd64e5091c87f698f605649.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM5MTc5OTkz_size_16_color_FFFFFF_t_70 8]: /images/20211211/ce701f6f97b74b34a49aec4425b73a4d.png