MyBatis study notes

1, Introduction to MyBatis

MyBatis is a semi automated persistence layer framework.

  • MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping.
  • MyBatis avoids almost all JDBC code and manually setting parameters and getting result sets.
  • MyBatis can use xml or annotations to configure and map the interface and java POJO s into database records.

2, MyBatis, JDBC and Hibernate


  • sql is sandwiched in java code blocks with high coupling.
  • Maintenance is not easy. sql changes frequently and needs ordinary modification


  • It is difficult to handle complex sql, and Hibernate is not easy to handle;
  • sql is automatically generated internally, which is not easy to optimize;
  • The fully automatic framework based on full mapping is difficult to do partial mapping, which leads to the decline of database performance.

Mybatis is a semi-automatic framework that separates sql from java coding, focusing on business and data.

3, MyBatis profile

  1. Global configuration file: mybatis-config.xml

    • Some global settings to guide mybatis to run correctly
    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
            PUBLIC "-// Config 3.0//EN"
            and Spring of context,property-placeholder Similarly, they all refer to external configuration files
            resource:Reference from the classpath
            url: A resource that references a disk path or a network path
    <!--    <properties url=""></properties>-->
        <properties resource=""></properties>
    <!--    setting yes MyBatis A very important adjustment setting in, in which the properties change MyBatis Runtime behavior of-->
    <!--        name:Of configuration items key;
                value: Value of configuration item
                mapUnderscoreToCamelCase:Turn on Automatic Hump naming mapping (if any) javabean The attributes of and the fields of the database meet the hump nomenclature)
            <setting name="mapUnderscoreToCamelCase" value="true"/>
    <!--    typeAliases(Type alias),Aliasing common types-->
    <!--        typeAlias:For one javaBean Alias. By default, the alias is the class name (case insensitive)-->
    <!--        alias:Specify an alias-->
    <!--        <typeAlias type="com.bean.Employee" alias="emp"></typeAlias>-->
    <!--       Batch alias ,The default alias is the class name-->
            <package name="com.bean"/>
    <!--        Generally, the full class name is used, which can be locked quickly-->
    <!--    The plug-in is mybatis Powerful functions of-->
    <!--    -->
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driverClassName}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.username}"/>
                    <property name="password" value="${jdbc.password}"/>
    <!--    Introduce the implementation file of each interface written by yourself-->
    <!--    Written sql The mapping file needs to be used mappers Register in-->
            resource:Find under classpath sql Mapping file
            url: Reference from disk or network path
            class: The full class name of the direct reference interface
                    1.Can xml Put on dao The interface is in the same directory, and the file name is consistent with the interface name
                    2.Use with annotation
    <!--        <mapper class="com.dao.EmployeeDao"></mapper>-->
    <!--        <mapper url=""></mapper>-->
    <!--        <mapper resource="com/dao/EmployeeDao.xml"/>-->
    <!--        <mapper class="com.dao.EmployeeDaoAnnotation"></mapper>-->
    <!--        Batch registration-->
            <package name="com.dao"/>

    Note: when using mappers for batch registration, you need to put xxxDao.xml and dao interface under the same package with the same name.

  2. SQL mapping file: xxxDao.xml

    • It is equivalent to an implementation description of Dao interface.
    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-// Mapper 3.0//EN"
        namespace:Namespace, write the full class name of the interface, which is equivalent to telling mybatis Which interface does this configuration file implement
    <mapper namespace="com.dao.EmployeeDao">
    <!--    Used to define a query operation
            id:Method name, which is equivalent to that the configuration is for the implementation of a method
            resultType:Specify the return value type after the method is run (the query operation must be specified)
        <select id="getEmpById" resultType="com.bean.Employee">
        select * from t_employee where id = #{id}
        <select id="getEmpByName" resultType="com.bean.Employee">
            select * from t_employee where empName=#{name}
    <!--    There is no need to write the return value type for addition, deletion and modification. The number of rows affected by addition, deletion and modification is returned
            MyBatis Automatic judgment, if it is a number( int.long)
            If it is Boolean(Affect automatic encapsulation of 0 rows false,otherwise true)
            #{property name}: get the value of the corresponding property from the passed in parameter object
        <update id="updateEmployee" >
            update t_employee
            set empname=#{empName},gender=#{gender},email=#{email}
            where id=#{id}
        <insert id="insertEmployee">
            insert into t_employee(empname,gender,email)
            values (#{empName},#{gender},#{email})
        <delete id="deleteEmployee">
            delete from t_employee
            where id=#{id}

4, plugins plug-in

Plug in is a very powerful mechanism provided by MyBatis. We can modify some core behaviors of MyBatis through plug-ins. Plug ins can intervene in the execution of any method of the four objects through dynamic proxy mechanism.

5, Use of annotations

MyBatis supports annotations. You can directly annotate the methods of dao interface without writing configuration files.

Example code:

public interface EmployeeDaoAnnotation {
    @Select("select * from t_employee where id = #{id}")
    public Employee getEmpById(Integer id);
    @Select("select * from t_employee where empName=#{name}")
    public Employee getEmpByName(String name);

    @Select("update t_employee\n" +
            "        set empname=#{empName},gender=#{gender},email=#{email}\n" +
            "        where id=#{id}")
    public int updateEmployee(Employee employee);

    @Select("delete from t_employee\n" +
            "        where id=#{id}")
    public Boolean deleteEmployee(Integer id);

    @Select("insert into t_employee(empname,gender,email)\n" +
            "        values (#{empName},#{gender},#{email})")
    public int insertEmployee(Employee employee);

The mybatis global configuration file declares the following:

        resource:Find under classpath sql Mapping file
        url: Reference from disk or network path
        class: The full class name of the direct reference interface
                1.Can xml Put on dao The interface is in the same directory, and the file name is consistent with the interface name
                2.Use with annotation
<!--        <mapper class="com.dao.EmployeeDao"></mapper>-->
<!--        <mapper url=""></mapper>-->
        <mapper resource="EmployeeDao.xml"/>
        <mapper class="com.dao.EmployeeDaoAnnotation"></mapper>

Note: important dao can write configuration, and simple dao can use annotation

6, Simple use

First, introduce the global configuration file mybatis-config.xml, and then create a SqlSessionFactory to produce SqlSession. Obtain the implementation of dao interface through SqlSession, and then operate the database.

public void test1() throws IOException {
    //Create an sqlSessionFactory according to the global configuration file
    //SqlSessionFactory: sqlSession factory, which is responsible for creating sqlSession objects
    //sqlSession: sql session (representing a session with the database)
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    SqlSession sqlSession=null;
    Employee emp=null;
    try {
        //Get a session with the database: getConnection()
        sqlSession = sqlSessionFactory.openSession();

        //Use sqlSession to operate the database and obtain the implementation of dao interface
        EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
        emp = employeeDao.getEmpById(1);
    } finally {

7, Various values of parameters

  1. Single parameter (basic data type)

    Value: #{write freely}

  2. Multiple parameters:

    Value: you can use 0, 1 (parameter index) or param1, param2

    Reason: as long as multiple parameters are passed in, mybatis will automatically encapsulate these parameters in a map. The key used during encapsulation is the index of the parameters and the first representation of the parameters.

    Map<String,Object> map=new HashMap<>();
    map.put("1",Value passed in);
    map.put("2",Value passed in);
  3. @param: specify a key for the parameter and name the parameter;

    You can tell mybatis to use our specified key when encapsulating the parameter map

  4. Incoming pojo

    Value: #{pojo's attribute value}

  5. Pass in map: encapsulate multiple parameters to be used

    Value: #{key}


    public Employee getEmpByIdAndName(@Param("name") String name, Integer id);
    public Employee getEmployeeByIdAndName(Map<String,Object> map);


    <select id="getEmpByIdAndName" resultType="com.bean.Employee" >
        select * from t_employee where empName=#{name} and id=${1}

    <select id="getEmployeeByIdAndName" resultType="com.bean.Employee" >
        select * from t_employee where empName=#{name} and id=${id}

​ Test:

import com.bean.Employee;
import com.dao.EmployeeDao;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.util.HashMap;
import java.util.Map;
public class MyBatisTest {
    SqlSessionFactory sqlSessionFactory;
    public void getSqlSessionFactory() throws IOException {
        String resource="mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    public void test4(){
        SqlSession sqlSession=null;
        try {
            sqlSession = sqlSessionFactory.openSession(true);
            EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
            Employee emp = employeeDao.getEmpByIdAndName("admin", 1);
        } finally {

    public void test5(){
        SqlSession sqlSession=null;
        try {
            sqlSession = sqlSessionFactory.openSession(true);
            EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
            Map<String,Object> map=new HashMap<>();
            Employee employee = employeeDao.getEmployeeByIdAndName(map);
        } finally {

8, The difference between #{} and ${}

In mybatis, there are two values:

  • #{attribute name}: it is the method of parameter precompiling. The positions of parameters are replaced by "?. the parameters are later set by the precompiled society. It is safe and will not have sql injection problems. It is recommended
  • ${attribute name}: not parameter precompiled, but directly concatenated with sql statements; unsafe

Precompiled sql statements are supported when there are only parameter positions.

Generally, #{} is used, which is safe. If you want to take values where parameter precompiling is not supported, ${}, such as table name.

9, Joint query and distributed query

We can use association and collection tags to map complex objects (the properties of objects contain objects or collections).

<!--Customize the encapsulation rules and encapsulate the results of the joint query using cascading attributes-->
    <resultMap id="myResult" type="com.bean.Key">
        <id property="id" column="id"></id>
        <result property="keyName" column="key_name"></result>
        <result property="" column="lid"></result>
        <result property="lock.LockName" column="lock_name"></result>

Use the association tag:

<!--    use mybatis Recommended association label-->
    <resultMap id="myResult" type="com.bean.Key">
        <id property="id" column="id"></id>
        <result property="keyName" column="key_name"></result>
<!--        javaType:Specify the type of this property-->
        <association property="lock" javaType="com.bean.Lock">
            <id property="id" column="lid"></id>
            <id property="LockName" column="lock_name"></id>

Solution to field loss: instead of directly using select *, write out each field and alias it to correspond to the property name of javaBean.

10, Dynamic sql

if tag

The where tag works with the if tag to dynamically query.

The where tag can help us remove redundant and in sql statements, but and should be written in front of each sql statement.

test in the if tag writes judgment statements, and some symbols need to use escape characters.

<select id="getStudentByCondition" resultMap="studentMap">
    select * from student
        <if test="id!=null and !id.equals(&quot;&quot;)">
        <if test="name!=null">
           and student_name like #{name}
        <if test="english!=null and english>=0 and english &lt;=100">
            and english &lt; #{english};

forEach label

        collection:Specifies the name of the collection to traverse key
        open: Start with what
        close: End with what
        separator: What is the separator
        item: Give each traversed element a variable name
    <select id="getStudentByCollection" resultMap="studentMap">

        select * from student where id in
        <foreach collection="ids" open="(" close=")" item="id" separator=",">

choose tag

<!--    choose Tags, when entering any one when When the tag is, it will not enter other tags-->
    <select id="getStudentByConditionChoose" resultMap="studentMap">
        select * from student
                <when test="id!=null and !id.equals(&quot;&quot;)">
                <when test="name!=null and !name.equals(&quot;&quot;)">
                    student_name like #{name}
                <when test="chinese">

set tag

<!--    Test dynamic update,set Labels can remove excess,-->
    <update id="updateStudent">
        update student
            <if test="name!=null and !name.equals(&quot;&quot;)">
                student_name = #{name},
            <if test="chinese!=null and chinese>=0 and chinese &lt;=100">
                chinese = #{chinese},
            <if test="math!=null and math>=0 and math &lt;=100">
                math = #{math},
            <if test="english!=null and english>=0 and english &lt;=100">
                english = #{english}

11, Caching mechanism

Introduction to MyBatis cache

MaBatis caching mechanism: Map, which can save some queried data.

  • L1 cache: thread level cache, local cache and sqlSession level cache. Different sqlsessions use different L1 caches.
  • L2 cache: a global cache that can be called in addition to the current thread and sqlSession.

Mechanism: as long as the data has been queried before, mybatis will save it in a cache (Map) and get it directly from the cache next time. However, if other additions, deletions and modifications are performed between them, mybatis will empty the first level cache.

L1 cache invalidation

  1. Different sqlsessions use different L1 caches.
  2. If the same method has different parameters, it may not have been queried before, and the sql will be reissued
  3. If any addition, deletion or modification is performed during this sqlSession, the L1 cache of this sqlSession will be emptied.
  4. Manually empty the cache.

L2 cache

L2 cache: global cache, namespace level cache.

After the sqlSession is closed or committed, the L1 cache will be placed in the L2 cache. mybatis is not used by default.

L2 cache usage:

  1. Enable L2 cache for global configuration

    <!--        Turn on the global cache switch-->
            <setting name="cacheEnabled" value="true"></setting>
  2. Configure a dao.xml file to use L2 cache

    <!--    Use L2 cache-->
  3. Let the corresponding javaBean realize serialization, that is, realize the Serializable interface.

Cached query data

  1. There will not be the same data in the L1 cache and L2 cache
    • When the L1 cache is closed, there will be L2 cache
    • If there is no data in the L2 cache, the L1 cache will be viewed. If there is no data in the L1 cache, the database will be queried. The query results of the database will be placed in the L1 cache.
  2. At any time, first look at the L2 cache and then the L1 cache. If there is no cache, you will query the database.

Cache related settings

12, Consolidate third-party cache

Integrate Ehcache

  1. Guide Package


  2. Ehcache must have a configuration file called ehcache.xml, which is placed in the root directory of the classpath

  3. Configure the use of custom caching in mapper.xml

    <cache type="org.mybatis.caches.ehcache.EhcacheCache"></cache>
  4. If other Daos use this cache

    <cache-ref namespace="com.dao.StudentDao"/>

Tags: Java Mybatis SQL SSM

Posted on Tue, 28 Sep 2021 13:55:09 -0400 by robmarston