Mybatis study notes

Time:2021-10-26

1. HelloWorld

Import mybatis

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>

1.1 profile method

Building sqlsessionfactory from XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/employee-mapper.xml"/>
    </mappers>
</configuration>

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">
<!--
 Namespace: namespace
 ID: unique identification
 ReturnType: return value type
 #{ID} takes the ID value from the passed parameter
-->
<mapper namespace="com.meituan.mybatis.bean.EmployeeMapper">
    <select id="selectEmp" resultType="com.meituan.mybatis.bean.Employee">
        select * from employee where id = #{id}
    </select>
</mapper>

JavaBean


public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String 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 + '\'' +
                '}';
    }
}

unit testing

/**
     *1. Create a sqlsessionfactory object according to the XML configuration file (global configuration file)
     *There are data sources and some operating environment information    
     *2. SQL mapping file, which configures each SQL and SQL encapsulation rules
     *3. Register the SQL mapping file in the global configuration file
     *4. Write code
     *1) get sqlsessionfactory according to the global configuration file
     *2) obtain the sqlsession through the sqlsession factory, and use the sqlsession to perform addition, deletion, modification and query. A sqlsession is a session between the representative and the database, which is closed after use
     *3) use the unique ID of the SQL to tell mybatis which SQL to execute. All the SQL is saved in the SQL mapper
     * @throws Exception
     */
    @Test
    public void test() throws Exception{
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. Get the sqlsession instance and directly execute the mapped SQL statements
        SqlSession sqlSession = sqlSessionFactory.openSession();

        /**
         * Retrieve a single row mapped from the statement key and parameter.
         * @param <T> the returned object type
         *@ param statement unique identifier matching the statement to use
         *@ parameter a parameter object to pass to the statement
         * @return Mapped object
         */
        Employee employee = sqlSession.selectOne("com.meituan.mybatis.bean.EmployeeMapper.selectEmp", 1);
        System.out.println(employee);

    }

Hump nomenclature:

When configuring the mybatis-config.xml configuration file,Note the order of nodes

<properties>...</properties>
<settings>...</settings>
<typeAliases>...</typeAliases>
<typeHandlers>...</typeHandlers>
<objectFactory>...</objectFactory>
<objectWrapperFactory>...</objectWrapperFactory>
<plugins>...</plugins>
<environments>...</environments>
<databaseIdProvider>...</databaseIdProvider>
<mappers>...</mappers>

Add settings

Support hump nomenclature
<setting name="mapUnderscoreToCamelCase" value="true"/>

1.2 interface programming

configuration 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">
<!--
 Namespace: namespace; In interface programming, it must be specified as the full class name of the interface
 ID: unique identification
 ReturnType: return value type
 #{ID} takes the ID value from the passed parameter; In interface programming, ID is changed to method name
-->
<mapper namespace="com.meituan.mybatis.dao.EmployeeMapper">
    <select id="getEmpById" resultType="com.meituan.mybatis.bean.Employee">
        select * from employee where id = #{id}
    </select>
</mapper>

Mapper interface

@Mapper
public interface EmployeeMapper {

    public Employee getEmpById(Integer id);
}

unit testing

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

    }

    private SqlSessionFactory getSqlSessionFactory() throws Exception{
        String resources = "mybatis-config.xml";
        InputStream is = Resources.getResourceAsStream(resources);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        return sqlSessionFactory;
    }

1.3 summary

  1. Interface programming

    Native: Dao = = = = > daoimpl

    mybatis: Mapper ==== > xxMapper.xml

  2. Sqlsession represents a session with the database. It must be closed after running out
  3. Sqlsession, like connection, is non thread safe. You should get a new object every time you use it and cannot write it as a member variable.
  4. The mapper interface does not implement classes, but mybatis will generate a proxy object for this interface
  5. Two important profiles

    Global configuration file: mybatis-config.xml contains database connection pool information, transaction manager information and other system operating environments

    SQL mapping file: saves the mapping information of each SQL statement

