SQL mapping file of mybatis

Time:2021-6-11

SQL mapping file

General structure of mapping file

<?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.atguigu.mybatis.dao.EmployeeMapper">
<!-- 
Namespace: name space; The full class name specified as the interface
ID: unique identification
Resulttype: return value type
#{ID}: get the ID value from the passed parameter
public Employee getEmpById(Integer id);
 -->
     <select id="getEmpById" resultType="com.atguigu.mybatis.bean.Employee">
        select * from tbl_employee where id = #{id}
    </select>
    
    <!-- public void addEmp(Employee employee); -->
    
    <insert id="addEmp" parameterType="com.atguigu.mybatis.bean.Employee"
        useGeneratedKeys="true" keyProperty="id" databaseId="mysql">
        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>

Namespace is specified as the full class name of the interface file

Test addition, deletion and modification

  • Mybatis allows you to add, delete, and modify the following types of return values directly
    Integer、Long、Boolean、void
    If you need a return value, you can directly modify the return value type in the mapper interface
  • We need to submit the data manually
    sqlSessionFactory.openSession();===》 Manual submission
    sqlSessionFactory.openSession(true);===》 Auto submit

Gets the value of the self incrementing primary key

If you want to get the value of the self incrementing primary key, you need to add it in the insert tag`
useGeneratedKeys, and useKeyproperty ` specifies which field is the primary key

<!-- public void addEmp(Employee employee); -->
    <!--  Parametertype: parameter type, which can be omitted, 
    Get the value of self incrementing primary key:
        MySQL supports auto incrementing primary keys and the acquisition of auto incrementing primary key values. Mybatis also uses statement. Getgenreatedkeys();
        useGeneratedKeys="true"; Using self increasing primary key to get primary key value strategy
        keyProperty; Specify the corresponding primary key attribute, that is, after mybatis obtains the primary key value, which attribute of JavaBean encapsulates the value
    -->
    <insert id="addEmp" parameterType="com.atguigu.mybatis.bean.Employee"
        useGeneratedKeys="true" keyProperty="id" databaseId="mysql">
        insert into tbl_employee(last_name,email,gender) 
        values(#{lastName},#{email},#{gender})
    </insert>

Gets the value of a non self incrementing primary key

Oracle does not support auto increment; Oracle uses sequence to simulate auto increment;
The primary key of each inserted data is the value obtained from the sequence;

<insert id="addEmp" databaseId="oracle">
        <!-- 
        Keyproperty: find out which property the primary key value encapsulates to JavaBean
        Order = "before": the current SQL runs before inserting SQL
               After: the current SQL runs after inserting SQL
        Resulttype: the return value type of the found data
        
        Before operation sequence:
            First, run the SQL of selectkey query ID; Find out the ID attribute encapsulated by the ID value to JavaBean
            When running the inserted SQL; You can get the value corresponding to the ID attribute
        After operation sequence:
            First, run the inserted SQL (take the new value from the sequence as the ID);
            Then run the SQL of selectkey query ID;
         -->
        <selectKey keyProperty="id" order="BEFORE" resultType="Integer">
            <!--  Write the SQL statement to query the primary key -- >
            <!-- BEFORE-->
            select EMPLOYEES_SEQ.nextval from dual 
            <!-- AFTER:
             select EMPLOYEES_SEQ.currval from dual -->
        </selectKey>
        
        <!--  The primary key at insertion is obtained from the sequence -- >
        <!-- 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>

Parameter processing

  • Single parameter

Mybatis doesn’t do special processing. You can get the parameter value by # {parameter name / arbitrary name}.

  • Multiple parameters

Multiple parameters are encapsulated into a map;
Key: Param1… Paramn, or the index of the parameter;
Value: the parameter value passed in
You need to get the parameter value through # {Param1}, # {param2}, or # {0}, # {1}

If it is a collection (list, set) type or an array, it will also be treated specially. It also encapsulates the incoming list or array in the map.
Key: Collection (Collection). If it is a list, you can also use this key (list); Array
For example:

<!-- Value: take out the value of the first ID: # {list [0]}-- >
public Employee getEmpById(List<Integer> ids);
  • Named parameters

Explicitly specify the key of the map when encapsulating parameters; For example, @ param (“Id”), @ param (“LastName”) can get values through # {ID}, # {LastName}

public Employee getEmpByIdAndLastName(@Param("id") Integer id,@Param("lastName")String lastName);
  • POJO

If multiple parameters happen to be the data model of our business logic, we can directly pass them into POJO;
#{attribute name}: retrieve the attribute value of the incoming POJO

  • Map

If multiple parameters are not data in the business model, there is no corresponding POJO, and they are not often used, we can also pass in map for convenience;
#{key}: get the corresponding value in the map

  • TO(Transfer Object)

If multiple parameters are not data in the business model, but are often used, it is recommended to write a to data transfer object

Page{
    int pageNum;
    int pageSize;
}

Combined with the source code, how does mybatis deal with parameters

Summary: map will be encapsulated when there are many parameters. To avoid confusion, we can use @ param to specify the key to be used in encapsulation;
#{key} can get the value in the map;

(@Param("id")Integer id,@Param("lastName")String lastName);
Paramnameresolver resolves the value of the parameter encapsulation map;
//1、names:{0=id, 1=lastName}; It's the constructor

    Determine the process:
    1. Get the value of @ param of each param annotated parameter: ID, LastName; Assign value to name;
    2. Parse one parameter each time to save information in the map: (key: parameter index, value: name value)
        Value of Name:
            Param annotation: the value of the annotation
            Not marked:
                1. Global configuration: useactual paramname (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. If the parameter is null, return it directly
    if (args == null || paramCount == 0) {
      return null;
     
    //2. If there is only one element and there is no param annotation; Args [0]: single parameter 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 value of 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
        //In addition, save each parameter to the map, using the new key: Param1... Paramn
        //Effect: param annotation can be # {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;
    }
  }
}

Getting parameter value

#{}: you can get the value of map or POJO object attribute;
${}: you can get the value of map or POJO object attribute;

difference:

#{}: in the form of precompile, parameters are set to SQL statements; PreparedStatement; Prevent SQL injection
${}: the extracted value is directly assembled in the SQL statement; There will be safety problems;
select * from tbl_employee where id=${id} and last_name=#{lastName}
Preparing: select * from tbl_employee where id=2 and last_name=?

In most cases, we should use # {} to get the parameter value;
Where native JDBC does not support placeholders, we can use ${} to take values
Such as sub table, sorting…; Split by year table

select * from ${year}_salary where xxx;
select * from tbl_employee order by ${f_name} ${order}

#More abundant usage:

Some rules for specifying parameters:
Javatype, JDBC type, mode (stored procedure), numericscale
Resultmap, typehandler, JDBC typename and expression (functions to be supported in the future);

JDBC type usually needs to be set under certain conditions
When our data is null, some databases may not recognize the default processing of null by mybatis. For example, Oracle (error reporting);

Jdbctype other: invalid type; Because mybatis maps the other type of native JDBC to all null, Oracle can't handle it correctly;
    
In the global configuration: JDBC type for null = other; Oracle does not support it; There are two ways
1、#{email,jdbcType=OTHER};
2、jdbcTypeForNull=NULL
    <setting name="jdbcTypeForNull" value="NULL"/>