Mybatis

1. Introduction

1.1 what is Mybatis

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-mgxkj2dd-1624359914422) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210607142647545. PNG)]

1.2 persistence

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-quvn6qdc-1624359914426) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-20210607142708497. PNG)]

1.3 durable layer

Dao layer, Service layer, Controller layer

- Code block that completes the persistence work
- The layer boundary is very obvious

1.4 why use Mybatis

- Help programmers store data into the database
- convenient
- conventional JDBC The code is too complex, simplified and framework
- no need Mybatis Yes, it's easier to get started. There is no distinction between high and low technology
- ![image-20210607142603511](C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210607142603511.png)

2. The first Mybatis

Idea: build environment – > introduce Mybatis – > write code – > test

2.1 construction environment

New project

1. Create a normal maven
2. Delete src directory
3. Import maven dependency

    <!--Import dependency-->
    <dependencies>
        <!--mysql drive-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

2.2 create a module

• Write the core configuration file of mybatis

  <?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">
  <!--Core profile-->
  <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://mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                  <property name="username" value="root"/>
                  <property name="password" value="root"/>
              </dataSource>
          </environment>
  
      </environments>
  
  </configuration>
  

• to configure

• Write the tool class of mybatis

  public class MybatisUtils {
  
      private static SqlSessionFactory sqlSessionFactory;
  
      static {
          try {
              //Get sqlSessionFactory object
              String resource = "mybatis-config.xml";
              InputStream inputStream = Resources.getResourceAsStream(resource);
              SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
          } catch (IOException e){
              e.printStackTrace();
          }
  
  
      }
      //Now that we have SqlSessionFactory, as the name suggests, we can get an instance of SqlSession from it.
      // SqlSession provides all the methods required to execute SQL commands in the database. You can directly execute the mapped SQL statements through the SqlSession instance.
      public static SqlSession getSqlSession(){
          return sqlSessionFactory.openSession();
      }
  
  
  }
  

2.3 coding

• Entity class

  public class User  {
      private int id;
      private String name;
      private String pwd;
  
      public User() {
      }
  
      public User(int id, String name, String pwd) {
          this.id = id;
          this.name = name;
          this.pwd = pwd;
      }
  
      public int getId() {
          return id;
      }
  
      public void setId(int id) {
          this.id = id;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      public String getPwd() {
          return pwd;
      }
  
      public void setPwd(String pwd) {
          this.pwd = pwd;
      }
  
      @Override
      public String toString() {
          return "User{" +
                  "id=" + id +
                  ", name='" + name + '\'' +
                  ", pwd='" + pwd + '\'' +
                  '}';
      }
  }
  

• Dao interface

  public interface UserDao {
      List<User> getUserList();
  }
   
  

• The interface implementation class is converted from the original UserDaoImpl to a Mapper 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=Bind a corresponding Dao/Mapper Interface-->
<mapper namespace="com.hao.dao.UserDao">

    <!--select Query statement-->
    <select id="getUserList" resultType="com.hao.pojo.User">
        select * from mybatis.user;

    </select>
</mapper>

2.4 testing

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-vtoyxzyg-1624359914429) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210608103438908. PNG)]

Register mappers in the core configuration file

• junit test

      @Test
      public void test(){
  
  //        Get sqlSession object
          SqlSession sqlSession = MybatisUtils.getSqlSession();
  
  //        Method 1: getMapper
          UserDao userDao = sqlSession.getMapper(UserDao.class);
          List<User> userList = userDao.getUserList();
  
          for (User user : userList) {
              System.out.println(user);
          }
  
  
  //        Close SqlSession
          sqlSession.close();
  
      }
  

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-zeyryuue-1624359914432) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210608103815067. PNG)]

3.CRUD

3.1 namespace

The package name in the namespace should be consistent with the package name of dao/mapper interface

3.2 select

• Select query statement:
  • id: corresponds to the method name in the namespace
  • Resulttype: return value of SQL statement execution

3.3 insert

3.4 update

3.5 delete

3.6 universal map

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-x7lhbw9n-1624359914434) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210608151151667. PNG)]

3.7 thinking questions

How to write fuzzy query?

1. When executing the code, pass the wildcard%

List<User> userList = mapper.getUserLike("%Hao%");

2. Use wildcards in sql splicing!

select * from mybatis.user where name like "%"#{value}"%"

4. Configuration analysis

4.1 core configuration file

