MyBatis

Chapter I framework overview

1. Three tier architecture

Basic concepts of MVC

In web development, mvc architecture pattern is used. Model: data, View: View, Controller: Controller

• Controller: receives the request, calls the service object, and displays the processing result of the request. Currently, the servlet is used as the controller
• View view: now use jsp,html,css,js. To display the processing result of the request and display the data in m
• Model data: from mysql, from files, and from the network

MVC action

1. Realize decoupling
2. Let mvc perform its duties. Each module is only responsible for its own functions
3. It makes the system more scalable and easier to maintain

Three tier architecture

1. Interface layer (view layer): it accepts the user's request, calls the service, and displays the processing results of the request. It includes jsp,html,servlet and other objects. The corresponding package controller
2. Business logic layer: process business logic and use algorithms to process data. Return the data to the interface layer. The corresponding is the service package, which contains many XXXService classes. For example, StudentService, OrderService,ShopService
3. Persistence layer (database access layer): access the database, or read files, access the network and obtain data. The corresponding package is dao. For example, StudentDao, OrderDao, ShopDao, etc

2. Processing flow of three-tier architecture request

3. Why use three layers?

1. The structure is clear, the coupling degree is low, and the division of labor of each layer is clear
2. High maintainability and scalability
3. Conducive to standardization
4. Developers can focus on the functional implementation of one layer in the overall structure
5. It is conducive to the reuse of logic in each layer

4. Three tier architecture mode and framework

Each layer corresponds to a frame

• Interface layer - spring MVC framework
  • Spring MVC data is a template added to Spring Framework version 3.0, which provides the spring framework with the ability to build web applications
• Business layer - Spring Framework
  • Spring framework is created to solve the complexity of software development. Spring uses basic JavaBean s to complete the previously very complex enterprise development
• Persistence layer - MyBatis framework
  • mybatis is an excellent persistence layer framework based on java. jdbc is encapsulated inside. Developers only need to pay attention to sql itself, instead of handling load drivers, creating connections, creating statement s and closing connections

5. Framework

1. What is a framework

Framework: it is a software that completes some functions. The calling between classes in the software has been specified. Some functions can be completed through these. The framework can be regarded as a template

The framework can be upgraded and transformed. The framework is safe

The framework is useful for one aspect, not omnipotent. It needs to be used in combination with other frameworks

6. Problems that can be solved by the framework

1. The framework can realize the integration of technology
2. The framework can improve the efficiency of development and reduce the difficulty

7. Advantages and disadvantages of JDBC accessing database

advantage:

1. Intuitive and easy to understand

Disadvantages:

1. There are many objects to create: connetcion, station, resultset
2. Register driver
3. Execute sql statement
4. Turn the ResultSet into an object and a List set
5. close resource
6. sql statements are mixed with logical code

8.MyBatis framework

1. Basic overview

Mybatis is a persistence layer framework, which can operate the database, add, delete, modify and query the database. It can be regarded as an advanced jdcb to solve the shortcomings of jdcb. It was originally an open source project ibatis of Apache. In 2010, the project was migrated from apache software foundation to google code and renamed mybatis. It was migrated to github.com in November 2013

2. Main problems solved by mybatis

1. Register driver
2. Create connection, statement and resultset objects used in jdbc
3. Execute the sql statement to get the ResultSet object
4. Handle the ResultSet, convert the data in the recordset into java objects, and put java objects into the set at the same time
5. Close the resource
6. Realize the decoupling of sql statement and java code. (in mybatis, put sql statement into xml file)

3.Mybatis official website

Official website: https://mybatis.org/mybatis-3/zh/getting-started.html

maven:

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>x.x.x</version>
</dependency>

Chapter 2 Introduction to MyBatis

2.1 first example

Implementation steps:

1. Create student table (id,name,email,age)
2. New maven project
3. Modify pom.xml

   1. Add dependency, mybatis dependency, mysql driver, unit test, junit

      <?xml version="1.0" encoding="UTF-8"?>
      
      <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
      <!--Current project coordinates-->
        <groupId>com.nguyenxb</groupId>
        <artifactId>ch01-first</artifactId>
        <version>1.0-SNAPSHOT</version>
      
      <!--jdk edition-->
        <properties>
          <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
          <maven.compiler.source>1.8</maven.compiler.source>
          <maven.compiler.target>1.8</maven.compiler.target>
        </properties>
      <!--Dependency list-->
        <dependencies>
      <!--    unit testing -->
          <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
          </dependency>
      <!--    mybatis rely on-->
          <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.1</version>
          </dependency>
      <!--    mysql drive-->
          <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.9</version>
          </dependency>
      
        </dependencies>
      
          <!--  Resource plugin in build Medium configuration resources,To prevent the failure of resource export-->
          <build>
              <resources>
                  <resource>
                      <!--  stay src/main/resources Directory can contain properties,xml file-->
                      <directory>src/main/java</directory>
                      <includes>
                          <include>**/*.properties</include>
                          <include>**/*.xml</include>
                      </includes>
                      <filtering>false</filtering>
                  </resource>
              </resources>
          </build>
      
      </project>
      
      

   2. Add resource plug-ins in < build >

4. Create the entity class Student. Define the attribute, and the attribute name is consistent with the column name
5. Create Dao interface and define the method of operating the database
6. Create an xml file (mapper file) and write sql statements
   1. The recommendation of the mybatis framework is to separate sql statements from java code
   2. Mapper file: the definition and dao interface are in the same directory, one table and one mapper file
7. Create the main configuration file (xml file) of mabatis: there is one in the resources directory
   1. Define the data source (DataSoure) object that creates the connection example
   2. Specify the location of other mapper files
8. Create test content
   1. Using the main method, test mybatis to access data
   2. You can also use junit to access the database

Detailed description of configuration file

File directory

mybatis.xml file

Mybatis main configuration file: mybatis.xml, storage path: src\main\resources\mybatis.xml

effect:

• Configure properties for connecting to the database
• Configure database operation log
• The file location of the configuration database operation statement

<?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">
<!-- 1.Constraint file: You can open it
    http://mybatis.org/dtd/mybatis-3-config.dtd
    Role of constraint file:Defines and limits the labels and attributes that can be used in the current file,And the order in which the labels appear-->
<configuration>

    <settings>
        <!--    Configuration log : name The value of is fixed, 
			value yes STDOUT_LOGGING  : Directly mybatis Log output of operation database to console -->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
<!--            Configure data sources: establish Connection object-->
            <dataSource type="POOLED">
<!--                driver:Driven content-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
<!--                Connecting to the database url: Cannot be used directly & Symbol,To convert it to html Entity is &amp; Represents & Symbol -->
                <property name="url" value="jdbc:mysql://localhost:3306/ssm?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf-8"/>
<!--                user name-->
                <property name="username" value="root"/>
<!--                User password-->
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
<!--    Specify other mapper File location:
            objective: Use the connection database information here to execute the connection of other files sql sentence
-->
    <mappers>
<!--
    use mapper of resource Attribute assignment mapper Path to file.
    This path is from target/classes Path start
        Use attention:
            resource = "mapper Path to file,use / Split path"
            One mapper resource Specify a mapper file
-->
        <mapper resource="com\nguyenxb\dao\StudentDao.xml"/>
    </mappers>
</configuration>

StudentDao.xml

Add, delete, modify and query statements for storing operation database

<?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">
<!--
    1.Constraint file: You can open it
    http://mybatis.org/dtd/mybatis-3-mapper.dtd
    Role of constraint file:Defines and limits the labels and attributes that can be used in the current file,And the order in which the labels appear

    2.mapper Is the root label
        namespace: Namespace,Must have a value,Cannot be empty.Unique value.
                    Recommended use Dao Fully qualified name of the interface.
        effect: Participate in identification sql Function of statement.

    3.stay mapper It can be written inside<insert>,<update>,<delete>,<select>Such label.
    <insert>Inside insert sentence, Indicates execution insert operation
    <update>Inside update sentence,
    <delete>Inside delete sentence,
    <select>Inside select sentence,
    These tags have no restrictions on the order in which they appear,And can appear many times
