mybatis_04---映射文件③select、resultMap

select查询的集中情况

1、查询单行数据返回单个对象

public Employee getEmpById(Integer id);

2、查询多行数据返回对象的集合

public List<Employee> getEmpsByNameLike(String name);

<!--如果返回的是一个集合，要挟集合中元素的类型-->
<select id="getEmpsByNameLike" resultType="dao.Employee">
	select * from tb_employee where name like #{name}
</select>

@Test
		public void test1() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			List<Employee> like = mapper.getEmpsByNameLike("%e%");
			for (Employee employee : like) {
				System.out.println(employee);
			}
			//手动提交数据
			openSession.commit();
		}finally {
			openSession.close();
		}
	}

3、查询单行数据返回Map集合

//返回一条记录的Map，key就是列名，value就是对应的值
public Map<String,Object> getEmployeeByIdReturnMap(Integer id );

<!-- 查询单行数据返回Map集合 -->
<select id="getEmployeeByIdReturnMap" resultType="map">
	select * from tb_employee where id=#{id}
</select>

@Test
		public void test1() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//获取到的sqlSession不会自动提交数据
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Map<String,Object> map = mapper.getEmpsByIdReturnMap(3);
			System.out.println(map);
			}
			openSession.commit();
		}finally {
			openSession.close();
		}
	}

‘

4、查询多行数据返回Map集合

//多条记录封装一个Map，Map<integer,Employee>；键就是这条记录的主键，值就是记录封装后的javaBean
@MapKey("id") // 指定使用对象的哪个属性来充当 map 的 key 
public Map<Integer,Employee> getEmpsByNameLikeReturnMap(String name);
<!--
	使用name作为map的key进行封装
@MapKey("name")
public Map<String,Employee> getEmpsByNameLikeReturnMap(String name);
-->

	@Test
		public void test1() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//获取到的sqlSession不会自动提交数据
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			//多条记录封装成一个map
			Map<Integer,Employee> map = mapper.getEmpsByNameLikeReturnMap("%r%");
			System.out.println(map);
			}
			//手动提交数据
			openSession.commit();
		}finally {
			openSession.close();
		}
	}

<select id="getEmpsByNameLikeReturnMap" resultType="dao.Employee">
	select * from tb_employee where name like #{name}
</select>

resultType的自动映射

1、autoMappingBehavior默认是PARTIAL，开启自动映射的功能。唯一的要求是列名和javaBean的属性名一致
2、如果autoMappingBehavior设置为null则会取消自动映射
3、数据库字段命名规范，POJO属性符合驼峰命名法，如A_COLUMN aColumn，可 以开启自动驼峰命名规则映射功能，mapUnderscoreToCamelCase=true

resultMap

resultMap自定义映射

映射文件

<?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="dao.EmployeeMapper2">
	
	<!-- 使用resultMap进行自定义映射 -->
	<!--
		type:自定义规则的java类型
		id：唯一id方便使用 
	 -->
	<resultMap type="dao.Employee" id="myEmp">
	<!-- 指定主键的封装规则
		id:定义主键会有底层优化
		column：指定哪一列
		property：指定对应的javaBean属性
	 -->
		<id column="id" property="id"/>
		<!-- 定义普通列封装规则 -->
		<result column="last_name" property="lastName"/>
		<!-- 其他不指定的列会自动封装，但是建议只要使用了result，就把所有的列的映射规则写上 -->
		<result column="sex" property="sex"/>
		<result column="email" property="email"/>
	</resultMap>
	<select id="getEmpById" resultMap="myEmp">
		select * from tb2_employee where id=#{id}
	</select>

<!-- 使用resultType进行自动映射
	<select id="getEmpById" resultType="dao.Employee">
		select * from tb2_employee where id=#{id}
	</select>
 -->
</mapper>

public interface EmployeeMapper2 {
	public Employee getEmpById(Integer id);
}

	@Test
	public void test2() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper2 mapper = openSession.getMapper(EmployeeMapper2.class);
			Employee employee = mapper.getEmpById(1);
			System.out.println(employee);
		}finally {
			openSession.close();
		}
		
		
	}

