11-Mybatis中mapper文件中#和$的区别

上一篇：10-Mybatis参数的使用之五–通过Map传值-了解-不建议用https://blog.csdn.net/fsjwin/article/details/109670536
mapper文件中#和$的区别应该是面试过程中容易问到的， 但对这个知识点的掌握也相对比较重要。

1.#占位符

前面我们用了这个占位符，比如：

    <!--Map传值-->
    <select id="selectStudentByMap"  resultType="com.yuhl.domain.Student">
        select id,name,email,age from student where name =#{name} or age =#{age}
    </select>

底层使用的是PreparedStatment可以防止sql注入：

==>  Preparing: select id,name,email,age from student where name =? or age =?
==> Parameters: yuhl(String), 11(Integer)

可以看到底层使用的是？，的方式，在实战中我们使用的话使用的就是#，不实用$.

2.$占位符

底层使用的是Statement，会产生sql注入：

==>  Preparing: select id,name,email,age from student where name ='yuhl'
==> Parameters: 
<==    Columns: id, name, email, age
<==        Row: 1003, yuhl, yuhl@163.com, 20
<==      Total: 1

没有？出现了，这样很危险，不能使用它

2.1 可以作为传参的值

看个具体例子：

1. StudentDao

    /**
     *  $的使用
     *
     */
    public List<Student> selectStudent4$(@Param("name") String name);

2. StudentDao.xml

    <!--$-->
    <select id="selectStudent4$"  resultType="com.yuhl.domain.Student">
        select id,name,email,age from student where name =${name}
    </select>

 

3. 测试：

  @Test
    public void test8() {
        SqlSession sqlsession = MybatisUtil.getSqlsession();
        StudentDao studentDao = sqlsession.getMapper(StudentDao.class);
        //可以吧查询的条件放进去，也可以使用一个单独的类ParaObject类封装就可以了。
        Map map = new HashMap();
        map.put("name", "yuhl");
        map.put("age", 11);
        List<Student> studentList = studentDao.selectStudent4$("'yuhl'");
        studentList.forEach(stu -> System.out.println(stu));
    }

结论：

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1988859660.
==>  Preparing: select id,name,email,age from student where name ='yuhl'
==> Parameters: 
<==    Columns: id, name, email, age
<==        Row: 1003, yuhl, yuhl@163.com, 20
<==      Total: 1
Student{id=1003, name='yuhl', email='yuhl@163.com', age=20}

4. 特别注意
   拼接需要加‘’单引号哦
   

2.2 可以替换sql中任何值，比如类名、表名等

这里两个例子，替换表名和列明，你可以替换任何地方的值哦！！！

1. StudentDao

 /**
     *  $的使用-替换字符串表名类名等
     *
     */
    public List<Student> selectStudent4$Order(@Param("column") String column);

    /**
     *  $的使用-替换字符串表名--注意可以替换任何东西
     *
     */
    public List<Student> selectStudent4$Table(@Param("table") String table);

2. StudentDao.xml

  <!-- $的使用-替换字符串表名类名等-->
    <select id="selectStudent4$Order"  resultType="com.yuhl.domain.Student">
        select id,name,email,age from student order by ${column}
    </select>

    <!-- $的使用-替换字符串表名类名等-->
    <select id="selectStudent4$Table"  resultType="com.yuhl.domain.Student">
        select id,name,email,age from ${table}
    </select>

3. 测试

 @Test
    public void test9() {
        SqlSession sqlsession = MybatisUtil.getSqlsession();
        StudentDao studentDao = sqlsession.getMapper(StudentDao.class);
        //可以吧查询的条件放进去，也可以使用一个单独的类ParaObject类封装就可以了。
        Map map = new HashMap();
        map.put("name", "yuhl");
        map.put("age", 11);
        List<Student> studentList = studentDao.selectStudent4$Order("name");//column
        studentList.forEach(stu -> System.out.println(stu));
    }

    @Test
    public void test10() {
        SqlSession sqlsession = MybatisUtil.getSqlsession();
        StudentDao studentDao = sqlsession.getMapper(StudentDao.class);
        //可以吧查询的条件放进去，也可以使用一个单独的类ParaObject类封装就可以了。
        Map map = new HashMap();
        map.put("name", "yuhl");
        map.put("age", 11);
        List<Student> studentList = studentDao.selectStudent4$Table("student");//column
        studentList.forEach(stu -> System.out.println(stu));
    }

4. 测试结果

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1988859660.
==>  Preparing: select id,name,email,age from student order by name
==> Parameters: 
<==    Columns: id, name, email, age
<==        Row: 1004, rll, yuhl@163.com, 20
<==        Row: 1003, yuhl, yuhl@163.com, 20
<==        Row: 1005, 大佬, yuhl@163.com, 20
<==        Row: 1001, 张三, zhangsan@qq.com, 20
<==        Row: 1002, 李四, lisi@qq.com, 28
<==      Total: 5
Student{id=1004, name='rll', email='yuhl@163.com', age=20}
Student{id=1003, name='yuhl', email='yuhl@163.com', age=20}
Student{id=1005, name='大佬', email='yuhl@163.com', age=20}
Student{id=1001, name='张三', email='zhangsan@qq.com', age=20}
Student{id=1002, name='李四', email='lisi@qq.com', age=28}

另个结果：

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1988859660.
==>  Preparing: select id,name,email,age from student
==> Parameters: 
<==    Columns: id, name, email, age
<==        Row: 1001, 张三, zhangsan@qq.com, 20
<==        Row: 1002, 李四, lisi@qq.com, 28
<==        Row: 1003, yuhl, yuhl@163.com, 20
<==        Row: 1004, rll, yuhl@163.com, 20
<==        Row: 1005, 大佬, yuhl@163.com, 20
<==      Total: 5
Student{id=1001, name='张三', email='zhangsan@qq.com', age=20}
Student{id=1002, name='李四', email='lisi@qq.com', age=28}
Student{id=1003, name='yuhl', email='yuhl@163.com', age=20}
Student{id=1004, name='rll', email='yuhl@163.com', age=20}
Student{id=1005, name='大佬', email='yuhl@163.com', age=20}

3.总结

工作总使用#，不用$

下一篇：12-Mybatis封装输出结果之一 – resultType （含typeAliases）https://blog.csdn.net/fsjwin/article/details/109673170