2. Mybatis global configuration file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <!--
        1. Mybatis can use properties to import the contents of external properties configuration files
            Attribute: Resource: introduces resources under the classpath
                        URL: introduce resources under the network path or disk path
    -->
    <properties resource="dbconfig.properties">
    </properties>

    <!--
        2. These are extremely important adjustment settings in mybatis, which will change the runtime behavior of mybatis.
            Setting: used to set each setting item
                Name: set item name
                Value: set item value
    -->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    
    <!--
        3. Typealiases is to set a short name for a Java type. It is only related to XML configuration, and its significance is only to reduce the redundancy of class fully qualified names.
            Aliases are not case sensitive
            Typealias: alias a Java type
            Type: Specifies the full class name of the type to be aliased; The default alias is the lower case class name: employee
            Alias: specify a new alias
            
            < package >: alias all classes under a package in batch
                    Each Java Bean in the package domain.blog will use the initial lowercase unqualified class name of the bean as its alias without annotation. For example, the alias of domain.blog.author is author; If there is an annotation, the alias is its annotation value.
                    Name: specify the package name (one package name for each class of the current package and all the following descendant packages)
                    
                    If there is the same class name under the sub package, mybatis will give the same package name and report an error. You can use @ alias to assign aliases to specific classes
    -->
    <typeAliases>
        <!--<typeAlias type="com.meituan.mybatis.config.bean.Employee" alias="emp"></typeAlias>-->
        <package name="com.meituan.mybatis.config"/>
    </typeAliases>
    
    <!--
        4. When mybatis sets a parameter in the Preparedstatement or takes a value from the result set, the typehandler will use the type processor to convert the obtained value into Java type in an appropriate way.
    -->
    
    <!--
        5. Plugins plug-in
    -->
    
    <!--
        6. environment  
        Mybatis can be configured to adapt to a variety of environments. This mechanism helps to apply SQL mapping to a variety of databases. In reality, there are many reasons to do so.
        Default specifies to use an environment to switch the environment, which can achieve fast environment switching.
        Environment configures a specific environment information. The ID represents the unique identification of the current environment. There must be two labels:
            Transaction manager: transaction manager,
                    Type specifies the type of transaction manager. There are two types of transaction managers in mybatis (that is, type = "[jdbc|managed]):
                        JDBC:typeAliasRegistry.registerAlias("JDBC", JdbcTransactionFactory.class);
                        MANAGED:typeAliasRegistry.registerAlias("MANAGED", ManagedTransactionFactory.class);
                        Support custom transaction manager and implement transactionfactory interface
            Datasource: data source
                Type: data source type. There are three built-in data source types (that is, type = "[unpooled | pooled | JNDI]):
                Support custom data sources, implement datasourcefactory interface, and type is the full class name of custom data sources
    -->
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    
    <!--
        7. Databaseidprovider: supports multiple database vendors
    -->
    
    <!--
        8. Mappers mapper
            Mapper registers an SQL map
                Resource: refers to the SQL mapping file under the classpath
                URL: refers to the mapping file under the network path or disk path
                Class: use the mapper interface to implement the fully qualified class name of the class
                    1. There is an SQL mapping file. The mapping file name must have the same name as the interface and be placed in the same directory as the interface
                    2. There is no SQL mapping file. All SQL is written on the interface using annotations
                Package: register all mapper interface implementations in the package as mappers
    -->
    <mappers>
        <mapper resource="mapper/employee-mapper.xml"/>
    </mappers>
    
    <!--
        Tags are written in order
    -->
</configuration>

3. Mybatis mapping file

The real power of mybatis lies in its mapping statement, which is also its magic. Because of its extraordinary power, the XML file of the 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 does better than ordinary methods.

3.1 get auto increment PK

<?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">
<!--
 Namespace: namespace
 ID: unique identification
 ReturnType: return value type
 #{ID} takes the ID value from the passed parameter
-->
<mapper namespace="com.meituan.mybatis.mapper.dao.EmployeeMapper">
    <select id="getEmpById" resultType="com.meituan.mybatis.mapper.bean.Employee">
        SELECT *
        FROM employee
        WHERE id = #{id}
    </select>

    <!-- Parametertype: can be omitted
        MySQL supports self incrementing primary keys, which can be obtained by using statement. Getgeneratedkeys(),
        Usegeneratedkeys = "true": policy of obtaining primary key value by using self incrementing primary key
        Keyproperty: Specifies the corresponding primary key property, that is, after mybatis obtains the primary key value, which property of the JavaBean encapsulates the value
    -->
    <insert id="addEmp" parameterType="com.meituan.mybatis.mapper.bean.Employee"
        useGeneratedKeys="true" keyProperty="id"
    >
        INSERT INTO employee (last_name, email, gender)
        VALUES (#{lastName}, #{email}, #{gender})
    </insert>

    <update id="updateEmp">
        UPDATE employee
        SET last_name = #{lastName}, email = #{email}, gender = #{gender}
        WHERE id = #{id}
    </update>

    <delete id="deleteEmpById">
        DELETE FROM employee WHERE id=#{id}
    </delete>
</mapper>

unit testing

/**
     *1. Mybatis allows you to add, delete or modify the following types of return values directly
     *      Integer Long Boolean
     *2. Submit data manually
     * @throws Exception
     */
    @Test
    public void test02() throws Exception {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //1. The obtained sqlsession will not be submitted automatically
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            Employee employee = new Employee(null, "jerry", "[email protected]", "2");
            System.out.println(employee);
            System.out.println("============");
            mapper.addEmp(employee);
            System.out.println(employee);
            //            employee.setLastName("jason");
//            employee.setId(3);
//            mapper.updateEmp(employee);
//            mapper.deleteEmpById(3);
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    private SqlSessionFactory getSqlSessionFactory() throws Exception{
        String resources = "mybatis-config.xml";
        InputStream is = Resources.getResourceAsStream(resources);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        return sqlSessionFactory;
    }

3.2 parameter processing

1) Single parameter: mybatis will not be specially processed

2) Multiple parameters

Exception:

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [arg1, arg0, param1, param2]
### Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [arg1, arg0, param1, param2]

Operation:

Method: public employee getempbyidandlastname (integer ID, string LastName);

Values: #{ID}, #{LastName}

Mybatis will be specially processed, and multiple parameters will be encapsulated into a map

​ key:param1….paramN

Value: the parameter value passed in

#{} is to get the specified key value from the map or the index of the parameter

Named parameters:

Explicitly specify the key of the encapsulated parameter value map: @ param (“Id”)

POJO:

If multiple parameters happen to be the data model of business logic, they can be directly passed into POJO:

#{property name}: get the property value of the incoming POJO

Map:

If multiple parameters are not data in the business model, and there is no corresponding POJO, they are not often used. For convenience, you can also pass in a map

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

3) Thinking about parameter encapsulation extension:

  1. public Employee getEmp(@Param(“id”))Integer id, String lastName);

    Value: id = = #{ID / Param1} LastName = = > #{param2}

  2. public Employee getEmp(Integer id, @Param(“e”) Employee emp);

    Value: id = = #{Param1} LastName = = #{param2. LastName / e.lastname}

  3. Special note: if it is a collection (list, set) type or array

    It will also be handled in a special way. It also encapsulates the incoming list or array in the map

    Key: Collection (Collection). If it is a list, you can also use (list)

    Array

    public Employee getEmpById(List<Integer> ids);

    Value: get the value of the first ID: #{list [0]}

