MyBatis_03_mapper

目录

1、映射文件-增删改查

1.1、com.jxx.mybatis.bean.Employee.java实体类

1.2、EmployeeMapper接口

1.3、EmployeeMapper映射文件

1.4、测试

2、映射文件-insert_mysql获取自增主键的值

2.1、Mapper映射文件

2.2、测试

3、映射文件-insert_oracle使用序列来模拟自增

3.1、Mapper映射文件

4、映射文件-参数处理-单个参数&多个参数&命名参数

4.1、EmployeeMapper接口

4.2、EmployeeMapper.xml映射文件

5、映射文件-参数处理-Pojo&Map&TO

5.1、EmployeeMapper接口

5.2、EmployeeMapper.xml映射文件

6、映射文件-参数处理-参数封装扩展思考

7、映射文件-参数处理-参数封装map的过程

8、映射文件-参数处理-#{}和${}的区别

8.1、共同点：

8.2、区别：

8.3、${}的适用场景：

9、映射文件-参数处理-取值时指定参数相关规则

10、映射文件-select-返回List&Map

10.1、com.jxx.mybatis.dao.EmployeeMapper.java接口

10.2、EmployeeMapper.xml映射文件

11、映射文件-select-resultMap-自定义结果映射规则

11.1、com.jxx.mybatis.dao.EmployeeMapperPlus.java接口

11.2、EmployeeMapperPlus.xml映射文件

12、映射文件-select-resultMap-关联查询-级联属性封装&association定义关联对象&association分步查询&分步查询的延迟加载

12.1、com.jxx.mybatis.bean.Employee.java实体类

12.2、com.jxx.mybatis.dao.EmployeeMapperPlus.java接口

12.3、com.jxx.mybatis.bean.Department.java实体类

12.4、com.jxx.mybatis.dao.DepartmentMapper.java接口

12.5、DepartmentMapper.xml映射文件

12.6、EmployeeMapperPlus.xml映射文件

13、映射文件-select-resultMap-关联查询-collection定义关联集合封装&collection分步查询&collection延迟加载

13.1、实体类

13.2、mapper接口

 13.3、mapper映射文件

14、映射文件-select-resultMap-discriminator鉴别器

14.1、mapper接口

14.2、mapper配置文件

---

1、映射文件-增删改查

1.1、com.jxx.mybatis.bean.Employee.java实体类

package com.jxx.mybatis.bean;

import org.apache.ibatis.type.Alias;

@Alias("emp")
public class Employee {
	
	private Integer id;
	private String lastName;
	private String email;
	private String gender;
	
	public Employee() {
		super();
	}
	
	public Employee(Integer id, String lastName, String email, String gender) {
		super();
		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 + "]";
	}

}

1.2、EmployeeMapper接口

package com.jxx.mybatis.dao;

import java.util.List;
import java.util.Map;

import com.jxx.mybatis.bean.Employee;

public interface EmployeeMapper {
	
	public Employee getEmpById(Integer id);

	public Long addEmp(Employee employee);

	public boolean updateEmp(Employee employee);

	public void deleteEmpById(Integer id);
	
}

1.3、EmployeeMapper映射文件

<?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.jxx.mybatis.dao.EmployeeMapper">

    <!-- public void addEmp(Employee employee); -->
    <!-- parameterType：参数类型，可以省略-->
    <insert id="addEmp" parameterType="com.jxx.mybatis.bean.Employee">
		insert into tbl_employee(last_name,email,gender)
		values(#{lastName},#{email},#{gender})
	</insert>

     
    <!-- public void updateEmp(Employee employee);  -->
    <update id="updateEmp">
		update tbl_employee
		set last_name=#{lastName},email=#{email},gender=#{gender}
		where id=#{id}
	</update>

    <!-- public void deleteEmpById(Integer id); -->
    <delete id="deleteEmpById">
		delete from tbl_employee where id=#{id}
	</delete>

</mapper>

1.4、测试

package com.jxx.mybatis.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jxx.mybatis.bean.Employee;
import com.jxx.mybatis.dao.EmployeeMapper;

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.Test;


public class MyBatisTest {
	

	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}

	/**
	 * 测试增删改
	 * 1、mybatis允许增删改直接定义以下类型返回值
	 * 		Integer、Long、Boolean、void
	 * 2、我们需要手动提交数据
	 * 		sqlSessionFactory.openSession();===》手动提交
	 * 		sqlSessionFactory.openSession(true);===》自动提交
	 * @throws IOException 
	 */
	@Test
	public void test03() throws IOException{
		
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		//1、获取到的SqlSession不会自动提交数据
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try{
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			//测试添加
			Employee employee = new Employee(null, "jerry",null, "1");
			mapper.addEmp(employee);
			System.out.println(employee.getId());
			
			//测试修改
			//Employee employee = new Employee(1, "Tom", "jerry@atguigu.com", "0");
			//boolean updateEmp = mapper.updateEmp(employee);
			//System.out.println(updateEmp);

			//测试删除
			//mapper.deleteEmpById(2);

			//2、手动提交数据
			openSession.commit();
		}finally{
			openSession.close();
		}
		
	}	
}