-->
<mapper namespace="com.nguyenxb.dao.StudentDao">
<!--    Query a student's Student
    <select> : Represents a query operation,Inside select sentence
    id : To execute sql Unique identification of the statement,Is a custom string
        Recommended use dao Method name in interface
    resultType:tell mybatis,implement sql sentence,What type of data is assigned to java object.
        resultType The value of is now used java The fully qualified name of the object

    Fully qualified name: namely mybatis Created by reflection mechanism resultType Objects in,And the field with the same name in the statement
    Assign to newly created object.then mybatis Then return the data
	#{studentId} is a placeholder 
-->
    <select id="selectStudentById" resultType="com.nguyenxb.entity.Student">
        select id,name,email,age from student where id = #{studentId}
    </select>
</mapper>

Student.java

Student entity class

package com.nguyenxb.entity;

public class Student {
    // Properties are the same as column names
    private Integer id;
    private String name;
    private String email;
    private Integer age;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                '}';
    }
}

StudentDao.java

Student query database interface

package com.nguyenxb.dao;

import com.nguyenxb.entity.Student;

public interface StudentDao {
    // Query a student
    Student selectStudentById(Integer id);
}

MyTest.java

package com.nguyenxb;

import com.nguyenxb.entity.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;

public class MyTest {
    // Do not Test the class name, otherwise it may conflict with the annotation
    // Test mybatis to execute sql statements
    @Test
    public void testSelectStudentById() throws IOException {
        // Call the method of an object in mybatis and execute the sql statement in the mapper file
        // mybatis core class: SqlSessionFactory

        // 1 define the location of the mybatis master configuration file and the relative path from the class path
        String config = "mybatis.xml";
        // 2 read the main configuration file and use the Resources class in the mybatis framework
        InputStream inputStream = Resources.getResourceAsStream(config);
        // 3 create a SqlSessionFactory object and use the SqlSessionFactoryBuilder object
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);

        // 4 get SqlSession object
        SqlSession sqlSession = factory.openSession();

        // 5 specify the id of the sql statement to be executed
        // sql id = mapper's namespace + ". + < Select > |update|insert|delete tag ID attribute
        String sqlId = "com.nguyenxb.dao.StudentDao"+"."+"selectStudentById";

        // 6 execute sql statements through sqlSession
        Student student = (Student)sqlSession.selectOne(sqlId,1001);

        System.out.println("use mybatis query:"+student.toString());

        // 7 close resources
        sqlSession.close();

    }
}

Detailed explanation of operation results:

2.2 concept

Basic concepts

1. Automatic commit: when your sql statement is executed, commit the transaction immediately. The database update operation is directly saved to the database
2. Manual (manual) commit: where you need to commit the transaction, execute the method, commit the transaction or roll back the transaction. mybatis defaults to manually commit the transaction and needs to commit the transaction when executing insert, update and delete statements

Example:

Note: if the code cannot be executed, please look forward to other related configurations

In StudentDao.xml, add the following statement to operate the database and the corresponding tag

<mapper namespace="com.nguyenxb.dao.StudentDao">
<!--    add to insert
     insert into student values(1003,"ha-ha","haha@qq.ocm",20)

     If passed to mybatis It's a java object, use#{property name} gets the value of the secondary property
     Attribute value to #Location of {} placeholder, mybatis executes this property, corresponding to getXxx()
     for example #{id}, execute getId();
-->
    <insert id="insertStudent">
        insert into student values(#{id},#{name},#{email},#{age})
    </insert>
</mapper>

Put this code into a class and import junit's jar package to run

 @Test
    public void testInsertStudent() throws IOException {
        // Define the location of the mybatis master configuration file
        String config = "mybatis.xml";
        // Read configuration file
        InputStream inputStream = Resources.getResourceAsStream(config);
        // Create an sqlSessionFactory object using the build method of sqlSessionFactoryBuilder
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // Get sqlSession object
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // Create sql id and insert data
        String sqlId = "com.nguyenxb.dao.StudentDao"+"."+"insertStudent";
        // Create an object to insert data into
        Student student = new Student();
        student.setId(1004);
        student.setName("Arthur East");
        student.setEmail("asd@qq.com");
        student.setAge(23);
        // Perform insert operation
        int rows = sqlSession.insert(sqlId,student);
        // Commit transaction
        sqlSession.commit();
        System.out.println("use mybatis Add a student,rows:"+rows);

        sqlSession.close();
    }

After successful execution, the database will directly insert a student object

Solve the problem that MyBatis cannot find the file (idea version)

1. First, check whether the mapping path in mybatis.xml is configured correctly

2. Check whether relevant sql statement mapping is added in XxxDao.xml, or whether the namespace in java code is consistent with the id corresponding to the sql tag (Note: there is a. Connection between the namespace and the id of the sql tag)

3. Add resource plug-in in pom.xml

     <!--  Resource plugin in build Medium configuration resources,To prevent the failure of resource export-->
       <build>
           <resources>
               <resource>
                   <!--  stay src/main/resources Directory can contain properties,xml file-->
                   <directory>src/main/java</directory>
                   <includes>
                       <include>**/*.properties</include>
                       <include>**/*.xml</include>
                   </includes>
                   <filtering>false</filtering>
               </resource>
           </resources>
       </build>
   

4. Re build project

5. Open the project directory, click pom.xml, right-click Maven, and click Reload project

1. Reload maven
   

2. Restart idea
   

3. Manually copy files

4. Recreate a new project

2.3 MyBatis object analysis

object

Resource, sqlsession, sqlsessionfactory, etc

1 Resource object

Resource class, as its name implies, is a resource used to read resource files. In fact, there are many methods to return different types of IO and objects by loading and parsing resource files

Define the location of the mybatis master configuration file and the relative path from the classpath

String config = "mybatis.xml";

Read the main configuration file and use the Resources class in the mybatis framework

InputStream inputStream = Resources.getResourceAsStream(config);

2 SqlSessionFactory object and SqlSessionFactoryBuilder object

• Get the SqlSessionFactory object, an important object, which is a heavyweight object: creating this object requires more resources and time. Just have one in the project
• SqlSessionFactory interface: it is used to create sqlsession objects
• DefaultSqlSessionFactory implementation class

public class DefaultSqlSessionFactory implements SqlSessionFactory {}

Methods commonly used in SqlSessionFactory interface:

// Get a default SqlSession object. By default, transactions need to be submitted manually
SqlSession openSession(); 
// The boolean parameter indicates whether the transaction is automatically committed, 
///true: create a SqlSession that automatically commits transactions
// false: equivalent to openSession without parameters
SqlSession openSession(boolean var1);

You can use SqlSessionFactoryBuilder objects to create SqlSessionFactory objects

SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);

3 SqlSession object

• Obtain the SqlSession object through the openSession method of the SqlSessionFactory object

SqlSession sqlSession = factory.openSession();

• SqlSession object is obtained through SqlSessionFactory. SqlSession itself is an interface

Implementation class: DefaultSqlSession, which is thread unsafe

public class DefaultSqlSession implements SqlSession {}

SqlSession object provides a large number of methods for executing sql statements:

selectOne: implement sql sentence,At most 1 line of record can be returned,If there is more than one line, an error will be reported
selectList: implement sql sentence,Return multiline data
selectMap: implement sql sentence,Return a Map result
insert:implement insert sentence
update: implement update sentence
delete: implement delete sentence
commit: Commit transaction
rollback:Rollback transaction

Note: SqlSession object is thread unsafe, so the steps are as follows:

1. Inside the method, obtain the SqlSession object before executing the sql statement
2. Call the SqlSession method to execute the sql statement
3. Close the SqlSession object and execute SqlSession.close()

