1.XML映射器
2.select
Select元素来定义查询操作
Id：唯一标识符
- 用来引用这条语句，需要和接口的方法名一致
parameterType：参数类型
- 可以不传，MyBatis会根据TypeHandler自动推断
resultType：返回值类型
- 别名或者全类名，如果返回的是集合，定义集合中元素的类型。不能和resultMap同时使用
1)返回List
public List<Employee> getEmpsByLastNameLike(String lastName);
<!-- public List<Employee> getEmpsByLastNameLike(String lastName) -->
<select id="getEmpsByLastNameLike" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where last_name like #{lastName}
</select>
List<Employee> like = mapper.getEmpsByLastNameLike("%e%");
for(Employee employee : like) {
System.out.println(employee);
}
2)返回单条Map数据
//返回一条记录的map key:列名 value:值 public Map<String,Object> getEmpByIdReturnMap(Integer id);
<!-- public Map<String,Object> getEmpByIdReturnMap(Integer id) -->
<!-- resultType:期望从这条语句中返回结果的类全限定名或别名.因为Map类型已经内置了所以我们只需要填map即可 -->
<select id="getEmpByIdReturnMap" resultType="map">
select * from tbl_employee where id=#{id}
</select>
Map<String, Object> map = mapper.getEmpByIdReturnMap(1); System.out.println(map);
3)返回多条Map数据
//多条记录封装为一个map Map<String, Employee> key:记录的主键 value:记录封装后的javabean
//通知mybatis封装成这个map的时候使用那个属性作为主键
// @MapKey("lastName")
// public Map<String,Employee> getEmpByLastNameLikeReturnMap(String lastName);
@MapKey("id")
public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName);
<!-- public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName) -->
<select id="getEmpByLastNameLikeReturnMap" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where last_name like #{lastName}
</select>
// Map<String, Employee> map = mapper.getEmpByLastNameLikeReturnMap("%r%");
// System.out.println(map);
Map<Integer, Employee> map = mapper.getEmpByLastNameLikeReturnMap("%r%");
System.out.println(map);
4)结果映射
5)高级结果映射
(1)自动映射
①.开启驼峰命名(默认规则)
对比开启mapUnderscoreToCamelCase和不开启mapUnderscoreToCamelCase的区别.
开启:Employee [id=1, lastName=plutoo, email=plutoo@atguigu.com, gender=1]
关闭:Employee [id=1, lastName=null, email=plutoo@atguigu.com, gender=1]
通过'开启'和'关闭'两个对比，我们清楚到在开启驼峰命名的情况下，mybatis会自动帮我们进行last_name的封装.
<settings > <setting name="mapUnderscoreToCamelCase" value="true"/> </settings>
<!-- public Employee getEmpByid(Integer id) -->
<!-- resultType:使用了emp是因为我们起了别名@Alias("emp") -->
<select id="getEmpById" resultType="emp">
select * from tbl_employee where id = #{id}
</select>
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
Employee empById = mapper.getEmpById(1);
System.out.println(empById);
}finally {
openSession.close();
}
}
②.关闭驼峰命名(自定义规则)
对比开启mapUnderscoreToCamelCase和不开启mapUnderscoreToCamelCase的区别.
开启:Employee [id=1, lastName=plutoo, email=plutoo@atguigu.com, gender=1]
关闭:Employee [id=1, lastName=plutoo, email=plutoo@atguigu.com, gender=1]
通过'开启'和'关闭'两个对比，我们是否开启驼峰命名的情况下，对mybatis我们进行last_name的封装并无影响,因为我们自定义了封装规则.
public Employee getEmpById(Integer id);
<settings>
<!-- <setting name="mapUnderscoreToCamelCase" value="true"/> -->
</settings>
<!-- 自定义某个javaBean的封装规则 -->
<!-- type:自定义规则的Java类型 -->
<!-- id:唯一id方便引用 -->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MySimpleEmp">
<!-- 指定主键列的封装规则,id定义主键底层会有优化 -->
<!-- column:指定列 -->
<!-- property:指定对应的javaBean属性 -->
<id column="id" property="id"/>
<!-- 定义普通封装规则 -->
<result column="last_name" property="lastName"/>
<!-- 其他不指定的列会自动封装.只要写resultMap就把全部的映射规则补全 -->
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</resultMap>
<!-- public Employee getEmpByid(Integer id) -->
<!-- resultType:使用了emp是因为我们起了别名@Alias("emp") -->
<!-- resultMap:对外部 resultMap 的命名引用。结果映射是 MyBatis 最强大的特性，如果你对其理解透彻，许多复杂的映射问题都能迎刃而解。 -->
<!-- resultType 和 resultMap 之间只能同时使用一个 -->
<select id="getEmpById" resultMap="MySimpleEmp">
select * from tbl_employee where id = #{id}
</select>
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
Employee empById = mapper.getEmpById(1);
System.out.println(empById);
}finally {
openSession.close();
}
}
(2)resultMap的应用场景
①.查询Employee的同时查询员工对应的部门(联合查询) Employee===Department
(1.数据表的创建
CREATE TABLE tbl_dept( id INT(11) PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(255) )
(2.添加外键约束
ALTER TABLE tbl_employee ADD COLUMN d_id INT(11); ALTER TABLE tbl_employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(d_id) REFERENCES tbl_dept(id);
(3.配置resultMap
public Employee getEmpAndDept(Integer id);
<!-- 联合查询:级联属性封装结果集 -->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyDifEmp">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="did" property="dept.id"/>
<result column="dept_name" property="dept.departmentName"/>
</resultMap>
<!-- public Employee getEmpAndDept(Integer id) -->
<!-- 场景一:查询Employee的同时查询员工对应的部门 -->
<!-- 员工与对应部门信息:id last_name gender d_id did dept_name (private Department dept;) -->
<select id="getEmpAndDept" resultMap="MyDifEmp">
SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name
FROM tbl_employee e,tbl_dept d
WHERE e.d_id=d.id AND e.id=#{id}
</select>
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
Employee empAndDept = mapper.getEmpAndDept(1);
System.out.println(empAndDept);
System.out.println(empAndDept.getDept());
}finally {
openSession.close();
}
}
②.查询Employee的同时查询员工对应的部门(association) Employee===Department 
public Employee getEmpAndDept(Integer id);
<!-- 使用association定义关联的单个对象的封装规则 -->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyDifEmp2">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<!-- association:可以指定联合的javaBean对象 -->
<!-- property:映射到列结果的字段或属性。如果用来匹配的 JavaBean 存在给定名字的属性，那么它将会被使用。 -->
<!-- javaType:JDBC类型.只需要在可能执行插入、更新和删除的且允许空值的列上指定 JDBC 类型。这是 JDBC 的要求而非 MyBatis 的要求。如果你直接面向 JDBC 编程，你需要对可能存在空值的列指定这个类型。 -->
<!-- <id column="did" property="id"/>|<id column="id" property="id"/> 这两个property都表示是id,但是column不可一样否则认定为同一个id -->
<association property="dept" javaType="com.atguigu.mybatis.bean.Department">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
</association>
</resultMap>
<!-- public Employee getEmpAndDept(Integer id) -->
<!-- 场景一:查询Employee的同时查询员工对应的部门 -->
<!-- 员工与对应部门信息:id last_name gender d_id did dept_name (private Department dept;) -->
<select id="getEmpAndDept" resultMap="MyDifEmp2">
SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name
FROM tbl_employee e,tbl_dept d
WHERE e.d_id=d.id AND e.id=#{id}
</select>
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
Employee empAndDept = mapper.getEmpAndDept(1);
System.out.println(empAndDept);
System.out.println(empAndDept.getDept());
}finally {
openSession.close();
}
}
③.查询Employee的同时查询员工对应的部门(association分步查询)
(1.Department.java
public class Department {
private Integer id;
private String departmentName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department [id=" + id + ", departmentName=" + departmentName + "]";
}
}
(2.DepartmentMapper.java
public interface DepartmentMapper {
public Department getDeptById(Integer id);
}
(3.EmployeeMapperPlus.java
public interface EmployeeMapperPlus {
public Employee getEmpByIdStep(Integer id);
}
(4.DepartmentMapper.xml
<mapper namespace="com.atguigu.mybatis.dao.DepartmentMapper">
<!-- public Department getDeptById(Integer id) -->
<select id="getDeptById" resultType="com.atguigu.mybatis.bean.Department">
select dept_name departmentName,id from tbl_dept where id=#{id}
</select>
</mapper>
(5.Employee.java
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
private Department dept;
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
public Employee() {
super();
// TODO Auto-generated constructor stub
}
public Employee(Integer id, String lastName, String email, String gender) {
this.id = id;
this.lastName = lastName;
this.email = email;
this.gender = gender;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
}
}
(6.EmployeeMapperPlus.xml
<mapper namespace="com.atguigu.mybatis.dao.EmployeeMapperPlus">
<!-- id last_name email gender d_id -->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyEmpByStep">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!-- association定义关联对象的封装规则 -->
<!-- select:表明当前属性是调用select指定的方法查出的结果 -->
<!-- column:指定将一列的值传给这个方法 -->
<!-- 流程:使用select指定的方法（传入column指定的这列参数的值）查出对象，并封装给property指定的属性-->
<association property="dept"
select="com.atguigu.mybatis.dao.DepartmentMapper.getDeptById"
column="d_id">
</association>
</resultMap>
<!-- 使用association进行分步查询 -->
<!-- 1、先按照员工id查询员工信息 -->
<!-- 2、根据查询员工信息中的d_id值去部门表查出部门信息 -->
<!-- 3、部门设置到员工中 -->
<!-- public Employee getEmpByIdStep(Integer id) -->
<select id="getEmpByIdStep" resultMap="MyEmpByStep">
select * from tbl_employee where id=#{id}
</select>
</mapper>
(7.MyBatisTest.java
@Test
public void test05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
Employee employee = mapper.getEmpByIdStep(1);
System.out.println(employee);
System.out.println(employee.getDept());
}finally {
openSession.close();
}
}
(8.延迟加载
观察Test中的查询两条的结果.通过控制台我们可以知道，开启了延迟加载后.我们查询的结果为单条单条的查询,按查询需要给控制台，不会一下子把查询结果全部给控制台.
旧版本的MyBatis需要额外的支持包
– asm-3.3.1.jar
– cglib-2.2.2.jar
<!-- 使用延迟加载(懒加载) (按需加载) --> <!-- 分段查询的基础之上加上两个配置 --> <!-- mybatis-config.xml全局配置中在<settings>中添加一下两条 --> <!-- <setting name="lazyLoadingEnabled" value="true"/> --> <!-- <setting name="aggressiveLazyLoading" value="false"/> -->
<settings > <!--显示的指定我们需要更改的配置的值，即使默认的。防止版本迭代后带来的问题 --> <setting name="lazyLoadingEnabled" value="true"/> <setting name="aggressiveLazyLoading" value="false"/> </settings>
System.out.println(employee); ------------------------------------------------------------------------------------------- System.out.println(employee); System.out.println(employee.getLastName());
④.查询部门的时候将部门对应的所有员工信息也查询出来
public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
}
<mapper namespace="com.atguigu.mybatis.dao.DepartmentMapper">
<!-- private Integer id; private String departmentName; private List<Employee> emps; -->
<!-- did dept_name ||(分割) eid last_name email gender -->
<!-- 嵌套结果集的方式，使用collection标签定义关联的集合类型的属性封装规则 -->
<resultMap type="com.atguigu.mybatis.bean.Department" id="MyDept">
<id column="did" property="id"/>
<result column="dept_name" property="departmentName"/>
<!-- collection:定义关联集合类型的属性的封装规则 -->
<!-- ofType:指定集合里面元素的类型 -->
<collection property="emps" ofType="com.atguigu.mybatis.bean.Employee">
<!-- 定义集合中元素的封装规则 -->
<id column="eid" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</collection>
</resultMap>
<!-- public Department getDeptByIdPlus(Integer id) -->
<select id="getDeptByIdPlus" resultMap="MyDept">
SELECT d.id did,d.dept_name dept_name,e.id eid,e.last_name last_name,e.email email,e.gender gender
FROM tbl_dept d
LEFT JOIN tbl_employee e
ON d.id=e.d_id
WHERE d.id=#{id}
</select>
</mapper>
@Test
public void test06() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
Department department = mapper.getDeptByIdPlus(1);
System.out.println(department);
System.out.println(department.getEmps());
}finally {
openSession.close();
}
}
⑤.查询部门的时候将部门对应的所有员工信息也查询出来(分步查询)
public interface EmployeeMapperPlus {
public List<Employee> getEmpsByDeptId(Integer deptId);
}
<mapper namespace="com.atguigu.mybatis.dao.EmployeeMapperPlus">
<!-- 场景二:查询部门的时候将部门对应的所有员工信息也查询出来;注释在DepartmentMapper.xml中 -->
<!-- public List<Employee> getEmpsByDeptId(Integer deptId) -->
<select id="getEmpsByDeptId" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where d_id=#{deptId}
</select>
</mapper>
<mapper namespace="com.atguigu.mybatis.dao.DepartmentMapper">
<!-- collection分段查询 -->
<resultMap type="com.atguigu.mybatis.bean.Department" id="MyDeptStep">
<id column="id" property="id"/>
<id column="dept_name" property="departmentName"/>
<collection property="emps"
select="com.atguigu.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
column="{deptId=id}" fetchType="lazy">
</collection>
</resultMap>
<!-- public Department getDeptByIdStep(Integer id) -->
<select id="getDeptByIdStep" resultMap="MyDeptStep">
select id,dept_name departmentName from tbl_dept where id=#{id}
</select>
</mapper>
@Test
public void test06() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
Department deptByIdStep = mapper.getDeptByIdStep(1);
System.out.println(deptByIdStep);
System.out.println(deptByIdStep.getEmps());
}finally {
openSession.close();
}
}
延迟加载
<!-- 场景二:查询部门的时候将部门对应的所有员工信息也查询出来;注释在DepartmentMapper.xml中 -->
<!-- public List<Employee> getEmpsByDeptId(Integer deptId) -->
<select id="getEmpsByDeptId" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where d_id=#{deptId}
</select>
<!-- collection分段查询 -->
<!-- 扩展:需要将多列的值传递 -->
<!-- 将多列的值封装map传递 -->
<!-- column="{key1=column1,key2=column2}" -->
<!-- fetchType="lazy|eager":表示使用延迟加载 lazy:延迟 eager:立即 -->
<resultMap type="com.atguigu.mybatis.bean.Department" id="MyDeptStep">
<id column="id" property="id"/>
<id column="dept_name" property="departmentName"/>
<collection property="emps"
select="com.atguigu.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
column="{deptId=id}" fetchType="lazy">
</collection>
</resultMap>
6).鉴别器
Department deptByIdStep = mapper.getDeptByIdStep(1); System.out.println(deptByIdStep); System.out.println(deptByIdStep.getEmps());
<!-- <discriminator javaType=""></discriminator> -->
<!-- 鉴别器:一个数据库查询可能会返回多个不同的结果集（但总体上还是有一定的联系的）。 鉴别器元素就是被设计来应对这种情况的 -->
<!-- 如果查出的是女生:就把部门信息查询出来,否则不查询 -->
<!-- 如果是男生:把last_name这一列的值赋值给email -->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyEmpDis">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!-- column:指定判定的列名 -->
<!-- javaType:列值对应的java类型 -->
<discriminator javaType="string" column="gender">
<!-- resultType:指定封装的结果类型；不能缺少 -->
<!-- 女生:就把部门信息查询出来 -->
<case value="0" resultType="com.atguigu.mybatis.bean.Employee">
<association property="dept"
select="com.atguigu.mybatis.dao.DepartmentMapper.getDeptById"
column="d_id">
</association>
</case>
<!-- 男生:把last_name这一列的值赋值给email -->
<case value="1" resultType="com.atguigu.mybatis.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="last_name" property="email"/>
<result column="gender" property="gender"/>
</case>
</discriminator>
</resultMap>
<select id="getEmpByIdStep" resultMap="MyEmpDis">
select * from tbl_employee where id=#{id}
</select>
3.insert update delete
1)insert
(1)支持自增方式数据库
若数据库支持自动生成主键的字段（比如 MySQL和 SQL Server）,则可以设置useGeneratedKeys=”true”，然后再把keyProperty 设置到目标属性上
public void addEmp(Employee employee);
<!-- public void addEmp(Employee employee) -->
<!-- parameterType:将会传入这条语句的参数的类全限定名或别名。这个属性是可选的。 -->
<!-- 获取自增主键的值 -->
<!-- mysql支持自增主键，自增主键值的获取，mybatis也是利用statement.getGenreatedKeys() -->
<!-- useGeneratedKeys:使用自增主键获取主键值策略 true|false -->
<!-- keyProperty:指定对应的主键属性;mybatis获取到主键值以后，将值封装给指定的javaBean属性 -->
<insert id="addEmp" parameterType="com.atguigu.mybatis.bean.Employee" useGeneratedKeys="true"
keyProperty="id">
insert into tbl_employee(last_name,email,gender) values(#{lastName},#{email},#{gender})
</insert>
(2)不支持自增方式数据库
而对于不支持自增型主键的数据库（例如Oracle），则可以使用 selectKey 子元素：selectKey 元素将会首先运行，id 会被设置，然后插入语句会被调用
public void addEmp(Employee employee);
<!-- 获取非自增主键的值:Oracle不支持自增.Oracle使用序列来模拟自增.每次插入的数据的主键是从序列中拿到的值.-->
<!-- selectKey:执行查询Key操作 -->
<!-- keyProperty:指定对应的主键属性;mybatis获取到主键值以后，将值封装给指定的javaBean属性 -->
<!-- statementType:MyBatis 支持 STATEMENT，PREPARED 和 CALLABLE 类型的映射语句，分别代表 Statement, PreparedStatement 和 CallableStatement 类型。 -->
<!-- resultType:结果的类型 -->
<!-- order:可以设置为 BEFORE 或 AFTER。如果设置为 BEFORE，它首先会生成主键，设置 keyProperty 再执行插入语句。如果设置为 AFTER，先执行插入语句，然后 selectKey 中的语句 -->
<insert id="addEmp" databaseId="oracle">
<selectKey keyProperty="id" order="BEFORE" resultType="Integer">
<!-- 编写查询主键的sql语句 -->
<!-- BEFORE -->
select EMPLOYEES_SEQ.nextval from dual
<!-- AFTER：-->
<!-- select EMPLOYEES_SEQ.currval from dual -->
</selectKey>
<!-- 插入时的主键是从序列中拿到的 -->
<!-- BEFORE:-->
insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL) values(#{id},#{lastName},#{email<!-- ,jdbcType=NULL -->})
<!-- AFTER：-->
<!-- insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL) values(employees_seq.nextval,#{lastName},#{email}) -->
</insert>
2)update
public void updateEmp(Employee employee);
<!-- public void updateEmp(Employee employee) -->
<update id="updateEmp">
update tbl_employee set last_name=#{lastName},email=#{email},gender=#{gender} where id=#{id}
</update>
3)delete
public void deleteEmpById(Integer id);
<!-- public void deleteEmpById(Integer id) -->
<delete id="deleteEmpById">
delete from tbl_employee where id=#{id}
</delete>
4)Test
/**
* 测试增删改
* 1、mybatis允许增删改直接定义一下的类型返回值 Integer Long Boolean void
* 2、我们需要手动提交数据
* 手动提交:sqlSessionFactory.openSession()
* 自动提交:sqlSessionFactory.openSession(true)
* @throws IOException
*
*/
@Test
public void test03() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// 测试添加
Employee employee = new Employee(null, "jerry", "atguigu@atguigu.com", "1");
mapper.addEmp(employee);
System.out.println(employee.getId());
//测试修改
// Employee employee = new Employee(1, "pluto", "pluto@pluto.com", "0");
// mapper.updateEmp(employee);
//测试删除
// mapper.deleteEmpById(2);
openSession.commit();
}finally {
openSession.close();
}
}
4.MyBatis参数
1)单个参数
可以接受基本类型，对象类型，集合类型的值。这种情况MyBatis可直接使用这个参数，不需要经过任何处理
public Employee getEmpById(Integer id);
select * from tbl_employee where id = #{id}
2)多个参数
异常：org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [1, 0, param1, param2]
public Employee getEmpByIdAndLastName(Integer id,String lastName);
select * from tbl_employee where id = #{id} and last_name=#{lastName}
任意多个参数，都会被MyBatis重新包装成一个Map传入.
Map的key是param1，param2，0，1…，值就是参数的值.
select * from tbl_employee where id=#{param1} and last_name=#{param2}
(1)命名参数
参数使用@Param起一个名字，MyBatis就会将这些参数封装进map中，key就是我们自己指定的名字.
- 明确指定封装参数时map的key；@Param("id");
- 多个参数会被封装成一个map
- key：使用@Param注解指定的值|value：参数值
- #{指定的key}取出对应的参数值
public Employee getEmpByIdAndLastName(@Param("id")Integer id,@Param("lastName")String lastName);
select * from tbl_employee where id=#{id} and last_name=#{lastName}
@Test
public void test04() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//1.获取到sqlSessionFactory但是不会自动提交数据
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmpByIdAndLastName(1, "plutoo");
System.out.println(employee);
}finally {
openSession.close();
}
}
(2)POJO
当这些参数属于我们业务POJO时.换句话说，如果多个参数正好是我们业务逻辑的数据模型，我们直接传递POJO.
public void getEmpPoJo(Employee employee);
<!-- public Employee getEmpPoJo(Integer id,String email) -->
<select id="getEmpPoJo" resultType="com.atguigu.mybatis.bean.Employee">
update tbl_employee
set last_name=#{lastName},email=#{email},gender=#{gender}
where id=#{id}
</select>
@Test
public void test04() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee(1, "plutoo", "plutoo@atguigu.com", "1");
mapper.getEmpPoJo(employee);
}finally {
openSession.close();
}
}
(3)Map
我们也可以封装多个参数为map，直接传递.
如果多个参数不是业务模型中的数据，没有对应的pojo，不经常使用，为了方便，我们也可以传入map
public Employee getEmpByMap(Map<String,Object> map);
<!-- public Employee getEmpByMap(Map<String,Object> map) -->
<select id="getEmpByMap" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where id=#{id} and last_name=#{lastName}
</select>
@Test
public void test04() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//1.获取到sqlSessionFactory但是不会自动提交数据
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
// Employee employee = mapper.getEmpByIdAndLastName(1, "pluto");
Map<String, Object> map = new HashMap<>();
map.put("id", 1);
map.put("lastName", "pluto");
Employee employee = mapper.getEmpByMap(map);
mapper.getEmpByMap(map);
System.out.println(employee);
}finally {
openSession.close();
}
}
(4)To:
如果多个参数不是业务模型中的数据，没有对应的pojo，不经常使用，为了方便，我们也可以传入map
(5)参数举例
public Employee getEmp(@Param("id")Integer id,String lastName);
取值：id==>#{id/param1} lastName==>#{param2}
public Employee getEmp(Integer id,@Param("e")Employee emp);
取值：id==>#{param1} lastName===>#{param2.lastName/e.lastName}
##特别注意：如果是Collection（List、Set）类型或者是数组，
也会特殊处理。也是把传入的list或者数组封装在map中。
key：Collection（collection）,如果是List还可以使用这个key(list)
数组(array)
public Employee getEmpById(List<Integer> ids);
取值：取出第一个id的值： #{list[0]}
(6)查看源码 MyBati怎么处理参数
参数多时会封装map，为了不混乱，我们可以使用@Param来指定封装时使用的key
public Object getNamedParams(Object[] args) {
final int paramCount = names.size();
//1、参数为null直接返回
if (args == null || paramCount == 0) {
return null;
//2、如果只有一个元素，并且没有Param注解；args[0]：单个参数直接返回
} else if (!hasParamAnnotation && paramCount == 1) {
return args[names.firstKey()];
//3、多个元素或者有Param标注
} else {
final Map<String, Object> param = new ParamMap<Object>();
int i = 0;
//4、遍历names集合；{0=id, 1=lastName,2=2}
for (Map.Entry<Integer, String> entry : names.entrySet()) {
//names集合的value作为key; names集合的key又作为取值的参考args[0]:args【1，"Tom"】:
//eg:{id=args[0]:1,lastName=args[1]:Tom,2=args[2]}
param.put(entry.getValue(), args[entry.getKey()]);
// add generic param names (param1, param2, ...)param
//额外的将每一个参数也保存到map中，使用新的key：param1...paramN
//效果：有Param注解可以#{指定的key}，或者#{param1}
final String genericParamName = GENERIC_NAME_PREFIX + String.valueOf(i + 1);
// ensure not to overwrite parameter named with @Param
if (!names.containsValue(genericParamName)) {
param.put(genericParamName, args[entry.getKey()]);
}
i++;
}
return param;
}
}
}
(@Param("id")Integer id,@Param("lastName")String lastName);
ParamNameResolver解析参数封装map的
//names：{0=id, 1=lastName}；构造器的时候就确定好了
流程：
①.获取每个标了param注解的参数的@Param的值:id，lastName赋值给name
②.每次解析一个参数给map中保存信息：（key：参数索引，value：name的值）
- name的值：
- 标注
- param注解：注解的值
- 无标注：
- 全局配置：useActualParamName(jdk1.8):name=参数名
- name=map.size()；相当于当前元素的索引
(7)参数值的获取
#{}：可以获取map中的值或者pojo对象属性的值；
${}：可以获取map中的值或者pojo对象属性的值；
select * from tbl_employee where id=${id} and last_name=#{lastName}
Preparing: select * from tbl_employee where id=2 and last_name=?
select * from tbl_employee where id=#{id} and last_name=#{lastName}
Preparing: select * from tbl_employee where id=? and last_name=?
①.区别
#{}:是以预编译的形式，将参数设置到sql语句中；PreparedStatement；防止sql注入
${}:取出的值直接拼装在sql语句中；会有安全问题；
大多情况下，我们去参数的值都应该去使用#{}；
原生jdbc不支持占位符的地方我们就可以使用${}进行取值
比如分表、排序。。。；按照年份分表拆分
select * from ${year}_salary where xxx;
select * from tbl_employee order by ${f_name} ${order}
<!-- public Employee getEmpByMap(Map<String,Object> map) -->
<select id="getEmpByMap" resultType="com.atguigu.mybatis.bean.Employee">
select * from #{tableName} where id=#{id} and last_name=#{lastName}
</select>
<!-- public Employee getEmpByMap(Map<String,Object> map) -->
<select id="getEmpByMap" resultType="com.atguigu.mybatis.bean.Employee">
select * from ${tableName} where id=#{id} and last_name=#{lastName}
</select>
@Test
public void test04() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//1.获取到sqlSessionFactory但是不会自动提交数据
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("id", 2);
map.put("lastName", "plutoo");
map.put("tableName", "tbl_employee");
Employee employee = mapper.getEmpByMap(map);
mapper.getEmpByMap(map);
System.out.println(employee);
}finally {
openSession.close();
}
}
②.#{}用法
jdbcType
在我们数据为null的时候，有些数据库可能不能识别mybatis对null的默认处理。比如Oracle(报错)
JdbcType OTHER:无效的类型.因为mybatis对所有的null都映射的是原生Jdbc的OTHER类型，oracle不能正确处理.
全局配置中:jdbcTypeForNull=OTHER；oracle不支持；
两种办法
- #{email,jdbcType=OTHER};
- jdbcTypeForNull=NULL
<setting name="jdbcTypeForNull" value="NULL"/>
参考文档:https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#
https://mybatis.org/mybatis-3/zh/configuration.html#settings
|
<settings> <!-- <setting name="mapUnderscoreToCamelCase" value="true"/> --> </settings> |
|
<!-- 自定义某个javaBean的封装规则 --> <!-- type:自定义规则的Java类型 --> <!-- id:唯一id方便引用 --> <resultMap type="com.atguigu.mybatis.bean.Employee" id="MySimpleEmp"> <!-- 指定主键列的封装规则,id定义主键底层会有优化 --> <!-- column:指定列 --> <!-- property:指定对应的javaBean属性 --> <id column="id" property="id"/>
<!-- 定义普通封装规则 --> <result column="last_name" property="lastName"/>
<!-- 其他不指定的列会自动封装.只要写resultMap就把全部的映射规则补全 --> <result column="email" property="email"/> <result column="gender" property="gender"/> </resultMap>
<!-- public Employee getEmpByid(Integer id) --> <!-- resultType:使用了emp是因为我们起了别名@Alias("emp") --> <!-- resultMap:对外部 resultMap 的命名引用。结果映射是 MyBatis 最强大的特性，如果你对其理解透彻，许多复杂的映射问题都能迎刃而解。 --> <!-- resultType 和 resultMap 之间只能同时使用一个 --> <select id="getEmpById" resultMap="MySimpleEmp"> select * from tbl_employee where id = #{id} </select> |