2、映射文件-insert_mysql获取自增主键的值

2.1、Mapper映射文件

<!-- public void addEmp(Employee employee); -->
<!-- 
parameterType：参数类型，可以省略.
获取自增主键的值：mysql支持自增主键,mybatis也是利用statement.getGeneratedKeys()获取自增主键值的；
    useGeneratedKeys="true":使用自增主键获取主键值策略
	keyProperty="id":指定对应的主键属性，也就是mybatis获取到主键值以后，将这个值封装给javaBean的哪个属性
-->
<insert id="addEmp" parameterType="com.jxx.mybatis.bean.Employee" useGeneratedKeys="true" keyProperty="id">
	insert into tbl_employee(last_name,email,gender)
	values(#{lastName},#{email},#{gender})
</insert>

2.2、测试

package com.jxx.mybatis.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jxx.mybatis.bean.Employee;
import com.jxx.mybatis.dao.EmployeeMapper;

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.Test;

public class MyBatisTest {
	
	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}

	@Test
	public void test03() throws IOException{
		
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		//1、获取到的SqlSession不会自动提交数据
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try{
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			//测试添加
			Employee employee = new Employee(null, "jerry4",null, "1");
			mapper.addEmp(employee);
			System.out.println(employee.getId());
			
			//测试修改
			//Employee employee = new Employee(1, "Tom", "jerry@atguigu.com", "0");
			//boolean updateEmp = mapper.updateEmp(employee);
			//System.out.println(updateEmp);

			//测试删除
			//mapper.deleteEmpById(2);

			//2、手动提交数据
			openSession.commit();
		}finally{
			openSession.close();
		}
		
	}	
}

3、映射文件-insert_oracle使用序列来模拟自增

3.1、Mapper映射文件

<!--
获取非自增主键的值：
	Oracle不支持自增；Oracle使用序列来模拟自增；
	每次插入的数据的主键是从序列中拿到的值；如何获取到这个值；
 -->
<insert id="addEmp" databaseId="oracle">
	<!--
	keyProperty:查出的主键值封装给javaBean的哪个属性
	order="BEFORE":当前sql在插入sql之前运行
		   AFTER：当前sql在插入sql之后运行
	resultType:查出的数据的返回值类型

	BEFORE运行顺序：
		先运行selectKey查询id的sql；查出id值封装给javaBean的id属性
		再运行插入的sql；就可以取出id属性对应的值
	AFTER运行顺序：
		先运行插入的sql（从序列中取出新值作为id）；
		再运行selectKey查询id的sql；
	 -->
	<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>

4、映射文件-参数处理-单个参数&多个参数&命名参数

• 单个参数：mybatis不会做特殊处理

        #{参数名/任意名}：取出参数值。

• 多个参数：mybatis会做特殊处理。多个参数会被封装成 一个map
   key：param1...paramN,或者参数的索引也可以
   value：传入的参数值
   #{}就是从map中获取指定的key的值；
      
      异常：
      org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [1, 0, param1, param2]
          方法：public Employee getEmpByIdAndLastName(Integer id,String lastName);
          取值：#{id},#{lastName}

• 【命名参数】：明确指定封装参数时map的key；@Param("id")

    多个参数会被封装成 一个map，
        key：使用@Param注解指定的值
        value：参数值
        #{指定的key}取出对应的参数值

4.1、EmployeeMapper接口

package com.jxx.mybatis.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;

import com.jxx.mybatis.bean.Employee;

public interface EmployeeMapper {

