Mybatis3_ Mapping file

Mybatis mapping file

The mapping file guides MyBatis how to add, delete, modify and query the database, which is of great significance.

Cache – the cache configuration for this namespace.
Cache ref – refers to the cache configuration of other namespaces.
resultMap – describes how to load objects from the database result set. It is the most complex and powerful element.
parameterMap – old style parameter mapping. This element has been discarded and may be removed in the future! Please use inline parameter mapping. This element is not described in the document.
sql – a repeatable sentence block that can be referenced by other statements.
Insert – map insert statements.
Update – map update statements.
Delete – map delete statements.
select – map query statements.

1. insert, update and delete elements

<!-- public void addEmp(Employee employee); -->
<!-- parameterType: Parameter type, which can be omitted.-->
<insert id="addEmp" parameterType="com.ginger.mybatis.bean.Employee">
	insert into employee(last_name,email,gender)
	values(#{lastName},#{email},#{gender})
</insert>

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

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

Mybatis allows you to directly define the following types of return values: Integer, Long, Boolean and void.

/**
 * Test addition, deletion and modification
 * 1,mybatis The following types of return values can be defined directly
 * 		Integer,Long,Boolean,void
 * 2,We need to submit data manually
 * 		sqlSessionFactory.openSession();===>Manual submission
 * 		sqlSessionFactory.openSession(true);===>Auto submit
 * @throws IOException 
 */
@Test
public void test03() throws IOException{
	SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
	//1. The obtained SqlSession will not automatically submit data
	SqlSession openSession = sqlSessionFactory.openSession();
	try{
		EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
		//Test add
		Employee employee = new Employee(1, "cockroach","xiaoqiang@qq.com", "1");
		mapper.addEmp(employee);
		System.out.println(employee.getId());
		
		//Test modification
		//Employee employee = new Employee(1, "Tom", "jerry@jerry.com", "0");
		//boolean updateEmp = mapper.updateEmp(employee);
		//System.out.println(updateEmp);

		//Test delete
		//mapper.deleteEmpById(2);
		//2. Submit data manually
		openSession.commit();
	}finally{
		openSession.close();
	}
}

1.1 generation method of primary key

If the database supports fields that automatically generate primary keys (such as MySQL and SQL Server), you can set useGeneratedKeys = "true" and then set the keyProperty to the target property.

<!-- public void addEmp(Employee employee); -->
<!-- parameterType: Parameter type, which can be omitted.
Get the value of self incrementing primary key:
    mysql It supports self incrementing primary keys and obtaining self incrementing primary key values, mybatis Also use statement.getGenreatedKeys(). 
    useGeneratedKeys="true": Use self incrementing primary key to obtain primary key value policy.
    keyProperty: Specify the corresponding primary key attribute, that is mybatis After obtaining the primary key value, encapsulate the value to javaBean Which attribute of the.
-->
<insert id="addEmp" parameterType="com.ginger.mybatis.bean.Employee"
        useGeneratedKeys="true" keyProperty="id" databaseId="mysql">
	insert into tbl_employee(last_name,email,gender) 
	values(#{lastName},#{email},#{gender})
</insert>

For databases that do not support self incrementing primary keys (such as Oracle), you can use the selectKey sub element: the selectKey element will run first, the id will be set, and then the insert statement will be called.

<!--
	Get the value of non self incrementing primary key:
		Oracle Self increment is not supported, Oracle Use sequence to simulate self increment.
		The primary key of each inserted data is the value obtained from the sequence; How to get this value.
 -->
<insert id="addEmp" databaseId="oracle">
	<!--
		keyProperty: The found primary key value is encapsulated to javaBean Which attribute of the.
		order="BEFORE": current sql Insert in sql Run before.
		   AFTER: current sql Insert in sql Run after.
		resultType: The return value type of the found data.
		
		BEFORE Running sequence:
		Run first selectKey query id of sql，Find out id Value encapsulation to javaBean of id Properties.
		Inserted at run time sql，You can take it out id Property.
		
		AFTER Running sequence: there may be some problems with this method. If multiple lines are inserted, the last one will overwrite the previous one, or will it be used BEFORE Better.
		Run inserted first sql(Take the new value from the sequence as id). 
		Rerun selectKey query id of sql. 
	-->
	<selectKey keyProperty="id" order="BEFORE" resultType="Integer">
	        <!-- Write query primary key sql sentence -->
	        <!-- BEFORE-->
	        select EMPLOYEES_SEQ.nextval from dual
	        <!-- AFTER: 
	        select EMPLOYEES_SEQ.currval from dual -->
	</selectKey>
	
    <!-- The primary key at the time of insertion is obtained from the sequence -->
    <!-- BEFORE: -->
    insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL)
    values(#{id},#{lastName},#{email})
    <!-- AFTER: 
    insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL)
    values(employees_seq.nextval,#{lastName},#{email}) -->
</insert>

1.1.1 selectKey

2. Parameters transfer

2.1 single parameter

Single parameter: it can accept the values of basic type, object type and collection type. In this case, MyBatis can directly use this parameter without any processing.
Single parameter: Mybatis will not do special processing, #{parameter name / arbitrary name}: get the parameter value.

<select id="getEmpById" resultType="com.ginger.mybatis.bean.Employee" databaseId="mysql">
	<!--select * from employee where id = #{id}-->
	select * from employee where id = #{suibian}
</select>

<select id="getEmpById" resultType="com.ginger.mybatis.bean.Employee" databaseId="mysql">
	<!--select * from employee where id = #{id}-->
	select * from employee where id = #{suibian}
</select>

//Interface
public interface EmployeeMapper {
	public Employee getEmpById(Integer id);
}

//test
@Test
public void test01() throws IOException {
	// 1. Get sqlSessionFactory object
	SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
	// 2. Get sqlSession object
	SqlSession openSession = sqlSessionFactory.openSession();
	try {
		// 3. Gets the implementation class object of the interface
		// A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
		EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
		Employee employee = mapper.getEmpById(1);
		System.out.println(employee);
	} finally {
		openSession.close();
	}
}

2.2 multiple parameters

Multiple parameters: any multiple parameters will be repackaged into a Map by MyBatis. The key of Map is param1, param2 or 0, 1... The value is the value of the parameter.
Multiple parameters: mybatis will do special processing.

Multiple parameters are encapsulated into a map.

key: param1... paramN, or the index of the parameter.
Value: the parameter value passed in.
#{} is to get the value of the specified key from the map.

2.2. 1. Multiple parameters use parameter names to obtain values (an error will be reported)

<!--  public Employee getEmpByIdAndLastName(Integer id,String lastName);-->
<select id="getEmpByIdAndLastName" resultType="com.ginger.mybatis.bean.Employee">
	select * from employee where id = #{id} and last_name=#{lastName}
</select>

// Interface
public interface EmployeeMapper {
	public Employee getEmpByIdAndLastName(Integer id, String lastName);
}

// test
@Test
public void test02() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        // A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        Employee employee = mapper.getEmpByIdAndLastName(1, "Xiao Ming");
        System.out.println(employee);
    } finally {
        openSession.close();
    }
}

