Mybatis

environment

• JDK1.8
• Mysql5.7
• maven 3.6.3
• IDEA

review

• JDBC
• Mysql
• Java Foundation
• Maven
• Junit

SSM framework: the best way to configure files: see the official website documents

Mybatis

1. Introduction to Mybatis (October 21, 2020)

1.1 what is Mybatis

How to get Mybatis

• maven warehouse:

<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>
123456

• Official documents in Chinese: https://mybatis.org/mybatis-3/zh/index.html
• Chinese w3c documents: https://www.w3cschool.cn/mybatis/mybatis-dyr53b5w.html
• Github:https://github.com/mybatis/mybatis-3

1.2 persistence

Data persistence

• Persistence is the process of transforming program data in persistent state and transient state
• Memory: loss upon power failure
• Database (Jdbc), io file persistence
• Examples of life: refrigerated, canned.

Why persistence?

• I don't want to lose some objects
• Memory is too expensive

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 do you need Mybatis?

• The helper program stores the data into the database
• convenient
• The traditional JDBC code is too complex to simplify – > framework – > automation
• advantage:

The most important point: many people use it!
Spring-SpringMVC-SpringBoot

2. The first Mybatis program

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

2.1 construction environment

Build database

CREATE DATABASE `mybatis`;

USE `mybatis`;

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

INSERT INTO `user`(`id`,`name`,`pwd`) VALUES
(1,'Yuan Zhisong','123456'),
(2,'Zhang San','123456'),
(3,'Li Si','123456')

New project

• Create a normal maven project
• Delete src directory
• Import maven dependencies

<!--import dependencies-->
    <dependencies>
        <!--mysql driver-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!--mybatis-->
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </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">
<!--configuration core file-->
<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://localhost:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <!--a Mapper.xml need regist in Mybatis core configuration file-->
    <mappers>
        <mapper resource="com/kuang/dao/UserMapper.xml"/>
    </mappers>
</configuration>

Set useSSL=false here.

• Write mybatis tool class