<mapper namespace="com.nguyenxb.dao.StudentDao">
<!--    Query a student's Student
    <select> : Represents a query operation,Inside select sentence
    id : To execute sql Unique identification of the statement,Is a custom string
        Recommended use dao Method name in interface
    resultType:tell mybatis,implement sql sentence,What type of data is assigned to java object.
        resultType The value of is now used java The fully qualified name of the object

    Fully qualified name: namely mybatis Created by reflection mechanism resultType Objects in,And the field with the same name in the statement
    Assign to newly created object.then mybatis Then return the data
-->
    <select id="selectStudentById" resultType="com.nguyenxb.entity.Student">
        select id,name,email,age from student where id = #{studentId}
    </select>
</mapper>

• Specify the id of the sql statement to be executed. Sqlid = the id attribute of the namespace + ". + select | update | insert | delete tag in the mapper

String sqlId = "com.nguyenxb.dao.StudentDao"+"."+"selectStudentById";

• Execute sql statement through sqlSession

Student student = (Student)sqlSession.selectOne(sqlId,1001);

• close resource

sqlSession.close();

2.4 basic usage of mybatis

mybatis tool class

MyBatisUtil.java

package com.nguyenxb.util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

/**
 * Tool class: get SqlSession object of MyBatis
 */
public class MyBatisUtil {