2.2. 2 use param1 and param2 or 0 and 1 to get the value

<!-- public Employee getEmpByIdAndLastName(Integer id,String lastName); -->
<select id="getEmpByIdAndLastName" resultType="com.ginger.mybatis.bean.Employee">
	select * from employee where id = #{param1} and lastname=#{param2}
</select>
perhaps
<!--  public Employee getEmpByIdAndLastName(Integer id,String lastName);-->
<select id="getEmpByIdAndLastName" resultType="com.gigner.mybatis.bean.Employee">
	select * from employee where id = #{0} and lastname=#{1}
</select>

// Interface
public interface EmployeeMapper {
	public Employee getEmpByIdAndLastName( Integer id, String lastName);
}

// test
@Test
public void test02() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        Employee employee = mapper.getEmpByIdAndLastName(1, "Xiao Ming");
        System.out.println(employee);
    } finally {
        openSession.close();
    }
}

2.3 named parameter @ Param

Naming parameters: give a name to the parameter @ Param, and MyBatis will encapsulate these parameters into the map. The key is the name we specify.

Named parameter: Specifies the key of the map, @ Param("id") when encapsulating parameters.

Multiple parameters are encapsulated into a map.

key: use the @ Param annotation to specify the value.
Value: parameter value.
#{the specified key} takes out the corresponding parameter value.