resultMap的关联查询

场景一：

	查询Employee的同时查询员工对应的部门
	Employee===Department
	一个员工有与之对应的部门信息

方式一：级联属性封装结果集（dept.id、dept.departmentName）

package dao;

public class Employee {
	private Integer id;
	private String lastName;
	private String email;
	private String sex;
	private Department dept;
	

	public Employee() {
		super();
		
	}

	@Override
	public String toString() {
		return "Employee [id=" + id + ", name=" + lastName + ", email=" + email + ", sex=" + sex + "]";
	}
	public Employee(Integer id, String name, String email, String sex) {
		super();
		this.id = id;
		this.lastName = name;
		this.email = email;
		this.sex = sex;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public Department getDept() {
		return dept;
	}

	public void setDept(Department dept) {
		this.dept = dept;
	}

	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}

	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
}

package dao;

public class Department {
	private Integer id;
	private String departmentName;	
	@Override
	public String toString() {
		return "Department [id=" + id + ", departmentName=" + 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;
	}
}

package dao;

import dao.Employee;
public interface EmployeeMapper {
	public Employee getEmpAndDept(Integer id);
}

<?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="dao.EmployeeMapper">
<!-- 场景一：
		查询Employee的同时查询员工对应的部门
		Employee===Department
		一个员工有与之对应的部门信息
		id last_name sex d_id 	did、dept_name（private String departmentName;）
 -->
 <!-- 方式一：
 			联合查询：级联属性封装结果集：dept.id 
 -->
 <resultMap type="dao.Employee" id="myDifEmp">
 	<id column="id" property="id"/>
 	<result column="last_name" property="lastName"/>
 	<result column="sex" property="sex"/>
 	<result column="did" property="dept.id"/>
 	<result column="dept_name" property="dept.departmentName"/>
 </resultMap>
 <!-- public Employee getEmpAndDept(Integer id); -->
 <select id="getEmpAndDept" resultMap="myDifEmp">
 	select e.id id,e.last_name last_name,e.sex sex,e.d_id d_id,d.id did,d.dept_name dept_name
 	from tb2_employee e,tb2_dept d 
 	where e.id=d.id and e.id=#{id} 
 	
 </select>
</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="dbconfig.properties"></properties>
<settings>
	<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />		
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}" />
				<property name="url" value="${jdbc.url}" />
				<property name="username" value="${jdbc.username}" />
				<property name="password" value="${jdbc.password}" />				
			</dataSource>
		</environment>
	</environments>
	<!-- 添加映射文件的注册 -->
	<mappers>
	    <mapper resource="dao/EmployeeMapper.xml"/>
	</mappers>
</configuration>

package test;

import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.jupiter.api.Test;
import dao.Employee;
import dao.EmployeeMapper;
public class TestEmpDaoImpl {
	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream in = Resources.getResourceAsStream(resource);		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		return sqlSessionFactory;
	}
	@Test
	public void test() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Employee empAndDept = mapper.getEmpAndDept(1);
			System.out.println(empAndDept);
			System.out.println(empAndDept.getDept());
		}finally {
			openSession.close();
		}
		
		
	}
}

方式二：使用association来定义关联的单个对象的封装规则

<?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="dao.EmployeeMapper">
 <!-- 方式二：使用association来定义关联的单个对象的封装规则 -->
  <resultMap type="dao.Employee" id="myDifEmp2">
 	<id column="id" property="id"/>
 	<result column="last_name" property="lastName"/>
 	<result column="sex" property="sex"/>
	<association property="dept" javaType="dao.Department">
		<id column="did" property="id"/>
		<result column="dept_name" property="departmentName"/>
	</association>
  </resultMap>
 <!-- public Employee getEmpAndDept(Integer id); -->
 <select id="getEmpAndDept" resultMap="myDifEmp2">
 	select e.id id,e.last_name last_name,e.sex sex,e.d_id d_id,d.id did,d.dept_name dept_name
 	from tb2_employee e,tb2_dept d 
 	where e.id=d.id and e.id=#{id} 
 	
 </select>
