MyBatis learning summary

Posted by mikegzarejoyce on Mon, 20 Sep 2021 00:02:09 +0200

0. Introduction

        This article is used to record my MyBatis learning content. Only some knowledge points that I think are commonly used are compiled. The reference materials are derived from the following:
       MyBatis official documents
       C language Chinese network compilation materials
       Station B crazy God says Java - MyBatis Related videos
        There may be some incomprehensible places. Don't spray if you don't like it.

1. What is MyBatis:

  • MyBatis is an open source, lightweight data persistence framework and an alternative to JDBC and hibernate.
  • MyBatis encapsulates JDBC internally, which simplifies the complicated process of loading drivers, creating links and creating statements. Developers only need to pay attention to the SQL statement itself.
  • Data persistence refers to the general term of converting the data model in memory into the storage model and converting the storage model into the data model in memory. My understanding is that the process of mapping the information in the entity class to the database and storing the information in the database in the form of entity class is called data persistence.
  • The technology to realize data persistence: ORM (Object Relational Mapping), which establishes the corresponding relationship between the object model and the relational database, and provides a mechanism to operate the data in the database through JavaBean objects.


  • SQL is written in XML, which is separated from the program logic code, reduces the coupling degree, facilitates unified management and optimization, and improves the reusability of the code;
  • Provide XML tags to support writing dynamic SQL statements;
  • Provide mapping labels to support the mapping between objects and ORM fields in the database;

2. MyBatis Download: Maven project import package