//SqlSessionFactory -->SqlSession
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;
    static {

        try {
            //Step 1 of using mybats: get sqlSessionFactory object
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (Exception 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(){
//        SqlSession sqlSession =  sqlSessionFactory.openSession();
//        return sqlSession;

        return sqlSessionFactory.openSession();
    }

}

2.3 coding

• Entity class

//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 transformed from the original UserDaoImpl into 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">

<mapper namespace="com.kuang.dao.UserDao">

    <!--sql-->
    <select id="getUserList" resultType="com.kuang.pojo.User">
        select * from mybatis.user
    </select>
</mapper>

2.4 testing

Note:

• org.apache.ibatis.binding.BindingException: Type interface com.kuang.dao.UserDao is not known to the MapperRegistry.

What is MapperRegistry?

Register mappers in the core configuration file

• junit test

@Test
    public void test(){
        //Step 1: 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();
    }

Possible problems:

1. The configuration file is not registered;
2. Binding interface error;
3. Incorrect method name;
4. Incorrect return type;
5. maven export resource problem (maven contract is greater than configuration problem)
   In POM Import from XML

	<build>
	    <resources>
	        <resource>
	            <directory>src/main/resources</directory>
	            <excludes>
	                <exclude>**/*.properties</exclude>
	                <exclude>**/*.xml</exclude>
	            </excludes>
	            <filtering>true</filtering>
	        </resource>
	        <resource>
	            <directory>src/main/java</directory>
	            <includes>
	                <include>**/*.properties</include>
	                <include>**/*.xml</include>
	            </includes>
	            <filtering>true</filtering>
	        </resource>
	    </resources>
	</build>

3,CRUD(2020-10-22)

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: is the method name in the corresponding namespace;
• Resulttype: return value of SQL statement execution!
• parameterType: parameter type!

1. Write interface

//Query user by id
    User getUserById(int id);

2. Write the sql statement in the corresponding mapper

	<select id="getUserById" parameterType="int" resultType="com.kuang.pojo.User">
        select * from mybatis.user where id = #{id}
	</select>

3. Test

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

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUserById(1);
        System.out.println(user);

        //Close SqlSession
        sqlSession.close();
    }

3.3 Insert

	<insert id="addUser" parameterType="com.kuang.pojo.User">
        insert into mybatis.user (id,name,pwd) values (#{id},#{name},#{pwd})
    </insert>

3.4 Update

	<update id="updateUser" parameterType="com.kuang.pojo.User">
        update mybatis.user set name=#{name},pwd=#{pwd} where id = #{id}
    </update>

3.5 Delete

	<delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id = #{id}
    </delete>

Note:

• Add, delete and modify transactions need to be submitted!

3.6 analysis error

1. There should be no Chinese errors in the comments in the xml file. Check whether you have UTF-8 or GBK code and change it to the corresponding one.

<?xml version="1.0" encoding="UTF-8" ?>

<?xml version="1.0" encoding="GBK" ?>

The test is successful.

1. Don't match the label wrong!
2. resource binding mapper requires a path!
3. The program configuration file must comply with the specification!
4. NullPointerException, not registered to resource!
5. maven resources have no export problem!

3.7 universal Map

Assuming that there are too many entity classes or tables, fields or parameters in the database, we should consider using Map!

//Universal Map
    int addUser2(Map<String,Object> map);

<!--The properties in the object can be directly taken out and passed map of key-->
    <insert id="addUser2" parameterType="map">
        insert into mybatis.user (id,pwd) values (#{userid},#{password})
    </insert>

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

         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         Map<String,Object> map = new HashMap<String, Object>();
         map.put("userid",4);
         map.put("password","123321");

         mapper.addUser2(map);

         sqlSession.commit();
         sqlSession.close();
     }

Pass parameters from map and directly get the key from sql! [parameterType=“map”]
Object transfer parameters, you can directly get the attribute of the object in sql! [parameterType=“Object”]
If there is only one basic type parameter, you can get it directly in sql!
Use Map or annotation for multiple parameters!

3.8 thinking questions

How to write fuzzy query?

1. When executing java code, pass wildcard%%

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

1. Use wildcards in sql splicing!

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

4. Configuration analysis (October 23, 2020)

4.1 core configuration file

• mybatis-config.xml
• The MyBatis configuration file contains settings and attribute information that deeply affect MyBatis behavior.
• configuration
  • properties
  • settings
  • typeAliases
  • typeHandlers
  • objectFactory (object factory)
  • plugins
  • environments
    • Environment (environment variable)
      • Transaction manager
      • dataSource
  • databaseIdProvider (database vendor ID)
  • mappers

4.2 environment configuration

Mybatis can be configured to adapt to a variety of environments
However, remember that although multiple environments can be configured, only one environment can be selected for each SqlSessionFactory instance.
There are two types of transaction managers (type="[JDBC|MANAGED]") in MyBatis
The default transaction manager of Mybatis is JDBC, and the connection pool is POOLED

4.3 properties

We can reference the configuration file through the properties attribute
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. [db.properties]
Write a configuration file
db.properties

	driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/mybatis?
	useSSL=false&useUnicode=true&characterEncoding=UTF-8
	username=root
	password=1234561234

Mapping in core configuration file

	<!--Import external profile-->    
	<properties resource="db.properties">        
		<property name="username" value="root"/>        
		<property name="pwd" value="123123"/>    
	</properties>

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

4.4 type aliases

• A type alias is a short name for a Java type.
• The meaning of existence is only to reduce the redundancy of class fully qualified names.

	<!--You can alias an entity class-->
    <typeAliases>
        <typeAlias type="com.kuang.pojo.User" alias="User" />
    </typeAliases>

You can also specify a package name. MyBatis will search for the required JavaBean s under the package name, such as:
Scan the package of an entity class, and its default alias is the class name of this class, with the first letter in lowercase!

	<!--You can alias an entity class-->
    <typeAliases>
        <package name="com.kuang.pojo"/>
    </typeAliases>

The first method is used when there are few entity classes.
If there are many entity classes, the second one is recommended.
The first can be a DIY alias, but the second can't. If you have to change it, you need to add annotations on the entity

@Alias("user")
//Entity class
public class User {}

4.5 setting

These are extremely important tuning settings in MyBatis, which change the runtime behavior of MyBatis.

4.6 other configurations

• typeHandlers
• objectFactory (object factory)
• plugins
  1. mybatis-generator-core
  2. mybatis-plus
  3. General mapper

4.7 mappers

MapperRegistry: register and bind our Mapper file;
Method 1: [recommended]

    <!--every last Mapper.xml All need to be in Mybatis Register in the core configuration file!-->
    <mappers>
        <mapper resource="com/kuang/dao/UserMapper.xml"/>
    </mappers>

Method 2: register with class file binding

    <!--every last Mapper.xml All need to be in Mybatis Register in the core configuration file!-->
    <mappers>
        <mapper class="com.kuang.dao.UserMapper"/>
    </mappers>

Note:

• Interface and its Mapper configuration file must have the same name!
• Interface and its Mapper configuration file must be in the same package!

Method 3: use scan package for injection binding

    <!--every last Mapper.xml All need to be in Mybatis Register in the core configuration file!-->    
    <mappers>        
    	<package name="com.kuang.dao"/>    
    </mappers>

Note:

• Interface and its Mapper configuration file must have the same name!
• Interface and its Mapper configuration file must be in the same package!

practice:

• Bringing a database configuration file into an external database
• Entity class alias
• Ensure the UserMapper interface and UserMapper XML changed to consistent! And put it under the same bag!

4.8 lifecycle and scope

Lifecycle and scope are critical, because incorrect use can lead to very serious concurrency problems.

SqlSessionFactoryBuilder:

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

SqlSessionFactory:

• In short, it can be imagined as: 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.
• The best scope for 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 instance of SqlSession is not thread safe, so it cannot be shared, so its best scope is the request or method scope.
• You need to close it immediately after use, otherwise the resources will be occupied!
  
  Each Mapper here represents a specific business!

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

5.1 problems

Fields in the database

Create a new project and test the inconsistency of entity class fields before copying

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

There is a problem with the test

//    select * from mybatis.user where id = #{id}
// Type processor
//    select id,name,pwd from mybatis.user where id = #{id}

resolvent:

• Alias

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

• resultMap solution

5.2 resultMap

Result set mapping

id name pwd
id name password

    <!--  Result set mapping  -->
    <resultMap id="UserMap" type="User">
        <!--column Fields in the database, property Properties in entity classes-->
        <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.
• The beauty of ResultMap -- you don't have to explicitly configure them.
• If only the world were always so simple.

6. Log (October 24, 2020)

6.1 log factory

If an exception occurs in a database operation, we need to troubleshoot it. Log is the best assistant!
Once: South, debug
Now: log factory!

• SLF4J
• LOG4J [Master]
• LOG4J2
• JDK_LOGGING
• COMMONS_LOGGING
• STDOUT_LOGGING [mastering]
• NO_LOGGING

Which log implementation to use in Mybatis can be set in settings!

STDOUT_LOGGING standard log output
In mybatis config XML core configuration file, configure our log!

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

6.2 Log4j

What is Log4j?

• Log4j is an open source project of Apache. By using log4j, we can control that the destination of log information transmission is console, file and GUI components
• We can also control the output format of each log;
• By defining the level of each log information, we can control the log generation process in more detail.
• It can be flexibly configured through a configuration file without modifying the application code.

1. First in POM Dependency package imported from log4j in XML file

    <dependencies>
        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

2. Create log4j. In the resources folder Properties file

#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

#Settings related to 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

#Settings related to file output
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File = ./log/kuang.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. In mybatis config In the XML core configuration file, configure log4j as the implementation of log!

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

4. Use of log4j, direct test run

Simple use

1. In the test class to use Log4j, import the package import. Org apache. Log4j. Logger;
2. Log object. The parameter is the class of the current class

static Logger logger = Logger.getLogger(UserDaoTest.class);

1. log level

        logger.info("info:Entered testLog4j");
        logger.debug("DEBUG:Entered testLog4j");
        logger.error("erro:Entered testLog4j");

7. Pagination

Think: why pagination!

• Reduce data processing

7.1 using Limit paging

Syntax: SELECT * from user limit startIndex,pageSize
SELECT  * from user limit 3 #[0,n]

Pagination using Mybatis, core SQL

1. Interface

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

1. Mapper.xml

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

1. 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",0);
        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

1. Interface

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

1. Mapper.xml

<!--    Pagination 2-->
    <select id="getUserByRowBounds" resultMap="UserMap">
        select * from mybatis.user
    </select>

1. test

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

        //RowBounds implementation
        RowBounds rowBounds = new RowBounds(0, 2);

        //Paging through java code level
        List<User> userList = sqlSession.selectList("com.kuang.dao.UserMapper.getUserByRowBounds",null,rowBounds);

        for (User user : userList) {
            System.out.println(user);
        }
        
        sqlSession.close();
    }

7.3 paging plug-in

You can understand it. When you use it, you need to know what it is!

8. Development with annotations (2020-10-25)

8.1 interface oriented programming

• I have studied object-oriented programming and interface before, but in real development, I often choose interface oriented programming.
• Root cause: decoupling, expandable, improved reuse. In layered development, the upper layer does not care about the specific implementation. Everyone abides by common standards, making the development easier and more standardized
• In an object-oriented system, various functions of the system are completed by many different objects. In this case, how each object implements itself is not so important to system designers;
• The cooperative relationship between various objects has become the key of system design. From the communication between different classes to the interaction between modules, we should focus on it at the beginning of system design, which is also the main work of system design. Interface oriented programming means programming according to this idea.

8.2 development using annotations

1. The annotation is implemented on the UserMapper interface and the UserMapper XML file

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

1. It needs to be in mybatis config Binding interface in XML core configuration file

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

1. test

    @Test
    public void getUsers(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.getUsers();

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

        sqlSession.close();
    }

Essence: reflection mechanism implementation
Bottom layer: dynamic agent!

Mybatis detailed execution process! (there are more applications later. Go through them again in detail)

8.3 CRUD

1. Automatically commit transactions when the MybatisUtils tool class is created!

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

1. Write the interface and add comments

public interface UserMapper {

    @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);

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

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

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

1. Test class

[Note: we must bind the interface registration to our core configuration file!]

About @ Param() annotation

• Parameters of basic type or String type need to be added
• Reference types do not need to be added
• If there is only one basic type, it can be ignored, but it is recommended to add it!
• What we refer to in SQL is the property name set in @ Param("") here!
  #Difference between {} and ${}

9. Lombok (you can use it if you are lazy)

Use steps:

1. Install Lombok plug-in in IDEA!
2. In project POM Import the jar package of Lombok from the XML file

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
        </dependency>

1. Just annotate the entity class!

@Data
@AllArgsConstructor
@NoArgsConstructor
@Getter and @Setter
@FieldNameConstants
@ToString
@EqualsAndHashCode
@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor
@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog
@Data
@Builder
@SuperBuilder
@Singular
@Delegate
@Value
@Accessors
@Wither
@With
@SneakyThrows

explain:

@Data:Nonparametric structure get,set,toString,hashCode,equals
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode
@ToString
@Getter and @Setter

10. Many to one processing

Many to one:

• Multiple students correspond to one teacher
• For students, association – multiple students, one teacher [many to one]
• For teachers, assemble – one teacher with many students [one to many]

SQL statement:

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

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

CREATE TABLE `student` (
	`id` INT(10) NOT NULL,
	`name` VARCHAR(30) DEFAULT NULL,
	`tid` INT(10) DEFAULT NULL,
	PRIMARY KEY (`id`),
	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 Ming','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES ('2','Xiao Hong','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES ('3','Xiao Zhang','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES ('4','petty thief','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES ('5','Xiao Wang','1');

Test environment construction

1. Import Lombok
2. New entity class Teacher, Student
3. Establish Mapper interface
4. Create mapper XML file
5. Bind and register our Mapper interface or file in the core configuration file! [there are many ways to choose]
6. Test whether the query is successful!

Nested processing according to results

    <!--Nested processing according to results    -->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from mybatis.student s,mybatis.teacher t
        where s.tid = t.id
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

Nested processing by query

    <!--
      Idea:
          1.Query all student information
          2.According to the student's tid，Find the corresponding teacher! Subquery-->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from mybatis.student
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--  For complex properties, we need to deal with objects separately: association Set: collection      -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

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

Review the Mysql many to one query method:

• Subquery
• Join table query

11. One to many processing

For example: a teacher has multiple students!
For teachers, it is a one to many relationship!

Environment construction

1. The environment is the same as just now
   Entity class:

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

@Data
public class Teacher {
    private int id;
    private String name;

    //A teacher has more than one student
    private List<Student> students;
}

Nested processing according to results

    <!--    Nested query by result-->
    <select id="getTeacher" resultMap="TeacherStudent">
            SELECT  s.id sid,s.name sname,t.name tname,t.id,tid
            from student s,teacher t
            where s.tid = t.id and t.id = #{tid}
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--  For complex properties, we need to deal with objects separately: association Set: collection
             javaType="" Specify the type of attribute!
             Collection, we use ofType obtain
             -->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

Nested processing by query

    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from mybatis.teacher where id = #{tid}
    </select>

    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="Student">
        select * from  mybatis.student where tid = #{tid}
    </select>

Summary

1. association - association [many to one]
2. Set collection [one to many]
3. javaType & ofType
   1. javaType is used to specify the type of attribute in the entity class
   2. ofType is used to specify the pojo type mapped to List or collection, and the constraint type in generic type!

Note:

• Ensure the readability of SQL and make it easy to understand as much as possible
• Note the problem of attribute names and fields in one to many and many to one!
• If the problem is difficult to troubleshoot, log can be used. Log4j is recommended

Interview frequency

• Mysql engine
• InnoDB underlying principle
• Indexes
• Index optimization

12. Dynamic SQL

What is dynamic SQL: dynamic SQL refers to generating different SQL statements according to different conditions

Using the feature of dynamic SQL can completely get rid of this pain.

stay MyBatis In previous releases, it took time to understand a large number of elements. With powerful OGNL Expression for, MyBatis 3 Most of the previous elements have been replaced, and the element types have been greatly simplified. Now there are fewer element types to learn than half of the original ones.

if
choose (when, otherwise)
trim (where, set)
foreach

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. Write configuration file
3. Writing entity classes

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime; //The property name and field name are inconsistent
    private int views;

}

1. Write entity classes corresponding to Mapper interface and Mapper XML file

IF

    <select id="queryBlogIF" parameterType="map" resultType="Blog">
        select * from mybatis.blog where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </select>

choose (when, otherwise)

    <select id="queryBlogChoose" parameterType="map" resultType="Blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author != null">
                    and author = #{author}
                </when>
                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>

trim (where, set)

    <select id="queryBlogIF" parameterType="map" resultType="Blog">
        select * from mybatis.blog
        <where>
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>

    </select>

    <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>

The so-called dynamic SQL is still an SQL statement in essence, but we can execute a logical code at the SQL level

Foreach

• Another common use scenario for dynamic SQL is traversal of collections (especially when building IN conditional statements).
• The foreach element is very powerful. It allows you to specify a collection and declare collection items and index variables that can be used in the element body. It also allows you to specify the beginning and end strings and the separator between collection item iterations. This element will not add extra delimiters by mistake. See how smart it is!
• Tip: you can pass any iteratable object (such as List, Set, etc.), Map object or array object to foreach as a Set parameter. When using an iteratable object or array, index is the sequence number of the current iteration, and the value of item is the element obtained in this iteration. When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.

    <!--select * from blog where 1=1 and (id=1 or id=2 or id=3)
        We are now passing on a universal message map，this map There can be a collection in!
        -->
    <select id="queryBlogForeach" parameterType="map" resultType="Blog">
        select * from mybatis.blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>

Dynamic SQL is splicing SQL statements. We just need to ensure the correctness of SQL and arrange and combine them according to the SQL format.
Recommendations:

• First write a complete SQL in Mysql, and then modify it into our dynamic SQL to achieve universal!

SQL fragment

Sometimes, we can extract some functional parts for reuse!

1. Extract common parts using SQL Tags

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

1. Use the Include tag reference where necessary

    <select id="queryBlogIF" parameterType="map" resultType="Blog">
        select * from mybatis.blog
        <where>
            <include refid="if-title-author"></include>
        </where>
    </select>

matters needing attention:

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

October 28 and 29 were delayed for two days due to physical discomfort

13. Cache (just understand)

13.1 introduction

1. What is Cache?

• There is temporary data in memory.
• Put the data frequently queried by users in the cache (memory), and users do not need to query the data from the disk (relational database query file) but from the cache, so as to improve the query efficiency and solve the performance problem of high concurrency system.

2. Why cache?

• Reduce the number of interactions with the database, reduce system overhead and improve system efficiency.

3. What kind of data can be cached?

• Frequently queried and infrequently changed data. [cache can be used]

13.2 Mybatis cache

• Mybatis includes a very powerful query caching feature that makes it easy to customize and configure caching. Caching can greatly improve query efficiency.

• Two level cache is defined by default in Mybatis system:

  L1 cache

  and

  L2 cache

  • By default, only L1 cache is on. (SqlSession level cache, also known as local cache)
  • L2 cache needs to be manually enabled and configured. It is a namespace level cache.
  • In order to improve scalability, Mybatis defines the Cache interface Cache. We can customize the L2 Cache by implementing the Cache interface.

13.3 L1 cache

• L1 cache is also called local cache:
  • The data queried during the same session with the database will be placed in the local cache.
  • In the future, if you need to get the same data, you can get it directly from the cache. There is no need to query the database

Test steps:

1. Open log!
2. The test queries the same record twice in a Session
3. View log output
   

Cache invalidation:

1. Inquire about different things;
2. Adding, deleting and modifying may change the original data, so the cache will be refreshed!
   
3. Query different mapper xml
4. Manually clean up the cache!
   

Summary: the L1 cache is enabled by default. It is only valid in one SqlSession, that is, the interval from getting the connection to closing the connection!
The first level cache is equivalent to a Map.

13.4 L2 cache

• L2 cache is also called global cache. The scope of L1 cache is too low, so L2 cache was born;
• Based on the namespace level cache, a namespace corresponds to a L2 cache;
• Working mechanism
  • When a session queries a piece of data, the data will be placed in the first level cache of the current session;
  • If the current session is closed, the L1 cache corresponding to the session is gone; But what we want is that the session is closed and the data in the L1 cache is saved to the L2 cache;
  • The new session query information can get the content from the L2 cache;
  • The data found by different mapper s will be placed in their corresponding cache (map);

Steps:

1. In mybatis config XML enable global cache

        <!--Show global cache enabled-->
        <setting name="cacheEnabled" value="true"/>

1. Turn on in Mapper where you want to use L2 cache

    <!--At present Mapper.xml Using L2 cache in-->
    <cache/>

You can also customize parameters

    <!--At present Mapper.xml Using L2 cache in-->
    <cache
            eviction="FIFO"
            flushInterval="60000"
            size="512"
            readOnly="true"/>

1. test

   1. Problem: if there are no custom parameters, an error will be reported. We need to serialize the entity class!

   Cause: java.io.NotSerializableException: com.kuang.pojo.User
   

Summary:

• As long as the L2 cache is enabled, it is valid under the same Mapper;
• 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!

13.5 cache principle

13.6 custom cache - ehcache (you can learn about it)

Ehcache is a widely used open source Java distributed cache, mainly for general cache.

To use ehcache in the program, you must first import the package!

Specify our ehcache cache implementation in mapper!

At present: Redis database is used for caching! K-V