    /*  
    【命名参数】：明确指定封装参数时map的key；@Param("id")
                多个参数会被封装成 一个map，
                key：使用@Param注解指定的值
                value：参数值
                #{指定的key}取出对应的参数值 
    */
	public Employee getEmpByIdAndLastName(@Param("id") Integer id, @Param("lastName") String lastName);

    /* 
    多个参数：mybatis会做特殊处理。多个参数会被封装成 一个map
             key：param1...paramN,或者参数的索引也可以
             value：传入的参数值
             #{}就是从map中获取指定的key的值； 
    */
    public Employee getEmpByIdAndLastName1(Integer id, String lastName);
	
    /*
    单个参数：mybatis不会做特殊处理
             #{参数名/任意名}：取出参数值。 
    */
	public Employee getEmpById(Integer id);
	
}

4.2、EmployeeMapper.xml映射文件

<?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.jxx.mybatis.dao.EmployeeMapper">

    <!-- 
    【命名参数】：明确指定封装参数时map的key；@Param("id")
                多个参数会被封装成 一个map，
                key：使用@Param注解指定的值
                value：参数值
                #{指定的key}取出对应的参数值 
    -->
    <!--  public Employee getEmpByIdAndLastName(Integer id,String lastName);-->
    <select id="getEmpByIdAndLastName" resultType="com.jxx.mybatis.bean.Employee">
 		select * from tbl_employee where id = #{id} and last_name=#{lastName}
 	</select>

    <!-- 
    多个参数：mybatis会做特殊处理。多个参数会被封装成 一个map
             key：param1...paramN,或者参数的索引也可以
             value：传入的参数值
             #{}就是从map中获取指定的key的值； 
    -->
    <!--  public Employee getEmpByIdAndLastName1(Integer id,String lastName);-->
    <select id="getEmpByIdAndLastName1" resultType="com.jxx.mybatis.bean.Employee">
 		select * from tbl_employee where id = #{param1} and last_name=#{param2}
 	</select>

    <!-- 
    单个参数：mybatis不会做特殊处理
             #{参数名/任意名}：取出参数值。 
    -->
    <select id="getEmpById" resultType="com.jxx.mybatis.bean.Employee">
		select * from tbl_employee where id = #{id}
	</select>

</mapper>

5、映射文件-参数处理-Pojo&Map&TO

• Pojo：如果多个参数是业务模型中的数据，直接传入pojo
      #{属性名}：取出传入的pojo的属性值    

• Map：如果多个参数不是业务模型中的数据，没有对应的pojo，不经常使用，为了方便，我们也可以传入map
      #{key}：取出map中对应的值

• TO： 如果多个参数不是业务模型中的数据，但是经常要使用，推荐来编写一个TO（Transfer Object）数据传输对象
      Page{
          int pageNumber;
          int pageSize;
      }

5.1、EmployeeMapper接口

package com.jxx.mybatis.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

import com.jxx.mybatis.bean.Employee;

public interface EmployeeMapper {
		
    /*
    Map：
        如果多个参数不是业务模型中的数据，没对应的pojo，不常使用，为了方便，我们也可以传入map
	    #{key}：取出map中对应的值
    */
	public Employee getEmpByMap(Map<String, Object> map);
	
    /*
    POJO：
         如果多个参数正好是我们业务逻辑的数据模型，我们就可以直接传入pojo；
	     #{属性名}：取出传入的pojo的属性值	
    */
	public Employee getEmpByIdAndLastName(@Param("id") Integer id, @Param("lastName") String lastName);

}

5.2、EmployeeMapper.xml映射文件

<?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.jxx.mybatis.dao.EmployeeMapper">

    <!-- public Employee getEmpByMap(Map<String, Object> map); -->
    <!-- 
    Map：
        如果多个参数不是业务模型中的数据，没对应的pojo，不常使用，为了方便，我们也可以传入map
	    #{key}：取出map中对应的值
    -->
    <select id="getEmpByMap" resultType="com.jxx.mybatis.bean.Employee">
 		select * from ${tableName} where id=${id} and last_name=#{lastName}
 	</select>

    <!-- 
    POJO：
        如果多个参数正好是我们业务逻辑的数据模型，我们就可以直接传入pojo；
	    #{属性名}：取出传入的pojo的属性值
    -->
    <!--  public Employee getEmpByIdAndLastName(Integer id,String lastName);-->
	<select id="getEmpByIdAndLastName" resultType="com.jxx.mybatis.bean.Employee">
 		select * from tbl_employee where id = #{id} and last_name=#{lastName}
 	</select>