3.3 how does mybatis handle parameters in combination with the source code

Paramnameresolver parses parameter encapsulation map

(1) names:(0=id, 1=lastName)

1) get the param value of each param annotation parameter: ID, LastName, and assign it to name

2) parse one parameter at a time and save the information in the map: key is the index value and value is the value of name

Value of Name:

Param annotation is marked, and the value of annotation

No label:

1. Global configuration: useactualparamname, name = parameter name (JDK1.8 required)

2. Name = map. Size () is equivalent to the index of the current element

​ names:{0=id, 1=lastName}

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. When traversing names, the constructor has been determined
      for (Map.Entry<Integer, String> entry : names.entrySet()) {
        //The value of names is used as the key of the new map, and the namekey is used as the reference of the value
        //Eg: {id = args [0], LastName = args [1]}, so you can get the corresponding value in the mapping file
        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;
    }
  }

3.4 acquisition of parameter values

#{}: 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: #{} the parameter is set to the SQL statement in the form of precompiled, Preparedstatement

${}: if the extracted value is directly assembled in the SQL statement, there will be a security problem

In most cases, #{} should be used for parameter values. In some cases, ${} can be used for values where the native JDBC does not support placeholders,

Such as sub table; Split select by yearfrom 2017_ Salary can be written as select from ${year}_salary

