Mybatis学习笔记6 - #{}和${}
#{}:可以获取map中的值或者pojo对象属性的值。
${}:可以获取map中的值或者pojo对象属性的值。
区别:
#{}:是以预编译的形式,将参数设置到sql语句中;PreparedStatement;防止sql注入
${}:取出的值直接拼装在sql语句中;会有安全问题;
大多情况下,我们去参数的值都应该去使用#{};
select * from tbl_employee where id=${id} and last_name=#{lastName}
Preparing: select * from tbl_employee where id=2 and last_name=?
${}的使用:原生jdbc不支持占位符的地方就可以使用${}进行取值
比如分表(按照年份分表拆分)、排序等
select * from ${year}_salary where xxx;
select * from tbl_employee order by ${f_name} ${order}
${}使用示例:
接口定义: package com.mybatis.dao; import com.mybatis.bean.Employee; import java.util.Map; public interface EmployeeMapper { public Employee getEmpByIdAndLastName(Map<String, Object> map); } mapper定义: <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mybatis.dao.EmployeeMapper"> <select id="getEmpByIdAndLastName" parameterType="java.util.Map" resultType="com.mybatis.bean.Employee"> select * from ${tableName} where id=${id} and last_name=#{lastName} </select> </mapper> 测试代码: package com.mybatis.demo; import java.io.*; import java.util.*; import com.mybatis.bean.Employee; import com.mybatis.dao.EmployeeMapper; import org.apache.ibatis.io.*; import org.apache.ibatis.session.*; import org.junit.Test; public class MyTest { public SqlSessionFactory getSqlSessionFactory() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); return new SqlSessionFactoryBuilder().build(inputStream); } @Test public void testSelect() throws IOException { SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(true); try { EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class); Map<String, Object> map = new HashMap<String, Object>(); map.put("id", 5); map.put("lastName", "jetty"); map.put("tableName", "tbl_employee"); Employee employee = mapper.getEmpByIdAndLastName(map); System.out.println(employee); } finally { openSession.close(); } } }
#{}更丰富的用法:
规定参数的一些规则:javaType、 jdbcType、 mode(存储过程)、 numericScale、resultMap、 typeHandler、 jdbcTypeName、 expression(未来准备支持的功能);
jdbcType通常需要在某种特定的条件下被设置:
在数据为null的时候,有些数据库可能不能识别mybatis对null的默认处理。比如Oracle(报错);
JdbcType OTHER:无效的类型;因为mybatis对所有的null都映射的是原生Jdbc的OTHER类型,oracle不能正确处理;
由于全局配置中:jdbcTypeForNull=OTHER;oracle不支持;两种办法
1、#{email,jdbcType=OTHER};
2、jdbcTypeForNull=NULL
<setting name="jdbcTypeForNull" value="NULL"/>