</mapper>

6、映射文件-参数处理-参数封装扩展思考

public Employee getEmp(@Param("id")Integer id,String lastName);
    取值：id==>#{id}   lastName==>#{param2}

public Employee getEmp(Integer id,String lastName);
    取值：id==>#{param1}   lastName==>#{param2}

public Employee getEmp(Integer id,Employee emp);
    取值：id==>#{param1}    lastName===>#{param2.lastName}

public Employee getEmp(Integer id,@Param("e")Employee emp);
    取值：id==>#{param1}    lastName===>#{e.lastName}

##特别注意：如果是Collection（List、Set）类型或者是数组， 也会特殊处理。也是把传入的list或者数组封装在map中。
key：Collection（collection）,如果是List==>(list)；数组==>(array)

public Employee getEmpById(List<Integer> ids);
    取值：取出第一个id的值：   #{list[0]}

7、映射文件-参数处理-参数封装map的过程

public class MyBatisTest {}==测试类

@Test
public void test04() throws IOException{
	SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
	//1、获取到的SqlSession不会自动提交数据
	SqlSession openSession = sqlSessionFactory.openSession();
	try{
        //生成EmployeeMapper的代理
		EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        //调用代理mapper的getEmpByIdAndLastName方法
		Employee employee = mapper.getEmpByIdAndLastName(1, "tom");
		System.out.println(employee);
	}finally{
		openSession.close();
	}
}

public class org.apache.ibatis.binding.MapperProxy<T> implements InvocationHandler, Serializable {} 

@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    try {
        /**
         * method.getDeclaringClass()
         * Returns the {@code Class} object representing the class or interface
         * that declares the executable represented by this object.
         * 返回{@code Class}对象，声明此对象表示的可执行文件的类或接口。
         */
		if (Object.class.equals(method.getDeclaringClass())) {
			return method.invoke(this, args);
		} else if (isDefaultMethod(method)) {
			return invokeDefaultMethod(proxy, method, args);
		}
    } catch (Throwable t) {
		throw ExceptionUtil.unwrapThrowable(t);
    }
    final MapperMethod mapperMethod = cachedMapperMethod(method);
    return mapperMethod.execute(sqlSession, args);
}

 public class org.apache.ibatis.binding.MapperMethod{}

public Object execute(SqlSession sqlSession, Object[] args) {
    Object result;
    switch (command.getType()) {
		case INSERT: {
			Object param = method.convertArgsToSqlCommandParam(args);
			result = rowCountResult(sqlSession.insert(command.getName(), param));
			break;
		}
		case UPDATE: {
			Object param = method.convertArgsToSqlCommandParam(args);
			result = rowCountResult(sqlSession.update(command.getName(), param));
			break;
		}
		case DELETE: {
			Object param = method.convertArgsToSqlCommandParam(args);
			result = rowCountResult(sqlSession.delete(command.getName(), param));
			break;
		}
		case SELECT:
			if (method.returnsVoid() && method.hasResultHandler()) {
				executeWithResultHandler(sqlSession, args);
				result = null;
			} else if (method.returnsMany()) {
				result = executeForMany(sqlSession, args);
			} else if (method.returnsMap()) {
				result = executeForMap(sqlSession, args);
			} else if (method.returnsCursor()) {
				result = executeForCursor(sqlSession, args);
			} else {
                //将参数转换为sql执行命令参数
				Object param = method.convertArgsToSqlCommandParam(args);
				result = sqlSession.selectOne(command.getName(), param);
			}
			break;
      case FLUSH:
			result = sqlSession.flushStatements();
			break;
      default:
			throw new BindingException("Unknown execution method for: " + command.getName());
    }
    if (result == null && method.getReturnType().isPrimitive() && !method.returnsVoid()) {
		throw new BindingException("Mapper method '" + command.getName() 
          + " attempted to return null from a method with a primitive return type (" + method.getReturnType() + ").");
    }
    return result;
}

public Object convertArgsToSqlCommandParam(Object[] args) {
	return paramNameResolver.getNamedParams(args);
}

public class org.apache.ibatis.reflection.ParamNameResolver{}