3. How to use mybatis:

  • To create a sample database:

    Create Table `website`(
    	`id` int(11) not null auto_increment,
        `name` varchar(20) not null,
        `url` varchar(30) default '',
        `age` tinyint(3) unsigned not null,
        `country` char(3) not null dafault '',
        `createTime` timestamp null default null,
        primary key(`id`)
    )engine=InnoDB default charset=utf8 collate=utf8_unicode_ci;
  • Create the corresponding entity class (also known as "persistent class"):

    public class Website{
        private int id;
        private String name;
        private String url;
        private int age;
        private String country;
        private Date createTime;
        //The Date here is the Date in the tool class provided by java, not the Date under the sql package
        //Try to keep the property name consistent with the field name in the database, otherwise some exceptions will occur
        //Omit getter and setter methods, omit all parameter construction and no parameter construction
        //The override of toString method is omitted here
  • Create the Dao layer operation interface and the related mapping file WebsiteDaoMapper.xml, as follows:

    public interface WebsiteDao{
        //WebsiteDao is written here temporarily for easy understanding. It can be changed to WebsiteMapper later
        //Add a new record to the database
        public int addWebsite(Website website);
        //Query all website information
        public List<Website> selectAllWebsite();
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper
    PUBLIC "-// Mapper 3.0//EN"
    <mapper namespace="">
    	<!--there xxx It means you wrote it WebsiteDao Package name-->
        <insert id="addWebsite" paramterType="">				insert into website(name,url,age,country,createTime)
        <!--insert Is an add tag that represents the sql The statement function is to add records-->
        <!--id It refers to the method name in the corresponding interface-->
        <!--#{} is a fixed format. Get the parameter in paramterType -- > in MyBatis
        <select id="selectAllWebsite" resultType="">
        	select * from website
        <!---resultType It refers to the return form of the result->
  • Create a configuration file and bind mapper: (mybatis-confg.xml)

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-// Config 3.0//EN"
        <!--Here is the configuration MyBatis Your log can be ignored-->
            <setting name="logImpl" value="LOG4J" />
        <environments default="development">
        	<!--You can set multiple environment,By changing default To set-->
            <transactionManager type="JDBC"/>
            <!--Set up transaction management-->
            <dataSource type="POLLED">
                <!--Set the data source, or set the connection pool-->
            	<property name="driver" value="......" />
                <property name="url" value="......" />
                <property name="username" value="......" />
                <property name="password" valye="......" />
                <!--Set the information required to connect to the database-->
        	<mapper resource="xxx/xxx/xxx/WebsiteDaoMapper.xml" />
            <!--Finish binding-->
  • Write test code:

    public class Test{
        public static void main(String[] args){
            InputStream config = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(config);
            SqlSession session = ssf.openSession();
            //This part of the code is highly reusable. It is recommended to write it with tool classes. At the same time, remember to close the resources after each test
            WebsiteDao websiteDao = session.getMapper(WebsiteDao.class);
            Website website = new Website("xxx","xxx",1,"xxx",new Date(/*Method of obtaining system time*/));
            List<Website> websiteList = websiteDao.selectAllWebsite();
            //Here, you can view the results of database operations through logs
  • The basic process is shown in the figure above. The role of MyBatis in this case is reflected in simplifying the preparation of SQL execution. If you use conventional JDBC, you need to connect to the database and create redundant code such as statement. MyBatis is used to make the code work pay more attention to the SQL statement itself through the configuration file, and it is more convenient to use.

  • Possible problems:

    • database configuration is written incorrectly
    • In MyBatis configuration file, the related package name is abnormal
    • The database connection needs to set the time zone
    • Static resource filtering (xml resources in non resource packages need to be filtered into tar projects)
    • In ResultType, if the property field of the set class name is inconsistent with the field name of the database, it cannot be injected

4. MyBatis core object: core interface and class, MyBatis core configuration file, SQL mapping file

5. Core interfaces and classes: SqlSessionFactoryBuilder, SqlSessionFactory, SqlSession;

  • Each MyBatis application takes an instance of a SqlSessionFactory object as the core. First, obtain the SqlSessionFactoryBuilder object. You can build the object according to the XML Configuration file or the instance of Configuration class, then obtain the SqlSessionFactory object through the build() method, and finally obtain the target SqlSession through the openSession() method of SqlSessionFactory.

    InputStream config = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(config);
    SqlSession session = ssf.openSession();
  • SqlSessionFactoryBuilder: this class is used to create SqlSessionFactory. Once SqlSessionFactory is created, this class has no value and should be destroyed. Therefore, the best scope of SqlSessionFactoryBuilder is the method body, which can be destroyed when used up. It can be realized through tool classes;

  • SqlSessionFactory: once created, SqlSessionFactory will exist in the whole life cycle of the program, that is, it will always exist as long as the program does not end. So what we need to do is not to create SqlSessionFactory repeatedly, so the best implementation is the singleton mode;

  • SqlSession: SqlSession instances cannot be shared and are not thread safe, so its best scope is in the method body, or in case of a request, that is, method or request, that is, when an Http request is received, you should create a SqlSession instance, then close it at the end and call the close() method;

    public MyBatisUtil{
        private volatile static SqlSessionFactory sqlSessionFactory;
        private MyBatisUtil(){}
        public static SqlSessionFactory getSqlSessionFactory(){
                        InputStream config = Resources.getResourceAsStream("mybatis-config.xml");
    					sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
            return sqlSessionFactory;
    SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
        //Related operations
    //try is not used here because there are exceptions to catch, but to use finally to ensure that sqlSession will be closed after use

6. MyBatis core profile:

  • properties tag:

    • Import external configuration files and generally set the relevant configuration of the database;

      <!--The file name is,Used to set database Connection related parameters-->
      <!--stay mybatis_config Files can be used directly ${}Reference in the form of-->
      <properties resource="" />
    • Properties sub element configuration: set the relevant contents of the database through the properties sub element property, and then refer to it in the following environments. In fact, it is basically the same as the above usage;

          <property name="username" value="......" />
          <property name="password" value="......" />
      <environment id="default">
      	<transactionManager type="JDBC" />
          <dataSource type="POOLED">
          	<property name="driver" value=${driver}/>
    • If there is the same variable name, properties takes precedence over the values of externally introduced variables, that is, internally defined external variables with the same name will be overwritten;

    • The difference between {} and ${}:

      • ${} is string replacement, that is, it will be directly replaced with the value of the target# {} is a precompiled process, which will add a double quotation mark "";
      • In MyBatis, when processing #{}, it will be replaced with, Use the set() method of PreparedStatement to assign values to prevent SQL injection; And deal with Time be yes straight meet for change become change amount of value , as fruit stay S Q L in send use {} is directly replaced with the value of the variable if used in SQL If {} is used in SQL, there is a risk of SQL injection.
  • typeAliases label: set type aliases

    • Class alias is set to reduce the redundant writing of fully qualified (including package name) class names;

      	<typeAliases alias="Author" type="" />
    • You can also specify a package name. MyBatis will search for the required JavaBeans under the package name. Compared with the above method, this method is more labor-saving if all JavaBeans are written in the same directory;

      	<package name="" />

      For each JavaBean in the package pojo, if there is no annotation, the initial lowercase unqualified class name of the Bean will be used as the alias. If there is annotation, the annotation shall prevail;

      public class Author{
  • settings tab: it is a very important adjustment setting in MyBatis. They will change the runtime behavior of MyBatis.

    Possible attributes:

    attributedescribeEffective valueDefault value
    cacheEnabledGlobally turn on or off any cache configured in all mapper configuration files, that is, set the switch of L2 cachetrue|falsetrue
    lazyLoadingEnabledGlobal switch for delayed loading (unclear function)true|falsefalse
    logImplSpecify the specific implementation of the log used by MyBatis. If it is not specified, it will be found automatically. If it cannot be found, it will not be setSLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGINGNot set

    How to set log:

    • Common logs are LOG4J and STDOUT_LOGGING. If the standard output log is used, no additional settings are required. If LOG4J is used, some additional operations are required;

    • Import dependency:

    • Configure log4j log settings through configuration files

      # Global log configuration
      # Output the log information with the level of ERROR to stdout. Stdout is defined below. You can also choose to output it to a file here
      log4j.rootLogger=ERROR, stdout
      # MyBatis log configuration
      # console output 
      log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
      # In order to achieve finer grained log output, you can also print only the logs of specific statements. The following configuration will print only the log of the statement selectBlog:
      # Alternatively, you can print a group of mapper logs. Just turn on the log function of the package where the mapper is located:
      # Set the output level of the log
  • mappers label:

    • Bind xml file of related Mapper

      	<mapper resource="xxx/xxx/xxx/WebsiteDapMapper.xml" />
    • Directly bind class files:

      	<mapper class="" />
    • Register all the mapper interface implementations in the package as mappers, that is, automatic scanning:

      	<package name="" />
    • Try to ensure that the interface and mapper configuration file names are consistent and under the same package

7. SQL mapping file

  • select tag: represents the query statement, as follows

    <select id="Method name" paramterType="Parameter type" resultType="Return type">
    	<!--Suppose the parameter here is int Type, variable name id,Then there-->
        select * from xxx where id = #{id}

    This statement represents all the information of the record whose corresponding ID is the parameter ID transmitted from xxx database. Here #{id} is to tell MyBatis to create a PreparedStatement, and then use "?" to replace the original one. Set the parameters through the set() method to prevent SQL injection;

    When setting the return property, you can set resultType and resultMap, but only one of them can be selected. resultMap is often used in multi table queries;

    When the resultType is set, MyBatis will automatically create a ResultMap behind the scenes, and then map the column to the JavaBean attribute according to the attribute name. Therefore, it is best to keep the attribute name of the entity class consistent with the field name of the database. If it is inconsistent, you can set the alias through select to complete the matching;

    The resultMap is used as follows:

    <select id="Method name" paramterType="Parameter type" resultMap="resultMap identification">
    	select * from xxx where id = #{id}
    <resultMap id="resultMap identification" Type="Return type, generally JavaBean Type of">
    	<result property="id" column="user_id" />
        <result property="username" column="user_name" />
        <result property="password" column="user_password" />

    For more complex queries, you can use resultMap to map query results one by one;

    If you need to pass multiple parameters, you can use Map to receive them, or use the annotation * * @ Param("...") * *. In this way, you can write SQL statements directly without selecting the parameter receiving attribute in the xml file;

  • insert tag, update tag and delete tag: add, modify and delete tags. The common attribute is id;

  • The sql tag can be used to define reusable sql fragments. It is generally not used very much and can be incomplete sql code

  • KaTeX parse error: Expected 'EOF', got '#' at position 4: {} and# ̲ Application of {}: #{} can be used to prevent... {} also plays a certain role in SQL.

    • For example, when we need to use the order by clause, we can set the SQL statement to "ORDER BY ${columName}", so that we can directly insert a non escaped string into the SQL;

    • For example, when we need to search for records according to some field names in the database, we can pass in the field name and target value to achieve our purpose without repeatedly writing multiple SQL. The specific code is as follows:

      @Select("select * from user where ${columnName} = #{value}")
      public List<User> getUserListBy
          		(@Param("columnName") String columnName,@Param("value") String value);
      //In this way, the field name and field value are injected at the same time to reduce duplicate code
      //Here we use annotations to implement Mapper

8. Use of collection and association attributes in resultmap

  • Why use association and collection?

    For example, classes customized in the Java system can be injected through the result attribute, while classes defined by ourselves can only be set by using the association attribute;

  • Association is mainly used to solve one-to-one association query, that is, like binding a student number to each student. Here, the student number and student are used as examples. The code is as follows:

    create table `student`(
    	`id` ........................ ,
        `name` ........................ ,
        `sex` ........................ ,
        `cardId` ........................ ,
    create teble `studentcard`(
    	`id` ........................ ,
        `studentId` ........................ ,
        `startDate` ........................,
        `endDate` ........................ ,
    public class Student{
        private int id;
        private String name;
        private int sex;
        private StudentCard studentCard;
    public class StudentCard{
        private int id;
        private int studentId;
        private Date startDate;
        private Date endDate;

    Queries for students include:

    <mapper namespace="">
    	<select id = "selectStudentById"
                resultMap = "Student">
        	select student.*,studentcard.* from student,studentcard 
            where = #{id} and = studentId
        <resultMap id="Student" type="">
            <id property="id" column="" />
            <result property="name" column="name" />
            <result property="sex" column="sex" />
            <association property="studentCard" javaType="">
                <id property="id" column="" />
                <result property="studentId" column="studentId" />
                <result property="startDate" column="startDate" />
                <result property="endDate" column="endDate" />

    The javaType here represents the class to which the association is mapped. This is a one-to-one association query. By writing SQL in this way, you can obtain all the required values in one query, which is convenient for understanding. There is another writing method, but I can't understand it, so I write it in this way;

  • Collection is mainly used to solve one to many association queries, that is, if an attribute in a user-defined class is a collection, it needs to be solved by collection. Take users and orders as examples, and the code is as follows;

    create table `order`(
    	`id` ........................ ,
        `ordernum` ........................ ,
        `userId` ........................ ,
    create table `user`(
        `id` ........................ ,
        `name` ........................ ,
        `pwd` ........................ ,
    public class User{
        private int id;
        private String name;
        private String pwd;
        private List<Order> orderList;
    public class Order{
        private int id;
        private int ordernum;

    Queries for users include:

    <mapper namespace="">
    	<select id="selectUserById" paramterType="int"
        	select user.*,,order.ordernum from user,order 
            where = #{id} and = #{id}
        <resultMap id="User" type="">
        	<id property="id" column="" />
            <result property="name" column="name" />
            <result property="pwd" column="pwd" />
            <collection property="orderList" ofType="">		
            	<id property="id" column="" />
                <result property="ordernum" column="ordernum" />

    The ofType here refers to the object type to be stored in the collection, and the SQL here actually has a problem, but I'm too lazy to change it. Through this way of querying all the information needed for play at one time, the problem of one to many association query can be completed concisely and clearly.

9. Common simple annotations and annotations for implementing SQL

  • @Insert: add
  • @Select: implement query
  • @Uodate: implementation update
  • @Delete: enables deletion
  • @Param: map multiple parameters (i.e., for passing parameters)

10. Writing dynamic SQL: complete the implementation of dynamic SQL using the nesting of relevant tags

  • if

    <if test="Judgment conditions">
    	SQL sentence
    <!--sql Statement only if the judgment condition is true Will be executed-->
    <select id="......" resultMap="......">
    	select xxx,xxx,xxx from xxx
        <if test="name!=null">
            <!--there name As a judgment condition, it is not necessary to add#{}-->
        	where name like #{name}
  • choose(when, otherwise): equivalent to switch in Java

    	<when test="Judgment condition 1">
        	SQL sentence
        <when test="Judgment condition 2">
        	SQL sentence
        <when test="Judgment condition 3">
        	SQL sentence
        	SQL sentence
    <!--In here, sql Execution will only follow one of these situations. Once matching is completed, execution is completed SQL After, it will jump out choose-->
    <select id="xxx" parameterType="xxx" resultMap="xxx">
    	select xxx,xxx,xxx from xxx where 1=1
        	<when test="name!=null and name!=``">
            	and name like concat(`%`,#{name},`%`)
            <when test="url!=null and url!=``">
            	and url like concat(`%`,#{url},`%`)
            	and age is not null
    <!--here test The logic inside passes and To connect. If it is a relationship of or, use or To connect-->
  • trim(where, set): in the above example, if there is no pre judgment condition of "1 = 1", there will be "where and". This is obviously wrong. We can avoid this error by setting the where tag;

    <select id="xxx" paramterType="xxx" resultMap="xxx">
    	select xxx,xxx,xxx from xxx 
        	<if test="name!=null and name!=``">
            	and name like concat(`%`,#{name},`%`)
            <if test="url!=null and url!=``">
            	and url like concat(`%`,#{url},`%`)

    In this way, the above problems can be solved. The where tag will automatically add where when generating the PreparedStatement, and judge at the same time. If the following condition is the first, or or or and will be automatically omitted. The set tag is designed to deal with the situation of update and eliminate the redundant commas appended to the end of the condition. An example is as follows:

    <update id="xxx" paramterTyp="xxx">
    	<!--Conventional update Statement update method-->
        <!--update xxx set xxx=#{xxx},xxx=#{xxx},xxx=#{xxx} where id=#{id}-->
        <!--Used set Label status-->
        update xxx
        	<if test="name!=null">
            <if test="url!=null">
        <!--set The label will be filled automatically set Keywords. If there are multiple keywords at the same time update The field will also automatically fill the gap","-->
        where id=#{id}

    Both the set tag and the where tag are just an application of the trim tag. You can customize your own special tags by using the trim tag. The format of the trim tag is as follows:

    <trim prefix="prefix" suffix="suffix" prefixOverrides="Ignore prefix characters" suffixOverrides="Ignore suffix characters">
    	SQL sentence
    prefix:to SQL Statement concatenates the previous prefix
    suffix:to SQL Statement splice last suffix
    prefixOverrides:remove SQL Keyword or character before statement
    suffixOverrides:remove SQL Keyword or character after statement
  • Foreach: the foreach tag is used to traverse the collection (especially when constructing IN conditional statements), such as:

    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
    	<!--The value of the object passed from the collection should be written here-->
        <!--General biography int perhaps String Type, so direct item That's it-->
    	item:Used to represent elements in the current traversal order
    	index:Represents the current traversal position
    	list:A collection of externally provided traversals
    	open:Indicates what symbol this part of the statement starts with after it is compiled
    	separator:Indicates what division is used between traversed elements after this part of the statement is compiled
    	close:Indicates what symbol this part of the statement ends with after being compiled
    Therefore, the general collocation IN Conditional statement use

11. MyBatis cache

  • What is caching

    • Temporary data in memory
    • Put the data frequently queried by users in the cache. When users query the data, they do not need to query from the disk (relational database data file), but from the cache, so as to improve the query efficiency.
    • It can reduce the number of interactions with the database, reduce the system overhead and improve the system efficiency
    • MyBatis system defines two-level Cache by default, one level Cache and two level Cache. By default, only the L1 Cache is enabled (SqlSession level, also known as local Cache). The L2 Cache needs to be manually enabled and configured. It is a namespace level Cache. At the same time, in order to improve scalability, MyBatis defines a Cache interface. We can customize the L2 Cache by implementing the Cache interface.
    • Cache purge policy:
      • LRU - least use principle, default
      • FIFO - first in first out principle
  • L1 cache

  • L2 cache: to enable L2 cache, in addition to enabling global cache in settings, you also need to add the following code to the SQL mapping file:

           flushInterval="60000"	<!--Refresh interval-->
           size="512"				<!--Maximum number of references-->

    The functions are as follows:

    • The results of all select statements in the mapping statement file will be cached
    • All insert, update and delete statements in the mapping statement file refresh the cache
    • Use LRU purge policy
    • The cache is not refreshed regularly
    • Without qualification, the cache holds 1024 references to a list or object
    • The cache is treated as a read / write cache, which means that the obtained object is not shared and can be safely modified by the caller

    A session queries a piece of data, which will be placed in the first level cache of the current session; If the current session is closed and the L2 cache is enabled in the configured corresponding mapper, the data will be transferred to the L2 cache, and the new session query information can get the content from the L2 cache. When using L2 cache, errors may be reported because there is no serialization. Just pay attention to implementing the serialization interface.

  • Cache query principle

    • First query whether there are cache records in the L2 cache
    • If there are no cache records in the L2 cache, query from the L1 cache
    • If the L1 cache cannot be found, a query request is sent to the database
  • Ehcache custom cache

    If you want to use custom cache, you need to first introduce dependencies, that is, Ehcache packages, and then add them to the configuration file:

    <cache type="org.mybatis.cache.ehcache.EhcacheCache" />

    Then configure the custom xml configuration file, ehcache;

    If you want to implement a custom Cache, you can implement the Cache interface through a custom class, and then change the field in the type to the address of the class you define.

  • Redis can also be used for caching, which is not introduced here. (it seems that the official development of redis is often used, but MyBatis is not used very much)

Topics: Java Mybatis