    private static SqlSessionFactory factory = null;
    static {
        String config = "mybatis.xml";
        try {
            InputStream inputStream = Resources.getResourceAsStream(config);
            factory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param autoCommitFlag true:Auto commit transaction, false: manually commit transaction
     * @return SqlSession object
     */
    public static SqlSession getSqlSession(boolean autoCommitFlag){
        SqlSession sqlSession = null;
        if (factory != null){
            if (autoCommitFlag){
                sqlSession = factory.openSession(true);
            }else {
                sqlSession = factory.openSession();
            }
        }
        return sqlSession;
    }

    /**
     * @return SqlSession object that manually commits the transaction
     */
    public static SqlSession getSqlSession(){
        SqlSession sqlSession = null;
        if (factory != null){
            sqlSession = factory.openSession();
        }
        return sqlSession;
    }
}

mapper file

StudentDao.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="com.nguyenxb.dao.StudentDao">
    <!--Enter your sql statement with (select | insert | update |delete) label-->
    <select id="selectStudentById" resultType="com.nguyenxb.entity.Student">
        select id,name,email,age from student where id = #{studentId}
    </select>
    <select id="selectStudents" resultType="com.nguyenxb.entity.Student">
        select * from student
    </select>
    <select id="insertStudent">
        insert into student values(#{id},#{name},#{email},#{age})
    </select>
</mapper>

Xxdao interface implementation class

Example: Student Dao interface implementation class StudentDaoImpl.java

package com.nguyenxb.dao.Impl;

import com.nguyenxb.dao.StudentDao;
import com.nguyenxb.entity.Student;
import com.nguyenxb.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class StudentDaoImpl implements StudentDao {
    @Override
    public Student selectStudentById(Integer id) {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        String sqlId = "com.nguyenxb.dao.StudentDao.selectStudentById";
        Student student = sqlSession.selectOne(sqlId, id);
        sqlSession.close();
        return student;
    }

    @Override
    public List<Student> selectStudents() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        String sqlId = "com.nguyenxb.dao.StudentDao.selectStudents";
        List<Student> students = sqlSession.selectList(sqlId);
        sqlSession.close();
        return students;
    }

    @Override
    public int insertStudent(Student student) {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        String sqlId = "com.nguyenxb.dao.StudentDao.insertStudent";
        int rows = sqlSession.insert(sqlId, student);
        sqlSession.commit();
        sqlSession.close();
        return rows;
    }
}

Test class

Test the StudentDaoImpl class

package com.nguyenxb.dao;

import com.nguyenxb.dao.Impl.StudentDaoImpl;
import com.nguyenxb.entity.Student;
import org.junit.Test;

import java.util.List;

public class StudentDaoTest2 {
    @Test
    public void testSelectStudentById(){
        StudentDaoImpl dao = new StudentDaoImpl();
        Student student = dao.selectStudentById(1001);
        System.out.println(student.toString());
    }
    @Test
    public void testSelectStudents(){
        StudentDaoImpl dao = new StudentDaoImpl();
        List<Student> students = dao.selectStudents();
        // lambda expression
        students.forEach(student -> System.out.println(student.toString()));
    }
    @Test
    public void testInsertStudent(){
        StudentDaoImpl dao = new StudentDaoImpl();
        Student student = new Student();
        student.setId(1007);
        student.setName("ASA");
        student.setEmail("as@qq.com");
        student.setAge(30);
        int rows = dao.insertStudent(student);
        System.out.println(rows);
    }
}

Chapter III Dao agent of MyBatis

3.1 dao agent

Basic concepts

Mybatis provides proxy: mybatis creates Dao interface implementation class objects to complete the execution of sql statements

Next, learn about the Dao proxy of MyBatis through the selectStudentById() method in StudentDaoImpl

/*
	String sqlId = "com.nguyenxb.dao.StudentDao.selectStudentById";
Student student = sqlSession.selectOne(sqlId, id);
     Test method: call dao's method
    Student student = dao.selectStudentById(1001);
	
mybatis Information necessary to operate the database: 
    (1) dao: The fully qualified type name can be obtained through reflection. Dao is of StudentDao type, and its fully qualified name is com.nguyenxb.dao.StudentDao
    (2) selectStudentById : dao The method name is the id of the tag in the mapper file. sqlId = "com.nguyenxb.dao.StudentDao.selectStudentById" can be obtained through dao.selectStudentById();
    (3) How to determine which method of SqlSession to call?
        1.According to the method return value of dao interface, if an object is returned, such as Student, SqlSession.selectOne() is called
        If the method in dao interface returns List, call selectList() of SqlSession;

        2.According to the label in the mapper file, if the label is < Insert >, call the SqlSession.insert() method

    * */

Therefore, in the mybatis framework, because it is found that the necessary information for executing sql statements can be determined by using dao method calls, mybatis simplifies the implementation of dao objects
mybatis proxy technology (dynamic proxy, Dao's dynamic proxy): the mybatis framework creates an interface implementation class object in memory according to your Dao interface during program execution

Example analysis: mybatis creates the implementation class StudentDaoImpl of StudentDao interface,
Use the StudentDaoImpl created by the framework to replace the manually implemented functions of the StudentDaoImpl class,
There is no need for developers to write the implementation class of dao interface

Actual situation: mybatis actually creates the Proxy class of the XxxDao interface, and mybatis internally implements XxxDaoImpl through the reflection mechanism

Proxy requirements for dao using MyBatis:

1. Namespace in mapper file: the fully qualified name (namespace + tag id) of dao interface must be used
2. The id of the tag in the mapper file must be the same as the method name in the dao interface

Implementation of Mybatis agent

Use the method getMapper(XxxDao.class) of the SqlSession object

For example, there is now a StudentDao interface, and the proxy method is used: that is, there is no need to manually implement the StudentDao interface

// Get sqlSession operation data
SqlSession sqlSession = MyBatisUtil.getSqlSession();
// Use proxy mode
StudentDao studentDao=sqlSession.getMapper(StudentDao.class);
// Query student information
Student student = studentDao.selectStudentById(1001);
System.out.println(student.toString());
sqlSession.close();

// In the above code
StudentDao studentDao=sqlSession.getMapper(StudentDao.class);
// Equivalent to
// Traditional way: that is, you need to implement the StudentDao interface, that is, you need to create the StudentDaoImpl class
 StudentDaoImpl dao = new StudentDaoImpl();

3.2 understanding parameters

Understand that parameters are sql statements that transfer data into mapper files through java programs. Parameters mainly refer to formal parameters of dao interfaces

parameterType

parameterType: indicates the type of parameter, which refers to the data type of the formal parameter of dao method. The data type of this formal parameter is used for mybatis. Mybatis is used when assigning values to the parameters of sql statements, such as preparedstatement.setxxx (position, value)

The dao interface method passes in a simple parameter

The parameter of the method in Dao interface is only a simple type (java basic type and String). The placeholder uses #{any character}, which is independent of the parameter name of the method

Example 1

dao interface definition

// Query a student by id
Student selectStudentById(Integer id);

The dao interface uses a simple type parameter. To get the parameter value in the mapper file, use #{any character}

mapper file

<select id="selectById" parameterType="java.lang.Integer" resultType="com.nguyenxb.entity.Student">
    select id,name,email,age from student where id = #{studentId}
</select>

test method

@Test
public void testSelectStudentById(){
    // Get sqlSession operation data
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    // Use proxy mode
    StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
    Student student = studentDao.selectStudentById(1001);
    System.out.println(student.toString());
    sqlSession.close();
}

Example 2

dao interface definition

// Query a student by email
Student selectByEmail(String email);

The dao interface uses a simple type parameter. To get the parameter value in the mapper file, use #{any character}

mapper file

<select id="selectByEmail" resultType="com.nguyenxb.entity.Student">
    select id,name,email,age from student where email=#{studentEmail}
</select>

test method

  @Test
    public void testOneParameter(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentDao dao = sqlSession.getMapper(StudentDao.class);
        Student student = dao.selectByEmail("as@qq.com");
        System.out.println(student.toString());
        sqlSession.close();
    }

dao interface method passes in multiple simple type parameters

@Param: named parameter, used before the formal parameter of the method to define the parameter name. This name can be used in the mapper file

Example 1

dao interface definition

/*
* Parameters of multiple simple types
* Use @ Param to name the parameter, and the annotation is provided by mybatis
* Position: in front of parameter definition
* Attribute: value custom parameter name
* Basic syntax format:
* @Param(value="myname") parameter
* Of course, you can also omit value, that is, @ Param("myname")
*
* */
List<Student> selectByNameOrAge(@Param(value = "myname") String name,
                                @Param("myage") Integer age);

mapper file

<!--    Parameters of multiple simple types
        When used@Param After naming, for example @Param(value="myname")
        stay maper in, use#{Named parameters} for example#{myname}
-->
    <select id="selectByNameOrAge" resultType="com.nguyenxb.entity.Student">
        select id,name,email,age from student where name=#{myname} or age =#{myage}
    </select>

test method

@Test
  public void testSelectByNameOfAge(){
      SqlSession sqlSession = MyBatisUtil.getSqlSession();
      StudentDao dao = sqlSession.getMapper(StudentDao.class);
      List<Student> students = dao.selectByNameOrAge("ADI",23);
     students.forEach(student -> System.out.println(student.toString()));
      sqlSession.close();
  }

The dao interface method passes in an object as a parameter

The formal parameter of the method is a java object that represents multiple parameters. The attribute value of the object is used as the parameter

example

Class definition

public class Student {
    private Integer id;
    private String name;
    private String email;
    private Integer age;
    // The set | get | toString method has been omitted
}

public class QueryParam {
    private Object p1;
    private Object p2;
     // The set | get | toString method has been omitted
}

dao interface definition

/*
* A java object as a parameter (corresponding to the attribute, each attribute has set and get methods)
* */
List<Student> selectByObject(Student student);

List<Student> selectByObject2(QueryParam param);

mapper file

<!--    One java Object as a parameter to a method,Use the properties of the object as parameter values
       Simple grammar: #{property name}, mybatis calls the getXxx method of this property to get the property value
-->
    <select id="selectByObject" resultType="com.nguyenxb.entity.Student">
        select id,name,email,age from student where name=#{name} or age = #{age}
    </select>
    
    <select id="selectByObject2" resultType="com.nguyenxb.entity.Student">
        select id,name,email,age from student where name =#{p1} or age=#{p2}
    </select>

test method

// Example 1
@Test
   public void testSelectByObject(){
       SqlSession sqlSession = MyBatisUtil.getSqlSession();
       StudentDao dao = sqlSession.getMapper(StudentDao.class);
       Student student = new Student();
       student.setName("ADI");
       student.setAge(15);
       List<Student> students = dao.selectByObject(student);
      students.forEach(student1 -> System.out.println(student.toString()));
       sqlSession.close();
   }
// Example 2
   @Test
   public void testSelectByObject2(){
       SqlSession sqlSession = MyBatisUtil.getSqlSession();
       StudentDao dao = sqlSession.getMapper(StudentDao.class);
       QueryParam queryParam = new QueryParam();
       queryParam.setP1("Arthur East");
       queryParam.setP2(15);
       List<Student> students = dao.selectByObject2(queryParam);
      students.forEach(student1 -> System.out.println(student1.toString()));
       sqlSession.close();
   }

In addition, the parameters of the sql statement of mybatis also support specifying data types

for example

#{property,javaType=int,jdbcType=NUMERIC}

<!--
property : Specific value passed in
javaType:java The incoming value of the interface is int type,
jdbcType: The field type of the database is NUMERIC type
-->

Interface definition

List<Student> selectByObject(Student student);

mapper file

<select id="selectByObject" resultType="com.nguyenxb.entity.Student">
    select id,name,email,age from student where
        name=#{name,javaType=java.lang.String,jdbcType=VARCHAR}
     or
        age = #{age,javaType=java.lang.Integer,jdbcType=INTEGER}
</select>

Multiple simple type parameters are passed in the dao interface, and the location is used

Parameter position: the formal parameter list of the method in the dao interface. From left to right, the parameter positions are 0,1,2

Syntax format: #{arg0} #{arg1}

dao interface definition

List<Student> selectByPosition(String name,Integer age);

mapper file

<!--    Get parameter values using location, dao Interface methods are parameters of multiple simple types
        grammar: #{arg0} #{arg1}....
-->
  <select id="selectByPosition" resultType="com.nguyenxb.entity.Student">
         select id,name,email,age from student where name = #{arg0} or age = #{arg1}
    </select>

test

@Test
public void testSelectByPosition(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    StudentDao dao = sqlSession.getMapper(StudentDao.class);
    List<Student> students = dao.selectByPosition("Arthur East",15);
   students.forEach(student1 -> System.out.println(student1.toString()));
    sqlSession.close();
}

Pass in the map parameter in the dao interface

map is a parameter of dao interface. You can use key to get the parameter value in mapper file

dao interface file

List<Student> selectStudentByMap(Map<String,Object> map);

mapper file

<!--    use map Transfer parameters
        stay mapper In the file, obtain map Value of, Yes key Acquired,grammar: #{key}
-->
    <select id="selectStudentByMap" resultType="com.nguyenxb.entity.Student">
         select id,name,email,age from student where name = #{myname} or age = #{myage}
    </select>

test

@Test
    public void testSelectByMap(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentDao dao = sqlSession.getMapper(StudentDao.class);
        Map<String,Object> map = new HashMap<>();
        map.put("myname","ha-ha");
        map.put("myage",23);

        List<Student> students = dao.selectStudentByMap(map);
       students.forEach(student1 -> System.out.println(student1.toString()));
        sqlSession.close();
    }

3.3 # and $differences

#Placeholder

Syntax: #{character}

The jdbc object that mybatis handles #{} uses is the preparestation object

<select id="selectById" parameterType="java.lang.Integer" resultType="com.nguyenxb.entity.Student">
    select id,name,email,age from student where id = #{studentId}
</select>

mybatis Create PrepareStatement object,implement sql sentence
String sql = "select id,name,email,age from student where id=?";
PrepareStatement pst = conn.prepareStatement(sql);
pst.setInt(1,1001);// Transfer parameters
ResultSet rs = pst.executeQuery();// Execute sql statement

#Features of {}:

• The PrepareStatement object is used to execute sql statements with high efficiency
• The PrepareStatement object can avoid sql statement injection and make sql statement execution safer
• #{} is often used as a column value, which is located to the right of the equal sign. The value of #{} position is related to the data type

$placeholder

Syntax: ${character}

mybatis executes the sql statement of ${placeholder}

<select id="selectById" parameterType="java.lang.Integer" resultType="com.nguyenxb.entity.Student">
    select id,name,email,age from student where id = #{studentId}
</select>

${} Represents a string connection,hold sql Other contents and of the statement ${}Content use string(+) Connected together
String sql = "select id,name,email,age from student where id=" + "1001";

mybatis establish Statement object,implement sql sentence
PrepareStatement pst = conn.createStatement(sql);
pst.setInt(1,1001);// Transfer parameters
ResultSet rs = pst.executeQuery();// Execute sql statement

Characteristics of ${}

• Using the Statement object to execute sql statements is inefficient
• The value of ${} placeholder uses string connection, which has the risk of sql injection. There are code security problems, such as incoming data 'ADI' or 1=1
• The ${} data is used as is and does not distinguish between data types
• ${} is often used as table name or column name to ensure data security

For example: (this is an example of operating data, which is rarely used)

dao interface

// Use of ${} placeholders
List<Student> queryStudent(@Param("studentName") String name);

mapper file

<!--    ${} Use of-->
    <select id="queryStudent" resultType="com.nguyenxb.entity.Student">
        select * from student where name = ${studentName}
    </select>

In the test, the 'ADI' is passed in with single quotation marks, and mybatis will not process any data

@Test
public void testQueryStudent(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    StudentDao dao = sqlSession.getMapper(StudentDao.class);
    List<Student> students = dao.queryStudent("'ADI'");

    students.forEach(student -> System.out.println(student.toString()));
    sqlSession.close();

}

Example 2:

Assume that the incoming value is id

${} general purpose: used to operate column names and table names. The corresponding sql statement is

select * from student order by id desc, which is the column

Note: using #{} is ineffective. The corresponding sql statement is

select * from student order by 'id' desc, which is a string

dao interface definition

// Use of ${} placeholder: operate the column name to get the list of students sorted in descending order
List<Student> queryStudentByColName(@Param("colName") String name);

mapper

<select id="queryStudentByColName" resultType="com.nguyenxb.entity.Student">
       select * from student order by ${colName} desc
   </select>

test

    @Test
public void testQueryStudentByColName(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    StudentDao dao = sqlSession.getMapper(StudentDao.class);
    List<Student> students = dao.queryStudentByColName("id");

    students.forEach(student -> System.out.println(student.toString()));
    sqlSession.close();

}

Example 3:

#Use {} with ${}

dao interface definition

// Use of ${} placeholders: operation column names
List<Student> queryStudents(
        @Param("tableName")String tableName,
        @Param("name") String name,
        @Param("colName") String colName);

mapper file

<select id="queryStudents" resultType="com.nguyenxb.entity.Student">
        select * from ${tableName} where name = #{name} order by ${colName} desc
    </select>

Test: query all the students in the student table whose name is ADI, and sort the output in descending order. The sql statement is as follows:

select * from student where name = 'ADI' order by id desc

@Test
public void testQueryStudents(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    StudentDao dao = sqlSession.getMapper(StudentDao.class);
    List<Student> students = dao.queryStudents("student",
            "ADI","id");

    students.forEach(student -> System.out.println(student.toString()));
    sqlSession.close();

}

3.4 encapsulating MyBatis output results

Encapsulate output result: MyBatis executes sql statement to get ResultSet, which is converted into java object

resultType

resultType represents a custom object

Query the database and return an object

resultType attribute: used when executing select. It appears as the attribute of < Select > tag

resultType: indicates the result type. mysql executes sql statements to get the type of java object. There are two values

(1) Fully qualified name of Java type, (2) use alias

 // Method in dao interface: query a student by id
    Student selectStudentById(Integer id);

<select id="selectStudentById" resultType="com.nguyenxb.entity.Student">
    select id,name,email,age from student where id = #{studentId}
</select>

resultType: Now use java The fully qualified name of the type. Means mybatis implement sql,hold ResultSet Convert data in to Student Object of type.mybatis Will do the following:
1.call com.nguyenxb.entity.Student Nonparametric construction method of,create object.
	Student student = new Student(); // Creating objects with reflections
2.A column with the same name is assigned to an attribute with the same name.
	student.setId(rs.getInt("id"));
	student.setName(rs.getString("name"));
3.obtain java object,If dao The return value of the interface is List aggregate,mybatis hold student Object placement List aggregate.

So execute Student mystudent = dao.selectById(1001);// Get the row of data with id = 1001 in the database. Assign the column value of this row of data to the attribute of mystudent object. That is, the data of each object we get is the value of each row in the database

resultType returns the basic rules of the object

When returning an object, if the property name of the object is the same as the column name, the data in the column will be assigned to the object. If it is different, it will not be assigned

Class definition

public class CustomObject {
    private Integer cid;
    private String cname;
    private String email;
    private Integer age;
    // The set | get |toString method is omitted
}

dao interface definition

CustomObject selectCustomObjectById(@Param("stuid") Integer id);

mapper file

<select id="selectCustomObjectById" resultType="com.nguyenxb.pojo.CustomObject">
    select id,name,email,age from student where id = #{stuid}
</select>

test

 @Test
public void testSelectCustomObjectById(){
    // Get sqlSession operation data
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    // Use proxy mode
    StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
    CustomObject customObject = studentDao.selectCustomObjectById(1001);
    System.out.println(customObject.toString());
    sqlSession.close();
}
//  The result is customobject {CID = null, CNAME ='null ', email ='null' 123456@qq.com ', age=12}

resultType represents a simple type

Query the database and return a value

dao interface definition

// Get row data in table
long countStudent();

mapper file

<!--    implement sql sentence,Get a value,Row by column-->
    <select id="countStudent" resultType="java.lang.Long">
        select count(*) from student
    </select>

test

@Test
  public void testCountStudent(){
      // Get sqlSession operation data
      SqlSession sqlSession = MyBatisUtil.getSqlSession();
      // Use proxy mode
      StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
    long countStudent = studentDao.countStudent();
    System.out.println("Number of rows:"+countStudent);
      sqlSession.close();
  }
// Result output: rows: 7

resultType represents a Map structure

Example 1: a map represents a row of data. The dao interface returns a map. The SQL statement can only obtain one row of records at most. If there is more than one row of records, an error will be reported

dao interface definition

// The query result returns a Map structure data
Map<Object,Object> selectMap(@Param("stuid")  Integer id);

mapper file

<!--
    The execution structure gets a Map Structural data, mybatis implement sql,hold ResultSet Turn into mao
    sql The execution structure is,List to do map of key,Column value value
    sql The execution result is a row of records,Turn into map The structure is correct.
-->
    <select id="selectMap" resultType="java.util.Map">
        select id,name,age from student where id = #{stuid}
    </select>

test

@Test
   public void testSelectMap(){
       // Get sqlSession operation data
       SqlSession sqlSession = MyBatisUtil.getSqlSession();
       // Use proxy mode
       StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
         Map<Object, Object> map = studentDao.selectMap(1005);
        System.out.println("map === "+map);
       sqlSession.close();
   }
// Result output: Map = = {name = Atherton, id=1005, age=23}

resultMap

resultMap: result mapping. The correspondence between custom column names and java object attributes. It is often used when column names and attribute names are different

Usage:

1. Define the resultMap tag first, and specify the corresponding relationship between column name and attribute name
2. Use the resultMap attribute in the select tab to specify the id value of the resultMap defined above

dao interface definition

CustomObject selectCustomObjectById(@Param("stuid") Integer id);

mapper file

<!--    use resultMap Define the relationship between columns and attributes-->
<!--    definition resultMap: Once defined, it can be used repeatedly
        id : to resultMap A name for the mapping relationship,Unique value
        type:java The fully qualified name of the type
-->
    <resultMap id="customMap" type="com.nguyenxb.pojo.CustomObject">
<!--        Define the correspondence between column names and attribute names-->
<!--        Primary key type usage id label : The of the database table id Assign to CustomObject Class cid -->
        <id column="id" property="cid"/>
<!--        Use of non primary key columns result label-->
        <result column="name" property="cname"/>
<!--        The column name and attribute name are the same without definition,Of course, there is no problem with the definition-->
        <result column="email" property="email"/>
        <result column="age" property="age"/>
    </resultMap>
<!--    use resultMap attribute,Specifies the of the mapping relationship id,notes : resultType and resultMap Only one can be used-->
    <select id="selectCustomObjectById" resultMap="customMap">
        select id,name,email,age from student where id = #{stuid}
    </select>

test

public void testSelectCustomObjectById(){
    // Get sqlSession operation data
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    // Use proxy mode
    StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
    CustomObject customObject = studentDao.selectCustomObjectById(1001);
    System.out.println(customObject.toString());
    sqlSession.close();
}
//Output: CustomObject{cid=1001, cname = 'Zhang San', email = 123456@qq.com ', age=12}

3.5 user defined alias

mybatis provides short, memorable names for java object definitions

To customize an alias:

1. In the mybatis main configuration file, use the typeAliases tag to declare aliases
2. In the mapper file, resultType = "alias"

Main configuration file: mybatis.xml

<!--Alias label at<settings>After label, Please refer to relevant for specific order dtd file-->   
<typeAliases>
<!--        First syntax format
            type : java The fully qualified name of the type (Custom type)
            alias:Custom alias

            advantage: Aliases can be customized
            shortcoming:Each type must be defined separately
-->
<!--        <typeAlias type="com.nguyenxb.entity.Student" alias="stu" />-->
<!--        <typeAlias type="com.nguyenxb.pojo.QueryParam" alias="qp"></typeAlias>-->

<!--        The second method
            name : Package name ,mybatis All class names in this package will be used as aliases(Case insensitive)
            advantage:Easy to use,Define aliases for multiple classes at once
            shortcoming: Alias cannot be customized,Must be a class name
-->
        <package name="com.nguyenxb.entity"/>

    </typeAliases>

mapper file:

<!--After configuring the alias in the main profile,Just use the alias directly-->
<!--The first method-->
<select id="selectStudentById" resultType="stu">
    select id,name,email,age from student where id = #{studentId}
</select>

<!--The second method-->
<select id="selectStudentById" resultType="student">
    select id,name,email,age from student where id = #{studentId}
</select>

3.6 solutions for different column names and java object attribute names

1. Use resultMap: customize the correspondence between column names and attribute names
2. Use resultType: the alias of the column is the same as the name of the java object

dao interface definition

CustomObject selectCustomObjectById2(@Param("stuid") Integer id);

mapper file

<!--    Use column aliases,Solve the problem of different column names and attribute names-->
 <select id="selectCustomObjectById2" resultType="com.nguyenxb.pojo.CustomObject">
        select id as cid,name cname,email,age from student where id = #{stuid}
    </select>

test

@Test
public void selectCustomObjectById2(){
    // Get sqlSession operation data
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    // Use proxy mode
    StudentDao dao = sqlSession.getMapper(StudentDao.class);
    CustomObject customObject = dao.selectCustomObjectById2(1001);
    System.out.println(customObject);
    sqlSession.close();
}
// Output: ustomObject{cid=1001, cname = 'Zhang San', email='123456@qq.com', age=12}

3.7 like fuzzy query

The first way

In the java program, assemble the content of like. Pass this content into the sql statement

dao interface definition

// like the first way
List<Student> selectLikeOne(@Param("name") String name);

mapper file

<select id="selectLikeOne" resultType="com.nguyenxb.entity.Student">
    select * from student where name like #{name}
</select>

test

@Test
   public void testSelectLikeOne(){
       // Get sqlSession operation data
       SqlSession sqlSession = MyBatisUtil.getSqlSession();
       // Use proxy mode
       StudentDao dao = sqlSession.getMapper(StudentDao.class);
       String name = "%Ah%";
       List<Student> students = dao.selectLikeOne(name);
       students.forEach(student -> System.out.println(student));
       sqlSession.close();
   }
/*Output:
Student{id=1004, name='Arthur East ', email='asd1@qq.com', age=23}
Student{id=1005, name='Arthur East ', email='asd2@qq.com', age=23}
Student{id=1006, name='Adie ', email='adi1@qq.com', age=15}
Student{id=1007, name='ASA ', email='as@qq.com', age=30}
Student{id=1008, name='Adie ', email='adi2@qq.com', age=15}
*/

The second way

In sql statements, organize the contents of like

Format of sql statement like: where name like "%" #{name} "%"

Note: "%" #{name} "%", there is a space between "%" and #{name}

dao interface

// like the second way
List<Student> selectLikeTwo(@Param("name") String name);

mapper file

<!--   like The second way-->
<!--Corresponding sql Statement is:select * from student where name like "%" ? "%" -->
<select id="selectLikeTwo" resultType="com.nguyenxb.entity.Student">
        select * from student where name like "%" #{name} "%"
    </select>

test

@Test
   public void testSelectLikeTwo(){
       // Get sqlSession operation data
       SqlSession sqlSession = MyBatisUtil.getSqlSession();
       // Use proxy mode
       StudentDao dao = sqlSession.getMapper(StudentDao.class);
       String name = "Ah";
       List<Student> students = dao.selectLikeTwo(name);
       students.forEach(student -> System.out.println(student));
       sqlSession.close();
   }
/* output
Student{id=1004, name='Arthur East ', email='asd1@qq.com', age=23}
Student{id=1005, name='Arthur East ', email='asd2@qq.com', age=23}
Student{id=1006, name='Adie ', email='adi1@qq.com', age=15}
Student{id=1007, name='ASA ', email='as@qq.com', age=30}
Student{id=1008, name='Adie ', email='adi2@qq.com', age=15}
*/

Chapter 4 dynamic SQL

• What is dynamic SQL:

Dynamic SQL: judge the conditions through various tags provided by mybatis to realize dynamic splicing of SQL statements. The condition judgment expression here is OGNL expression. The commonly used dynamic SQL tags are < if >, < where > < choose >, < foreach >, etc. the syntax format is similar to JSTL

• explain

The method of the same dao can represent different sql statements according to different conditions, mainly because the where part changes

When using dynamic sql, the formal parameters of dao methods use java objects

• When to use dynamic sql?

Using dynamic sql in multi condition query

Entity symbol table

Symbol	explain	html entity
<	less than	&lt;
>	greater than	&gt;
<=	Less than or equal to	&lt;=
>=	Greater than or equal to	&gt;=

4.1 if label

grammar

Note: only if, not else

<if test="boolean Judgment result">
    sql code
</if>

Basic Usage

mapper file

<select id="selectStudent" resultType="com.nguyenxb.entity.Student">
    select * from student  
    <if test="condition">
        sql Statement 1
    </if>
     <if test="condition">
        sql Statement 2
    </if>
</select>

sql statement: select * from student is called the main sql statement, and sql statement 1 and sql statement 2 in the if tag are called partial sql statements. There can be multiple if tags

When the < if > tag meets the conditions, some sql statements in the < if > tag will be spliced after the main sql statement

example

dao interface

// if when using dynamic sql, the parameters use objects
List<Student> selectIf(Student student);

mapper file

<!--if
    test: Use the property value of the object as a condition
-->
    <select id="selectIf" resultType="com.nguyenxb.entity.Student">
        select * from student
        where
        <if test="name != null and name !=''">
            name = #{name}
        </if>
        <if test="age > 0">
            or age &lt; #{age}
        </if>
    </select>

test

    @Test
    public void selectIf(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentDao dao = sqlSession.getMapper(StudentDao.class);
        Student student = new Student();
         // Test 1: the query is successful: the query name is ADI or younger than 20
        // sql statement generated by mybatis: select * from student where name =? or age < ?
        student.setName("ADI");
        student.setAge(20);

        // Test 2: query succeeded
        // sql statement generated by mybatis: select * from student where name =?
//        student.setName("ADI");

        // Test 3: query failed, sql statement syntax error:
        //sql statement generated by mybatis: select * from student where or age > 20
//        student.setName(null);
//        student.setAge(20);

        List<Student> students = dao.selectIf(student);
        students.forEach(stu -> System.out.println(stu));
        sqlSession.close();
    }

4.2 where label

When using the if tag, it is easy to cause syntax errors in sql statements. Using the where tag can solve the syntax problems caused by if

When using the WHERE tag, there are one or more if tags inside. When an if tag determines that the condition is true, the WHERE tag will be converted to the WHERE keyword and attached to the back of the main sql statement. If none of the conditions of the if tag is true, the WHERE tag and the if inside will be ignored. The WHERE tag will delete the or | and directly connected to it

grammar:
<where>
    <if test="Condition 1">sql Statement 1</if>
    <if test="Condition 2">sql Statement 2</if>
</where>

dao interface

// where tag
List<Student> selectWhere(Student student);

mapper file

<select id="selectWhere" resultType="com.nguyenxb.entity.Student">
 select * from student
     <where>
         <if test="name != null and name !=''">
             or name = #{name}
         </if>
         <if test="age > 0">
             or age &gt; #{age}
         </if>
     </where>
 </select>

test

      @Test
    public void selectWhere(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentDao dao = sqlSession.getMapper(StudentDao.class);
        Student student = new Student();
         // Test 1: the query is successful, and the query name is ADI's or older than 20
          // sql statement generated by mybatis: select * from student where name =? or age > ?
        student.setName("ADI");
        student.setAge(20);

          // Test 2: query succeeded
          //sql statement generated by mybatis: select * from student WHERE name =?
//          student.setName("ADI");

        // Test 3: the query is successful, and the query age is greater than 20
          // sql statement generated by mybatis: select * from student where age >?
        student.setName(null);
        student.setAge(20);

        List<Student> students = dao.selectWhere(student);
        students.forEach(stu -> System.out.println(stu));
        sqlSession.close();
    }

4.3 foreach cycle

java splicing sql statement: query student information with id in idList

@Test
    public void testForEach(){
        List<Integer> idList = new ArrayList<>();
        idList.add(1001);
        idList.add(1002);
        idList.add(1003);

        // Splicing sql statements
        StringBuilder sb = new StringBuilder();
        sb.append("select * from student where id in ");
        sb.append("("); // Character at the beginning of the loop
        // Append data of idList to sql string
        for (int i = 0; i < idList.size(); i++) {
            // Get members in idList
            Integer item = idList.get(i);// Members in the collection
            sb.append(item);
            sb.append(",");//Separator between collection members
        }
        // Delete the last comma after the data
        sb.deleteCharAt(sb.length()-1);
        sb.append(")");//Character at the end of the loop
        System.out.println(sb);
// Output: select * from student where id in (100110021003)
    }

Syntax format:
 <foreach collection="Collection type" open="Start character" close="Ending character" item="Members in the collection" separator="Separator between collection members">
     #{value of item}
                    
</foreach>

Label properties:
collection: Indicates whether the object of the loop is an array or an array list aggregate. 
	If dao The formal parameter of an interface method is an array, be collection="array" .
	If dao The formal parameters of the interface method are List, be collection = "list".
open : Character at the beginning of the loop. Namely :  sb.append("(");
close: Character at the end of the loop. Namely :  sb.append(")");
item : Collection member, Custom traversal. Namely :  Integer item = idList.get(i);
separator : Separator between collection members. Namely :  sb.append(",");
#Value of {item}: get the value of the collection member

The < foreach > tag is used to traverse arrays and collections. Note that:

• Collection indicates the collection type to be traversed, such as list, array, etc
• Open, close and separator are SQL splicing of traversal content
• Generally used in in statements

The first way to use foreach

dao interface definition

List<Student> selectForeach1(List<Integer> idList);

mapper file

    <select id="selectForeach1" resultType="com.nguyenxb.entity.Student">
        select * from student
        <if test="list != null and list.size > 0">
            where id in
            <foreach collection="list" open="(" close=")" separator="," item="myId">
                #{myId}
            </foreach>
        </if>

    </select>

test

@Test
public void selectForeach1(){
    List<Integer> idList = new ArrayList<>();
    idList.add(1001);
    idList.add(1002);
    idList.add(1003);

    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    StudentDao dao = sqlSession.getMapper(StudentDao.class);
    // sql statement generated by mybaitis: select * from student where id in (?,?,?) 
    List<Student> students = dao.selectForeach1(idList);
    students.forEach(stu-> System.out.println(stu));
    sqlSession.close();

}

Use foreach the second way

dao interface definition

List<Student> selectForeach2(List<Student> studentList);

mapper file, in which #{stu.id} indicates that the value of id attribute of student object is retrieved. The same is true for others

<select id="selectForeach2" resultType="com.nguyenxb.entity.Student">
       select * from student
       <if test="list != null and list.size > 0">
           where id in
           <foreach collection="list" open="(" close=")" separator="," item="stu">
               #{stu.id}
           </foreach>
       </if>

   </select>

test

@Test
   public void selectForeach2(){
       List<Student> idList = new ArrayList<>();
    Student student = new Student();
    Student student1 = new Student();
    student.setId(1004);
    student1.setId(1005);
    idList.add(student);
    idList.add(student1);

    SqlSession sqlSession = MyBatisUtil.getSqlSession();
       StudentDao dao = sqlSession.getMapper(StudentDao.class);
       List<Student> students = dao.selectForeach2(idList);
       students.forEach(stu-> System.out.println(stu));
       sqlSession.close();

   }

4.4 code snippets

The sql tag identifies a piece of sql code, which can be table name, several fields and where conditions. The contents of the sql tag can be reused in other places

Usage:

stay mapper Defined in file sql code snippet: <sql id="Unique string">part sql sentence </sql>

In other locations,use include Tag to reference a snippet of code

For example, the mapper of the case using the first method of foreach can be modified as follows to complete the query

<!--Code snippet definition --> 
<sql id="SelectStudentField">
        id,name,email,age
    </sql>

    <sql id="selectStudent">
         select <include refid="SelectStudentField"/> from student
    </sql>

<!--Using code snippets-->
<select id="selectForeach1" resultType="com.nguyenxb.entity.Student">
    <include refid="selectStudent"></include>
    <if test="list != null and list.size > 0">
        where id in
        <foreach collection="list" open="(" close=")" separator="," item="myId">
            #{myId}
        </foreach>
    </if>

</select>

Chapter V MyBatis configuration file

5.1 configuration file

There are two categories of mybatis configuration files:

1. mybatis master profile

   Description: the mybatis master configuration file provides the global settings of mybatis, including the contents, logs, data sources, and the location of mapper files

2. mapper file for mybatis

   Description: the mapper file of mybatis is used to write sql statements. Generally, a mapper file is written for a table

5.2 setting part

settings is the global setting of mybatis, which affects the operation of the whole mybatis. This setting is generally the default value

<settings>
  <setting name="cacheEnabled" value="true"/>
  <setting name="lazyLoadingEnabled" value="true"/>
  <setting name="multipleResultSetsEnabled" value="true"/>
  <setting name="useColumnLabel" value="true"/>
  <setting name="useGeneratedKeys" value="false"/>
  <setting name="autoMappingBehavior" value="PARTIAL"/>
  <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
  <setting name="defaultExecutorType" value="SIMPLE"/>
  <setting name="defaultStatementTimeout" value="25"/>
  <setting name="defaultFetchSize" value="100"/>
  <setting name="safeRowBoundsEnabled" value="false"/>
  <setting name="mapUnderscoreToCamelCase" value="false"/>
  <setting name="localCacheScope" value="SESSION"/>
  <setting name="jdbcTypeForNull" value="OTHER"/>
  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>

5.2 typealias alias

Set alias

<typeAliases>
  <typeAlias alias="Author" type="domain.blog.Author"/>
  <typeAlias alias="Blog" type="domain.blog.Blog"/>
  <typeAlias alias="Comment" type="domain.blog.Comment"/>
  <typeAlias alias="Post" type="domain.blog.Post"/>
  <typeAlias alias="Section" type="domain.blog.Section"/>
  <typeAlias alias="Tag" type="domain.blog.Tag"/>
</typeAliases>

You can also specify a package name. MyBatis will search for the required Java beans under the package name, such as:

<typeAliases>
  <package name="domain.blog"/>
</typeAliases>

5.3 configuration environment

environments: Environmental label, Multiple can be configured in it environment
	attribute: default,Must be some environment of id Attribute value.express mybatis Default connected database.
environment : Represents the connection information of a database.
	attribute: id Custom environment representation.Unique value
transactionManager: Transaction manager
	attribute: type Represents the type of transaction manager
	Attribute value: 
		(1) jdbc: use connction object,from mybatis Complete the transaction by yourself.
		(2)MANAGED: Administration,Indicates that the digital processing is handed over to the container implementation(The transaction submission is completed by other software,RollBACK )
dataSource: data source, establish Connection object,Connect to database.
	attribute: type Type of data source
	Attribute value:
		(1) POOLED, mybatis Will be created in memory PooledDataSource type,Manage multiple Connection Connection object,Connection pool is used.(Often use this)
		(2) UNPOOLED,Do not use connection pool,mybatis Create a UnPooledDataSource This class,Each execution sql Create under statement Connection object,In execution sql sentence,Last closed Connection.
		(3) JNDI: java Naming and directory services


<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC" />
        <!--Configure data sources: establish Connection object-->
        <dataSource type="POOLED">
            <!--driver:Driven content-->
            <property name="driver" value="com.mysql.jdbc.Driver"/>
            <!--Connecting to the database url: Cannot be used directly & Symbol,To convert it to html Entity is &amp; Represents & Symbol -->
            <property name="url" value="jdbc:mysql://localhost:3306/ssm?
            useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf-8"/>
            <!--user name-->
            <property name="username" value="root"/>
            <!--User password-->
            <property name="password" value="root"/>
        </dataSource>
    </environment>
</environments>

5.4 using the attribute profile of the database

The database configuration information needs to be put into a separate file for independent management. The extension of this file is properties. In this file, the user-defined format of key=value is used to represent the data

Use steps:

1. In the resources directory, create xxx.properties
2. In the file, use the format of key=value to define data
   • For example: jdbc.url = jdbc:mysql://localhost:3306/springdb
3. In the mybatis main configuration file, use the properties tag to reference the external property configuration file
4. Where the value is used, use ${key} to obtain the value corresponding to the key (that is, the value to the right of the equal sign)

Example:

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm?
  useSSL=true&useUnicode=true&characterEncoding=utf-8"
jdbc.username=root
jdbc.password=root

mybatis.xml

Set the external configuration file at the position of the first tag in the configuration tag in the mybatis file

<!--    Use external properties profile
        resource: Specifies the file of a property under the classpath
-->
    <properties resource="jdbc.properties"/>

Use the contents in the external attribute configuration file in mybatis.xml. The usage is as follows:

<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"/>
        <!--Configure data sources: establish Connection object-->
        <dataSource type="POOLED">
            <!--driver:Driven content-->
            <property name="driver" value="${jdbc.driver}"/>
            <!--Connecting to the database url: Cannot be used directly & Symbol,To convert it to html Entity is &amp; Represents & Symbol -->
            <property name="url" value="${jdbc.url}"/>
            <!--user name-->
            <property name="username" value="${jdbc.username}"/>
            <!--User password-->
            <property name="password" value="${jdbc.password}"/>
        </dataSource>
    </environment>
</environments>

5.5 mapper label

Use the mapper label to specify the location of other mapper files

Mapper tags use two common formats: be careful not to load mapper repeatedly

<mappers>
<!--        The first way, resource="mapper Path to file
            advantage:Clear documents.The loaded file is explicit.The location of files is flexible.
            shortcoming:Many documents and notes,The amount of code will be large,Difficult management.
"-->
        <mapper resource="com\nguyenxb\dao\StudentDao.xml"/>
<!--        The second way, use<package>
                name : Package name, mapper Package name of the file.
                characteristic:Take everything in this bag mapper file,One load.

                Use requirements:
                1.mapper Documents and dao Interfaces are in the same directory
                2.mapper Documents and dao The interface name is exactly the same
-->
        <package name="com.nguyenxb.dao"/>
    </mappers>

Chapter 6 PageHelper

PageHelper does data paging. Add the paged sql content after your select statement. If you use mysql database, he adds the limit statement in select * from student

Use steps:

1. Add pagehelper dependency

   <dependency>
     <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper</artifactId>
     <version>5.1.10</version>
   </dependency>
   

2. In the main configuration file of mybatis, add the plugins declaration. Note: add it before the environments tab

   <!--    Declaration plug-in-->
       <plugins>
           <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
       </plugins>
   

example:

dao interface definition

// pageHelper usage
List<Student> selectAllStudents();

mapper file

<select id="selectAllStudents" resultType="com.nguyenxb.entity.Student">
    select * from student order by id
</select>

test

@Test
public void testSelectAllStudents(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    StudentDao dao = sqlSession.getMapper(StudentDao.class);
    //Method calling PageHelper
    PageHelper.startPage(1,3);
    List<Student> students = dao.selectAllStudents();
    students.forEach(stu-> System.out.println(stu));
    sqlSession.close();
}

Test output:

PageHelper's method was not called, output:

sql statement generated by mybatis: select * from student order by id

/*
Student{id=1001, name='Zhang San ', email='123456@qq.com', age=12}
Student{id=1003, name='Ha ha ', email='haha@qq.ocm', age=20}
Student{id=1004, name='Arthur East ', email='asd1@qq.com', age=23}
Student{id=1005, name='Arthur East ', email='asd2@qq.com', age=23}
Student{id=1006, name='Adie ', email='adi1@qq.com', age=15}
Student{id=1007, name='ASA ', email='as@qq.com', age=30}
Student{id=1008, name='Adie ', email='adi2@qq.com', age=15}
*/

Call the PageHelper method and output:

sql statement generated by mybatis:

When the number of pages is not the first page, that is, as in test 2, the sql statement generated by mybatis:

​ select * from student order by id LIMIT ?, ?

When the number of pages is the first page, that is, as in tests 1 and 3, the sql statement generated by mybatis:

​ select * from student order by id LIMIT ?

/*
Test 1: 
Call the method of PageHelper, that is, PageHelper.startPage(1,3) output:

Student{id=1001, name='Zhang San ', email='123456@qq.com', age=12}
Student{id=1003, name='Ha ha ', email='haha@qq.ocm', age=20}
Student{id=1004, name='Arthur East ', email='asd1@qq.com', age=23}

Test 2: 
Call the method of PageHelper, that is, PageHelper.startPage(2,3) output:

Student{id=1005, name='Arthur East ', email='asd2@qq.com', age=23}
Student{id=1006, name='Adie ', email='adi1@qq.com', age=15}
Student{id=1007, name='ASA ', email='as@qq.com', age=30}	

Test 3:
Call the method of PageHelper, that is, PageHelper.startPage(1,4) output:

Student{id=1001, name='Zhang San ', email='123456@qq.com', age=12}
Student{id=1003, name='Ha ha ', email='haha@qq.ocm', age=20}
Student{id=1004, name='Arthur East ', email='asd1@qq.com', age=23}
Student{id=1005, name='Arthur East ', email='asd2@qq.com', age=23}
*/