public ParamNameResolver(Configuration config, Method method) {
    final Class<?>[] paramTypes = method.getParameterTypes();
    final Annotation[][] paramAnnotations = method.getParameterAnnotations();
    final SortedMap<Integer, String> map = new TreeMap<Integer, String>();
    int paramCount = paramAnnotations.length;
    // get names from @Param annotations
    // 获取标注@Param注解的参数名
    for (int paramIndex = 0; paramIndex < paramCount; paramIndex++) {
		if (isSpecialParameter(paramTypes[paramIndex])) {
			// skip special parameters
			continue;
		}
		String name = null;
		for (Annotation annotation : paramAnnotations[paramIndex]) {
			if (annotation instanceof Param) {
                //如果标注@Param注解,hasParamAnnotation = true,注解的value值赋给name
				hasParamAnnotation = true;
				name = ((Param) annotation).value();
				break;
			}
		}
        //如果没有标注@Param注解
		if (name == null) {
			// @Param was not specified.
			if (config.isUseActualParamName()) {
				name = getActualParamName(method, paramIndex);
			}
			if (name == null) {
				// use the parameter index as the name ("0", "1", ...)
				// gcode issue #71
				name = String.valueOf(map.size());
			}
		}
		map.put(paramIndex, name);
    }
    names = Collections.unmodifiableSortedMap(map);
}

public Object getNamedParams(Object[] args) {
	final int paramCount = names.size();
	if (args == null || paramCount == 0) {
        //参数为空时返回null
		return null;
	} else if (!hasParamAnnotation && paramCount == 1) {
        //参数只有一个且没有标注@Param注解返回第1个参数值:args[0]
		return args[names.firstKey()];
	} else {
        //多个参数或者有标注@Param注解
		final Map<String, Object> param = new ParamMap<Object>();
		int i = 0;
        //遍历map：names={0:id,1:lastName};args=[1,"Tom"]
		for (Map.Entry<Integer, String> entry : names.entrySet()) {
			param.put(entry.getValue(), args[entry.getKey()]);
			// add generic param names (param1, param2, ...)
            // 生成(param1, param2, ...)参数
			final String genericParamName = GENERIC_NAME_PREFIX + String.valueOf(i + 1);
			// ensure not to overwrite parameter named with @Param
            // 确保不会重写@Param注解标注的参数
            // 效果：有Param注解可以#{指定的key}，或者#{param1}
			if (!names.containsValue(genericParamName)) {
			  param.put(genericParamName, args[entry.getKey()]);
			}
			i++;
		}
		return param;
	}
}

8、映射文件-参数处理-#{}和${}的区别

8.1、共同点：

#{}和${}都可以获取map中的值或者pojo对象属性的值

8.2、区别：

 

分析：

• #{}：是以预编译的形式，将参数设置到sql语句中==PreparedStatement，防止sql注入
• ${}：取出的值直接拼装在sql语句中，会有安全问题；

结论：

• 大多情况下，我们去参数的值都应该去使用#{}；

8.3、${}的适用场景：

原生jdbc不支持(预编译)占位符的地方我们就可以使用${}进行取值，比如分表、排序；

• 分表（按照年份分表拆分）：select * from ${year}_salary where xxx;
• 排序（排序名，升降序等）：select * from tbl_employee order by ${field_name},${order_desc/asc}

9、映射文件-参数处理-取值时指定参数相关规则

用#{}规定参数的一些规则：javaType、 jdbcType、 mode（存储过程）、 numericScale、resultMap、 typeHandler、 jdbcTypeName、 expression；

jdbcType通常需要在某种特定的条件下被设置：

• 在我们数据为null的时候，有些数据库可能不能识别mybatis对null的默认处理。比如Oracle（报错）；
• mybatis对所有的null都映射的是原生Jdbc的OTHER类型，oracle不能正确处理（JdbcType OTHER：无效的类型；）;

由于全局配置中：jdbcTypeForNull=OTHER；Oracle不支持；有如下两种解决办法

• mapper映射文件中#{email,jdbcType=NULL};

• mybati全局配置文件中设置<setting name="jdbcTypeForNull" value="NULL"/>

10、映射文件-select-返回List&Map

10.1、com.jxx.mybatis.dao.EmployeeMapper.java接口

package com.jxx.mybatis.dao;