<!-- public Employee getEmpByIdAndLastName(Integer id,String lastName); -->
<select id="getEmpByIdAndLastName" resultType="com.ginger.mybatis.bean.Employee">
	select * from employee where id = #{id} and lastname=#{lastName}
</select>

// Interface
public Employee getEmpByIdAndLastName(@Param("id") Integer id, @Param("lastName") String lastName);

// test
@Test
public void test02() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        // A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        Employee employee = mapper.getEmpByIdAndLastName(2, "Xiao Wang");
        System.out.println(employee);
    } finally {
        openSession.close();
    }
}

2.4 POJO

POJO: when these parameters belong to our business POJO, we directly pass the POJO.

POJO: if multiple parameters happen to be the data model of our business logic, we can directly pass them into POJO.
#{property name}: get the property value of the incoming pojo.

<!--public Employee getEmpByEmployee(Employee employee);-->
<select id="getEmpByEmployee" resultType="com.ginger.mybatis.bean.Employee">
	select * from employee where id = #{id} and lastname=#{lastName}
</select>

// Interface
public Employee getEmpByEmployee(Employee employee);

// test
@Test
public void test03() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        // A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        Employee employee = new Employee();
        employee.setId(2);
        employee.setLastName("Xiao Wang");
        Employee result = mapper.getEmpByEmployee(employee);
        System.out.println(result);
    } finally {
        openSession.close();
    }
}

2.5 Map

Map: we can also encapsulate multiple parameters as map and pass them directly.

Map: if multiple parameters are not data in the business model, and there is no corresponding pojo, they are not often used. For convenience, we can also pass in map.
#{key}: get the corresponding value in the map.

<!-- public Employee getEmpByMap(Map<String, Object> map); -->
<select id="getEmpByMap" resultType="com.ginger.mybatis.bean.Employee">
    select * from employee where id=#{id} and lastname=#{lastName}
</select>

// Interface
public Employee getEmpByMap(Map<String, Object> map);

// test
@Test
public void test04() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        // A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("id",3);
        map.put("lastName", "petty thief");
        Employee employee = mapper.getEmpByMap(map);
        System.out.println(employee);
    } finally {
        openSession.close();
    }
}

2.6 TO

If multiple parameters are not data in the business model but are often used, it is recommended TO write a TO (Transfer Object) data Transfer Object.

public Page{
	int index;
	int size;
}

2.7 parameter transfer extension

// Multiple parameters are passed. The first parameter specifies the key value of map and can be taken by #{id} or #{param1}. The second parameter does not specify the key value of map, so it can only be taken by #{param2}.
// Value: id = = > #{ID / Param1} LastName = = > #{param2}
public Employee getEmp(@Param("id")Integer id,String lastName);

// Pass multiple parameters. If the first parameter does not specify a map key value, you can only use #{param1} value. If the second parameter specifies a map key value, you can use #{param2.lastName}
// Or #{emp.lastName} value.
public Employee getEmp(Integer id,@Param("emp")Employee emp);
// Value: id = = > #{Param1} LastName = = > #{param2. LastName / EMP. LastName}

