Mybatis笔记 03：映射文件

1. 基本的增删改操作

目前的版本已经可以无需指定参数类型（parameterType="Employee"可以省略）

<insert id="insertEmployee">
	insert into tbl_employee (last_name, email, gender)
	values (#{lastName}, #{email}, #{gender})
</insert>

<update id="updateEmployee">
	update tbl_employee
	set last_name = #{lastName}, email = #{email}, gender = #{gender}
	where id = #{id}
</update>

<delete id="deleteEmployeeById">
	delete from tbl_employee
	where id = #{id}
</delete>

在定义接口方法时，可以直接将返回值定义为Integer，Long或者boolean类型，Mybatis可以直接将结果封装成对应的类型

public interface EmployeeMapper {
	public Employee selectEmployeeById(Integer id);
	public Long insertEmployee(Employee employee);
	public boolean updateEmployee(Employee employee);
	public Integer deleteEmployeeById(Integer id);
}

在测试时需要注意要手动提交数据

public void test2() throws Exception {
	InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
	SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
	SqlSession session = factory.openSession();//此时的session默认关闭自动提交
	/* 业务代码 */
	session.commit();//手动提交事务
    session.close();
}

也可以传入true来实现自动提交

SqlSession session = factory.openSession(true);

---

• 获取自增主键的值：在配置文件中添加useGeneratedKeys属性值为true，添加keyProperty属性，用来指定获取到的主键值封装给JavaBean的哪个属性

<insert id="insertEmployee" useGeneratedKeys="true" keyProperty="id">
	insert into tbl_employee (last_name, email, gender)
	values (#{lastName}, #{email}, #{gender})
</insert>

此时再获取参数的id属性值就可以得到自增主键的值

Employee employee = new Employee(null, "周翔", "0", "zhouxiang@qq.com");
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
employeeMapper.insertEmployee(employee);
System.out.println(employee.getId());

2. 参数处理

---

2.1 单个参数：

使用#{参数名}就可以取出参数值

<select id="selectEmployeeById" resultType="com.dudu.domain.Employee">
	select id, last_name as lastName, email, gender 
	from tbl_employee 
	where id = #{idtest}
</select>

public Employee selectEmployeeById(Integer id);

大括号中的参数名可以与接口中定义的形参名不一致

2.2 多个参数：

默认情况

多个参数会被封装成一个Map，#{}就是从Map中获取指定的key的值，在封装时，key的值是param1, ... ,paramN，或者可以使用参数的索引，从0开始

<select id="selectEmployeeByNameAndId" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	where id = #{param1} and last_name = #{param2}
</select>

public Employee selectEmployeeByNameAndId(Integer id, String lastName);

命名参数

使用@Param注解明确的指定封装参数时Map的key

public Employee selectEmployeeByNameAndId(@Param("id") Integer id, @Param("lastName") String lastName);

此时使用#{指定的key}即可取出对应的参数值

<select id="selectEmployeeByNameAndId" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	where id = #{id} and last_name = #{lastName}
</select>

传入POJO

如果多个参数正好是业务逻辑的数据模型，就可以直接传入pojo，此时使用#{属性名}就可以取出对应的属性值

传入Map

如果多个参数不是业务逻辑中的数据模型，没有对应的pojo，在不常用的情况下可以传入Map，此时#{key}就可以取出map中对应的值

HashMap<String, Object> map = new HashMap<>();
map.put("id",2);
map.put("lastName","简隋英");
Employee employee = employeeMapper.selectEmployeeByMap(map);

<select id="selectEmployeeByMap" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	where id = #{id} and last_name = #{lastName}
</select>

传入TO

如果多个参数不是业务逻辑中的数据模型，没有对应的pojo，在常用的情况下，推荐编写一个TO（Transfer Object）数据传输对象

特殊情况

如果参数是Collection类型或者是数组的话，在封装时使用的key是collection（List可以使用list，数组可以使用array）

public Employee selectEmployeeById(List<Integer> ids);

<select id="selectEmployeeById" resultType="com.dudu.domain.Employee">
	select id, last_name as lastName, email, gender 
	from tbl_employee 
	where id = #{list[0]}
</select>

List<Integer> list = new ArrayList<>();
list.add(2);
Employee employee = employeeMapper.selectEmployeeById(list);

2.3 # 与 $ 取值的区别

#是以预编译的形式，将参数设置到SQL语句中，相当于PreparedStatement，可以防止sql注入
$取出的值直接拼装在SQL语句中，会存在安全问题

select id, last_name, email, gender from tbl_employee where id = 2 and last_name = ?

大多数情况下取参数的值都应该使用#，但当原生JDBC不支持占位符的位置时，可以使用$

2.4 # 取值时指定参数相关规则

• javaType
• jdbcType：在数据为null时，有些数据库（比如Oracle）不能识别mybatis对null的特殊处理，此时必须设置
  mybatis对所有的null值都映射的是原生JDBC的OTHER类型，Oracle不识别这个类型，因此在取参数值时，需要写成#{id, jdbcType=NULL}，也可以在全局配置文件中设置

<settings>
	<setting name="jdbcTypeForNull" value="NULL"/>
</settings>

• mode（存储过程）
• numericScale（小数点后位数）
• resultType
• typeHandler
• jdbcTypeName

3. select查询操作

• id：唯一标识符，需要和接口的方法名一致
• resultType：返回值类型，值为别名或者全类名，如果返回的是集合，定义集合中元素的类型。不能与resultMap同时使用

3.1 返回List

public List<Employee> selectEmployeeByName(String lastName);

<select id="selectEmployeeByName" resultType="com.dudu.domain.Employee">
	select id, last_name as lastName, email, gender 
	from tbl_employee 
	where last_name like #{lastName}
</select>

3.2 返回Map

封装一条记录（key为属性名，value为对应的属性值）

public Map<String, Object> selectEmployeeByIdReturnMap(Integer id);

<select id="selectEmployeeByIdReturnMap" resultType="map">
	select id, last_name as lastName, email, gender 
	from tbl_employee 
	where id = #{id}
</select>

{gender=0, last_name=简隋英, id=2, email=jiansuiying@qq.com}

封装多条记录（key为一条数据的主键，value为数据对应的JavaBean）

@MapKey("id")//指定封装Map的时候使用哪个属性作为key
public Map<Integer, Object> selectEmployeeByIdReturnMapWithBean(Integer id);

<select id="selectEmployeeByIdReturnMapWithBean" resultType="com.dudu.domain.Employee" >
	select id, last_name as lastName, email, gender 
	from tbl_employee 
	where id < #{id}
</select>

{1=Employee{id=1, lastName=‘费渡’, gender=‘0’, email=‘feidu@qq.com’},
2=Employee{id=2, lastName=‘简隋英’, gender=‘0’, email=‘jiansuiying@qq.com’}}

3.3 自定义结果映射规则

使用resultMap标签来自定义结果映射规则

<mapper namespace="com.dudu.dao.EmployeeMapper">
	<resultMap id="MyEmp" type="com.dudu.domain.Employee">
		<!-- 列 = 属性 对应 -->
		<id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
    </resultMap>
	<!-- 通过id来引用 -->
    <select id="selectEmployeeById" resultMap="MyEmp">
    	select id, last_name, email, gender
        from tbl_employee
        where id = #{id}
    </select>
</mapper>

如果数据表列名和属性名是相同的话是可以不在resultMap中再次定义，但习惯性都会重新定义一次

3.4 关联查询

---

3.4.1 使用级联属性来封装JavaBean中的对象

public Employee selectEmpWithDeptByID(Integer id);

<mapper namespace="com.dudu.dao.EmployeeMapper">
	<resultMap id="MyEmp" type="com.dudu.domain.Employee">
		<id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <!-- 使用级联属性进行封装 -->
        <result column="dept_id" property="dept.id"/>
        <result column="dept_name" property="dept.deptName"/>
    </resultMap>
    
    <select id="selectEmpWithDeptByID" resultMap="MyEmp">
    	select te.id, last_name, email, gender, dept_id, dept_name
    	from tbl_employee as te left join tbl_dept td on te.dept_id = td.id
    	where te.id = #{id}
    </select>
</mapper>

3.4.2 使用association标签来定义单个对象的封装规则

<mapper namespace="com.dudu.dao.EmployeeMapper">
	<resultMap id="MyEmp" type="com.dudu.domain.Employee">
		<id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <!-- 
        	property="dept"指定哪个属性是联合的对象 
        	javaType="Department"指定这个属性对象的类型【不能省略】 
        -->
        <association property="dept" javaType="com.dudu.domain.Department">
        	<id column="id" property="id"/>
        	<result column="dept_name" property="deptName"/>
        </association>
    </resultMap>
    
    <select id="selectEmpWithDeptByID" resultMap="MyEmp">
    	select te.id, last_name, email, gender, dept_id, dept_name
    	from tbl_employee as te left join tbl_dept td on te.dept_id = td.id
    	where te.id = #{id}
    </select>
</mapper>

使用association进行分步查询

<mapper namespace="com.dudu.dao.EmployeeMapper">
	<resultMap id="MyEmp" type="com.dudu.domain.Employee">
		<id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <!-- 
			property属性指定哪个属性是联合的对象
			select属性指定用于查询该对象的SQL语句
			column属性指定将哪一列作为参数传给select中的查询
			该配置的含义为：使用dept_id列作为参数，将selectDeptById方法的结果作为dept对象的值
		 -->
        <association property="dept"
        			 select="com.dudu.dao.DepartmentMapper.selectDeptById"
        			 column="dept_id"/>
    </resultMap>
    
    <select id="selectEmployeeById" resultMap="MyEmp">
    	select * from tbl_employee
    	where id = #{id}
    </select>
</mapper>

<mapper namespace="com.dudu.dao.DepartmentMapper">
	<select id="selectDeptById" resultType="com.dudu.domain.Department">
		select id, dept_name from tbl_dept where id = #{id}
	</select>
</mapper>

分步查询可以实现延迟加载（按需加载）：在全局配置文件中加入如下配置

<configuration>
	<settings>
		<setting name="lazyLoadingEnabled" value="true"/>
		<setting name="aggressiveLazyLoading" value="false"/>
	</settings>
</configuration>

此时，如果不需要部门的相关信息，则部门mapper对应的查询方法不会执行

3.4.3 使用collection来定义集合类型的封装规则

部门类中有员工List属性，通过部门id来查询部门的信息

<mapper namespace="com.dudu.dao.DepartmentMapper">
	<resultMap id="MyDept" type="com.dudu.domain.Department">
		<id column="dept_id" property="id"/>
		<result column="dept_name" property="deptName"/>
		<!-- 指定集合中元素的类型 -->
		<collection property="employees" ofType="com.dudu.domain.Employee">
			<!-- 定义集合中元素的封装规则 -->
			<id column="emp_id" property="id"/>
			<result column="last_name" property="lastName"/>
			<result column="gender" property="gender"/>
			<result column="email" property="email"/>
		</collection>
	</resultMap>
	
	<select id="selectDeptById" resultMap="MyDept">
		select
			td.id as dept_id,
			td.dept_name as dept_name,
			te.id as emp_id,
			te.last_name as last_name,
			te.email as email,
            te.gender as gender
        from
            tbl_dept as td
            left join tbl_employee as te
        on td.id = te.dept_id
        where td.id = #{id}
    </select>
</mapper>

分步查询

<mapper namespace="com.dudu.dao.DepartmentMapper">
	<resultMap id="MyDept" type="com.dudu.domain.Department">
		<id column="id" property="id"/>
		<result column="dept_name" property="deptName"/>
		<collection property="employees"
					select="com.dudu.dao.EmployeeMapper.selectEmployeeByDeptId"
					column="id"/>
    </resultMap>
	<select id="selectDeptById" resultMap="MyDept">
		select id, dept_name from tbl_dept
		where id = #{id}
    </select>
</mapper>

<mapper namespace="com.dudu.dao.EmployeeMapper">
	<resultMap id="MyEmp" type="com.dudu.domain.Employee">
		<id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
    </resultMap>
	<select id="selectEmployeeByDeptId" resultMap="MyEmp">
		select id, last_name, gender, email from tbl_employee
		where dept_id = #{deptId}
    </select>
</mapper>

关于延迟加载的设置此时同样生效

扩展：

1. 当分步查询需要传递多个参数时，可以将参数封装成一个Map：{ 参数名=相应的列名, … }

<collection property="employees"
			select="com.dudu.dao.EmployeeMapper.selectEmployeeByDeptId"
			column="{deptId = id, ......}"/>

2. fetchType属性
   在主配置文件中配置过懒加载（lazy）后，也可以使用该属性来改变为立即加载（eager）

<collection property="employees"
			select="com.dudu.dao.EmployeeMapper.selectEmployeeByDeptId"
			column="{deptId = id, ......}"
			fetchType="eager"/>/>

3.4.4 使用discriminator鉴别器：

判断某列的值，然后根据某列的值改变封装行为

<resultMap id="MyEmp" type="com.dudu.domain.Employee">
	<id column="id" property="id"/>
	<result column="last_name" property="lastName"/>
	<result column="gender" property="gender"/>
	<result column="email" property="email"/>
	<!-- 需要判断的列名，和该列的java类型 -->
	<discriminator javaType="string" column="gender">
		<case value="1" resultType="com.dudu.domain.Employee">
			<association property="dept"
						 select="com.dudu.dao.DepartmentMapper.selectDeptById"
						 column="dept_id"/>
		</case>
		<case value="0" resultType="com.dudu.domain.Employee">
			<id column="id" property="id"/>
			<result column="last_name" property="lastName"/>
			<result column="gender" property="gender"/>
			<result column="email" property="email"/>
		</case>
    </discriminator>
</resultMap>