import java.util.List;

import com.jxx.mybatis.bean.Employee;

public interface EmployeeMapper {

    //返回【多条记录】的map：Map<Integer,Employee>；键是这条记录的主键，值是记录封装后的javaBean
	//@MapKey:告诉mybatis封装这个map的时候使用哪个属性作为map的key
	@MapKey("lastName")
	public Map<String, Employee> getEmpByLastNameLikeReturnMap(String lastName);

    //返回【一条记录】的map：Map<String, Object>；键就是列名，值就是列名对应的值
	public Map<String, Object> getEmpByIdReturnMap(Integer id);

    //返回List
	public List<Employee> getEmpsByLastNameLike(String lastName);
}

10.2、EmployeeMapper.xml映射文件

<?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.jxx.mybatis.dao.EmployeeMapper">

    <!--public Map<Integer, Employee> getEmpByLastNameLikeReturnMap(String lastName);-->
    <!-- resultType：如果返回的是多个记录的map，则为map中值的类型  -->
    <select id="getEmpByLastNameLikeReturnMap" resultType="com.jxx.mybatis.bean.Employee">
 		select * from tbl_employee where last_name like #{lastName}
 	</select>

    <!--public Map<String, Object> getEmpByIdReturnMap(Integer id);  -->
    <!-- resultType：如果返回的是一个记录的map，则为map  -->
    <select id="getEmpByIdReturnMap" resultType="map">
 		select * from tbl_employee where id=#{id}
 	</select>

    <!-- public List<Employee> getEmpsByLastNameLike(String lastName); -->
    <!-- resultType：如果返回的是一个集合，则为集合中元素的类型  -->
    <select id="getEmpsByLastNameLike" resultType="com.jxx.mybatis.bean.Employee">
		select * from tbl_employee where last_name like #{lastName}
	</select>
</mapper>

11、映射文件-select-resultMap-自定义结果映射规则

11.1、com.jxx.mybatis.dao.EmployeeMapperPlus.java接口

package com.jxx.mybatis.dao;

import java.util.List;

import com.jxx.mybatis.bean.Employee;

public interface EmployeeMapperPlus {
	
    //resultMap:自定义结果集映射规则
	public Employee getEmpById(Integer id);
}

11.2、EmployeeMapperPlus.xml映射文件

<?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.jxx.mybatis.dao.EmployeeMapperPlus">

	<!--
        自定义某个javaBean的封装规则
	        type：自定义规则的Java类型
	        id:唯一id，方便select的resultMap的引用
	-->
	<resultMap type="com.jxx.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>
	
	<!-- resultMap:自定义结果集映射规则 -->
	<!-- public Employee getEmpById(Integer id); -->
	<select id="getEmpById"  resultMap="MySimpleEmp">
		select * from tbl_employee where id=#{id}
	</select>
</mapper>

12、映射文件-select-resultMap-关联查询-级联属性封装&association定义关联对象&association分步查询&分步查询的延迟加载

12.1、com.jxx.mybatis.bean.Employee.java实体类

package com.jxx.mybatis.bean;

import org.apache.ibatis.type.Alias;

@Alias("emp")
public class Employee {
	
	private Integer id;
	private String lastName;
	private String email;
	private String gender;
	private Department dept;
	
	public Employee() {
		super();
	}
	
	public Employee(Integer id, String lastName, String email, String gender) {
		super();
		this.id = id;
		this.lastName = lastName;
		this.email = email;
		this.gender = gender;
	}
	
	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 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 + '\'' +
				", dept=" + dept +
				'}';
	}
}

12.2、com.jxx.mybatis.dao.EmployeeMapperPlus.java接口

package com.jxx.mybatis.dao;

import java.util.List;

import com.jxx.mybatis.bean.Employee;

public interface EmployeeMapperPlus {
	
    //1、联合查询：级联属性封装结果集
    //2、使用association定义关联的单个对象的封装规则
	public Employee getEmpAndDept(Integer id);
	
    //3、使用association进行分步查询
	public Employee getEmpByIdStep(Integer id);
	
	public List<Employee> getEmpsByDeptId(Integer deptId);

}

12.3、com.jxx.mybatis.bean.Department.java实体类

package com.jxx.mybatis.bean;

import java.util.List;

public class Department {
	
	private Integer id;
	private String departmentName;
	