/*
	Special note: if the parameter is a Collection (list, Set) type or array, it will also be handled in a special way, and the passed in list or array will be encapsulated in the map.
		key: Collection(The key value of the passed in List or Set is collection). If it is a List key value, you can also use this key(list) array.
*/
public Employee getEmpById(Collection<Integer> collection);
//Value: get the value of the first id: #{collection[0]}
public Employee getEmpById(List<Integer> ids);
//Value: get the value of the first id: #{list[0]}
public Employee getEmpById(Integer[] ids);
//Value: get the value of the first id: #{array[0]}

3. Parameter processing

3.1 how to handle parameters in combination with the source code Mybatis

Summary: multiple parameters will be encapsulated map，In order not to be confused, we can use @Param To specify the key. 
#{key} can get the value in the map;

(@Param("id")Integer id,@Param("lastName")String lastName);
ParamNameResolver Analytic parameter encapsulation map of
//1,names: {0=id, 1=lastName}； It's determined when the constructor
	Determine the process:
	1.Get each bid param Of annotated parameters @Param Value of: id，lastName；  Assign to name;
	2.Parse one parameter at a time map Save information in:( key: Parameter index, value: name (value of)
		name Value of:
			Marked param Annotation: the value of the annotation
			No label:
				1.Global configuration: useActualParamName(jdk1.8): name=Parameter name
				2.name=map.size()；Equivalent to the index of the current element
	{0=id, 1=lastName,2=2}
				
args[1，"Tom",'hello']:
public Object getNamedParams(Object[] args) {
    final int paramCount = names.size();
    //1. Return directly if the parameter is null
    if (args == null || paramCount == 0) {
      return null;
     
    //2. If there is only one element and there is no Param annotation; args[0]: a single parameter is returned directly
    } else if (!hasParamAnnotation && paramCount == 1) {
      return args[names.firstKey()];
      
    //3. Multiple elements or Param annotation
    } else {
      final Map<String, Object> param = new ParamMap<Object>();
      int i = 0;
      
      //4. Traverse the names set; {0=id, 1=lastName,2=2}
      for (Map.Entry<Integer, String> entry : names.entrySet()) {
     
      	//The value of the names set is used as the key; The key of the names set is also used as the reference 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
        //Additionally, save each parameter to the map, using the new key: Param1 paramN
        //Effect: param annotation can #{specified key}, or #{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;
    }
  }
}

3.2 #{} and ${} parameter value acquisition

#{}: you can get the value in the map or the value of the pojo object attribute.
${}: you can get the value in the map or the value of the pojo object attribute.

#Difference between {} and ${}:

select * from employee where id=${id} and lastname=#{lastName}
Preparing: select * from employee where id=3 and lastname=?
	difference:
		#{}: in the form of precompiled, set the parameters to the sql statement, PreparedStatement, to prevent sql injection.
		${}:The extracted values are directly assembled in sql Statement, there will be security problems.
		In most cases, the values of parameters should be used#{}. 
		
		Primordial jdbc Where placeholders are not supported, we can use them ${}Take values.
		Such as table splitting and sorting...Split by year.
			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.ginger.mybatis.bean.Employee">
    select * from ${tableName} where id=${id} and lastname=#{lastName}
</select>

// Interface
public Employee getEmpByMap(Map<String, Object> map);

// test
@Test
public void test05() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("id",3);
        map.put("lastName", "petty thief");
        map.put("tableName","employee");
        Employee employee = mapper.getEmpByMap(map);
        System.out.println(employee);
    } finally {
        openSession.close();
    }
}

3.3 #{} more usage

javaType can usually be determined from the parameter object.
If null is passed as a value, the JDBC type needs to be set for all columns that may be empty.
For numeric types, you can also set the number of digits reserved after the decimal point.
The mode property allows you to specify IN, OUT, or INOUT parameters. If the parameter is OUT or INOUT, the real value of the parameter object property will be changed, just as expected when obtaining the output parameter.
Supported properties for parameter location.
javaType,jdbcType,mode,numericScale,resultMap,typeHandler,jdbcTypeName,expression.
In fact, what is usually set is.
JDBC type may be specified for empty column names.

