Summary of Mybatis

1. What is mybatis

Mybatis is an excellent persistence layer framework that supports custom sql, stored procedures, and advanced mapping. Its prototype is ibatis, which was included by the Apache Foundation and renamed mybatis

2. What can mybatis do?

Mybatis can configure and map primitive types using simple XML or annotations, interfaces and java entity classes as records in the database

3. Why use mybatis

- Reduce the amount of code
- Be based on sql Sentences, easy to learn
- Understand the underlying assembly process (relative to hibernate)
- SQL Statements are encapsulated in a configuration file to facilitate unified maintenance and management (and notes can replace configuration files, of course), reducing program coupling
- Easy program debugging

4. Use of mybatis

4.1 First you have to have a library with tables

[External chain picture transfer failed, source may have anti-theft chain mechanism, it is recommended to save the picture and upload it directly (img-9bnPFHPK-160110611) (C:\UsersJAVASMAppDataRoamingTyporatypora-user-images0.png)]

Here I first used navicat to create some pre-equipped data for testing

4.2 Create a new java project

[External chain picture transfer failed, source station may have anti-theft chain mechanism, it is recommended to save the picture and upload it directly (img-lcwVEp9W-160110632) (C:UsersJAVASMAppDataRoamingTyporatypora-user-imagespng)]

I'm using maven directly here, so it's easy to download dependencies

[External chain picture transfer failed, source station may have anti-theft chain mechanism, it is recommended to save the picture and upload it directly (img-Hn3Rv9rF-16330610633) (C:UsersJAVASMAppDataRoamingTyporatypora-user-images92.png)]

Use lombok to facilitate testing by modifying fields, repeating the getset method of entity classes, and toString

4.3 Write entity classes corresponding to tables in the database

package com.demo.entity;
@Data
public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
}

4.4 Write an XML configuration file

[External chain picture transfer failed, source station may have anti-theft chain mechanism, it is recommended to save the picture and upload it directly (img-esE2ES0C-16010610635) (C:UsersJAVASMAppDataRoamingTyporatypora-user-imagespng)]

This is an introduction to the official documentation, which is sufficient in terms of configuration files

[External chain picture transfer failed, source station may have anti-theft chain mechanism, it is recommended to save the picture and upload it directly (img-4E2GLKhL-16330610635) (C:UsersJAVASMAppDataRoamingTyporatypora-user-images6.png)]

The order of configurations must be written strictly according to the specifications of mybatis, otherwise they will be compiled incorrectly

	----mybatis-config.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>

    <properties resource="db.properties"/>

<!--    Environment variable groups can specify environment variables-->
    <environments default="development">
<!--        ID by development environment variable-->
        <environment id="development">
<!--            Transaction management type-->
            <transactionManager type="JDBC"/>
<!--            data source   type Value POOLED UNPOOLED Pools and non-pools-->
            <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>
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>
</configuration>

4.5 Write a mapping file (UserMapper.xml)

----UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xxx">
    <select id="findAll" resultType="com.demo.entity.User">
        SELECT * FROM `mydata`.user;
    </select>
</mapper>

4.6 Write test classes for testing

package com.demo;

public class Demo1 {

    public static void main(String[] args) throws IOException, SQLException {
//        Get File Stream
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//        Get sqlSessionFactory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//
        SqlSession sqlSession = factory.openSession();
        List<User> list = sqlSession.selectList("xxx.findAll");
        System.out.println(list);
    }
}

The results of the query are as follows:

[User(id=1, username=Sheng Shiming, birthday=null, sex=2, address=null), User(id=10, username=Zhang Jiaji, birthday=Thu Jul 10 00:00:00 CST 2014, sex=1, address=Beijing), User(id=16, username=Zhang Jidong, birthday=null, sex=1, address=Zhengzhou, Henan), User(id=22, username=Sun Xun, birthday=null, sex=1, address=Zhengzhou, Henan), User(id=24, username=Zhou Zhiruo, birthday=null, sex=1, address=Zhengzhou, Henan), User(id=25, username=Zhao Min, birthday=null, sex=1, address=Zhengzhou, Henan), User(id=26, username=Xiao Zhao, birthday=null, sex=null, address=null)]

