Mybatis Basics

mybatis summary:

  1. The most basic version of mybatis is a dao layer framework based on the configuration file, which realizes the operation of the database by configuring in the configuration file

    1. Configure mybatis-config.xml: log class used.
    2. Then configure the environment. You need to configure the transaction manager and data source.
    3. The last is the scanning of configuration mapping files, the most basic resource scanning, followed by class scanning, and finally package scanning.
    4. Because many classes involved in the mapping file need to be encapsulated, and it is troublesome to write the full class name when repacking all classes, mybatis supports type distinguished names
    5. For writing resource files, there are four most important things
      1. namespace: when the corresponding interface is not configured, it can be used to give different namespaces to different functions operating the database.
      2. Method name id
      3. Splitting of parameters
      4. Encapsulation of results. ResultMapper
    6. Call to database:
      1. Read in total configuration file
      2. Create a dialog factory for sql according to the configuration file
      3. With factories, create sqlsessions
      4. Then, according to the sqlSession call method, query and modify are realized through selectXXX and updataXXX(). The parameters are the id coordinates defined in the resource file, including the naming and corresponding incoming parameters.
      5. Submit changes.

    Based on the above basic operations, some problems are derived:

    1. The method passed in or returned each time is complex and unfriendly

      Solution: alias TypeAlias

      • At the lowest level, configure all aliases in mybatis-config.xml. Note: scanning packages is case insensitive
      • You can override the previous method by aliasing @ Alias annotation on class objects
      • Applicable to all profiles
    2. Complex transfer parameters:

      • The original methods of transferring parameters are: (parameters can be determined automatically without writing)
        • Basic data type
        • Pass a pair phenomenon and unpack it directly with its attributes in sql statements
        • Pass multiple parameters and encapsulate the parameters into a HashMap. Map<string,object>
      • Now: use the interface class instead of the original xml configuration
        • Core idea: set the interface, and then let mybatis automatically inject query methods. The xml file is used to inject methods, and the interface provides method calls.
        • Premise:
          • The namespace of xml is similar to the package name.
          • The method name and id are similar
          • The base type of the return value is the same. If it is a list, it does not affect.
          • ResultMapper needs to re encapsulate the data. If the attribute names are exactly the same, the default wrapper can be used.
        • Parameters:
          • In addition to the basic, there is no need to encapsulate multiple parameters. Start with paramk (k > = 1) and argx (x > = 0)
          • @Param("name") renames the parameter to facilitate later Mina's reference
    3. How to avoid method injection of mapper file:

      Solution: annotation injection method

      • @Select
      • @Update
      • @Delete
      • @Insert
    4. Auto generate primary key

      The useGeneratedKeys parameter in the Mybatis configuration file only takes effect for the insert statement. The default value is false. When set to true, it means that if the inserted table takes the self incrementing column as the primary key, JDBC is allowed to support the automatic generation of the primary key and return the automatically generated primary key.

      <insert id="insert" useGeneratedKeys="true" keyProperty="id">
          insert into flower(name,price) values(#{flowerName},#{flowerPrice})

      Or it can be queried twice: < selectkey > tag

      <insert id="insert">
          <selectKey order="AFTER" keyProperty="id" resultType="int">
              select @@identity;
          insert into flower(name,price) values(#{flowerName},#{flowerPrice})
      after Is the query after insertion id Return to entity class
  2. The select statement is too long or requires conditional judgment

    Solution: the OGNL tag generates sql statements dynamically.

  3. multi-table query

    • It is divided into joint query and embedded query

      • Joint query, sql statement multi table query, and the contents of the query are embedded in a resultMapper

      • Embedded query. Specify another query and the conjunction column in the query. Because in the field design of general attributes, each class will have a connection attribute, which does not exist in the database, and a layer of query needs to be embedded.

        <select id="selectAll"  resultMap="rm2">
            select  * from class;
        <resultMap id="rm2" type="class">
            <id property="classno" column="classno"/>
            <result property="className" column="className"/>
            <collection property="studentList" column="classno" ofType="stu" select="com.test.mapper.StuDao.selectByClassNo" fetchType="lazy"/>
            <select id="selectByClassNo" resultType="stu">
                select <include refid="stu"/> from stu
                    classno = #{param1}
        collection Can be reconfigured to realize mapping.
        javaType The return value after thorough encapsulation is equivalent to secondary encapsulation.
        column Connection properties
    • Annotation driven

    • @Result Implement result set encapsulation
      @Results It can be used with @ Result to encapsulate multiple Result sets
      @ResultMap Implement the encapsulation defined by referencing @ Results
      • @Result replaces ResultMapper, which is essentially the same thing
      • @Result replaces the pairing of all attributes
      • id represents the primary key
      • One to one query
      • many1 to many query
      @Results(id = "rm9",value ={
              @Result(id = true, property = "sid", column = "sid"),
              @Result(property = "sname",column = "sname"),
              @Result(property = "sex", column = "sex"),
              @Result(property = "classno",column = "classno"),
              @Result(property = "aClass", javaType = Class.class,column = "classno", one=@One(select = "com.test.mapper.ClassDao2.selectOne",fetchType = FetchType.LAZY))
      })//Only special mapping is required
      @Select("select sid, sname, sex, classno from stu")
      List<Student> selectAll1();
      @Select("select * from class where classno = #{param1}")
      public Class selectOne1();
    • Considerations for one to many queries

      • javaType="list" actual return type
      • ofType: child element type. All names are either full class names or aliases
      <collection property="studentList" column="classno" javaType="list" ofType="stu" select="com.test.mapper.StuDao.selectByClassNo" fetchType="lazy"/>

1. Parameters of configuration file


package scan

<!--		<mapper resource="com/test/mapper/StudentMapper.xml"/>-->
<!--		<mapper class="com.test.mapper.Student2"/>-->
		<package name="com.test.mapper"/>

working principle

  • Find all the interfaces first
  • Then root out the interface name and find the corresponding xml
  • Therefore, the interface name must be consistent with xml.
    • If it's not package scanning, it's not necessary to be consistent. Just need the domain name to be consistent with the fully qualified name of the interface.

Scan xml

Find the corresponding interface according to the domain name. Therefore, the domain name and interface should be consistent.

2. Mapper profile and global profile


It can be written or not, and the input parameters will be automatically recognized. To write, you need to write right, which can be omitted

Three methods of parameter transmission

  • Incoming normal data type
  • Incoming object
  • Pass in HashMap through #{keyword};

The number of propagation parameters can only be one.


It can be omitted when it is int.


If the attributes of the class do not correspond to the fields of the table, we need to construct an attribute mapper, which is used by ResultMapper.

  • Identifier of the id type mapper
  • type maps to a class.
  • < ID > and < reuslt > primary key tags and element tags.
  • collection is the mapping label of the class object.

In the select tag, the result is automatically mapped to the mapper.

Cannot automatically correspond to Null

Solution to automatic mapping

  1. Alias the table queried by mysql
  2. Manually map ResultType

3.1 mapper

You can construct an interface with the same name as the id of the xml class, the same type of input parameters, the same type of return value (either a single entity or an array), and the same name of the function. The class generated by the xml file automatically inherits the interface function. You can then call the statement by calling the interface function.


  1. Normal insertion

  2. When there is a self incrementing primary key, you can add it normally, or declare to mybatis who is the self incrementing primary key. Mybatis will automatically call jdbc.getGeneratedKeys() to obtain the self incrementing number of the primary key, and then assign it to the current object stuId and add data.

stuId is automatically assigned.

2.sql statement defines the statement blocks that are reused < include / >

<sql id="userColumns"> id,username,password</sql>
<select id="selectUser" parameterType="int" resultType="hashmap">
select <include refid="userColumns"/> 
    from userinfo where id = #{id};

#And $

#{} represents a placeholder, which is automatically filled by preparedStatement by calling setXXX().

${} is equivalent to splicing. It will automatically take out data and splice strings. Generally, quotation marks are required.

3. Log file log4j

The file configuration class is generally placed under src, which is the source directory. It can be called java or src.

log level

Logs have multiple levels. Low level can output high-level questions.

For example, the above debug can output all types of information, while error can only output error and fatal information.

Global and local levels

  • log4j.rootLogger is global level
  • The following two are local level debug ging. They write the namespace, which is the namespace of our mapper class configuration file. All methods under this namespace automatically adjust the level.
  • Namespaces can be defined at will.

Why is it a namespace for xml.

The domain name of xml is the name of xml. If you use the mapper interface proxy.

The domain name cannot be called casually. It can be regarded as the implementation class of the interface, so the domain name needs to be the same as the name of the interface.


Attribute value:

<typeAliase type="" alias="">

  • type is the full qualification of the original class
  • Alias alias name

Alias multiple classes

Import packages directly.

  • <package name="">
  • alias does not need to be written. alias is the class name and is not case sensitive.

Basic type

The base type itself has been aliased. So you can use it directly. The first letters are lowercase.

If there is a list, write the full path.


The configuration in typealias will be automatically overwritten and invalidated.

3.2 properties file

dtd specifies the emission sequence of labels.

Rearrange the label according to the error prompt.

Then mybatis.xml refers to resources and the path of the file through the properties tag.

The log type can be specified or not specified. If not specified, he will find it himself.

4. Basic operation - add, delete, modify and query

Three types:

  1. Selectlist ("fully qualified ID").

  2. selectOne("")

  3. The return value is map. Selectmap ("which field is ID", "key")

Advantages: it is convenient to find the corresponding value. If you use the primary key as the key, you can quickly find the content of the corresponding primary key

1. Fuzzy query

  • Note that the string #{} will not be parsed and will be treated as a character.
  • Pass in the spliced parameter "% baidu%" or pass in the spliced concat('%',param, '%').

2. Addition, deletion and modification

Modify manual submission.

  • sqlSession.close()
  • Or we can pass in true when constructing openSession, openSession (true)

3. Automatically obtain the self incremented value of the primary key

You can use useGeneralKey to automatically call the primary key auto increment function and assign it to the inserted object. Then we insert it into the object to have a primary key.

Get the primary key for the object.

​ There are two schemes in total. The first scheme is more secure. In essence, it is safe to query after insertion in multithreading.

​ When multiple people operate together, the obtained primary key values may be disordered, so the second scheme is not necessarily correct.

5.Mapper advanced agent

mapper proxy is an interface binding.

  • Keep the domain name consistent with the class name
  • The id needs to be consistent with the method name

The return value types can be inconsistent to some extent, including parameter types.

Pass in multiple parameters

​ In xml files, parameters can be identified automatically without writing. If you write input parameters, you can only write one. Therefore, we need to wrap the output parameters, which can be wrapped with hashMap.

​ If we use mapper's proxy interface, we can design many parameters in the proxy interface, and then pass in multiple parameters. At this time, there is no need to write parameters in xml.

How do the parameters in the xml and mapper interfaces pair

  1. paramx is used in xml to refer to the parameters in the mapper interface method. x represents the position. It starts from 1.
  2. You can also use argx to represent. x starts at 0.

  1. The annotation tells xml the parameter name corresponding to each parameter in xml‘
  2. If you pass in a class, it will be unpacked automatically and the properties will be corresponding. If you pass in multiple classes, you need to specify the properties of which class with arg or param or annotation.

4. Dynamic sql

All tags are used to construct legal sql statements, which are mainly used to dynamically generate characters.

1.if tag

  • test is the judgment condition, which says OGNL
    <select id="selectMore" resultMap="flowerInfo">
        select * from flower where 1=1
--         OGNL expression
        <if test="param1!=null and param1!=''">
            and name=#{param1}
        <if test="param2!=null and param2!=0">
            and price=#{param2}

2.where label

Function, automatically add the where keyword. Automatically remove the first and.

    <select id="selectMore" resultMap="flowerInfo">
        select * from flower 
--         OGNL expression
            <if test="param1!=null and param1!=''">
                and name=#{param1}
            <if test="param2!=null and param2!=0">
                and price=#{param2}

3.choose label

Select one of several cases, similar to if else

    <select id="selectMore2" resultMap="flowerInfo">
        select * from flower
                <when test="param1!=null and param1=''">
                <!--If it is established, it will not continue to be implemented-->
                <when test="param2!=0">
                    and price=#{param2}
                    1 = 1

4.set label

It can only be used in modification.

It will automatically add the set keyword and delete the last comma.

update flower
    	<if test="param1!=null">
        	name = #{param1},
        <!--Automatically removed comma-->
        <if test="param2!=0">
    where id = #{param3};

5.trim label

Infix treatment

  • Prefix add prefix
  • prefixOverrides removes the prefix, if present
  • Suffix add suffix
  • suffixOverrides removes suffixes, if present
  • The function coincides with where and set
update flower
    <!--add prefix set,Remove possible suffixes,-->
    <trim prefix="set" suffixOverrides=",">
    	<if test="param1!=null">
        	name = #{param1},
        <!--Automatically removed comma-->
        <if test="param2!=0">
    where id = #{param3};


If the content of how to delete is an array or collection, it is difficult to write sql statements because the elements in the array or collection are irregular.

The collection needs to be traversed, and forEach turns the elements of the collection into a character.

When multiple values are actually uploaded, mybatis is automatically encapsulated into a map. The keyword of list is list and the keyword of array is array, so we use variables through keywords.

	delete from flower where id in
   <!-- collection Collection type list perhaps array item Points to each element in the collection-->
    <foreach collection="list" item="i" open="("  close=")", separator=",">


A tool for splicing characters to form a new string. Its function is similar to concat.

The writing of value is the addition of characters.

<select id="selectMore4" resultType="flowerInfo">
    select * from flower
        <if test="param1!=null">
            <!--New variable name and value, OGNL Language characters need to be used''Identify-->
        	<bind name="pa" value="'%'+param1+'%'"/>
            name like #{pa}

8.sql tag

The efficiency of checking the * sign in the select statement is relatively low.

It is used to specify common code fragments, which are referenced through include

<include refid="sqa"/>

sql statements can define code blocks

<sql id="sqa">

<select id="" resultType="flowerInfo">
	select <include refid="sqa"/> from flower

5. Multi table query

N+1 mode

​ For the foreign keys in the database, we use a class reference member in the java class instead of the foreign key connected table to store the results of two joint searches.

Association one-to-one association

Set the correlation function on the manual mapper.

  • select which sql statement you want to execute. You can directly use namespace+id to locate the query statement.
  • Column as a parameter (that is, the connected column)
  • javaType return value type
  • Property to which property the result is saved.

The ResultMap is mapped manually. When a corresponding mapped data cannot be found, a query statement is activated again.

    <resultMap id="rm2" type="stu">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="sex" column="sex"/>
        <result property="classno" column="classno"/>
<!--        aclass The type of the property is class,Associated statement selectOne(Can be sql A statement can also refer to a query block) column Is a connection field-->
        <association property="aClass" javaType="class" column="classno" select="com.test.mapper.ClassDao.selectOne"/>

    <select id="selectAll" resultMap="rm2">
        select <include refid="stu"/> from stu;

Association supports multi-level association

  • It not only supports the return results of correlation functions
  • You can also associate a manual mapper.
  • Secondary associations can be written as nested

Write separately

<resultMap type="Card" id="cardMap">
      <id property="cardId" 
      <result property="cardNum" 
      <result property="address" 

<resultMap type="User" id="userMap">
     <result property="userName" 
     <result property="age" 
     <association property="card" 

Nested type

<resultMap type="User" id="userMap">
   <result property="userName" 
   <result property="age" 
      <id property="cardId" 
      <result property="cardNum"
      <result property="address" 
  • column: when the associated statement needs multiple parameters, we use map to encapsulate multiple parameters.

    		<collection property="htAuthorityDTO"  ofType="com.sailod.shiro.dto.HtAuthorityDTO"
    		 select="selectAuthority" column="{htAuthorityId2 = htAuthorityId ,currentUserId2 = currentUserId}"   >
  • javaType: Specifies the return type java.util.ArrayList

  • The types contained in the ofType collection.

collection: one to many Association

It corresponds to a set, and the set corresponds to student

<resultMap id="BaseResultMap" type="" >
		<id column="id" 		 property="id"      jdbcType="VARCHAR" />
		<result column="content" property="content" jdbcType="VARCHAR" />
		<result column="type"    property="type"    jdbcType="VARCHAR" />
		<result column="sort"    property="sort"    jdbcType="INTEGER" />
		<collection property="options" javaType="java.util.ArrayList" ofType="">
			<id column="o_id" property="id" jdbcType="VARCHAR" />
			<result column="o_content" property="content" jdbcType="VARCHAR" />
			<result column="o_sort" property="sort" jdbcType="INTEGER" />
		<!-- Alias of the column o_id,o_content,o_sort , The alias is used because the primary and child tables have these fields
			 Here to write ofType, javaType You can still not write -->

Multi table query statement

If the search content is a double table connection, the public fields can be omitted. The search results of the two tables can be divided into the main class and the additional class according to the direct relationship between the two classes. The overlapping fields of the main class are not repeated, the additional class will automatically become a list, and none of the receiving fields can be used

<resultMap id="rm3" type="class">
	<id property="classno" column="classno"/>
    <result property="className" column="className"/>
    <collection property="list" ofType="stu">
    	<id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="sex" column="sex"/>
        <result property="classno" column="classno"/>


  1. Business code, implemented through java code
  2. N+1, implemented with association and collection sets, multiple queries
  3. Multi table query SQL. (ResultMap can be implemented through annotation.)

Loading mode

There are two loading methods: delayed loading and active loading, which are used when loading attributes. They are generally used for multi table query, and some results are queried first.

Active loading

As in the above example, if we use sql Association syntax, the corresponding students will also load when selecting all classes, which is called active loading.

Deferred logging is not to load associations first.

  • Through global configuration, setting name = "lazyloading enabled" value = "true", each attribute is loaded only when it is used.
  • Turn off lazy loading for each property< setting name="aggressiveLazyLoading" value="false"/>
  • Turn on special properties for lazy loading. fetchType="lazy" using label (collection)
<setting name="lazyLoadingEnabled" value="true"/>

<setting name="aggressiveLazyLoading" value="false"/>


Lazy loading is to load the corresponding properties when using properties.


​ Get data directly from the cache to reduce the pressure on the server, especially when there are many queries. mybatis is divided into L1 cache and L2 cache.

List<Class> classes = classDao.selectAll();

The second query is read in the cache. The L1 cache is automatically enabled, which is the sqlSession cache. The cache level is at the sqlSession level.

Different sqlsessions cannot share the cache.

L2 cache

Cache: cache records.

Called sqlSessionFactory level cache, it can cache data between different sqlsessions.

Enable L2 cache

In fact, you need to start the global cache (the default is "cacheEnabled"), and then open a single file.

Add < cache > to the mapping file mapper

Relationship between caches

When querying, L2 cache -- > L1 cache -- > dB, in which the data found is saved in L1 cache first. When close() or commit, the contents of L1 cache are moved to L2 cache.

cache details

  • Enable L2 cache

    @CacheNamespace(blocking = true)
  • type custom cache class, which needs to inherit the cache class org.apache.ibatis.cache.Cache.

  • readOnly cache content is read-only.

  • Cache policy: when the cache is emptied, such as LRU, which has been used least recently.

  • Flush interval the refresh interval in milliseconds. Adding, deleting and modifying will cause the data in the cache to disappear and the cache to empty.

  • Turn off the cache of a single query statement by using the useCache tag of the statement.

Annotation substitution


Set the statement on the method

Annotations generally only use the addition, deletion, modification and query of a single table and multi condition query.




Source code interpretation

Parsing of Mybatis.XML


Read the xml file and turn it into a file input stream

2. Design mode

Factory design pattern, using factory batch to generate objects.

Build (InputStream) - > xmlconfigbuilder() parse -- > parse the root node to obtain the node object, and then parse it with reflection to obtain the Configuration class Configuration. ---- > Then use the Configuration class to construct the factory.


Use the default factory class DefaultSqlSessionFactory to create openSession(). -- > Create a defaultsqlsession -- >


Dynamic agent mode is also a design mode

Help us create implementation classes dynamically.

Call getMapper from Configration.

Generate proxy objects and help us generate implementation classes: (JDK dynamic proxy)


  • MapperMethod, which contains the extract method.

The insert update delete defined in the tag will call the corresponding update() method in sqlSession.

All queries call the selectList() method.



Posted by chedong on Wed, 24 Nov 2021 03:19:14 -0800