#{}: richer usage:
	Some rules for specifying parameters:
	javaType, jdbcType, mode(Stored procedure) numericScale,resultMap, typeHandler, jdbcTypeName, expression(Functions to be supported in the future).

	jdbcType It usually needs to be set under certain conditions:
		In our data null When, some databases may not be recognized mybatis yes null Default processing of, for example Oracle(Error reporting).
		
		JdbcType OTHER: Invalid type because mybatis For all null Are mapped to native Jdbc of OTHER Type, oracle Not handled correctly, mysql It can be handled.
		
		Because in the global configuration: jdbcTypeForNull=OTHER，oracle I won't support it. There are two solutions
		1,#{email,jdbcType=NULL};
		2,jdbcTypeForNull=NULL
			<setting name="jdbcTypeForNull" value="NULL"/>

By default, the null value of the MyBatis pair is mapped to the JDBC OTHER type. The OTHER type is recognized by Mysql, but not by Oracle, so an error will be reported.

<!-- Oracle Database environment -->
<insert id="addEmp" databaseId="oracle">
	<selectKey keyProperty="id" order="BEFORE" resultType="Integer">
		<!-- Write query primary key sql sentence -->
		<!-- BEFORE-->
		select EMPLOYEES_SEQ.nextval from dual 
	</selectKey>
	
	<!-- The primary key at the time of insertion is obtained from the sequence -->
	<!-- BEFORE:-->
	insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL) 
	values(#{id},#{lastName},#{email<!-- ,jdbcType=NULL -->}) 
</insert>

// Interface
public Long addEmp(Employee employee);

// test
@Test
public void test03() throws IOException{
  	// 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
	try{
		EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
		//Test add
		//Insert a null value in the mailbox location
		Employee employee = new Employee(null, "jerry3",null, "1");
		mapper.addEmp(employee);
		System.out.println(employee.getId());
		openSession.commit();
	}finally{
		openSession.close();
	}
}

4. select element

select element to define the query operation.
Id: unique identifier.
The method name used to reference this statement must be consistent with that of the interface.
parameterType: parameter type.
It can be omitted. MyBatis will automatically infer according to TypeHandler.
resultType: return value type.
Alias or full class name. If a collection is returned, define the type of elements in the collection. It cannot be used together with resultMap.

4.1 automatic mapping of returned results

Global setting

autoMappingBehavior is PARTIAL by default. The function of automatic mapping is enabled. The only requirement is that the column name is consistent with the javaBean attribute name.
If autoMappingBehavior is set to null, automatic mapping is cancelled.
The database field naming is standard, and the POJO attribute conforms to the hump naming method, such as A_COLUMN, we can turn on the automatic hump naming rule mapping function, mapUnderscoreToCamelCase=true.

4.2 resultType

4.2. 1. The returned value of resulttype is List

<!-- public List<Employee> getEmpsByLastNameLike(String lastName); -->
<!--resultType: If you return a collection, write the type of the element in the collection  -->
<select id="getEmpsByLastNameLike" resultType="com.ginger.mybatis.bean.Employee">
	select * from tbl_employee where lastname like #{lastName}
</select>

// Interface
public List<Employee> getEmpsByLastNameLike(String lastName);

// test
@Test
public void test06() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        List<Employee> like = mapper.getEmpsByLastNameLike("%Small%");
        for (Employee employee : like) {
            System.out.println(employee);
        }
    } finally {
        openSession.close();
    }
}

4.2. 2. The returned value of resulttype is Map

4.2. 2.1 return a record

<!--public Map<String, Object> getEmpByIdReturnMap(Integer id);  -->
<select id="getEmpByIdReturnMap" resultType="map">
	select * from employee where id=#{id}
</select>

// Interface
// Returns the map of a record. The key is the column name and the value is the corresponding value.
public Map<String, Object> getEmpByIdReturnMap(Integer id);

// test
@Test
public void test07() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        Map<String, Object> map = mapper.getEmpByIdReturnMap(1);
        System.out.println(map);
    } finally {
        openSession.close();
    }
}

