Chapter 4 MyBatis mapping file

4.1 introduction to mybatis mapping file

1. The real power of MyBatis lies in its mapping statement and its magic. Because of its extraordinary power, the XML file of mapper is relatively simple. If you compare it with JDBC code with the same function, you will immediately find that nearly 95% of the code is saved. MyBatis is built for SQL and is better than normal methods.
2. The SQL mapping file has a few top-level elements (in the order they should be defined):

Cache – the cache configuration for the given namespace.
Cache ref – a reference to another namespace cache configuration.
resultMap – the most complex and powerful element that describes how to load objects from a database result set.
parameterMap - obsolete! Old fashioned style parameter mapping. Inline parameters are preferred, this element may be removed in the future, and will not be recorded here.
sql - a block of reduplicative sentences that can be referenced by other statements.
Insert – map insert statement
 Update – map update statement
 Delete - map delete statement
	select - map query language

4.2 Mybatis uses insert|update|delete|select to complete CRUD

4.2.1 select

1. Mapper interface method

public Employee getEmployeeById(Integer id );

1. Mapper map file

<select id="getEmployeeById" 
          resultType="com.atguigu.mybatis.beans.Employee" 
          databaseId="mysql">
		 select * from tbl_employee where id = ${_parameter}
</select>

4.2.2 insert

1. Mapper interface method

public Integer  insertEmployee(Employee employee);

1. Mapper map file