• mybatis-
• Mybatis's configuration file contains settings and attribute information that will deeply affect mybatis's behavior

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-d4gaxmre-1624359914434) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210608155345232. PNG)]

4.2 environment configuration

Mybatis can be configured to adapt to a variety of environments

However, keep in mind that although multiple environments can be configured, only one environment can be selected for each SqlSessionFactory instance

Learn to use and configure multiple operating environments

The default transaction manager of Mybatis is JDBC, and the connection pool is POOLED

4.3 properties

We can refer to the configuration file through the properties property

These properties are externally configurable and dynamically replaceable. They can be configured in a typical Java property file or passed through the child elements of the properties element.

Write a configuration file

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
username=root
password=root

Introduce in core configuration file

<properties resource="db.properties"/>

• You can import external files directly
• You can add some attribute configurations
• If the two files have the same field, the external configuration file is preferred

4.4 aliases

• Is to set a short name for the java type

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-5e0umrn3-1624359914435) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-2021060816344516. PNG)]

4.5 setting

This is a very important adjustment setting in Mybatis. They will change the runtime behavior of Mybatis

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-ij8daxvt-1624359914436) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-20210608163615295. PNG)]

4.6 others

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-6chdi4xr-1624359914436) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210608163931677. PNG)]

4.7 mappers

MapperRegistry: register and bind our Mapper file

• Mode 1

<!--every last mapper All need to be in Mybatis Register in core profile-->
<mappers>
    <mapper resource="UserMapper.xml"></mapper>
</mappers>

• Method 2 (register with class binding)

<!--every last mapper All need to be in Mybatis Register in core profile-->
<mappers>
    <mapper class="com.hao.dao.UserMapper"/>
</mappers>

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-q3kdfsrx-1624359914437) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-2021060816494487. PNG)]

• Method 3

      <!--every last mapper All need to be in Mybatis Register in core profile-->
      <mappers>
          <package name="com.hao.dao"/>
      </mappers>
  

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-ykynj38e-1624359914438) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-2021060816494487. PNG)]

4.8 life cycle

Declaration cycle and scope are crucial, and incorrect use can cause very serious concurrency problems

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-lkrqn8w2-1624359914439) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-20210608172851370. PNG)]

SqlSessionFactoryBuilder

• Once SqlSessionFactory is created, it is no longer needed
• local variable

SqlSessionFactory

• To put it bluntly, it is the database connection pool
• Once SqlSessionFactory is created, it should always exist during the operation of the application. There is no reason to discard it or recreate another instance
• Therefore, the best scope of SqlSessionFactory is the application scope
• The simplest is to use singleton mode or static singleton mode

SqlSession

• A request to connect to the connection pool

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-cg4oh8y6-1624359914439) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210608173514877. PNG)]

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-oafwez2t-1624359914440) (C: \ users \ administrator \ appdata \ roaming \ typora \ typera user images \ image-20210608173954700. PNG)] each mapper in this represents a specific business

5. Solve the problem of inconsistency between attribute name and field name

Fields in the database

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-h5oof7pb-1624359914441) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210609093332186. PNG)]

Create a new project to test the inconsistency of entity class fields

• Change the original pwd to password

public class User {
    private int id;
    private String name;
    private String password;
}

• Query the user according to the id, and there is a problem

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-nchmob5g-1624359914441) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210609095322908. PNG)]

resolvent

• Alias

      <select id="getUserById" parameterType="int" resultType="com.hao.pojo.User">
          select id,name,pwd as password from mybatis.user where id = #{id}
      </select>
  

• Result set mapping

  	<resultMap id="UserMap" type="User">
          <!--column Is a field in the database, properties Is the property name in the entity class-->
          <result column="id" property="id"/>
          <result column="name" property="name"/>
          <result column="pwd" property="password"/>
      </resultMap>
      
      <select id="getUserById" parameterType="int" resultMap="UserMap">
          select * from mybatis.user where id = #{id}
      </select>
  

• The resultMap element is the most important and powerful element in MyBatis.

• The design idea of ResultMap is to achieve zero configuration for simple statements. For more complex statements, you only need to describe the relationship between statements.

• That's the beauty of ResultMap -- you don't have to explicitly configure them.

6. Log

6.1 log factory

If there is an exception in a database operation, we need to make a mistake. The log is the best assistant!

Once: soyt debug

Now: log factory

[the external link image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-cbw0seqg-1624359914442) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210609103357092. PNG)] which log is used to realize it? Set it in the setting!

STDOUT_LOGGING standard log output

In the Mybatis core configuration file, configure our logs