4.2. 2.2 return multiple records

<!--public Map<Integer, Employee> getEmpByLastNameLikeReturnMap(String lastName); -->
<select id="getEmpByLastNameLikeReturnMap" resultType="com.ginger.mybatis.bean.Employee">
	select * from employee where lastname like #{lastName}
</select>

// Interface
// Multiple records encapsulate a map: Map < string, employee >: the key is the primary key of the record, and the value is the javaBean encapsulated by the record.
// @MapKey: tell mybatis which attribute to use as the key of the map when encapsulating the map. Note that the key type specified by @ MapKey should be the same as the key type of the return value map, otherwise an error will be reported.
@MapKey("lastName") // If the return value is map and multiple records are returned, specify the key value of map. If it is not specified, an error will be reported.
public Map<String, Employee> getEmpByLastNameLikeReturnMap(String lastName);

// test
@Test
public void test08() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        Map<String, Employee> map = mapper.getEmpByLastNameLikeReturnMap("%Small%");
        Set<String> keySet = map.keySet();
        for (String key : keySet) {
            System.out.println(key+"---"+map.get(key));
        }
        System.out.println("--------------");
        System.out.println(map);
    } finally {
        openSession.close();
    }
}

4.3 resultMap

Customize resultMap to realize advanced result set mapping.

constructor: Class is used to inject results into the constructor when instantiating.
	idArg: ID Parameter, mark the result as ID Can help improve overall effectiveness.
	arg: A common result injected into the construction method.
id: One ID Results, mark results as ID Can help improve overall effectiveness.
result: Inject into field or JavaBean Property.
association: A complex type association into which many results will be packaged.
	Embedded result mapping: the association of the result mapping itself, or reference one.
collection: Set of complex types
	Embedded result map: the set of result maps themselves,Or refer to one.
discriminator: Use the result value to determine which result mapping to use.
	case: Result mapping based on some values
		Embedded result mapping: in this case, the result also maps itself, so it can contain many same elements, or it can refer to an external result mapping.

4.3.1 id & result

id and result map the value of a single column to an attribute or field of a simple data type (string, integer, double precision floating-point number, date, etc.).

<!--
	Customize a javaBean Packaging rules for
	type: Custom rule Java Type.
	id:only id Easy to quote.
-->
<resultMap id="MySimpleEmp" type="com.ginger.mybatis.bean.Employee">
	<!--
		Specifies the encapsulation rule for the primary key column.
		id Defining a primary key will optimize the underlying.
		column: Specify which column.
		property: Specify the corresponding javaBean Properties.
	-->
	<id column="id" property="id"/>
	<!-- Define common column encapsulation rules. In order to demonstrate, I will lastname Column changed to last_name. -->
	<result column="last_name" property="lastName"/>
	<!-- Other unspecified columns are automatically encapsulated: we just write resultMap Just write all the mapping rules. -->
	<!-- If there are some columns and javaBean In fact, the mapping rules do not need to be written, but it is recommended to write only resultMap Just write all the mapping rules.-->
	<result column="email" property="email"/>
	<result column="gender" property="gender"/>
</resultMap>

<!-- resultMap: Custom result set mapping rules.-->
<!-- public Employee getEmpById(Integer id); -->
<select id="getEmpById"  resultMap="MySimpleEmp">
	select * from tbl_employee where id=#{id}
</select>

// Interface
public Employee getEmpById(Integer id);

// test
@Test
public void test09() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpById(1);
        System.out.println(employee);
    } finally {
        openSession.close();
    }
}

4.3.2 association

Complex object mapping.
A property in a POJO may be an object.
We can use federated queries and encapsulate objects by cascading attributes.

4.3. 2.1 encapsulating objects by cascading attributes

<!--
    Scenario 1:
        query Employee Query the corresponding department of the employee at the same time
        Employee===>Department
        An employee has corresponding department information;
        id  last_nam  email  gender  depa_id  did  dept_name (private Department dept)