</mapper>

resultMap_association 分步查询

public interface EmployeeMapper {
	public Employee getEmpByIdStep(Integer id);
}

employeeMapper.xml

<!-- 使用association进行分布查询:
 		1、先按照员工id查询员工信息
 		2、根据员工信息中的d_id值去部门表查询部门信息
 		3、部门信息设置到员工中
  -->
  <!-- id	last_name	email	sex		d_id -->
  <resultMap type="dao.Employee" id="myEmpByStep">
  	<id column="id" property="id"/>
  	<result column="last_name" property="lastName"/>
  	<result column="email" property="email"/>
 	<result column="sex" property="sex"/>
 	<!-- association定义关联对象的封装规则
 	property ：指定关联的对象
 	select：表明当前属性是调用select指定的方法查处的结果
 	coulumn:指定那一列的值传给这个方法
	流程：使用select指定的方法（传入column指定的这列参数的值）查处对象，并封装给property指定的属性
 	-->
 	<association property="dept" select="dao.DepartmentMapper.getDepById" column="d_id">
 	</association>
  </resultMap>
 <!-- public Employee getEmpByIdStep(Integer id); -->
 <select id="getEmpByIdStep" resultMap="myEmpByStep">
	select * from tb2_employee where id=#{id}
 </select>
</mapper>

public interface DepartmentMapper {
	public Department getDepById(Integer id);
}

<mapper namespace="dao.DepartmentMapper">
	<!-- public Department getDepById(Integer id); -->
	<select id="getDepById" resultType="dao.Department">
		select id,dept_name departmentName from tb2_dept where id=#{id}
	</select>
</mapper>

	@Test
	public void test() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Employee employee = mapper.getEmpByIdStep(2);
			System.out.println(employee);
			System.out.println(employee.getDept());
		}finally {
			openSession.close();
		}		
	}

resultMap_关联查询&延迟加载

可以使用延迟加载来提升查询的效率
使用延迟加载:
Employee–>Dept

每次查询Employee对象的时候，都将一起查询出来
部门信息在使用到的时候再去查询，
分段查询的基础之上在全局配置文件中加上配置

<settings>
<!-- 开启延迟加载 -->
	<setting name="lazyloadingEnabled" value="true"/>
<!-- 设置加载的数据是按需还是全部 -->
	<setting name="aggressiveLazyLoading" value="false"/>
</settings>

按需加载

resultMap_关联查询_collection定义关联集合封装规则

public interface DepartmentMapper {
	public Department getDepById2(Integer id);
}

DepartmentMapper.xml

 <!-- 场景二：
 		查询部门的时候将部门的信所有员工信息返回
  -->
	<!-- collection嵌套结果集的方式，定义关联的集合类型元素的封装规则 -->
<!-- public class Department {
	private Integer id;
	private String departmentName;
	private List<Employee> emps;
	
	did	dept_name	||	eid	last_name	email	sex
 -->
 	<resultMap type="dao.Department" id="myDept">
 		<id column="did" property="id"/>
 		<result column="dept_name" property="departmentName"/>
 		<!-- collection定义关联集合类型的属性的封装规则 
 			 ofType:指定集合里面元素的类型
 		-->
 		<collection property="emps" ofType="dao.Employee">
 		<!-- 定义这个集合中元素的封装规则 -->
 			<id column="eid" property="id"/>
 			<result column="last_name" property="lastName"/>
 			<result column="email" property="email"/>
 			<result column="sex" property="sex"/>
 		</collection>
 	</resultMap>
 	<select id="getDepById2" resultMap="myDept">
		select d.id did,dept_name dept_name,e.id eid,e.last_name last_name,e.email email,e.sex sex
		from tb2_dept d
		left join tb2_employee e
		on d.id=e.id
		where d.id=#{id}
 	</select>

@Test
	public void test2() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
			Department department = mapper.getDepById2(1);
			System.out.println(department);
			System.out.println(department.getEmps());
		}finally {
			openSession.close();
		}
	}