Opening JDBC Connection
Created connection 2005733474.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@778d1062]
==>  Preparing: select * from mybatis.user where id = ? 
==> Parameters: 2(Integer)
<==    Columns: id, name, pwd
<==        Row: 2, Jiao Shanshan, 20000310
<==      Total: 1
User{id=2, name='Jiao Shanshan', password='20000310'}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@778d1062]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@778d1062]
Returned connection 2005733474 to pool.

6.2 LOG4J

What is log4j

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-wmqwcqhj-1624359914443) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210609105046130. PNG)]

1. Import the package of log4j first

<!--log4j-->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

2.log4j.properties

#Output the log information with the level of DEBUG to the two destinations of console and file. The definitions of console and file are in the following code
log4j.rootLogger=DEBUG,console,file

#Relevant settings of console output
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#Relevant settings for file output
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/hao.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#Log output level
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

3. Configure log4J as the implementation of log

    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

1. Use of log4j

[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Opening JDBC Connection
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Created connection 1105423942.
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@41e36e46]
[com.hao.dao.UserMapper.getUserById]-==>  Preparing: select * from mybatis.user where id = ? 
[com.hao.dao.UserMapper.getUserById]-==> Parameters: 2(Integer)
[com.hao.dao.UserMapper.getUserById]-<==      Total: 1
User{id=2, name='Jiao Shanshan', password='20000310'}
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@41e36e46]
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@41e36e46]
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Returned connection 1105423942 to pool.

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-cozcxgia-1624359914448) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210609112235310. PNG)]

7. Pagination

7.1 using Limit paging

• Interface

      //paging
      List<User> getUserByLimit(Map<String,Integer> map);
  

• Mapper.xml

      <select id="getUserByLimit" parameterType="map" resultMap="UserMap">
          select * from mybatis.user limit #{startIndex},#{pageSize}
      </select>
  

• test

  @Test
  public void getUserByLimit(){
     SqlSession  sqlSession = MybatisUtils.getSqlSession();
  
     UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  
      HashMap<String,Integer> map = new HashMap<String, Integer>();
      map.put("startIndex",1);
      map.put("pageSize",2);
  
  
     List<User> userList =  mapper.getUserByLimit(map);
  
      for (User user : userList) {
          System.out.println(user);
      }
  
     sqlSession.close();
  }
  

7.2 rowboundaries paging

No longer use SQL for paging

• Interface

      //Pagination 2
      List<User> getUserByRowBounds();
  

• mapper.xml

<select id="getUserByRowBounds" resultMap="UserMap">
    select * from mybatis.user
</select>

• test

  @Test
  public void getUserByRowBounds(){
      SqlSession  sqlSession = MybatisUtils.getSqlSession();
  
  
      //RowBounds implementation
      RowBounds rowBounds = new RowBounds(1,2);
  
      //Paging through Java code level
      List<User> userList =  sqlSession.selectList("com.hao.dao.UserMapper.getUserByRowBounds",null,rowBounds);
  
      for (User user : userList) {
          System.out.println(user);
      }
  
      sqlSession.close();
  }
  

  8. Development using annotations

  8.1 interface oriented programming

  [the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-7ugb2jgt-1624359914449) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210609143054494. PNG)]

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-swolgdvc-1624359914451) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210609143245869. PNG)]

8. Development using annotations

8.1 interface oriented programming

[the external chain image transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-yj0qd0vu-1624359914452) (file: / / C: / users / administrator / appdata / roaming / typora / typora user images / image-20210609143054494. PNG? Lastmodify = 1623232359)]

[the external link image transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-pmu6bwqd-1624359914453) (file: / / C: \ users \ administrator \ appdata \ roaming \ typora user images \ image-20210609143245869. PNG? Lastmodify = 1623232359)]

8.2 development using annotations

1. Annotation is implemented on the interface

@Select("select * from user")
List<User> getUsers();

2. The interface needs to be bound in the core configuration file!

<!--Binding interface-->
<mappers>
    <mapper class="com.hao.dao.UserMapper"/>
</mappers>

3. Test

@Test
public void test(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> list = mapper.getUsers();

    for (User user : list) {
        System.out.println(user);
    }


    sqlSession.close();
}

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-tbsg1d4y-1624359914454) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-20210609162812167. PNG)]

8.3 CRUD

We can automatically commit transactions when the tool class is created

public static SqlSession getSqlSession(){
    return sqlSessionFactory.openSession(true);
}