3.5 #{} value rules

Richer usage

Some rules for specifying parameters:

Javatype, jdbctype, mode (stored procedure), numericscale, resultmap, typehandler, jdbctypename, expression

JDBC type participation needs to be set under certain conditions

When the data is null, some databases may not recognize the default processing of null by mybatis, such as Oracle. All nulls by mybatis are mapped to the other type of the native JDBC, which cannot be processed by Oracle and can be processed by mysql

1、#{email, jdbcType=OTHER}

2. In the global configuration file mybatis-config.xml:<setting name="jdbcTypeForNull" value="NULL" />

3.6 select returns list and map

  • Return to list
<!-- Resulttype: if a collection is returned, write the type of the element in the collection -- >
    <select id="getEmpsByLastNameLike" resultType="com.meituan.mybatis.mapper.bean.Employee">
        SELECT * FROM employee WHERE last_name LIKE #{lastName}
    </select>
  • Return the map. The key is the column name and the value is the corresponding value
<!-- Map is a custom alias for mybatis -- >
<select id="getEmpByIdReturnMap" resultType="map">
    SELECT * FROM employee WHERE id=#{id}
</select>
  • Multiple records are encapsulated into a map. The map < integer, employee > key is the primary key of the record, and the value is the JavaBean encapsulated by the record
    <select id="getEmpByLastNameLikeReturnMap" resultType="com.meituan.mybatis.mapper.bean.Employee">
        SELECT * FROM employee WHERE last_name LIKE #{lastName}
    </select>
    
@MapKey("id")
public Map<Integer, Employee> getEmpByLastNameLikeReturnMap(String lastName);

3.7 custom result mapping encapsulation rules

resultMap  Element is the most important and powerful element in mybatis. It allows you to start from 90% JDBC  ResultSets  Data extraction is liberated from the code and in some cases allows you to do things that JDBC does not support. In fact, when joint mapping complex statements, it is likely to replace thousands of lines of equivalent code. The design idea of resultmap is that simple statements do not need explicit result mapping, while more complex statements only need to describe their relationship.

<resultMap id="myEmp" type="com.meituan.mybatis.mapper.bean.Employee">
        <!-- Column specifies which column, and property specifies the corresponding JavaBean property
            ID: Specifies the encapsulation rule of the primary key column, which will optimize the rule at the bottom
        -->
        <id column="id" property="id"/>
        <!-- Define encapsulation rules for common columns -- >
        <result column="last_name" property="lastName"/>
        <!-- Other unspecified columns will be automatically encapsulated. It is recommended to write the mapping rules of all columns as long as you write resultmap -- >
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </resultMap>
    
    <select id="getEmpById" resultMap="myEmp">
        SELECT *
        FROM employee
        WHERE id = #{id}
    </select>

3.8 Association query

  • The first way to write resultmap:
