一、#{}、${}
#{}是使用占位符？的方式来传递参数，会对要传递的参数进行校验和处理，不会发生SQL注入攻击； ${}会将参数直接拼接到SQL中，会有SQL注入攻击风险。
二、关联查询
该查询案例涉及两个类，一个是员工类Employee，另一个是部门类Department。
Employee类代码
public class Employee {
private String id;
private String name;
private String phone;
private String email;
private Department department;
...
}
Department类代码
public class Department {
private String id;
private String name;
...
}
EmployeeMapper接口代码
public interface EmployeeMapper {
public Employee getEmployeeById(@Param("id") String id);
}
DepartmentMapper接口代码
public interface DepartmentMapper {
public Department getDepartmentById(@Param("id") String id);
}
1、一对一关联查询
将查询到的值一一对应关联对象的属性值
employeemapper.xml配置
<resultMap id="employee" type="com.mybatistest.pojo.Employee">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="phone" property="phone"/>
<result column="email" property="email"/>
<result column="department_id" property="department.id"/>
<result column="department_name" property="department.name"/>
</resultMap>
<select id="getEmployeeById" resultMap="employee">
select employee.id id,employee.name name, phone,email,
department.id department_id,department.name department_name
from employee inner join department on employee.department_id = department.id
where employee.id = #{id}
</select>
2、一对多关联查询
通过在resultMap中定义association标签定于关联的对象
employeemapper.xml配置
<resultMap id="employee" type="com.mybatistest.pojo.Employee">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="phone" property="phone"/>
<result column="email" property="email"/>
<association property="department" javaType="com.mybatistest.pojo.Department">
<id property="id" column="department_id"/>
<result property="name" column="department_name"/>
</association>
</resultMap>
<select id="getEmployeeById" resultMap="employee">
select employee.id id,employee.name name, phone,email,
department.id department_id,department.name department_name
from employee inner join department on employee.department_id = department.id
where employee.id = #{id}
</select>
3、分步查询
思路：首先通过EmployeeMapper查询员工表的数据，并将数据填充到Employee对象中，然后再调用DepartmentMapper根据第一步查询获取的department_id作为查询条件获取部门的数据，最后将数据填充到第一步获取的Employee对象中。
departmentmapper.xml配置
<select id="getDepartmentById" resultType="com.mybatistest.pojo.Department">
select id,name
from department
where id = #{id}
</select>
employeemapper.xml配置
<resultMap id="employee" type="com.mybatistest.pojo.Employee">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="phone" property="phone"/>
<result column="email" property="email"/>
<association property="department" select="com.mybatistest.dao.DepartmentMapper.getDepartmentById"
column="department_id"/>
</resultMap>
<select id="getEmployeeById" resultMap="employee">
select id, name, phone,email,department_id
from employee
where employee.id = #{id}
</select>
该查询存在的问题：适合使用外部查询结果只有一条记录的查询。如果外部查询有多条记录，那么内部查询会有多次查询。
解决方案：通过使用延时加载可以减少内部查询的次数。
4、延时加载 所谓的延时加载就是指再需要使用到数据时，才会查询。
a、全局延时加载配置
<setting name="lazyLoadingEnabled" value="true"/>
b、通过局部延时加载配置不需要延时加载的查询
<association property="department" select="com.mybatistest.dao.DepartmentMapper.getDepartmentById"
column="department_id" fetchType="eager"/>
启动延时加载配置时执行的操作：
@Test
public void testEmployeeMapper(){
SqlSession session = SqlSessionUlti.getSqlSession();
EmployeeMapper employeeMapper = null;
try {
employeeMapper = session.getMapper(EmployeeMapper.class);
Employee employee = employeeMapper.getEmployeeById("202100101");
String name = employee.getName();
System.out.println(name);
System.out.println("++++++++++++++++++++++++++++++++++++");
Department department = employee.getDepartment();
System.out.println(department);
}finally {
session.close();
}
}
执行结果：
==> Preparing: select id, name, phone,email,department_id from employee where employee.id = ?
==> Parameters: 202100101(String)
<== Columns: id, name, phone, email, department_id
<== Row: 202100101, 李四, 1234567, null, 10010
<== Total: 1
李四
++++++++++++++++++++++++++++++++++++
==> Preparing: select id,name from department where id = ?
==> Parameters: 10010(String)
<== Columns: id, name
<== Row: 10010, 销售部
<== Total: 1
Department{id='10010', name='销售部'}
注意事项：默认调用对象的equals,clone,hashCode,toString方法时，会触发加载操作。可以通过全局设置设置lazyLoadTriggerMethods的值，来设置触发的方法。
<!--只用获取需要加载的对象时，才会触发加载条件-->
<setting name="lazyLoadTriggerMethods" value=""/>
