相同点:
区别:
#{}:是以预编译的形式,将参数设置到sql语句中;PreparedStatement;防止sql注入;
${}:取出的值直接拼装在sql语句中;会有安全问题;
大多情况下,我们去参数的值都应该去使用#{};
什么情况下会使用 ${} 呢?
在原生 jdbc 不支持占位符的地方就可以使用${} 进行取值,比如分表(表名的拼接,按照年份分表拆分)、排序等。
select * from 2016_salary
select * from ${year}_salary where xxx;
select * from emp order by ${f_name} ${order}
1、#{} 取值
EmployeeMapper 接口中的方法:
public void insertEmp(Employee employee);
EmployeeMapper.xml 中的配置:
<!--public void insertEmp(Employee employee);
-->
<insert id="insertEmp"parameterType="com.njf.mybatis.bean.Employee">insert into tbl_employee(`last_name`, `email`, `gender`)
values(#{lastName}, #{email}, #{gender})
</insert>
测试:
@Test
public void testInsert() throwsIOException {
//1、获取 sqlSessionFactory
SqlSessionFactory sqlSessionFactory =getsqlSessionFactory();
//2、获取 sqlSession 实例,能直接执行已经映射的 SQL 语句
SqlSession sqlSession =sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee(null, "张三", "男", "zhangsan@126.com");
mapper.insertEmp(employee);
sqlSession.commit();
} finally{
sqlSession.close();
}
}
运行结果:
2、${} 取值
修改 mapper.xml 中的配置如下:
<!--public void insertEmp(Employee employee);
-->
<insert id="insertEmp"parameterType="com.njf.mybatis.bean.Employee">insert into tbl_employee(`last_name`, `email`, `gender`)
values(${lastName}, ${email}, ${gender})
</insert>
运行结果:
可以发现这时并没有运行成功,这条 SQL 语句与数据库的类型不匹配。
再次修改 mapper.xml,给 ${} 加上单引号!
<!--public void insertEmp(Employee employee);
-->
<insert id="insertEmp"parameterType="com.njf.mybatis.bean.Employee">insert into tbl_employee(`last_name`, `email`, `gender`)
values('${lastName}', '${email}', '${gender}')
</insert>
运行结果:
此时就可以运行成功了。
3、总结
select * from ${year}_salary where xxx; //按年份表查询
select * from tbl_employee order by ${f_name} ${order} //排序
1、当传输参数为单个String或基本数据类型和其包装类时
(1)#{}可以以任意的名字获取参数值
在接口中声明方法:
public Employee getEmployeeById(Integer id);
在对应的映射文件中配置:
<!--public Employee getEmployeeById(Integer id);
-->
<select id="getEmployeeById"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = #{id}
</select>
运行结果:
如果修改了Mapper.xml中的名字呢,还可以获取参数吗?
<!--public Employee getEmployeeById(Integer id);
-->
<select id="getEmployeeById"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = #{aaa}
</select>
运行结果:
可以看出,修改了SQL语句中的参数名字,也可以获取参数并赋值成功!
(2)${}只能以 ${value}或 ${_parameter}获取
mapper.xml 中的配置信息以 ${value} 取值
<!--public Employee getEmployeeById(Integer id);
-->
<select id="getEmployeeById"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee
where id = ${value}
</select>
第一次运行结果:
修改 mapper.xml 中的配置为:${_parameter}
<!--public Employee getEmployeeById(Integer id);
-->
<select id="getEmployeeById"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee
where id = ${_parameter}
</select>
第二次运行结果:
如果修改 mapper.xml中的配置为 ${id} 呢?
<!--public Employee getEmployeeById(Integer id);
-->
<select id="getEmployeeById"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee
where id = ${id}
</select>
运行结果:
运行失败了!
如果再改成随意的一个名字呢?
<!--public Employee getEmployeeById(Integer id);
-->
<select id="getEmployeeById"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee
where id = ${aaa}
</select>
运行结果:
还是运行失败!
所以,对于${}获取单个参数,只能使用 ${value}或 ${_parameter}
2、当传输参数为JavaBean时
3、当传输多个参数时(不用考虑类型)
当传输多个参数时,MyBatis会默认将这些参数放到Map集合中。
有两种方式来实现:
① 存储时键为:0,1,2,3,... N-1,值就是参数本身
② 存储时键为:param1,param2,param3...paramN,值是参数本身。
注意:每个参数都是有顺序的,获取和存放都是有顺序的。(内部用SortedMap来实现)
1、#{} 取值
有两种方式:
① 使用 #{0},#{1} 来依次获取参数;
② 使用#{param1}, #{param2},#{paramN} 依次来获取参数
mapper 的映射文件中以 #{N} 来获取参数
<!--public Employee getEmployeeByIdAndLastName(Integer id, String name);
-->
<select id="getEmployeeByIdAndLastName"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = #{0} and last_name = #{1}
</select>
运行结果:
mapper 的映射文件中以 #{paramN} 来获取参数
<!--public Employee getEmployeeByIdAndLastName(Integer id, String name);
-->
<select id="getEmployeeByIdAndLastName"resultType="Employee">
<!--select id, last_name lastName, email, gender from tbl_employee where id = #{0} and last_name = #{1}-->select id, last_name lastName, email, gender from tbl_employee where id = #{param1} and last_name = #{param2}
</select>
运行结果:
2、${} 取值
有一种方式:使用 ${param1},${param2}依次来获取参数,但是要注意 ${}的单引号问题!!!
(1)Mapper.xml中以 ${paramN}来获取参数
映射文件:
<!--public Employee getEmployeeByIdAndLastName(Integer id, String name);
-->
<select id="getEmployeeByIdAndLastName"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = ${param1} and last_name = ${param2}
</select>
运行结果:
并没有成功,对于String类型忘记加单引号了!!!
给String类型加上单引号后:
<!--public Employee getEmployeeByIdAndLastName(Integer id, String name);
-->
<select id="getEmployeeByIdAndLastName"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = ${param1} and last_name = '${param2}'
</select>
运行结果:
此时就可以运行成功了。
(2)Mapper.xml中以 ${0}来获取参数
映射文件配置:
<!--public Employee getEmployeeByIdAndLastName(Integer id, String name);
-->
<select id="getEmployeeByIdAndLastName"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = ${0} and last_name = ${1}
</select>
运行结果:
可以发现这种方式并不能正确获取参数。
那如果给String类型加上单引号,应该也是不可以的。
加上单引号后,以 '${0}'来获取参数
<!--public Employee getEmployeeByIdAndLastName(Integer id, String name);
-->
<select id="getEmployeeByIdAndLastName"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = ${0} and last_name = '${1}'
</select>
运行结果:
这种也不可以获取参数。
对于${} 取值的方式,所以只能使用 ${paramN}的方式来获取参数。
4、当传输自定义的Map参数时
public Employee getEmployeeByMap(Map<String, Object> map);
<!--public Employee getEmployeeByMap(Map<String, Object> map);
-->
<select id="getEmployeeByMap"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = #{id} and last_name = #{lastName}
</select>
@Test
public void test() throwsIOException {
//1、获取 sqlSessionFactory
SqlSessionFactory sqlSessionFactory =getsqlSessionFactory();
//2、获取 sqlSession 实例,能直接执行已经映射的 SQL 语句
SqlSession sqlSession =sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("id", "1"); //这里的 key 需要与 xml 中的 key 一致
map.put("lastName", "Tom"); //这里的 key 需要与 xml 中的 key 一致
Employee emp =mapper.getEmpByMap(map);
System.out.println("emp = " +emp);
} finally{
sqlSession.close();
}
}
<!--public Employee getEmployeeByMap(Map<String, Object> map);
-->
<select id="getEmployeeByMap"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = ${id} and last_name = '${lastName}'
</select>
5、命名参数
可以通过 @Param("key")为map集合指定键的名字,也可以使用 #{paramN}的方式来取值
Mapper 接口中的方法:
public Employee getEmpByIdAndEnameByParam(@Param("id") int id, @Param("lastName")String lastName);
Mapper.xml 中的配置以 #{key} 来取值:
<!--public Employee getEmpByIdAndEnameByParam(@Param("id")String id, @Param("lastName")String lastName);
-->
<select id="getEmpByIdAndEnameByParam"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = #{id} and last_name = #{lastName}
</select>
运行结果:
Mapper.xml中的配置以 ${key}来取值
<!--public Employee getEmpByIdAndEnameByParam(@Param("id")String id, @Param("lastName")String lastName);
-->
<select id="getEmpByIdAndEnameByParam"resultType="Employee">select id, last_name lastName, email, gender from tbl_employee where id = ${id} and last_name = '${lastName}'
</select>
运行结果:
6、当传输的是Collection/Array时
Collection/Array 会被MyBatis封装成一个map传入,Collection对应的 key是collection,Array对应的 key是array,如果确定是List集合,key还可以是list。
四、#{} 更丰富的用法1、参数位置支持的属性
规定参数的一些规则:
javaType、 jdbcType、 mode(存储过程)、 numericScale、resultMap、 typeHandler、 jdbcTypeName、 expression(未来准备支持的功能);
2、jdbcType通常需要在某种特定的条件下被设置
由于全局配置中:jdbcTypeForNull=OTHER;oracle不支持;两种办法
1、#{email,jdbcType=NULL};
2、jdbcTypeForNull=NULL
<setting name="jdbcTypeForNull" value="NULL"/>
实际上通常被设置的是:可能为空的列名指定 jdbcType
insert into orcl_employee(id,last_name,email,gender)
values(employee_seq.nextval,#{lastName,jdbcType=NULL },#{email},#{gender})
3、参数处理
参数也可以指定一个特殊的数据类型:
#{property, javaType=int, jdbcType=NUMERIC}
#{height, javaType=double, jdbcType=NUMERIC, numericScale=2}
javaType 通常可以从参数对象中来去确定;
如果 null 被当作值来传递,对于所有可能为空的列,jdbcType 需要被设置;
对于数值类型,还可以设置小数点后保留的位数;
mode 属性允许指定 IN, OUT 或 INOUT 参数。如果参数为 OUT 或 INOUT,参数对象属性的真实值将会被改变,就像在获取输出参数时所期望的那样。