<!--
        Scenario 1:
            When querying employee, you can also query the Department of the employee
    -->
    <resultMap id="myDifEmp" type="com.meituan.mybatis.mapper.bean.Employee">
        <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="dept_name" property="dept.departmentName"/>
    </resultMap>
    <select id="getEmpAndDept" resultMap="myDifEmp">
        SELECT e.id id, e.last_name lastName, e.email email, e.gender gender, e.d_id d_id, d.id did , d.dept_name dept_name from employee e, department d 
        WHERE e.d_id = d.id AND e.id=#{id}
    </select>
  • The second way to write resultmap
<resultMap id="myDifEmp2" type="com.meituan.mybatis.mapper.bean.Employee">
        <id column="id" property="id" />
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        <!--
        Association can specify the federated JavaBean object
            Property = "dept" specifies which property is a federated object
            Javatype: Specifies the type of this property object
        -->

        <association property="dept" javaType="com.meituan.mybatis.mapper.bean.Department">
            <id column="did" property="id"/>
            <result column="dept_name" property="departmentName"/>
        </association>
    </resultMap>
  • Step by step query using Association
<resultMap id="myEmpByStep" type="com.meituan.mybatis.mapper.bean.Employee">
        <id column="id" property="id" />
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        <!-- Association defines the encapsulation rules of associated objects
            Select: indicates that the current attribute is the result of calling the method specified in select
            Column: specifies which column value to pass to this method
        -->
        <association property="dept" select="com.meituan.mybatis.mapper.dao.DepartmentMapper.getDeptById"
            column="d_id"
        ></association>
    </resultMap>

    <select id="getEmpByIdStep" resultMap="myEmpByStep">
        SELECT * FROM employee WHERE id=#{id}
    </select>

The select part in association is

<mapper namespace="com.meituan.mybatis.mapper.dao.DepartmentMapper">
    <select id="getDeptById" resultType="com.meituan.mybatis.mapper.bean.Department">
      SELECT* FROM department WHERE id=#{id}
    </select>
    
</mapper>

3.9 delayed loading

<!-- Deferred loading can be used
    Employee===》Dept
        Each time the employee object is queried, the Department information is queried together, and the required information is:
         Check the Department information when using it
         On the basis of step-by-step query, add two configurations to realize delayed loading
-->

mybatis-config.xml

<!-- Displays the value specifying each configuration that needs to be changed, even if it is the default, in case of problems caused by version change -- >
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>

test

    @Test
    public void test07() throws Exception {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            Employee employee = sqlSession.selectOne("com.meituan.mybatis.mapper.dao.EmployeeMapperPlus.getEmpByIdStep", 1);
            System.out.println(employee.getLastName());
            System.out.println(employee.getDept().getDeptName());
        } finally {
            sqlSession.close();
        }
    }

Output:

A segment of SQL query appears in the two outputs

14:23:18.093 [main] DEBUG com.meituan.mybatis.mapper.dao.EmployeeMapperPlus.getEmpByIdStep - ==>  Preparing: SELECT * FROM employee WHERE id=? 
14:23:18.133 [main] DEBUG com.meituan.mybatis.mapper.dao.EmployeeMapperPlus.getEmpByIdStep - ==> Parameters: 1(Integer)
14:23:18.227 [main] DEBUG com.meituan.mybatis.mapper.dao.EmployeeMapperPlus.getEmpByIdStep - <==      Total: 1
tom
14:23:18.228 [main] DEBUG com.meituan.mybatis.mapper.dao.DepartmentMapper.getDeptById - ==>  Preparing: SELECT* FROM department WHERE id=? 
14:23:18.228 [main] DEBUG com.meituan.mybatis.mapper.dao.DepartmentMapper.getDeptById - ==> Parameters: 2(Integer)
14:23:18.269 [main] DEBUG com.meituan.mybatis.mapper.dao.DepartmentMapper.getDeptById - <==      Total: 1
Sales Department

3.10 Association set encapsulation rules defined by collection

  • How to nest result sets
<!-- Scene 2
    When querying a department, you can also query all the employee information corresponding to the Department
