This paper is a dynamic node MyBatis tutorial My study notes.

Chapter I

1. Three tier architecture

(1) Functions of three layers

• User Interface Layer: accepts user data and displays the processing results of requests, including jsp, html, servlet, etc. Corresponding controller package;

• Business Logic Layer: accept the data passed from the presentation layer, check the data, calculate the business logic, and call the data access layer to obtain the data. Corresponding service package;

• Data Access Layer (DAL): also known as persistence layer, it deals with databases. Corresponding DAO package.

(2) Class interaction in three layers

Users use presentation layer -- > business logic layer -- > data access layer -- > database

(3) Three layer corresponding processing framework

Presentation layer -- servlet -- spring MVC framework

Business logic layer - service class - Spring Framework

Data access layer -- DAO class -- MyBatis framework

2. JDBC programming

(1) JDBC instance

public void findStudent() {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try { //Register mysql driver
        Class.forName("com.mysql.jdbc.Driver");
        // Basic information of connection data: url, username, password
        String url = "jdbc:mysql://localhost:3306/springdb";
        String username = "root";
        String password = "123456";
        // Create connection object
        conn = DriverManager.getConnection(url, username, password);
        // Save query results
        List<Student> stuList = new ArrayList<>();
        // Create a Statement to execute sql statements 
        stmt = conn.createStatement();
        // Execute a query, create a recordset,
        rs = stmt.executeQuery("select * from student");
        while (rs.next()) {
            Student stu = new Student();
            stu.setId(rs.getInt("id"));
            stu.setName(rs.getString("name"));
            stu.setAge(rs.getInt("age"));
            // Take out the data from the database, turn it into a Student object, and encapsulate it into a List collection
            stuList.add(stu);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            //close resource 
            if (rs != null) ;
            {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

(2) JDBC disadvantages

• More code and low development efficiency
• You need to pay attention to the creation and destruction of Connection, Statement and ResultSet objects
• The results of the ResultSet query need to be encapsulated as a List
• Too many duplicate codes
• Business code is mixed with database operations

3. Overview of mybatis framework

(1) MyBatis function

SQL mapping: you can map a row of data in the database table to a java object. Operating this object is equivalent to operating the data in the table.

Data access: add, delete, modify and query the database

• Driver of registration database
• Create Connection, Statement and ResultSet objects that must be used in JDBC
• Obtain sql from xml, execute sql statements, and convert ResultSet results into java objects
• close resource

(2) Introductory case

The project name is 01 hellomybatis, and the directory structure is as follows:

1) Create student table and insert data

Create a student table in the mybatis Library of MySQL:

CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(80) NOT NULL,
  `email` varchar(100) NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`,`age`)
) ENGINE=InnoDB;


INSERT INTO `student` VALUES ('1001', 'Li Si', 'lisi@qq.com', '11');
INSERT INTO `student` VALUES ('1002', 'Zhang San', 'zhangsan@126.com', '12');

2) Add maven's MyBatis coordinates and MySQL driven coordinates

Create maven project and add the following dependencies in pom.xml file

<!-- mybatis rely on -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.5</version>
</dependency>

<!-- mysql drive -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>

<!-- lombok rely on -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>

The version numbers can be different. The @ Data annotation provided by lombok can add get, set, toString methods to entity classes to simplify the code.

3) Create entity class Student

package cn.ecnu.domain;

import lombok.Data;

// The recommendation is the same as the table name, which is convenient for memory
@Data
public class Student {
    // Define the attribute. At present, the attribute name and column name are required to be consistent
    private Integer id;
    private String name;
    private String email;
    private Integer age;
}

4) Create the DAO interface of the persistence layer and define the method of operating the database

package cn.ecnu.dao;

import cn.ecnu.domain.Student;

import java.util.List;

// Interface operation student table
public interface StudentDAO {

    // Query all data of student table
    public List<Student> selectStudents();
}

5) Create a configuration file used by MyBatis

It is also called SQL mapping file: write SQL statements. Generally, one table and one SQL mapping file are xml files

<?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="cn.ecnu.dao.StudentDAO">
    <!--
        id: To execute sql Unique identification of the statement, mybatis Will use this id To find the to execute sql Statement, which can be customized, but requires the method name in the interface

        resultType: Indicates the result type, yes sql Obtained after statement execution ResultSet，Traverse this ResultSet obtain java The type of the object, the value, and the fully qualified name of the write type
    -->
    <select id="selectStudents" resultType="cn.ecnu.domain.Student">
        select id,name,email,age from student order by id;
    </select>
</mapper>

<!--
SQL Mapping files: Writing sql sentence, mybatis Will perform these sql
1. Specify constraint file
    <!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

    mybatis-3-mapper.dtd Is the name of the constraint file and the extension is dtd of

2. Function of constraint file: limit and check that the labels and attributes appearing in the current file must comply with mybatis Requirements

3. mapper It is the root label of the current file and is required
   namespace: Namespace, unique value, can be a custom string. Required use DAO Fully qualified name of the interface

4. In the current file, you can use specific labels to represent specific operations of the database
    <select>: Represents executing a query
    <update>: Indicates the operation of updating the database. What is written in it is update sentence
    <insert>: It means insert. What's written in it is insert sentence
    <delete>: It means delete. What's written in it is delete sentence
-->

6) Create a master profile for MyBatis

A project has a main configuration file, which provides the link information of the database and the location information of the sql mapping file

<?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>
    <!--
        Environment configuration: link information for data
        default: Must be with a environment of id The same value, used to tell mybatis Using the connection information by database, that is, which database is accessed
        -->
    <environments default="mysql">
        <!--
            environment: A database information configuration, environment
            id: Name of the data source, a unique value, user-defined
        -->
        <environment id="mysql">
            <!--
                transationManager: mybatis Transaction type
                    type: JDBC(Indicates use jdbc Medium Connection Object commit,rollback (transaction processing)
            -->
            <transactionManager type="JDBC"/>
            <!--
                dataSource: Data source to link databases
                    type: POOLED，Represents the connection pool using the database
            -->
            <dataSource type="POOLED">
                <!--Four elements of connecting to a database-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <!-- sql Location of the mapping file -->
    <mappers>
        <!--One mapper The tag specifies the location of a file and the path information starting from the classpath-->
        <mapper resource="cn/ecnu/dao/StudentDAO.xml"/>
    </mappers>

</configuration>

<!--
    mybatis Main configuration file: mainly defines the configuration information of data, sql Location of the mapping file

    1. Constraint file
        <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">

       mybatis-3-config.dtd: Name of the constraint file

    2. configuration: Root label

-->

After writing the configuration file of mybatis, the resources under resources will be automatically packaged into the target directory during maven's compile, but StudentDAO.xml will not be automatically packaged. You need to set the location of the configuration file to be scanned in pom.xml

<build>
    <resources>
        <resource>
            <!-- Directory where -->
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
    </resources>
</build>

7) Create and use MyBatis class

Accessing the database through MyBatis

package cn.ecnu;

import cn.ecnu.domain.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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MyApp {

    public static void main(String[] args) throws IOException {
        // Access mybatis to read student data
        // 1. Define the name of the mybatis master configuration file, starting from the root of the classpath (target/classes)
        String config="mybatis.xml";
        // 2. Read the file represented by config
        InputStream in = Resources.getResourceAsStream(config);
        // 3. Create SqlSessionFactoryBuilder object
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
        // 4. Create SqlSessionFactory object
        SqlSessionFactory factory=builder.build(in);
        // 5. [important] get the SqlSession object and get the SqlSession from the SqlSessionFactory
        SqlSession sqlSession = factory.openSession();
        // 6. [important] specify the id of the sql statement to be executed, and the id value of the namespace + ". + tag in the sql mapping file
        String sqlId="cn.ecnu.dao.StudentDAO"+"."+"selectStudents";
        // 7. Execute sql statement and find the statement through sqlId
        List<Student> studentList=sqlSession.selectList(sqlId);
        // 8. Output results
        studentList.forEach(System.out::println);
        // 9. Close the SqlSession object
        sqlSession.close();
    }
}

The output result of the run is the two pieces of data inserted into the database.

(3) Insert data

Add and insert the corresponding method in the StudentDAO.java interface:

// Insertion method
/*
* Parameter: student is the inserted data
* Return value: number of records inserted
* */
public int insertStudent(Student student);

Add the sql statement corresponding to the insertion method in StudentDAO.xml:

<insert id="insertStudent">
    insert into student values(#{id},#{name},#{email},#{age});
</insert>

Where #{attribute name} is the attribute value obtained from the input parameter student class of the insertStudent method.

Steps 6, 7 and 8 of MyApp.java need to be modified, and others remain unchanged:

// 6. [important] specify the id of the sql statement to be executed, and the id value of the namespace + ". + tag in the sql mapping file
String sqlId = "cn.ecnu.dao.StudentDAO" + "." + "insertStudent";
// 7. Execute sql statement and find the statement through sqlId
Student student = new Student();
student.setId(1003);
student.setName("Fei Zhang");
student.setEmail("zhangfei@sina.com");
student.setAge(21);
int num = sqlSession.insert(sqlId, student);
// mybatis does not automatically commit transactions by default, so you should manually commit transactions after insert, update and delete
sqlSession.commit();
// 8. Output results
System.out.println("implement insert Results: " + num);

Chapter II

1. Introduction to main categories

(1) Resources

A class in mybatis, which is responsible for reading the main configuration file

InputStream in = Resources.getResourceAsStream(config);

(2) SqlSessionFactoryBuilder

Create SqlSessionFactory object

SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
SqlSessionFactory factory=builder.build(in);

(3) SqlSessionFactory

For heavyweight objects, the program takes a long time to create an object and uses more resources. In the whole project, one is enough

The implementation class of SqlSessionFactory interface is DefaultSqlSessionFactory

SqlSessionFactory function: get SqlSession object

SqlSession sqlSession = factory.openSession();

openSession() method description:

• openSession(): if there are no parameters, obtain the SqlSession object of non auto commit transaction
• openSession(boolean): when the input parameter is true, obtain the SqlSession of the automatically committed transaction; otherwise, obtain the SqlSession of the non automatically committed transaction

(4) SqlSession

SqlSession interface: defines methods for operating data, such as selectOne(), selectList(), insert(), update(), delete(), commit(), rollback()

The implementation class of SqlSession interface is DefaultSqlSession

Usage requirements: the SqlSession object is not thread safe and needs to be used inside the method. Before executing the sql statement, use openSession() to obtain the SqlSession object. After executing the sql statement, you need to execute SqlSession.close() to close it, so as to ensure that its use is thread safe

2. Traditional DAO usage

Add a corresponding implementation class for the StudentDAO interface, and put the business logic code into the implementation class instead of directly into the main method or unit test method. The project name is 02 mybatisutils, and the code organization structure is as follows:

The contents of the implementation class file StudentDAOImpl.java are:

package cn.ecnu.dao.impl;

import cn.ecnu.dao.StudentDAO;
import cn.ecnu.domain.Student;
import cn.ecnu.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class StudentDAOImpl implements StudentDAO {

    @Override
    public List<Student> selectStudents() {
        // Get SqlSession
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //[important] specify the id of the sql statement to be executed and the id value of the namespace + ".. + tag in the sql mapping file
        String sqlId = "cn.ecnu.dao.StudentDAO" + "." + "selectStudents";
        // Execute the sql statement and find the statement through sqlId
        List<Student> studentList = sqlSession.selectList(sqlId);
        // Close the SqlSession object
        sqlSession.close();
        return studentList;
    }

    @Override
    public int insertStudent(Student student) {
        // Get SqlSession
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //[important] specify the id of the sql statement to be executed and the id value of the namespace + ".. + tag in the sql mapping file
        String sqlId = "cn.ecnu.dao.StudentDAO" + "." + "insertStudent";
        // Execute the sql statement and find the statement through sqlId
        int num = sqlSession.insert(sqlId, student);
        // mybatis does not automatically commit transactions by default, so you should manually commit transactions after insert, update and delete
        sqlSession.commit();
        return num;
    }
}

The contents in the unit test file StudentTest.java are:

package cn.ecnu;

import cn.ecnu.dao.StudentDAO;
import cn.ecnu.dao.impl.StudentDAOImpl;
import cn.ecnu.domain.Student;
import org.junit.Test;

import java.util.List;

public class StudentTest {

    @Test
    public void selectStudentsTest() {
        StudentDAO dao = new StudentDAOImpl();
        /*
         * List<Student> studentList = dao.selectStudents(); call
         * 1. dao Object. Lei Xiang is SudentDAO. The fully qualified name is cn.ecnu.dao.StudentDAO
         *   A fully qualified name is the same as a namespace
         *
         * 2. Method name, selectStudents. This method is the id value in the mapper file
         *
         * 3. The method of SqlSession to be called by MyBatis can also be determined by the return value of the method in dao
         *   If the return value is List, the SqlSession.selectList() method is called
         *   If the return value is not List, such as int, it will be called if the labels in mapper are < Insert >, < update >
         *   SqlSession insert, update, etc
         *
         * MyBatis Dynamic proxy: MyBatis obtains the information of executing sql statements according to dao's method call.
         *   MyBatis According to the dao interface, create an implementation class of dao interface and create the object of this class,
         *   Complete the SqlSession call method to access the database.
         * */
        List<Student> studentList = dao.selectStudents();
        studentList.forEach(System.out::println);
    }

    @Test
    public void insertStudentTest() {
        Student student = new Student();
        student.setId(1004);
        student.setName("Liu Bei");
        student.setEmail("liubei@sina.com");
        student.setAge(22);
        StudentDAO dao = new StudentDAOImpl();
        int num = dao.insertStudent(student);
        System.out.println("insert Execution result of operation:" + num);
    }
}

Other documents are consistent with the previous ones.

Chapter III

1. Dynamic agent

Use SqlSession.getMapper(dao interface. class) to get the object of this dao interface.

The project name is 03 proxy Dao, and the directory structure is as follows:

Compared with 02 mybatisutils, the implementation class file StudentDAOImpl.java is deleted and StudentTest.java is modified to:

import cn.ecnu.dao.StudentDAO;
import cn.ecnu.domain.Student;
import cn.ecnu.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class StudentTest {

    @Test
    public void selectStudentsTest() {
        /*
         * Use the dynamic proxy mechanism of mybatis and sqlsession. Getmapper (interface method)
         * getMapper It can obtain the implementation class object corresponding to dao interface
         * */
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        StudentDAO dao = sqlSession.getMapper(StudentDAO.class);
        // Call dao's method to perform database operations
        List<Student> students = dao.selectStudents();
        for (Student stu : students) {
            System.out.println("student: " + stu);
        }
    }

    @Test
    public void insertStudentTest() {
        Student student = new Student();
        student.setId(1005);
        student.setName("Guan Yu");
        student.setEmail("guanyu@sina.com");
        student.setAge(22);
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        StudentDAO dao = sqlSession.getMapper(StudentDAO.class);
        int num = dao.insertStudent(student);
        System.out.println("insert Execution result of operation:" + num);
    }
}

2. Pass in parameters

Transfer data from java code to sql statement of mapper file. parameterType: an attribute written in the mapper file, which represents the attribute in the dao interface

(1) A simple type parameter

mybatis calls both basic data types and String types of java simple types. Get the value of a parameter of a simple type in the mapper file, using #{any character}

The project name is 04 param, and the directory structure is:

The content of StudentDAO.java is modified as follows:

package cn.ecnu.dao;

import cn.ecnu.domain.Student;

import java.util.List;

// Interface operation student table
public interface StudentDAO {
    // Query by id
    /*
     * A simple type parameter
     * Simple type: mybatis calls both the basic data type and String type of java simple types
     * Get the value of a parameter of a simple type in the mapper file, using #{any character}
     * */
    public Student selectStudentById(Integer id);
}

The content in StudentDAO.xml is modified as follows:

<?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="cn.ecnu.dao.StudentDAO">
    <!--
        parameterType: dao Data type of method parameter in interface
        parameterType The value of is java Data type fully qualified name or mybatis Defined alias
        For example: parameterType="java.lang.Integer"
              parameterType="int"

        be careful: parameterType Not mandatory, mybatis The type of interface parameters can be returned through the reflection mechanism, so it can not be. Generally, it is not written

        use#After {}, when mybatis executes sql, it uses the PreparedStatement object in jdbc
        from mybatis Execute the following code:
        1. mybatis establish connection,PreparedStatement object
            Stirng sql = "select id,name,email,age from student where id=?;"
            PreparedStatement pst = conn.preparedStatement(sql);
            pst.setInt(1.1001);

        2. implement sql Package as resultType="cn.ecnu.domain.Student"This object
            ResultSet rs = ps.executeQuery();
            Student student = null;
            while(rs.next()){
                // Take a row of data from the database table and store it in a java object attribute
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setEmail(rs.getString("email"));
                student.setAge(rs.getInt("age"));
            }

            // Return value assigned to dao method call
            return student;
    -->
    <select id="selectStudentById" parameterType="java.lang.Integer" resultType="cn.ecnu.domain.Student">
        select id,name,email,age from student where id=#{id};
    </select>
</mapper>

The content in StudentTest.java is modified as follows:

package cn.ecnu;

import cn.ecnu.dao.StudentDAO;
import cn.ecnu.domain.Student;
import cn.ecnu.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class StudentTest {

    @Test
    public void selectStudentByIdTest() {
        /*
         * Use the dynamic proxy mechanism of mybatis and sqlsession. Getmapper (interface method)
         * getMapper It can obtain the implementation class object corresponding to dao interface
         * */
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        StudentDAO dao = sqlSession.getMapper(StudentDAO.class);
        // Call dao's method to perform database operations
        Student student = dao.selectStudentById(1002);
        System.out.println(student);
    }
}

(2) Multiple parameters - use @ Param

Use @ Param to name the parameter. Add in StudentDAO.java:

/*
* Multiple parameters: name the parameter, and add @ param (user defined parameter name) before the formal parameter definition
* */
List<Student> selectMultiPram(@Param("myname") String name, @Param("myage") Integer age);

Add in StudentDAO.xml:

<!-- Multiple parameters, using@Param name -->
<select id="selectMultiPram" resultType="cn.ecnu.domain.Student">
    select id,name,email,age from student where name=#{myname} or age=#{myage};
</select>

Add in StudentTest.java:

@Test
public void selectMultiParmTest() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);
    // Call dao's method to perform database operations
    List<Student> studentList = dao.selectMultiPram("Fei Zhang", 22);
    for (Student student : studentList) {
        System.out.println(student);
    }
}

(3) Multiple parameters - use object

Add the entity class cn.ecnu.vo.QueryParam.java of query parameters, which is as follows:

package cn.ecnu.vo;

import lombok.Data;

@Data
public class QueryParam {
    private String paramName;
    private Integer paramAge;
}

Add in StudentDAO.java:

/*
* Multiple parameters, using java objects as parameters of methods in the interface
* */
List<Student> selectMultiObject(QueryParam param);

Add in StudentDAO.xml:

<!--
   Multiple parameters, using java Object as the actual value of the parameter
   Use object syntax:#{property name, javaType = type name, JDBC type = data type}
               javaType: finger java Data type of property in
               jdbcType: Refers to the data type in the database
               For example:#{paramName, javaType=java.lang.String, jdbcType=VARCHAR}

   Simplified method:#{attribute name}. The values of javaType and JDBC type can be obtained by mybatis reflection. It is unnecessary to provide them
-->
<select id="selectMultiObject" resultType="cn.ecnu.domain.Student">
    select id,name,email,age from student where
    name=#{paramName, javaType=java.lang.String, jdbcType=VARCHAR}
    or age=#{paramAge, javaType=java.lang.Integer, jdbcType=INTEGER};
</select>

Add in StudentTest.java:

@Test
public void selectMultiObjectTest() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);

    QueryParam param = new QueryParam();
    param.setParamName("Zhang San");
    param.setParamAge(21);

    // Call dao's method to perform database operations
    List<Student> studentList = dao.selectMultiObject(param);
    for (Student student : studentList) {
        System.out.println(student);
    }
}

(4) Multiple parameters - by location

Use in StudentDAO.xml:

• Before mybatis 3.4: #{0}, #{1} etc
• After mybatis 3.4: #{arg0}, #{arg1} etc

It is easy to make mistakes. It is not recommended to use it.

(5) Multiple parameters - use Map

The formal parameter in StudentDAO.java is a Map

key} of #{map used in StudentDAO.xml

Just understand.

(6) The difference between #{} and ${}

• #Use? Placeholders occupy positions in sql statements. Using PreparedStatement to execute sql is efficient
• #It can avoid sql injection and is more secure
• $does not use placeholders. It is a string connection. It uses the Statement object to execute sql, which is inefficient
• $has the risk of sql injection and lacks security
• $can replace table or column names

3. Output results

(1) Result typeresulttype

mybatis executes sql statements to get java objects

resultType result type: after the sql statement is executed, the data is converted to a java object. The type of the object is arbitrary, which can be entity class, int type, etc.

Treatment method:

• mybatis executes the sql statement, then calls the class's no parameter construction method to create the object.
• mybatis assigns the specified column value of ResultSet to the property with the same name

Value of resultType:

• Fully qualified class name
• The alias of type, such as java.lang.Integer, is int

(2) Define aliases for custom types

Create a 05 returnresult project, define it in the mybatis main configuration file, and use < typealias > to define the alias. You can use custom aliases in resultType.

Add the following under the < configuration > tag in mybatis.xml:

<!-- settings: control mybatis Global behavior, printable log -->
<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

<!-- Define alias -->
<typeAliases>
    <!--
        The first way:
        You can specify a type and a custom alias
        type: Fully qualified name of the custom type
        alias: Alias (short, easy to remember)
    -->
    <typeAlias type="cn.ecnu.domain.Student" alias="student"/>

    <!--
        The second way:
        <package> name Is the package name. For all classes in the package, the class name is the alias (the class name is not case sensitive)
		When the same entity class exists in multiple different packages, problems will arise. Is fully qualified class name the safest
    -->
    <package name="cn.ecnu.domain"/>
</typeAliases>

Then change the content in resultType to student in StudentDAO.xml, and run the unit test with the same result as before.

The return type can also be set to map < object, Object >, and only one row of records can be returned. The key is the field name in the table, and the value is the value corresponding to the field.

(3) resultMap

resultMap and resultType cannot be used at the same time

Result mapping: specify the correspondence between column names and java object attributes. Usage scenario:

• Which attribute is assigned to the custom column
• When the column name and property name are inconsistent, you must use resultMap

Add in StudentDAO.java:

List<Student> selectAllStudents();

Under the mapper tag in StudentDAO.xml, add:

<!--
    use resultMap: 
    1. stay<mapper>Customize under label resultMap
    2. stay select Label use resultMap To reference the defined
-->

<!--
    custom resultMap
    id: Custom name, which represents the user-defined name resultMap
    type: java The fully qualified name of the type
-->
<resultMap id="studentMap" type="cn.ecnu.domain.Student">
    <!-- Column names and java Attribute correspondence -->
    <!-- Primary key column usage id label
         column: Field name of the table
         property: java Property name of type
     -->
    <id column="id" property="id"></id>
    <!-- Use of non primary key columns result -->
    <result column="name" property="name"></result>
    <result column="email" property="email"></result>
    <result column="age" property="age"></result>
</resultMap>

<select id="selectAllStudents" resultMap="studentMap">
    select id,name,email,age from student;
</select>

Add in StudentTest.java:

@Test
public void selectAllStudentsTest() {
    /*
     * Use the dynamic proxy mechanism of mybatis and sqlsession. Getmapper (interface method)
     * getMapper It can obtain the implementation class object corresponding to dao interface
     * */
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);
    // Call dao's method to perform database operations
    List<Student> studentList = dao.selectAllStudents();
    for (Student student : studentList) {
        System.out.println(student);
    }
}

(4) The second way to handle different field names and attribute names

Create MyStudent.java entity class:

package cn.ecnu.domain;

import lombok.Data;

// The recommendation is the same as the table name, which is convenient for memory
@Data
public class MyStudent {
    // Define the attribute. At present, the attribute name and column name are required to be consistent
    private Integer stuId;
    private String stuName;
    private String stuEmail;
    private Integer stuAge;
}

Add in StudentDAO.java:

 List<MyStudent> selectMyStudent();

Add in StudentDAO.xml:

<!-- The second processing method when the field name is different from the attribute name -->
<select id="selectMyStudent" resultType="cn.ecnu.domain.MyStudent">
    select id as stuId, name as stuName,email as stuEmail, age as stuAge from student;
</select>

Add in StudentTest.java:

@Test
public void selectMyStudentTest() {
    /*
     * Use the dynamic proxy mechanism of mybatis and sqlsession. Getmapper (interface method)
     * getMapper It can obtain the implementation class object corresponding to dao interface
     * */
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);
    // Call dao's method to perform database operations
    List<MyStudent> studentList = dao.selectMyStudent();
    for (MyStudent student : studentList) {
        System.out.println(student);
    }
}

(5) Two ways of fuzzy query

Add in StudentDAO.java:

/*
* The first kind of fuzzy query specifies the content of like in java code
* */
List<Student> selectLikeOne(String name);

/*
* The second kind of fuzzy query is spliced with% in mapper
* */
List<Student> selectLikeTwo(String name);

Add in StudentDAO.xml:

<!-- The first fuzzy query, in java Specified in code like Content of -->
<select id="selectLikeOne" resultType="cn.ecnu.domain.Student">
    select id,name,email,age from student where name like #{name};
</select>

<!-- The second kind of fuzzy query, in mapper Splicing in file like The content of,"%"And#The space between {name} must have -- >
<select id="selectLikeTwo" resultType="cn.ecnu.domain.Student">
    select id,name,email,age from student where name like "%" #{name} "%";
</select>

Add in StudentTest.java:

@Test
public void selectLikeOneTest() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);
    // Prepare the content of like
    String name = "%Lee%";
    List<Student> studentList = dao.selectLikeOne(name);
    for (Student student : studentList) {
        System.out.println(student);
    }
}

@Test
public void selectLikeTwoTest() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);
    // Prepare the content of like
    String name = "Zhang";
    List<Student> studentList = dao.selectLikeTwo(name);
    for (Student student : studentList) {
        System.out.println(student);
    }
}

Chapter 4 dynamic SQL

Dynamic sql means that the content of sql changes. Different sql statements can be obtained according to conditions, mainly the where part changes.

The implementation of dynamic sql uses the tags provided by mybatis: < if >, < where >, < foreach >, etc

1. < if > label

Syntax:

<if test="judge java Property value of the object">
    part sql sentence
</if>

Add in StudentDAO.java:

// Dynamic sql, using java objects as parameters
List<Student> selectStudentIf(Student student);

Add in StudentDAO.xml:

<!-- if
    <if: test="use java The attribute value of the object is used as the judgment condition">

    be careful where Back 1 = 1 The role of is when name Followed when empty or There will be problems
-->
<select id="selectStudentIf" resultType="cn.ecnu.domain.Student">
    select id,name,email,age from student
    where 1 = 1
    <if test="name != null and name != ''">
        and name = #{name}
    </if>
    <if test="age > 0">
        or age > #{age}
    </if>
</select>

Add in StudentTest.java:

@Test
public void selectStudentIfTest() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);

    Student student = new Student();
    student.setName("Li Si");
    student.setAge(20);

    // Call dao's method to perform database operations
    List<Student> studentList = dao.selectStudentIf(student);
    for (Student stu : studentList) {
        System.out.println(stu);
    }
}

2. < where > label

It is used to contain multiple < if > tags. When one of multiple if tags is established, < where > will automatically add a where keyword and remove the redundant and, or, etc. in the if.

Add in StudentDAO.java:

// Use of where
List<Student> selectStudentWhere(Student student);

Add in StudentDAO.xml:

<!--
    <where>It contains multiple<if>，Multiple if When one was established,<where>One will be added automatically where keyword,
    And remove if Redundant in and,or Wait.
-->
<select id="selectStudentWhere" resultType="cn.ecnu.domain.Student">
    select id,name,email,age from student
    <where>
        <if test="name != null and name != ''">
            name = #{name}
        </if>
        <if test="age > 0">
            or age > #{age}
        </if>
    </where>
</select>

Add in StudentTest.java:

@Test
public void selectStudentWhereTest() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);

    Student student = new Student();
    student.setAge(20);

    // Call dao's method to perform database operations
    List<Student> studentList = dao.selectStudentWhere(student);
    for (Student stu : studentList) {
        System.out.println(stu);
    }
}

3. < foreach > label

Loop the array in java and the list collection. It is mainly used in in sql statements. For example, query three students whose student IDs are 1001, 1002 and 1003.

<foreach collection="" item="" open="" close="" separator="">   
</foreach>

• collection: the type of method parameter in the interface. If it is an array, use array; if it is a list, use list

• item: a custom variable that represents array and collection members

• open: the character at the beginning of the loop

• close: the character at the end of the loop

• Separator: separator between collection members

Add in StudentDAO.java:

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

Add in StudentDAO.xml:

<select id="selectStudentForeachOne" resultType="cn.ecnu.domain.Student">
    select id,name,email,age from student
    where id in
    <foreach collection="list" item="myid" open="(" close=")" separator=",">
        #{myid}
    </foreach>
</select>

Add in StudentTest.java:

@Test
public void selectStudentForeachOneTest() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);

    List<Integer> idList = new ArrayList<>();
    idList.add(1001);
    idList.add(1002);
    idList.add(1003);

    // Call dao's method to perform database operations
    List<Student> studentList = dao.selectStudentForeachOne(idList);
    for (Student stu : studentList) {
        System.out.println(stu);
    }
}

Add in StudentDAO.java:

List<Student> selectStudentForeachTwo(List<Student> Students);

Add in StudentDAO.xml:

<select id="selectStudentForeachTwo" resultType="cn.ecnu.domain.Student">
    select id,name,email,age from student
    where id in
    <foreach collection="list" item="student" open="(" close=")" separator=",">
        #{student.id}
    </foreach>
</select>

Add in StudentTest.java:

@Test
public void selectStudentForeachTwoTest() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);

    List<Student> students = new ArrayList<>();
    Student s1 = new Student();
    s1.setId(1001);
    students.add(s1);
    Student s2 = new Student();
    s2.setId(1002);
    students.add(s2);
    Student s3= new Student();
    s3.setId(1003);
    students.add(s3);

    // Call dao's method to perform database operations
    List<Student> studentList = dao.selectStudentForeachTwo(students);
    for (Student stu : studentList) {
        System.out.println(stu);
    }
}

4. Code snippets

Reuse some sql statements

Define first

<sql id="Custom name (unique)"> 
    sql Statement, table name, field name, etc
</sql>

Reuse

<include refid="id Value of" />

Add in StudentDAO.xml:

<sql id="studentSql">
    select id,name,email,age from student
</sql>

<select id="selectStudentForeachTwo" resultType="cn.ecnu.domain.Student">
    <include refid="studentSql"/>
    where id in
    <foreach collection="list" item="student" open="(" close=")" separator=",">
        #{student.id}
    </foreach>
</select>

Chapter V

1. Attribute profile of database

The database connection information is put into a separate file, which is separated from the main configuration file of mybatis, in order to facilitate the modification, storage and processing of the information of multiple databases.

• Define a property configuration file in the resources directory, xxx.properties, such as jdbc.properties. In the attribute configuration file, define data in the format of key = value. Key is generally used as a multi-level directory, such as jdbc.mysql.driver

  # Contents in jdbc.properties file
  jdbc.driver = com.mysql.jdbc.Driver
  jdbc.url = jdbc:mysql://localhost:3306/mybatis
  jdbc.username = root
  jdbc.password = root
  

• In the main configuration file of mybatis, use the < property > tag to specify the location of the file. Where values need to be used, use ${}

  <dataSource type="POOLED">
      <!--Four elements of connecting to a database-->
      <property name="driver" value="${jdbc.driver}"/>
      <property name="url" value="${jdbc.url}"/>
      <property name="username" value="${jdbc.username}"/>
      <property name="password" value="${jdbc.password}"/>
  </dataSource>
  

2. Processing of multiple mapper files

When there are multiple mapper files, there are two import methods in the main configuration file of mybatis

Mode 1:

<mappers>
    <!--One mapper The tag specifies the location of a file and the path information starting from the classpath-->
    <mapper resource="cn/ecnu/dao/StudentDAO.xml"/>
</mappers>

Mode 2:

<mappers>
    <!-- The second way: use the package name
      name: xml Documents( mapper The name of the package in which the file is located, and all the files in this package xml File load once
         use package Requirements for:
         1. mapper The file name should be the same as the interface name and case sensitive
         2. mapper Documents and dao The interface needs to be in the same directory
    -->
    <package name="cn.ecnu.dao"/>
</mappers>

Note that these two methods cannot coexist, otherwise an error will be reported.

Chapter VI

PageHelper: for data paging

Add dependency in pom.xml file:

<!-- join PageHelper Dependency, paging -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.10</version>
</dependency>

Add in the mybatis main configuration file mybatis.xml:

<!-- stay environments Add before label -->
<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>

Add interface method in StudentDAO.java:

// Using the PageHelper paging plug-in
List<Student> selectAll();

Add the corresponding SQL in StudentDAO.xml. Note that it cannot end with a semicolon here, otherwise an error will occur during string splicing:

<select id="selectAll" resultType="cn.ecnu.domain.Student">
    select * from student order by id
</select>

Add the unit test file in StudentTest.java:

@Test
public void selectAllTest() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    StudentDAO dao = sqlSession.getMapper(StudentDAO.class);
    /*
     * Add PageHelper method, paging
     * pageNum: What page, starting with 1
     * pageSize: How many rows of data are there on a page
     * */
    PageHelper.startPage(1, 2);
    List<Student> studentList = dao.selectAll();
    for (Student stu : studentList) {
        System.out.println(stu);
    }
}