And our mybatis works

5.Dynamic proxy interface for use

In the above test, we just tested whether mybatis is working properly, and in formal use, our mapping file for mybatis corresponds to a Dao layer interface, creating a dynamic proxy object for it to use. The proxy interface creates a proxy object with the following points to note:

• The mapper's namespace is the fully qualified class name (including the package path and class name) for the corresponding interface

• The ID of mapper's SQL statement label, etc., needs to be the same as the method name of the interface

• ResultType of mapper's return value type or ResultType in ResultMap is the same as the return value of the interface or the return value of the interface is the same as the generic in the collection

  Create an interface

  package com.demo.dao;
  
  public interface UserDao {
      List<User> findAll();
  }
  
  

  Modify map file mapper

  ----UserMapper.xml
  <?xml version="1.0" encoding="UTF-8" ?>
  
  <!DOCTYPE mapper
          PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="UserMapper">
      <select id="findAll" resultType="com.demo.entity.User">
          SELECT * FROM `mydata`.user;
      </select>
  </mapper>
  
  

  Modify the test class for testing

  package com.demo;
  
  public class Demo1 {
  
      public static void main(String[] args) throws IOException, SQLException {
  //        Get File Stream
          InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
  //        Get sqlSessionFactory
          SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
  //
          SqlSession sqlSession = factory.openSession();
          //Get Dynamic Proxy Object
          UserDao mapper = sqlSession.getMapper(UserDao.class);
          List<User> all = mapper.findAll();
          System.out.println(all);
      }
  }
  
  

  [User(id=1, username=Sheng Shiming, birthday=null, sex=2, address=null), User(id=10, username=Zhang Jiaji, birthday=Thu Jul 10 00:00:00 CST 2014, sex=1, address=Beijing), User(id=16, username=Zhang Jidong, birthday=null, sex=1, address=Zhengzhou, Henan), User(id=22, username=Sun Xun, birthday=null, sex=1, address=Zhengzhou, Henan), User(id=24, username=Zhou Zhiruo, birthday=null, sex=1, address=Zhengzhou, Henan), User(id=25, username=Zhao Min, birthday=null, sex=1, address=Zhengzhou, Henan), User(id=26, username=Xiao Zhao, birthday=null, sex=null, address=null)]
  
  Process finished with exit code 0
  
  
  

  You can also get results, and the dynamic proxy succeeds at this point

6. Use of parameters

There are several ways to use parameters, which are discussed in two main categories. ParameterTypes written in mapping files can be omitted from writing

6.1 Single parameter

For a single parameter, if the parameter is a base type or string, we can use ${parameter name} or #{parameter name} to receive the value of the parameter in the SQL statement without any comment.

#{} represents a placeholder and is better than SQL performance and security (SQL injection issues), so it is used in most cases

${} represents the splicing of strings, which is rarely used

Write an example

    <select id="findOne" parameterType="int" resultType="com.demo.entity.User">
        SELECT * FROM `mydata`.user where id = ${id}
    </select>

#UserDao

public interface UserDao {
    List<User> findAll();
    User findOne(Integer id);
}

#Test

UserDao mapper = sqlSession.getMapper(UserDao.class);
User one = mapper.findOne(10);
System.out.println(one);
//User(id=10, username=Zhang Jiaji, birthday=Thu Jul 10 00:00 CST 2014, sex=1, address=Beijing)

Normal use

6.2 Reception of multiple parameters

There are several ways to receive multiple parameters

• Encapsulated entity classes can be passed in directly using the attribute values in the entity class, but only one entity class must be passed in

          User user = new User();
          user.setUsername("Zhang Jiaji");
          user.setId(10);
          User one = mapper.findOneByUser(user);
          System.out.println(one);
  
  

  ​

      <select id="findOneByUser" parameterType="com.demo.entity.User" resultType="com.demo.entity.User">
          SELECT * FROM `mydata`.user where `username` = #{username} and `id` = #{id}
      </select>
  
  