-->
    <resultMap id="MyDept" type="com.meituan.mybatis.mapper.bean.Department">
        <id column="did" property="id" />
        <result column="dept_name" property="deptName"/>
        <!-- Collection defines the encapsulation rules for attributes associated with collection types -- >
        <collection property="emps" ofType="com.meituan.mybatis.mapper.bean.Employee">
            <!-- Define the encapsulation rules for the elements in this book -- >
            <id column="eid" property="id"/>
            <result column="last_name" property="lastName"/>
            <result column="email" property="email"/>
            <result column="gender" property="gender"/>
        </collection>
    </resultMap>
    <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 department d 
        LEFT JOIN employee e 
        ON d.id=e.d_id
        WHERE d.id=#{id}
    </select>
  • Step by step method
    <resultMap id="MyDeptStep" type="com.meituan.mybatis.mapper.bean.Department">
        <id column="id" property="id"/>
        <result column="departmentName" property="deptName"/>
        <collection property="emps" select="com.meituan.mybatis.mapper.dao.EmployeeMapper.getEmpByDeptId"
            column="id"
        ></collection>
    </resultMap>
    <select id="getDeptByIdStep" resultMap="MyDeptStep">
        SELECT id,dept_name departmentName FROM department WHERE id=#{id}
    </select>
  • Extension:

    Multi column transmission value

    Encapsulated as map transfer

    ​ column=”{key1=val1, key2=val2}”

    Fetchtype = “lazy”, indicates that delayed loading is used

    -Lazy: delay loading
    • Eager: load now

3.11 descriptor discriminator

<discriminator javaType="int" column="draft">
  <case value="1" resultType="DraftPost"/>
</discriminator>

Sometimes a single database query may return a result set of many different (but hopefully related) data types. The discriminator element is designed to handle this situation, including the inheritance hierarchy of classes. The discriminator is very easy to understand because it behaves like a switch statement in the Java language.

The definition discriminator specifies the column and javatype properties. Column is where mybatis looks up the comparison value. Javatype is the appropriate type that needs to be used to ensure equivalence testing (although strings can be useful in many cases). For example:

<resultMap id="vehicleResult" type="Vehicle">
  <id property="id" column="id" />
  <result property="vin" column="vin"/>
  <result property="year" column="year"/>
  <result property="make" column="make"/>
  <result property="model" column="model"/>
  <result property="color" column="color"/>
  <discriminator javaType="int" column="vehicle_type">
    <case value="1" resultMap="carResult"/>
    <case value="2" resultMap="truckResult"/>
    <case value="3" resultMap="vanResult"/>
    <case value="4" resultMap="suvResult"/>
  </discriminator>
</resultMap>

4. Dynamic SQL

One of the powerful features of mybatis is its dynamic SQL. Mybatis supports ognl expressions.

4.1 if where trim

<!--
        Query employee, demand: the value of the entire field is the query criteria of which field is carried
    -->
    <select id="getEmpsByConditionIf" resultType="com.meituan.mybatis.dynamic.bean.Employee">
        SELECT * FROM employee WHERE
        <!--
          Test: judgment expression (ognl)
          c: if test
         Judge from parameters
        You should write escape characters when you meet special symbols
        &&Need to be written as & amp;
        "Need to be written as & quot;
        -->
        <if test="id != null">
            id=#{id}
        </if>
        <if test="lastName != null and lastName!=''">
            AND last_name LIKE #{lastName}
        </if>
        <if test="email!=null and email.trim()!=&quot;&quot;">
            AND email=#{email}
        </if>
        <if test="gender==0 or gender == 1">
            AND gender=#{gender}
        </if>
    </select>

When querying, some conditions (such as ID) are missing, which may cause problems in SQL assembly

Solution:

1、WHERE 1=1

WHERE 1=1 
<if test = "">
    AND ***
</if>

2. Scheme recommended by mybatis:<where>Tag, including all query conditions, mybatis will remove the SQL assembled in the where tag and the extra and or;whereIt will only remove the extra and or in front