	public List<Employee> getEmps() {
		return emps;
	}
	public void setEmps(List<Employee> emps) {
		this.emps = emps;
	}
	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
				+ "]";
	}
}

12.4、com.jxx.mybatis.dao.DepartmentMapper.java接口

package com.jxx.mybatis.dao;

import com.jxx.mybatis.bean.Department;

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

}

12.5、DepartmentMapper.xml映射文件

<?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.jxx.mybatis.dao.DepartmentMapper">
	<!--public Department getDeptById(Integer id);  -->
	<select id="getDeptById" resultType="com.jxx.mybatis.bean.Department">
		select id,dept_name departmentName from tbl_dept where id=#{id}
	</select>
</mapper>

12.6、EmployeeMapperPlus.xml映射文件

<?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.jxx.mybatis.dao.EmployeeMapperPlus">
    <!-- 
	场景一：
		查询Employee的同时查询员工对应的部门：Employee → Department
		一个员工有与之对应的部门信息；
		id,last_name,gender,d_id
        did,dept_name (private Department dept;)
	-->
	 
	<!--
		1、联合查询：级联属性封装结果集
	-->
	<resultMap type="com.jxx.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>

	<!-- 
		2、使用association定义关联的单个对象的封装规则；
	-->
	<resultMap type="com.jxx.mybatis.bean.Employee" id="MyDifEmp2">
		<id column="id" property="id"/>
		<result column="last_name" property="lastName"/>
		<result column="gender" property="gender"/>
		
		<!--  
        association可以指定联合的javaBean对象
		    property="dept"：指定哪个属性是联合的对象
		    javaType：指定这个属性对象的类型[不能省略]
		-->
		<association property="dept" javaType="com.jxx.mybatis.bean.Department">
			<id column="did" property="id"/>
			<result column="dept_name" property="departmentName"/>
		</association>
	</resultMap>

	<!--  public Employee getEmpAndDept(Integer id);-->
	<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>

    <!-- 3、使用association进行分步查询：
		①、先按照员工id查询员工信息
		②、根据查询员工信息中的d_id值去部门表查出部门信息
		③、部门设置到员工中；
	 -->
	 
	<!-- id,last_name,email,gender,d_id -->
	<resultMap type="com.jxx.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.jxx.mybatis.dao.DepartmentMapper.getDeptById"
	 		column="d_id">
 		</association>
	</resultMap>
	<!-- public Employee getEmpByIdStep(Integer id); -->
	<select id="getEmpByIdStep" resultMap="MyEmpByStep">
		select * from tbl_employee where id=#{id}
		<if test="_parameter!=null">
	 		and 1=1
	 	</if>
	</select>
    <!-- 
        4、可以使用延迟加载（懒加载）；(按需加载) Employee → Dept：
	 		①、我们每次查询Employee对象的时候，部门信息在我们使用的时候再去查询；
	 		②、分段查询的基础之上在mybatis-config中setting标签上加上两个配置：（注：显示的指定每个我们需要更改的配置的值，即使他是默认的。防止版本更新带来的问题）
            lazyLoadingEnabled=true
            aggressiveLazyLoading=false
	-->
</mapper>

13、映射文件-select-resultMap-关联查询-collection定义关联集合封装&collection分步查询&collection延迟加载

13.1、实体类

package com.jxx.mybatis.bean;

import org.apache.ibatis.type.Alias;

@Alias("emp")
public class Employee {
	
	private Integer id;
	private String lastName;
	private String email;
	private String gender;
	private Department dept;
	
	public Employee() {
		super();
	}
	
	public Employee(Integer id, String lastName, String email, String gender) {
		super();
		this.id = id;
		this.lastName = lastName;
		this.email = email;
		this.gender = gender;
	}
	
	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 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 + '\'' +
				", dept=" + dept +
				'}';
	}
}

package com.jxx.mybatis.bean;

import org.apache.ibatis.type.Alias;

@Alias("emp")
public class Employee {
	
	private Integer id;
	private String lastName;
	private String email;
	private String gender;
	private Department dept;
	
	public Employee() {
		super();
	}
	
	public Employee(Integer id, String lastName, String email, String gender) {
		super();
		this.id = id;
		this.lastName = lastName;
		this.email = email;
		this.gender = gender;
	}
	
	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 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 + '\'' +
				", dept=" + dept +
				'}';
	}
}