• Use Map to pass in multiple parameters

  ​

          Map<String,Object> map = new HashMap<>();
          map.put("username","Zhang Jiaji");
          map.put("id",10);
          User one = mapper.findOneByMap(map);
          System.out.println(one);
  
  

  	<select id="findOneByMap" resultType="com.demo.entity.User">
          SELECT * FROM `mydata`.user where `username` = #{username} and `id` = #{id}
      </select>
  
  

• Use the comment @Param ("xxx") modifier parameter to receive in a mapping file SQL statement using #{xxx} or ${xxx}

  ​

  	#Dao
      public interface UserDao {
          User findOneByParameters(@Param("id") Integer id,@Param("usr") String username);
      }
  
  	#Test
  
         User one = mapper.findOneByParameters(10,"Zhang Jiaji");
         System.out.println(one);
  
  

      <select id="findOneByParameters" resultType="com.demo.entity.User">
          SELECT * FROM `mydata`.user where `username` = #{usr} and `id` = #{id}
      </select>
  
  

• Use #{param1},#{param2},#{param3} or #{arg0},#{arg1},#{arg2} in the mapping file to receive in order, note that param starts from 1 and Arg starts from 0. However, neither method is recommended because the semantics are not clear enough to be confused, and later maintenance modifications are prone to ambiguity.

  It is worth noting that after using the @param annotation, arg will not work and param will continue to work

  ​

  	#Dao
      public interface UserDao {
          User findOneByParameters(Integer id, String username);
      }
  
  

      <select id="findOneByParameters" resultType="com.demo.entity.User">
          SELECT * FROM `mydata`.user where `username` = #{param2} and `id` = #{arg0}
      </select>
  
  

• If multiple encapsulation classes are passed in, you can use "." to get their properties

  	#Dao
      public interface UserDao {
          User findOneByTest(User user,Map map);
      }
      #Test
          User user = new User();
          user.setUsername("Zhang Jiaji");
          user.setId(10);
          Map<String,Object> map = new HashMap<>();
          map.put("username","Zhang Jiaji");
          map.put("id",10);
          User one = mapper.findOneByTest(user,map);
          System.out.println(one);
  
  

      <select id="findOneByTest" resultType="com.demo.entity.User">
          SELECT * FROM `mydata`.user where `username` = #{param2.username} and `id` = #{arg0.id}
      </select>
  
  

7. Multi-table Query

ReultMap is used for multi-table queries

Where the labels in resultMap are equal

PS: One of the important things to note is that the order of the labels will be wrong if the order is disrupted. The order can also be viewed on the official website, as follows

[External chain picture transfer failed, source station may have anti-theft chain mechanism, it is recommended to save and upload pictures directly (img-6PQjAkNU-16330610637) (C:UsersJAVASMAppDataRoamingTyporatypora-user-images7.png)]

   <resultMap id="user" type="com.demo.entity.User" autoMapping="true">
        <id property="id" column="id"/>
        <result property="birthday" column="birthday"/>
        <association property="demo1" javaType="com.demo.Demo1" fetchType="lazy"/>
        <collection property="demo1List" ofType="com.demo.Demo1"/>
    </resultMap>

You can use extends inheritance in the resultMap tag, inheriting resultMap from another XML configuration to write the namespace of the other mapper.resultMapID

AutoMapping can also be used in the resultMap tag to turn on automatic mapping.The same column and attribute names of the underlying type allow you to stop writing results. This automatic mapping can actually be turned on in the configuration file, and then you need to assign False to AutoMapping in the resultMap separately if you don't want to turn it on. It is worth noting that opening AutoMapping in the resultMap, also known as the parent tag, does not affect the child tags.If a subtag needs to be turned on to specify ture in the tag's attribute AutoMapping, the same applies when the global default is turned on and off

The association s and collection s tags can use the select tag to specify the subquery, and the column name passing of the subquery uses the colunm attribute tag to turn on lazy loading, while the two reference types in the corresponding entity class are javaType and ofType.

The discriminator tag is currently used sparingly and there is not much discussion

​