<insert id="insertEmployee" 
		parameterType="com.atguigu.mybatis.beans.Employee"  
			databaseId="mysql">
		insert into tbl_employee(last_name,email,gender) values(#{lastName},#{email},#{gender})
</insert>

4.2.3 update

1. Mapper interface method

public Boolean  updateEmployee(Employee employee);

1. Mapper map file

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

4.2.4 delete

1. Mapper interface method

public void  deleteEmployeeById(Integer id );

1. Mapper map file

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

4.3 primary key generation method, get primary key value

4.3.1 primary key generation method

1. Support auto increase of primary key, such as MySQL database
2. Auto increment of primary key is not supported, for example, Oracle Database

4.3.2 get primary key value

1. 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.

<insert id="insertEmployee" 	parameterType="com.atguigu.mybatis.beans.Employee"  
			databaseId="mysql"
			useGeneratedKeys="true"
			keyProperty="id">
		insert into tbl_employee(last_name,email,gender) values(#{lastName},#{email},#{gender})
</insert>

4.4 parameter transfer

4.4.1 method of parameter transmission

1. Single common (basic / package + String) parameter
   In this case, MyBatis can directly use this parameter without any processing.
   Value: {free write}
2. Multiple parameters
   Any number of parameters will be repackaged into a Map by MyBatis. The key of Map is param1, param2, or 0, 1 , value is the value of the parameter
   Value: {0 1 2 N / param1 param2 …… paramN}
3. Named parameter
   Use @ Param to name the parameters. MyBatis will encapsulate these parameters into the map. key is the name we specify
   Value: {name specified by oneself / Param1 param2 paramN}
4. POJO
   When these parameters belong to our business POJO, we directly pass the POJO
   Value: attribute name of {POJO}
5. Map
   We can also encapsulate multiple parameters as map and directly transfer them
   Value: {use the key specified when encapsulating Map}
6. Collection/Array
   It will be encapsulated by MyBatis as a map and passed in. The key corresponding to Collection is Collection, and the key corresponding to array is array. If it is determined to be a list Collection, the key can also be a list
   Value:

Array: #{array}
Collection(List/Set): #{collection}
List : #{collection / list}

4.4.2 source code analysis of parameter transfer

1. Take named parameters for example:

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

1. Source code:
   Premise: args=[1024, Cang teacher] names = {0 = ID, 1 = LastName}

public Object getNamedParams(Object[] args) {
    final int paramCount = names.size();
    if (args == null || paramCount == 0) {
      return null;
    } else if (!hasParamAnnotation && paramCount == 1) {
      return args[names.firstKey()];
    } else {
      final Map<String, Object> param = new ParamMap<Object>();
      int i = 0;
      for (Map.Entry<Integer, String> entry : names.entrySet()) {
        param.put(entry.getValue(), args[entry.getKey()]);
        // add generic param names (param1, param2, ...)
        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;
    }
  }

4.4.3 parameter processing

1. Properties supported by parameter location:
   javaType,jdbcType,mode,numericScale,resultMap,typeHandler,jdbcTypeName,expression
2. In fact, it is usually set to specify a JDBC type for an empty column name, for example:

insert into orcl_employee(id,last_name,email,gender) values(employee_seq.nextval,#{lastName, ,jdbcType=NULL },#{email},#{gender})    --Oracle

4.4.4 parameter acquisition method

1. {key}: single common type, POJO type, multiple parameters and set type are preferred

    Get the value of the parameter and precompile it into SQL. Safety. PreparedStatement
   

2. ${key}: single common type, POJO type, multiple parameters and set type can be taken
   Note: take a single common type parameter, KaTeX parse error: Expected 'EOF', got '#' at position 110: tatement Principle: if we can use the value of {}, we will give priority to {}, {{} {}.

  For example: where native JDBC does not support placeholders, you can use ${}
  Select  column1 ,column2…  from table where condition group by group ID having condition order by sort field desc / ASC limit x, X 

4.5 several situations of select query

1. Query single line data return single object

public Employee getEmployeeById(Integer id );

1. Query the collection of multi row data return objects

public List<Employee> getAllEmps();

1. Query single row data and return Map set

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

1. Query multi row data and return Map set

@MapKey("id") // Specifies which attribute of the object is used as the key of the map
public Map<Integer,Employee>  getAllEmpsReturnMap();

4.6 resultType automatic mapping

1. autoMappingBehavior is PARTIAL by default, which enables automatic mapping. The only requirement is that the column name is the same as the javaBean attribute name
2. If autoMappingBehavior is set to null, the automap will be cancelled
3. Database field naming specification, POJO attribute conforms to hump naming method, such as a  column, we can enable automatic hump naming rule mapping function, mapUnderscoreToCamelCase=true

4.7 resultMap custom mapping

1. Custom resultMap to implement advanced result set mapping
2. id: used to complete the mapping of primary key values
3. result: used to complete the mapping of common columns
4. association: a complex type association; many results will be packaged into this type
5. collection: a set of complex types

4.7.1 id&result

<select id="getEmployeeById" resultMap="myEmp">
		select id, last_name,email, gender from tbl_employee where id =#{id}
</select>

<resultMap type="com.atguigu.mybatis.beans.Employee" id="myEmp">
		<id column="id"  property="id" />
		<result column="last_name" property="lastName"/>
		<result column="email" property="email"/>
		<result column="gender" property="gender"/>
</resultMap>

4.7.2 association

1. The attribute in POJO may be an object. We can use union query to encapsulate the object in the way of cascading attributes. We can use association tag to define the encapsulation rules of the object

public class Department {
	private Integer id ; 
	private String departmentName ;
//  Omit get/set method
}

public class Employee {
	private Integer id ; 
	private String lastName; 
	private String email ;
	private String gender ;
	private Department dept ;
    // Omit get/set method
}

1. Use cascading:

<select id="getEmployeeAndDept" resultMap="myEmpAndDept" >
		SELECT e.id eid, e.last_name, e.email,e.gender ,d.id did, d.dept_name FROM tbl_employee e , tbl_dept d   WHERE e.d_id = d.id  AND e.id = #{id}
</select>
	
	<resultMap type="com.atguigu.mybatis.beans.Employee" id="myEmpAndDept">
		<id column="eid" property="id"/>
		<result column="last_name" property="lastName"/>
		<result column="email" property="email"/>
		<result column="gender" property="gender"/>
	    <!-- Cascade mode -->
		<result column="did" property="dept.id"/>
	<result column="dept_name" property="dept.departmentName"/>
</resultMap>

1. Association'

<resultMap type="com.atguigu.mybatis.beans.Employee" id="myEmpAndDept">
		<id column="eid" property="id"/>
		<result column="last_name" property="lastName"/>
		<result column="email" property="email"/>
		<result column="gender" property="gender"/>
		<association property="dept" javaType="com.atguigu.mybatis.beans.Department">
			<id column="did" property="id"/>
			<result column="dept_name" property="departmentName"/>
		</association>
</resultMap>

4.7.3 step by step query of Association

1. In the actual development, there should be specific adding, deleting, modifying and querying methods for each entity class, that is, DAO layer. Therefore, for the demand of querying employee information and corresponding department information, you can complete the query step by step.
   ① First query employee information by employee id
   ② Then through the foreign key (Department id) in the queried employee information to query the corresponding department information

<select id="getEmployeeAndDeptStep" resultMap="myEmpAndDeptStep">
		 select id, last_name, email,gender,d_id  from tbl_employee where id =#{id}
</select>
	
<resultMap type="com.atguigu.mybatis.beans.Employee" id="myEmpAndDeptStep">
		<id column="id"  property="id" />
		<result column="last_name" property="lastName"/>
		<result column="email" property="email"/>
		<result column="gender" property="gender"/>
		<association property="dept" 			select="com.atguigu.mybatis.dao.DepartmentMapper.getDeptById" 
				column="d_id" fetchType="eager">
		</association>
</resultMap>

4.7.4 Association step-by-step query uses delayed loading

1. On the basis of step-by-step query, you can use delay loading to improve the efficiency of query. You only need to configure the following in the global Settings:

<! -- enable delay loading -- >
<setting name="lazyLoadingEnabled" value="true"/>
<! -- set whether the loaded data is on demand or all -- >
<setting name="aggressiveLazyLoading" value="false"/>

4.7.5 collection

1. The attribute in POJO may be a collection object. We can use union query to encapsulate the object in the way of cascading attributes. We can use collection label to define the encapsulation rules of the object

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

1. Collection

<select id="getDeptAndEmpsById" resultMap="myDeptAndEmps">
		SELECT d.id did, d.dept_name ,e.id eid ,e.last_name ,e.email,e.gender 
		FROM tbl_dept d  LEFT OUTER JOIN tbl_employee e ON  d.id = e.d_id 
		WHERE d.id = #{id}
	</select>
	<resultMap type="com.atguigu.mybatis.beans.Department" id="myDeptAndEmps">
		<id column="did" property="id"/>
		<result column="dept_name" property="departmentName"/>
		<!-- 
			property: Associated property name
			ofType: Types of elements in the collection
		 -->
		<collection property="emps"  ofType="com.atguigu.mybatis.beans.Employee">
			<id column="eid" property="id"/>
			<result column="last_name" property="lastName"/>
			<result column="email" property="email"/>
			<result column="gender" property="gender"/>
		</collection>
</resultMap>

4.7.6 step by step query of collection

1. In the actual development, there should be a specific way to add, delete, modify and query each entity class, that is, DAO layer, so
   For the demand of querying department information and all corresponding employee information, you can complete the query step by step.
   ③ First query department information by department id
   ④ Then the Department id is used as the employee's foreign key to query the corresponding department information

<select id="getDeptAndEmpsByIdStep" resultMap="myDeptAndEmpsStep">
	 	select id ,dept_name  from tbl_dept where id = #{id}
	 </select>
	 <resultMap type="com.atguigu.mybatis.beans.Department" id="myDeptAndEmpsStep">
	 	<id column="id" property="id"/>
	 	<result column="dept_name" property="departmentName"/>
	 	<collection property="emps" 
	 			select="com.atguigu.mybatis.dao.EmployeeMapper.getEmpsByDid"
	 			column="id">
	 	</collection>
 </resultMap>

4.7.7 collection step-by-step query using delayed loading

4.7.8 extension: step by step query of multi column value transfer

1. If a step-by-step query needs to be passed to multiple parameters in the called query, multiple parameters need to be encapsulated into a Map for passing. The syntax is as follows: {k1=v1, k2=v2 }
2. In the called query side, the value should refer to the map value method, and the value should be strictly based on the key used to encapsulate the map

4.7.9 extension: fetchType attribute of association or collection

1. You can set the fetchType in the and tags to specify whether to use delay loading for this query. The default value is fetchType = "lazy". If you don't want to use delay loading in this query, you can set it to fetchType = "eager"
2. fetchType can flexibly set whether queries need to use delay loading or not, without turning off global delay loading settings because a query does not want to use delay loading