<where>
    <!--
              Test: judgment expression (ognl)
              c: if test
             Judge from parameters
            You should write escape characters when you meet special symbols
            &&Need to be written as & amp;
            "Need to be written as & quot;
            -->
    <if test="id != null">
        id=#{id}
    </if>
    <if test="lastName != null and lastName!=''">
        AND last_name LIKE #{lastName}
    </if>
    <if test="email!=null and email.trim()!=&quot;&quot;">
        AND email=#{email}
    </if>
    <if test="gender==0 or gender == 1">
        AND gender=#{gender}
    </if>
</where>

3. Use of trim Tags

<select id="getEmpsByConditionTrim" resultType="com.meituan.mybatis.dynamic.bean.Employee">
        SELECT * FROM employee
        <!---
         Prefix = "" prefix: the result of concatenation of the whole string is in the trim label body. Prefix will add a prefix to the whole string after concatenation
         Prefixoverrides = "" prefix overrides: remove redundant characters in front of the entire string
         Suffix = "" suffix: suffix is added to the whole string
         Suffixoverrides = "" remove the characters after the entire string. ""
        -->
        <trim prefix="where" suffixOverrides="and">
            <if test="id != null">
                id=#{id} AND
            </if>
            <if test="lastName != null and lastName!=''">
                 last_name LIKE #{lastName} AND
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                email=#{email} AND
            </if>
            <if test="gender==0 or gender == 1">
                gender=#{gender}
            </if>
        </trim>
    </select>

4.2 choose set tag

Choose branch selection is equivalent to switch case with break

<!-- 
        If you bring ID, use ID; if you bring LastName, use LastName
    -->
    <select id="getEmpsByConditionChoose" resultType="com.meituan.mybatis.dynamic.bean.Employee">
        SELECT * FROM employee
        <where>
<!--             If you bring ID, use ID; if you bring LastName, use LastName-->
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="lastName!=null">
                    last_name LIKE #{lastName}
                </when>
                <when test="email!=null">
                    email=#{email}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

The set tag is used for update to remove the extra “,”

4.3 foreach

<select id="getEmpsByConditionForeach" resultType="com.meituan.mybatis.dynamic.bean.Employee">
      SELECT * FROM employee WHERE id IN
        <!--
            Collection: Specifies the collection to traverse
                List type parameters are specially processed and encapsulated in a map. The key of the map is called list
            Item: assign the traversed element to the specified variable
                #{variable name} can get the value of the variable, that is, the element currently traversed
            Separator: separator between each element
            Open: concatenate a starting character from all the traversal results
            Close: all the traversal results are spliced with an ending character
            Index: index. The index is used when traversing the list
                        When traversing a map, index represents the key of the map, and item is the value of the map
        -->
        <foreach collection="ids" item="item_id" separator="," open="(" close=")">
            #{item_id}
        </foreach>

    </select>