• Write the interface and add comments

@Select("select * from user")
List<User> getUsers();

//Method has multiple parameters. All parameters must be preceded by @ Param("id") annotation
@Select("select * from user where id = #{id}")
User getUserById(@Param("id") int id);

//Add users
@Insert("insert into user(id,name,pwd) values(#{id},#{name},#{pwd})")
int addUser(User user);

//delete user
@Delete("delete from user where id = #{id}")
int deleteUser(int id);

//Modify user
@Update("update user set name = #{name},pwd = #{pwd} where id = #{id}")
int updateUser(User user);

• Test class

8.4 notes on @ Param()

• Parameter of basic type or String type
• The reference type does not need to be added
• If there is only one basic type, you can not use it, but it is recommended to use it
• What we refer to in SQL is the attribute name set by @ Param()

9. Detailed implementation process of mybatis

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-jttwyl1u-1624359914454) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210609175058515. PNG)]

10. Lombok

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-s5bgqrdq-1624359914455) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210610100211102. PNG)]

11. Many to one processing

Build database

CREATE TABLE `teacher`(
`id` INT(10) NOT NULL PRIMARY KEY,
`name` VARCHAR(30) DEFAULT NULL
)ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO teacher(`id`,`name`) VALUES (1,'Miss Jiao')

CREATE TABLE `student`(
`id` INT(10) NOT NULL PRIMARY KEY,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `student`(`id`,`name`,`tid`) VALUES ('1','Xiao Chi','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES ('2','Naranjito ','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES ('3','Xiao Huang','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES ('4','Little green','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES ('5','indigo plant','1');

Test environment construction

1. Import lombok

2. Create entity classes Teacher and Student

3. Create Mapper interface

4. Create mapper XML file

5. Bind and register the mapper file in the core configuration file

6. Test whether the connection is successful

Nested processing by query

<!--1.Query all student information
	2.According to the student's tid Query the corresponding teacher
	Similar to subquery-->
<select id="getStudent" resultMap="Student-Teacher">
    select * from student
</select>

<resultMap id="Student-Teacher" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

Nested processing according to results

    <!--Nested processing according to results-->
    <select id="getStudent2" resultMap="Student-Teacher2">
        select s.id sid,s.name sname,s.tid stid,t.name tname
        from student s,teacher t
        where s.tid=t.id
    </select>
    <resultMap id="Student-Teacher2" type="com.hao.pojo.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="com.hao.pojo.Teacher">
            <result property="id" column="stid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

Review the many to one query method of Mysql

One to many

Many to one

12. One to many processing

For example, a teacher has multiple students

Nested processing according to results

    <!--Nested queries by result-->
    <select id="getTeacher" resultMap="Teacher-Student">
        select t.name tname,t.id tid,s.id sid,s.name sname
        from student s,teacher t
        where t.id = s.tid and t.id = #{tid}
    </select>
    
    <resultMap id="Teacher-Student" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>

        <!--Complex attributes need to be handled separately,
            Object: association Set: collection javaType =""Specifies the type of property
            We use the generic information in the collection ofType obtain-->
        <collection property="students" ofType="Student">
            <result property="sid" column="id"/>
            <result property="sname" column="name"/>
            <result property="stid" column="id"/>
        </collection>
    </resultMap>

Nested processing by query

    <!--Nested processing by query-->
    
    <select id="getTeacher2" resultMap="Teacher-Student2">
        select * from teacher where id = #{tid}
    </select>
    
    <resultMap id="Teacher-Student2" type="Teacher">
        <collection property="students"  column="id" javaType="ArrayList" ofType="Student" select="getStudent"/>
    </resultMap>
    
    <select id="getStudent" resultType="Student">
        select * from student where tid = #{tid}
    </select>

Summary

1. association many to one

2. collection one to many

3.javaType & ofType

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-blgplxup-1624359914456) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210610155653566. PNG)]

Note:

• Ensure the readability of sql and make it easy to understand
• Note the problem of attribute names and fields in one to many and many to one
• If the problem is not easy to troubleshoot, you can use Log4j

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-mg3uacvr-1624359914457) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-20210610160052625. PNG)]

13. Dynamic SQL

What is dynamic sql: generate different sql statements according to different conditions

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-t91vxmf0-1624359914458) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210610160743629. PNG)]

Build environment

CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT 'Blog id',
`title` VARCHAR(100) NOT NULL COMMENT 'Blog title',
`author` VARCHAR(30) NOT NULL COMMENT 'Blogger',
`create_time` DATETIME NOT NULL COMMENT 'Creation time',
`views` INT(30) NOT NULL COMMENT 'Views'
)ENGINE=INNODB DEFAULT CHARSET=utf8

Create a basic project

1. Guide Package

2. Prepare configuration file

3. Write entity class

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-nr23v4zg-1624359914458) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-20210611091609974. PNG)]

4. Write entity class interface Mapper file and Mapper XML file

IF

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-din6jekq-1624359914459) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-20210611091629531. PNG)]

choose(when,otherwise)

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-yeitkjnh-1624359914460) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-20210611091650142. PNG)]

trim(where,set)

<!--Update blog-->
<update id="updateBlog" parameterType="map">
    update mybatis.blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author},
        </if>

    </set>
    where id = #{id}
</update>

SQL fragment

Sometimes we will extract the common parts for reuse

1. Use SQL to extract common parts

<sql id="if-title-author">
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

2. Use the Include tag reference where necessary

<!--if Implementation dynamics SQL-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from blog where 1=1
    <include refid="if-title-author"></include>
</select>

matters needing attention:

• It is best to define SQL fragments based on a single table
• Do not have a where tag

Foreach

<!--Query page 1-2-3 Recorded blog-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <foreach collection="ids" open="and (" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>

@Test
public void queryBlogForEach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    HashMap map= new HashMap();

    ArrayList<Integer> ids= new ArrayList<Integer>();
    map.put("ids",ids);
    ids.add(1);
    ids.add(2);

    List<Blog> blogs = mapper.queryBlogForeach(map);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }


    sqlSession.close();
}

14. Cache

1. Introduction

• Query: connect to the database and consume resources
  • The results of a query are temporarily stored in a place where one can directly access -- > memory: cache
  • When querying the same data again, directly go to the cache instead of the database

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-pv5oekhc-1624359914461) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-20210611111421490. PNG)]

14.2Mybatis cache

14.3 L1 cache

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-xfdgxynv-1624359914462) (C: \ users \ administrator \ appdata \ roaming \ typora \ user images \ image-20210611113541842. PNG)]

Opening JDBC Connection
Created connection 1125381564.
==>  Preparing: select * from user where id = ? 
==> Parameters: 1(Integer)
<==    Columns: id, name, pwd
<==        Row: 1, Hao Jianchao, 20000310
<==      Total: 1
User(id=1, name=Hao Jianchao, pwd=20000310)
============================
User(id=1, name=Hao Jianchao, pwd=20000310)
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4313f5bc]
Returned connection 1125381564 to pool.

• Modify a record between two queries and reload the cache

Opening JDBC Connection
Created connection 1125381564.
==>  Preparing: select * from user where id = ? 
==> Parameters: 1(Integer)
<==    Columns: id, name, pwd
<==        Row: 1, Hao Jianchao, 20000310
<==      Total: 1
User(id=1, name=Hao Jianchao, pwd=20000310)
==>  Preparing: update mybatis.user set name = ?,pwd = ? where id = ? 
==> Parameters: Satan(String), 2314(String), 1(Integer)
<==    Updates: 1
============================
==>  Preparing: select * from user where id = ? 
==> Parameters: 1(Integer)
<==    Columns: id, name, pwd
<==        Row: 1, Satan, 2314
<==      Total: 1
User(id=1, name=Satan, pwd=2314)
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4313f5bc]
Returned connection 1125381564 to pool.

• Cache invalidation:

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-2jzofowh-1624359914463) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210611115047582. PNG)]

Summary: the L1 cache is enabled by default and is only valid in one sqlsession, that is, the time period from getting the connection to closing the connection

The L1 cache is equivalent to a map

14.4 L2 cache

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-aglda82o-1624359914464) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210611140845032. PNG)]

step

1. Enable global cache

<!--Explicitly enable global cache-->
<setting name="cacheEnabled" value="true"/>

2. Enable in the mapper to use L2 cache

<!--In the current mapper.xml Use L2 cache in files-->
<cache/>

3. Test

Problem: we need to serialize the entity class! Otherwise, an error will be reported

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-yb1t6axb-1624359914464) (C: \ users \ administrator \ appdata \ roaming \ typora \ typora user images \ image-20210611142620378. PNG)]

Summary:

-  As long as the L2 cache is enabled, in the same Mapper It will be effective next time
-  All data will be put in the first level cache first
-  Only when the session is submitted or closed, it will be submitted to the L2 cache