-->
<!--
    Joint query: cascade attributes encapsulate the result set.
    Here's a point you must pay attention to	employee，deparment	Of these two tables id The names are the same and must be distinguished (the first one may be used) id)，Otherwise there will be problems.
-->
<resultMap type="com.ginger.mybatis.bean.Employee" id="MyDifEmp">
    <id column="id" property="id"/>
    <result column="last_name" property="lastName"/>
    <result column="email" property="email"/>
    <result column="gender" property="gender"/>
    <result column="did" property="dept.id"/>
    <result column="depa_name" property="dept.departmentName"/>
</resultMap>

<!--public Employee getEmpAndDept(Integer id);-->
<select id="getEmpAndDept" resultMap="MyDifEmp">
	SELECT
      e.id,
      e.last_name last_name,
      e.email,
      e.gender,
      e.depa_id,
      d.id did,
      d.depa_name
    FROM
      employee e,
      department d
    WHERE d.id = e.depa_id
    AND d.id = #{id}
</select>

// Interface
public Employee getEmpAndDept(Integer id);

// test
@Test
public void test10() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpAndDept(1);
        System.out.println(employee);
    } finally {
        openSession.close();
    }
}

4.3.2.2 association label defines the encapsulation rules of objects

<!--
	use association Defines the encapsulation rules for an associated single object.
-->
<resultMap type="com.ginger.mybatis.bean.Employee" id="MyDifEmp2">
    <id column="id" property="id"/>
    <result column="last_name" property="lastName"/>
    <result column="email" property="email"/>
    <result column="gender" property="gender"/>

    <!--
        association: You can specify a federated javaBean object
        property="dept": Specifies which property is a federated object
        javaType: Specify the type of this property object[Cannot be omitted]
    -->
    <association property="dept" javaType="com.ginger.mybatis.bean.Department">
        <id column="did" property="id"></id>
        <result column="depa_name" property="departmentName"></result>
    </association>
</resultMap>

<!--  public Employee getEmpAndDept(Integer id);-->
<select id="getEmpAndDept" resultMap="MyDifEmp2">
	SELECT
	  e.id,
	  e.last_name last_name,
	  e.email,
	  e.gender,
	  e.depa_id,
	  d.id did,
	  d.depa_name
	FROM
	  employee e,
	  department d
	WHERE d.id = e.depa_id
	AND d.id = #{id}
</select>

// Interface
public Employee getEmpAndDept(Integer id);

// test
@Test
public void test10() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpAndDept(1);
        System.out.println(employee);
    } finally {
        openSession.close();
    }
}

4.3.2.3 association segmented query

<!--EmployeeMapper.xml-->
<!--
    use association Step by step query:
        1,First according to the employee id Query employee information.
        2,Query employee information according to d_id Go to the Department table to find out the Department information.
        3,Set the Department to the employee.
-->
<!--  id  last_name  email  gender  depa_id  -->
<resultMap type="com.ginger.mybatis.bean.Employee" id="MyDifEmp3">
    <id column="id" property="id"/>
    <result column="last_name" property="lastName"/>
    <result column="email" property="email"/>
    <result column="gender" property="gender"/>

    <!--
        association Define encapsulation rules for associated objects
   		select: Indicates that the current property is a call select The results of the specified method.
    	column: Specifies which column value to pass to this method.
	    Process: Using select Specified method (passed in) column The specified value of this column of parameters) finds out the object and encapsulates it to property The specified properties.
	-->
    <association column="did"
                 select="com.ginger.mybatis.dao.DepartmentMapper.getDeptById"
                 property="dept"></association>
</resultMap>

<!--  public Employee getEmpByIdStep(Integer id);-->
<select id="getEmpByIdStep" resultMap="MyDifEmp3">
	select * from employee where id=#{id}
</select>

<!--DepartmentMapper.xml-->
<!--public Department getDeptById(Integer id);  -->
<select id="getDeptById" resultType="com.ginger.mybatis.bean.Department">
	select id,depa_name departmentName from department where id=#{id}