resultMap_关联查询_collection分布查询&延迟加载

 	<!-- collection分布查询&延迟加载 -->
 	<!-- public class Department {
	private Integer id;
	private String departmentName;
	private List<Employee> emps;
 -->
 	<resultMap type="dao.Department" id="myDeptStep">
 		<id column="id" property="id"/>
 		<result column="dept_name" property="departmentName"/>
 		<collection property="emps" select="dao.EmployeeMapper.getEmpsByDeptId" column="id"></collection>
 	</resultMap>
 	<!-- public Department getDepByIdStep(Integer id); -->
 	<select id="getDepByIdStep" resultMap="myDeptStep">
 		select id,dept_name from tb2_dept where id=#{id}
 	</select>

 <!-- 	public List<Employee> getEmpsByDeptId(Integer deptId); -->
<select id="getEmpsByDeptId" resultType="dao.Employee">
	select * from tb2_employee where d_id=#{deptId}
</select>

public class Department {
	private Integer id;
	private String departmentName;
	private List<Employee> emps;

public interface DepartmentMapper {
	public Department getDepByIdStep(Integer id);

public class Employee {
	private Integer id;
	private String lastName;
	private String email;
	private String sex;
	private Department dept;
	
public interface EmployeeMapper {
	public List<Employee> getEmpsByDeptId(Integer deptId);


//测试
	@Test
	public void test2() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
			Department department = mapper.getDepByIdStep(1);
			System.out.println(department);
			System.out.println(department.getEmps());
		}finally {
			openSession.close();
		}
	}

resultMap_分步查询多列值&fetchType

 	<!-- 扩展：多列的值传递过去
 				将多列的值封装map传递，
 				column=“{key1=column1,key2=column2}”
 				
 		 fetchType="lazy":表示使用延迟加载
 				lazy：延迟
 				eager：立即
 	 	 -->
  	<resultMap type="dao.Department" id="myDeptStep">
 		<id column="id" property="id"/>
 		<result column="dept_name" property="departmentName"/>
 		<collection property="emps" select="dao.EmployeeMapper.getEmpsByDeptId" column="{deptId=id}" fetchType="lazy"></collection>
 	</resultMap>

resultMap_discriminator鉴别器

<!--<discriminator javaType=""></discriminator>  
 	鉴别器：mybatis可以使用discriminator判断某列的值，然后根据某列的值改变封装行为
 	
 	封装Employee：
 		如果查出来的是女生，就把部门信息查询出来，否则不查询
 		如果查询来的是男生，将last_name这一列的值赋值给email
 -->
<resultMap type="dao.Employee" id="myEmpDis">
 	<id column="id" property="id"/>
  	<result column="last_name" property="lastName"/>
  	<result column="email" property="email"/>
 	<result column="sex" property="sex"/>
 	<!--
 		javaType：列值对应java类型
 		 column:指定判定的列名
 	 -->
 	<discriminator javaType="string" column="sex">
 	<!--女生。resultType：指定封装的结果类型  -->
 		<case value="0" resultType="dao.Employee">
 			 <association property="dept" select="dao.DepartmentMapper.getDepById" column="d_id">
 			</association>
 		</case>
 	<!--男生，  将last_name这一列的值赋值给email-->
 		<case value="1" resultType="dao.Employee">
 			 	<id column="id" property="id"/>
			  	<result column="last_name" property="lastName"/>
			  	<result column="last_name" property="email"/>
			 	<result column="sex" property="sex"/>
 		</case>
 	</discriminator>
 </resultMap>
  <select id="getEmpByIdStep" resultMap="myEmpDis">
	select * from tb2_employee where id=#{id}
 </select>

	@Test
	public void test() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Employee employee = mapper.getEmpByIdStep(2);
			System.out.println(employee);
			System.out.println(employee.getDept());
		}finally {
			openSession.close();
		}		
	}