13.2、mapper接口

package com.jxx.mybatis.dao;

import com.jxx.mybatis.bean.Department;

public interface DepartmentMapper {
	
    //1、collection定义关联集合封装
	public Department getDeptByIdPlus(Integer id);
    //2、collection分步查询
	public Department getDeptByIdStep(Integer id);
}

package com.jxx.mybatis.dao;

import java.util.List;

import com.jxx.mybatis.bean.Employee;

public interface EmployeeMapperPlus {
	
	public List<Employee> getEmpsByDeptId(Integer deptId);

}

 13.3、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.jxx.mybatis.dao.EmployeeMapperPlus">

	<!-- 
	场景二：
		查询部门的时候将部门对应的所有员工信息也查询出来：注释在DepartmentMapper.xml中
	 -->
	<!-- public List<Employee> getEmpsByDeptId(Integer deptId); -->
	<select id="getEmpsByDeptId" resultType="com.jxx.mybatis.bean.Employee">
		select * from tbl_employee where d_id=#{deptId}
	</select>

</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.jxx.mybatis.dao.DepartmentMapper">

	<!-- 
	public class Department {
			private Integer id;
			private String departmentName;
			private List<Employee> emps;
    }
	did,dept_name  ||  eid,last_name,email,gender  
	-->
	 
	<!--1、使用collection标签定义关联的集合类型的属性封装规则：嵌套结果集的方式  -->
	<resultMap type="com.jxx.mybatis.bean.Department" id="MyDept">
		<id column="did" property="id"/>
		<result column="dept_name" property="departmentName"/>
		<!-- 
			collection：定义关联集合类型的属性的封装规则 
			ofType：指定集合里面元素的类型
		-->
		<collection property="emps" ofType="com.jxx.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>
	

	<!-- 2、collection：分段查询 -->
	<resultMap type="com.jxx.mybatis.bean.Department" id="MyDeptStep">
		<id column="id" property="id"/>
		<id column="dept_name" property="departmentName"/>

        <!-- 
            注意：将多列的值封装map传递；
                column="{key1=column1,key2=column2}"
                此处为：column="{deptId=id}" 
        -->
		<collection property="emps" 
			select="com.jxx.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
			column="id" fetchType="lazy">
        </collection>
	</resultMap>
	<!-- public Department getDeptByIdStep(Integer id); -->
	<select id="getDeptByIdStep" resultMap="MyDeptStep">
		select id,dept_name from tbl_dept where id=#{id}
	</select>
	
	<!-- 
        3、扩展：多列的值传递过去：
			将多列的值封装map传递；column="{key1=column1,key2=column2}"
		    fetchType="lazy"：表示使用延迟加载；
				- lazy：延迟
				- eager：立即
	-->
</mapper>

14、映射文件-select-resultMap-discriminator鉴别器

14.1、mapper接口

package com.jxx.mybatis.dao;

import java.util.List;

import com.jxx.mybatis.bean.Employee;

public interface EmployeeMapperPlus {
	
	public Employee getEmpByIdStep2(Integer id);

}

14.2、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.jxx.mybatis.dao.EmployeeMapperPlus">
	 <!-- =======================鉴别器============================ -->
	 <!-- 
        <discriminator javaType=""></discriminator>
		鉴别器：mybatis可以使用discriminator判断某列的值，然后根据某列的值改变封装行为封装Employee：
			如果查出的是女生：就把部门信息查询出来，否则不查询；
			如果是男生，把last_name这一列的值赋值给email;
	 -->
	 <resultMap type="com.jxx.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:指定封装的结果类型；不能缺少。/resultMap-->
	 		<case value="0" resultType="com.jxx.mybatis.bean.Employee">
	 			<association property="dept" 
			 		select="com.jxx.mybatis.dao.DepartmentMapper.getDeptById"
			 		column="d_id">
		 		</association>
	 		</case>
	 		<!--男生 ;如果是男生，把last_name这一列的值赋值给email; -->
	 		<case value="1" resultType="com.jxx.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>
	 <!--  public Employee getEmpByIdStep(Integer id);-->
	 <select id="getEmpByIdStep2" resultMap="MyEmpDis">
		select * from tbl_employee where id=#{id}
		<if test="_parameter!=null">
			and 1=1
		</if>
	 </select>
</mapper>