</select>

// EmployeeMapper interface
public Employee getEmpAndDept(Integer id);

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

// test
@Test
public void test11() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpAndDept(3);
        System.out.println(employee);
    } finally {
        openSession.close();
    }
}

4.3.2.3 association segmented query delayed loading

You can actually use deferred loading (lazy loading)(Load on demand):
	Employee===>Dept: 
		Every time we check Employee Objects will be queried together.
		We can check the Department information when we use it.
		Two configurations are added on the basis of segmented query.

In the main configuration file of Mybatis, add the enable delay loading configuration in settings.

<settings>
	<!--The displayed value specifies the value of each configuration we need to change, even if it is the default. Prevent problems caused by version updates  -->
	<setting name="lazyLoadingEnabled" value="true"/>
	<setting name="aggressiveLazyLoading" value="false"/>
</settings>

Older versions of MyBatis require additional support packages.

asm-3.3.1.jar
cglib-2.2.2.jar

I still use the above test class to test whether there is delayed loading or not. What is delayed loading.

// test
@Test
public void test11() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpAndDept(3);
    } finally {
        openSession.close();
    }
}

4.3.3 collection

4.3. 3.1 the collection tag defines the encapsulation rules of objects

<!-- 
	Scenario 2:
		When querying a department, you can also query all the employee information corresponding to the Department.
-->
<!--Nested result sets, using collection Label defines the property encapsulation rule for the associated collection type-->
<resultMap type="com.ginger.mybatis.bean.Department" id="MyDept">
	<id column="id" property="id"/>
	<result column="depa_name" property="departmentName"/>
	<!-- 
		collection: Defines encapsulation rules for attributes associated with collection types
		ofType: Specifies the type of element in the collection
	-->
	<collection property="emps" ofType="com.ginger.mybatis.bean.Employee">
		<!-- Define the encapsulation rules for the elements in this collection -->
		<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,
	  d.depa_name,
	  e.id eid,
	  e.last_name,
	  e.email,
	  e.gender,
	  e.depa_id
	FROM
	  department d
	  LEFT JOIN employee e
		ON d.id = e.depa_id
	WHERE d.id = #{id}
</select>

// Interface
public Department getDeptByIdPlus(Integer id);

// test
@Test
public void test13() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
        Department department =   mapper.getDeptByIdPlus(1);
        System.out.println(department);
    } finally {
        openSession.close();
    }
}

4.3.3.2 collection segmented query

<!-- DepartmentMapper.xml -->
<!-- collection: Segmented query -->
<resultMap type="com.ginger.mybatis.bean.Department" id="MyDept2">
	<id column="id" property="id"/>
	<id column="depa_name" property="departmentName"/>
	<collection property="emps" 
		select="com.ginger.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
		column="id" ></collection>
</resultMap>

<!-- public Department getDeptByIdStep(Integer id); -->
<select id="getDeptByIdStep" resultMap="MyDept2">
	select id,depa_name from department where id=#{id}
</select>

<!-- EmployeeMapperPlus.xml -->
<!-- public List<Employee> getEmpsByDeptId(Integer deptId); -->
<select id="getEmpsByDeptId" resultType="com.ginger.mybatis.bean.Employee">
    select * from employee where depa_id=#{deptId}
</select>

// Interface
public Department getDeptByIdStep(Integer id);

// Interface
public List<Employee> getEmpsByDeptId(Integer deptId);

// test
@Test
public void test14() throws IOException {
    // 1. Get sqlSessionFactory object
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    // 2. Get sqlSession object
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        // 3. Gets the implementation class object of the interface
        //A proxy object will be automatically created for the interface, and the proxy object will execute the addition, deletion, modification and query methods
        DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
        Department department =   mapper.getDeptByIdStep(1);
        System.out.println(department);
    } finally {
        openSession.close();
    }
}

4.3.3.3 collection segmented query delayed loading