Batch insert:

    <insert id="addEmpsByConditionForeach">
        INSERT INTO employee (last_name, email, gender, d_id) VALUES
        <foreach collection="list" item="emp" separator=",">
            (#{emp.lastName}, #{emp.email}, #{emp.gender}, #{emp.dept.id})
        </foreach>
    </insert>

Note: list type parameters will be specially processed and encapsulated in the map. The key of the map is called list. If you want to customize the parameter name passed in at this time, you can

1、@Param(“*“)

2. Pass the list into your own map

4.4 built in parameters of mybatis

Mybatis has two internal cause parameters by default

1、 _ Parameter: represents the entire parameter

Single parameter:_ Parameter is this parameter

Multiple parameters: the parameters will be encapsulated into a map_ Parameter represents the map

2、 _ Databaseid: if the databaseidprovider tag is configured,

​ _ Databaseid is the alias representing the current database

4.5 bind

Bind can bind the value of the ognl expression to a variable for later reference, for example:

<bind name="_lastName" value="'%'+lastName+'%'">

4.6 SQL extract reusable fragments

Defining reusable fragments using SQL Tags

<sql id="insertColumn">
    employee_id, last_name, email
</sql>

Use the include tag to reference reusable fragments

<include refid"insertColumn"></include>

5. Caching mechanism

Caching can greatly improve query efficiency. Mybatis defines a two-level cache by default:

  • By default, only the first level cache (sqlsession level cache, also known as local cache) is on.
  • L2 cache needs to be manually enabled and configured. It is a namespace level cache, also known as global cache.
  • In order to improve scalability, mybatis defines the cache interface cache. You can customize the L2 cache by implementing the cache interface

5.1 L1 cache

The data queried during the same session with the database will be placed in the local cache. In the future, if you need to obtain the same data, you can get it directly from the cache. There is no need to query the database. Mybatis enables L1 cache by default.

L1 cache invalidation

  • Sqlsession is different
  • The sqlsession is the same, but the query conditions are different (this data does not exist in the current L1 cache)
  • Sqlsessions are the same, and additions, deletions and modifications are performed during the two queries (which may invalidate the current data)
  • Manually emptied the cachesqlSession.clearCache();

5.2 L2 cache

Based on the cache at the namespace level, a namespace corresponds to a L2 cache

Working mechanism:

1. For a session, query an ovry, and the data will be placed in the first level cache of the current session

2. If the current session is closed, the data in the L1 cache will be saved to the L2 cache; For new session query information, you can refer to the L2 cache

3. The data found in different namespaces will be placed in their corresponding cache (map)

4. The detected data will be placed in the L1 cache by default. Only after the session is submitted or closed, the data in the L1 cache will be transferred to the L2 cache

use:

1. Enable global L2 cache configuration,<setting name="cacheEnabled" value="true"/>

2. Configure in a namespace

<cache  eviction="FIFO" flushInterval="60000"  size="512" readOnly="true"/>
  • eviction

    1. LRU – least recently used: removes objects that have not been used for the longest time.

    2. FIFO – first in first out: remove objects in the order they enter the cache.

    3. Soft – soft reference: removes objects based on garbage collector status and soft reference rules.

    4. Weak – weak references: more actively remove objects based on garbage collector status and weak reference rules.

    Default LRU

  • Flush interval: cache refresh interval

    How often is the cache emptied? It is not emptied by default. The unit is milliseconds

  • Readonly: read only

    True: read only. Mybatis believes that all operations to obtain data from the cache are read-only operations and will not modify the data. In order to speed up the retrieval speed, mybatis will directly give the reference of data in the cache to the user. Features: unsafe, fast

    False: not read-only. Mybatis believes that the acquired data may be modified. Mybatis will clone a new data to users by using serialization and deserialization technology. Features: safe, slow

  • Size: how many elements are cached
  • Type: specify the full class name of the custom cache and implement the cache interface

3. POJO needs to implement a serialization interface

5.3 cache related settings and properties

1、<setting name="cacheEnabled" value="true"/>Turn cache on or off

2. Each select tag has a usecache attribute. True means used and false means not used (L1 cache is still used and L2 cache is not used)

3. The flushcache attribute value of each addition, deletion and modification tag is true, that is, after the addition, deletion and modification are completed, the cache should be cleared, including level 1 and level 2 caches. The query tag defaults to flushcache = “false”. When it is set to true, the cache will be emptied before each query, and the cache is not used

4、sqlSession.clearCache();Just clear the L1 cache

5. Localcachescope: local cache scope (L1 cache). Session and state can be selected. Statement can disable caching

5.4 cache principle

Mybatis study notes

Recommended Today

Swift advanced (XV) extension

The extension in swift is somewhat similar to the category in OC Extension can beenumeration、structural morphology、class、agreementAdd new features□ you can add methods, calculation attributes, subscripts, (convenient) initializers, nested types, protocols, etc What extensions can’t do:□ original functions cannot be overwritten□ you cannot add storage attributes or add attribute observers to existing attributes□ cannot add parent […]