MyBatis learning record

1, MyBatis overview

1. Software development architecture - three-tier architecture

1) User Interface layer

• Presentation layer, view layer
• Dealing with users
• Receive user data and display the processing result of the request
• jsp ，html ，servlet
• Corresponding package: controller package (servlet)
• Corresponding framework: servlet spring MVC (framework)

2) Business Logic Layer

• Received the data transmitted by the interface layer
• Computational logic
• Call the database to get the data
• Corresponding package: service package (XXXService class)
• Corresponding framework: service class – spring (framework)

3) Data access layer

• Access the database, query, modify and delete the data
• Corresponding package: dao package (XXXDao class)
• Corresponding framework: dao class – mybatis (framework)

4) Interaction of classes in three layers
User interface layer - > business logic layer - > data access layer (persistence layer) - > Database (mysql)

2. Frame

1) What is a framework

• A Framework is a reusable design of a whole or part of a system
• Framework is an application framework and template that can be customized by application developers
• The framework is actually semi-finished software, which is a group of components
• Framework safe, reusable and continuously upgraded software

2) Why frame

• The most important problem to be solved by the framework is technology integration
• Can provide development efficiency

3) What is the MyBatis framework

• Mybatis is the MyBatis SQL Mapper Framework for Java
• SQL mapper: SQL mapping
  You can map a row of data in a database table to a java object.
  A row of data can be regarded as a java object. Operating this object is equivalent to operating the data in the table
• Data Access Objects (DAOs): data access, adding, deleting, modifying and querying the database.

4) Why use the MyBatis framework

• Defects in using JDBC:
  ——There are many codes and the development efficiency is low
  ——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
  ——There are more duplicate codes
  ——Business code and database operation are mixed together
• MyBatis action:
  ——Reduce the complexity of using JDBC, and there is no need to write repeated creation of connection and statement
  ——You don't have to write code to close resources
  ——Directly use java objects to represent the result data

5) Functions provided by Mybatis

• It provides the ability to create connection, statement and resultset without developers creating these objects
• It provides the ability to execute sql statements without you executing sql
• It provides the ability to cycle sql, convert the results of sql into java objects and List sets

// MyBatis can complete the following functions internally without writing the following code manually
	  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);
	  }

• It provides the ability to close resources without closing connection, statement and resultset

2, Getting started with MyBatis

1. Download MyBatis and environment configuration

1) Download address
https://github.com/mybatis/mybatis-3/releases

2) Environment path settings

2. New database table

CREATE TABLE `student` (
 `id` int(11) NOT NULL ,
 `name` varchar(255) DEFAULT NULL,
 `email` varchar(255) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Create IDEA project

1) Create a new Empty Project

2) Create Maven Module and select Maven archetype QuickStart

3) Delete the App and AppTest created by default

4) Configure Maven's POM XML file

• Add mybatis coordinates

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

• Add mysql driver coordinates

    <!-- mysql Drive dependency -->
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.16</version>
    </dependency>

• Add a plug-in to copy the resources file into the target (if there is still no in the target, ReBuild the project or copy it manually)

<!--Put build in-->
<resources>
	 <resource>
		 <directory>src/main/java</directory><!--Directory of-->
		 <includes><!--Including the.properties,.xml The files will be scanned-->
		 <include>**/*.properties</include>
		 <include>**/*.xml</include>
		 </includes>
		 <filtering>false</filtering>
	 </resource>
</resources>

Note: the dependent class libraries will be automatically downloaded to the target folder specified by maven

5) Create Student entity class

• Declare variables with the same attributes and names as the data table
• Create constructors and get and set methods
• Override toString() method

package com.bjpowernode.entity;

// The name of the recommended entity class is consistent with that of the table class, which is easy to remember
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;

    public Student(Integer id, String name, String email, Integer age) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.age = age;
    }

    public Student() {
    }

    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 +
                '}';
    }
}

6) Implementation of DAO interface for operating database

• Declare a method to query all data in the table and put the results into a list

package com.bjpowernode.dao;

import com.bjpowernode.entity.Student;
import java.util.List;

// Interface, the student table needs to be operated
public interface StudentDao {

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

7) Create an sql mapping configuration file used by MyBatis

• In the same directory folder as DAO interface
• The name of the configuration file is consistent with the DAO interface, that is, studentdao xml
• Write sql statements according to the format requirements of MyBatis

<?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.bjpowernode.dao.StudentDao">
<!--
    select: Represents a query
    id: implement sql The unique identifier of the statement, which can be customized. It is recommended to use the method name in the interface
    resultType: Indicates the result type. It is recommended to directly use the fully qualified name of the type
    no resultSetType，If you write wrong, you will report an error:
    Must have list "FORWARD_ONLY SCROLL_INSENSITIVE SCROLL_SENSITIVE DEFAULT " Value in
-->

    <select id="selectStudents" resultType="com.bjpowernode.entity.Student">
        select id,name,email,age from student order by id
    </select>
</mapper>

<!--
    sql Mapping files: Writing sql Statement, 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">

      Among them, mybatis-3-mapper.dtd Files are constraint files

    2,Check that the attributes and constraints in the file must comply with the current restrictions mybatis requirement

    3,mapper Is the root label of the current file - the default
        namespace: Is a namespace, a unique value, or a custom string
                    However, it is required to 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, including:
            <select>
            <update>
            <insert>
            <delete>
-->

8) Create master profile

• Create an xml file under src/resources /. The name can be customized
• Write relevant information according to the rules
• It is mainly used to write relevant information about connecting to the database

<?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: database connection information
        default: Must be with someone environment of id Same value
        tell mybatis The connection information of which database is used, that is, which database is accessed
    -->
    <environments default="My_development">
        <!-- environment: A configuration environment for database information
                id: A unique value, customized, representing the name of the environment
        -->
        <environment id="My_development">
            <!--
                transactionManager: mybatis Transaction type
                type: JDBC-Indicates use jdbc Medium Connection Object commit,rollback
            -->
            <transactionManager type="JDBC"/>
            <!--
                dataSource: Represents the data source and links the database
                type: Indicates the type of data source, POOLED Indicates the use of connection pools
            -->
            <dataSource type="POOLED">
                <!--
                    driver/user/username/password The name of is fixed and cannot be customized
                    value Replace the value in with your own
                    url In, the database should be followed by:?serverTimezone=UTC；Otherwise, an error will be reported
                -->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- sql mapper(sql Mapping file)Location of -->
    <mappers>
        <!--
            One mapper Label specifies the location of a file
            Path information starting from classpath
            target/classes(Classpath)
            [Compile first and then find target Goals in sql Map the file and copy the path]
        -->
        <mapper resource="com/bjpowernode/dao/StudentDao.xml"/>
    </mappers>
</configuration>

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

9) Create test program file

• Create myapp Java file
• Read the main configuration file mybatis XML and create related objects required for Sql execution
• Use sqlsession related objects to execute statements and output

package com.bjpowernode;


import com.bjpowernode.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 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 the data of student table
        // 1. Define the name of the mybatis master configuration file, starting from the root directory of the classpath (target/classes)
        String config = "mybatis.xml";  // That is, the main configuration file under the previously defined resources
        // 2. Read config file
        InputStream in = Resources.getResourceAsStream(config);
        // 3. Create SqlSessionFactoryBuilder object
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        // 4. Create SqlSessionFactory object with builder
        SqlSessionFactory factory = builder.build(in);
        // 5. Get SqlSession object from factory [key]
        SqlSession sqlsession = factory.openSession();
        // 6. Specify the identification of the sql statement to be executed, that is, through the namespace + '. In the sql mapping file+ id value of tag [key]
        String sqlId = "com.bjpowernode.dao.StudentDao" + '.' + "selectStudents";
        // 7. Execute sql statement through sqlId
        List<Student> stuList = sqlsession.selectList(sqlId);
        // 8. Output results
        for(Student stu : stuList){
            System.out.println("Student information queried:" + stu);
        }
        // sqlsession object 9. Close
        sqlsession.close();
    }
}

The results are as follows:

10) Overall process and documents

• First, POM XML file - add and configure mybatis, mysql and resource automatic replication plug-ins
• Secondly, write entity class program - define relevant variables, which is consistent with the structure and content of the data table
• Next, the DAO file of the entity class -- declares the corresponding method
• Then, create the sql mapping mapper(xml) file corresponding to the DAO file for writing sql statements
• Secondly, write the main configuration xml file, configure the sql mapping file in the previous step, and determine the accessed database and database connection information
• Finally, create the actual application and test program files to realize the functions
  
  11) Configure log function
• mybatis. The log configuration is added to the XML file, and the executed sql statements and parameters can be output on the console

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

4. Addition, deletion and modification of sentences

1) Add information

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

2) Delete information

<delete id="deleteStudent">
 delete from student where id=#{studentId}
</delete>

3) Modify information

<update id="updateStudent">
 update student set age = #{age} where id=#{id}
</update>

5. sql related objects used by MyBatis

1) Resources class

• InputStream in = Resources.getResourceAsStream("mybatis.xml");
• A class in mybatis, which is responsible for reading the main configuration file

2)SqlSessionFactoryBuilder

• SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
• Create SqlSessionFactoryBuilder object

3)SqlSessionFactory

• SqlSessionFactory factory = builder.build(in);
• Creating SqlSessionFactory objects takes a long time to create and uses a lot of resources. In the whole project, one is enough
• The function is to obtain SqlSession object: SqlSession sqlsession = factory openSession();
• openSession(true) gets the SqlSession of the auto commit transaction
  openSession(false) SqlSession object that does not automatically commit transactions
  When the parameter is empty, the default value is false

4)SqlSession

• The method of operating data is defined: selectone(), selectlist(), insert(), update(), delete(), commit(), rollback()
• SqlSession object is not thread safe: it 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 close it and execute SqlSession close()

6. Creation and use of MyBatis tool class

1) Create MyBatisUtils tool class

• src/../ Create mybatisutils under utils / java
• The implementation can return the sqlSession object

package com.bjpowernode.entity;

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;

public class MyBatisUtils {
	// Create a factory object from a static code block
    private static SqlSessionFactory factory = null;
    static {
        String config = "mybatis.xml";
        try {
            InputStream in = Resources.getResourceAsStream(config);

            factory = new SqlSessionFactoryBuilder().build(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
	// Return sqlSession object through static method
    public static SqlSession getSqlSession(){
        SqlSession sqlSession = null;
        if(factory != null){
            sqlSession = factory.openSession();
        }
        return sqlSession;
    }
}

2) Use of tools

package com.bjpowernode;

import com.bjpowernode.entity.MyBatisUtils;
import com.bjpowernode.entity.Student;
import org.apache.ibatis.session.SqlSession;

import java.io.IOException;
import java.util.List;

public class MyApp2 {

    public static void main(String[] args) throws IOException {

        // Get sqlsession object
        SqlSession sqlsession = MyBatisUtils.getSqlSession();
        // Specify the identification of the sql statement to be executed, that is, through the namespace + '. In the sql mapping file+ id value of tag [key]
        String sqlId = "com.bjpowernode.dao.StudentDao" + '.' + "selectStudents";
        // Execute sql statement through sqlId
        List<Student> stuList = sqlsession.selectList(sqlId);
        // Output results
        for(Student stu : stuList){
            System.out.println("Student information queried:" + stu);
        }
        // Close the sqlsession object
        sqlsession.close();
    }
}

3, MyBatis dynamic proxy

1. Implementation steps

1) Dao's interface implementation class is no longer used

2) getMapper get proxy object

• Dynamic proxy: using sqlsession Getmapper (dao interface. class) gets the object of this dao interface
• Get method 1:

SqlSession session = factory.openSession();
StudentDao studentDao = session.getMapper(StudentDao.class);

• Get method 2:

StudentDao studentDao = MyBatisUtil.getSqlSession().getMapper(StudentDao.class);

3) Use the Dao proxy object method to execute sql statements

// select method:
@Test
public void testSelect() throws IOException {
 List<Student> studentList = studentDao.selectStudents();
 studentList.forEach( stu -> System.out.println(stu));
}
// insert method:
@Test
public void testInsert() throws IOException {
 Student student = new Student();
 student.setId(1006);
 student.setName("Lin Hao");
 student.setEmail("linhao@163.com");
 student.setAge(26);
 int nums = studentDao.insertStudent(student);
 System.out.println("use Dao Add data:"+nums);
}
// update method
@Test
public void testUpdate() throws IOException {
 Student student = new Student();
 student.setId(1006);
 student.setAge(28);
 int nums = studentDao.updateStudent(student);
 System.out.println("use Dao Modify data:"+nums);
}
// delete method
@Test
public void testDelete() throws IOException {
 int nums = studentDao.deleteStudent(1006);
 System.out.println("use Dao Modify data:"+nums);
}

2. Incoming parameters

1)parameterType

• An attribute written in the mapper file
• Represents the data type of the parameter of the method in the dao interface
• Its value is the fully qualified name of java data type or the alias defined by mybatis
• It is not mandatory. mybatis can find parameter types through the reflection mechanism, so it can not be written by default

alias	Type of mapping
_byte	byte
_long	long
_short	short
_int	int
_integer	int
_double	double
_float	float
_boolean	boolean
string	String
byte	Byte
long	Long
short	Short
int	Integer
integer	Integer
double	Double
float	Float
boolean	Boolean
date	Date
decimal	BigDecimal
bigdecimal	BigDecimal

2) Pass in a simple parameter

• The parameters of Dao interface method have only one simple type (java basic type and String), that is, there is only one placeholder #{any character}
• After using #{}, MySQL executes sql by using the PreparedStatement object in jdbc. The actual process is as follows:
  mybatis creates Connection and PreparedStatement objects
  String sql = "..... =?;"
  PreparedStatement ps = conn.preparedStatement(sql);
  ps.setInt(1, num);
  Execute sql encapsulation and encapsulate it as a resultType object
  ResultSet rs = ps.executeQuery():
  while(rs.next()){...}

3) Pass in multiple parameter 1 -- use @ Param

• In case of multiple parameters, use @ Param to name the parameters yourself
• If interface exists
  public List<Student> selectMulitParam(@Param("myname") String name, @Param("myage") Integer age)
  Where, @ Param("parameter name") String name
  When in use, it is as follows:
  

<!-- mapper In the document, it is also convenient to write -->
<select>
	select * from s_table where name=#{myname} or age=#{myage}
</select>

4) Pass in multiple parameters 2 -- use object

• Multiple parameters. java objects are used. The attribute value of java is the parameter value required by sql
• Syntax format: #{property, javatype = data type name in Java, JDBC type = data type name}
• javaType, jdbcType and MyBatis can be detected. Generally, they do not need to be set. Common format #{property}
• Implementation steps:
  Create an object queryparam that holds parameter values java
  Method declared in interface
  mapper file modification configuration
  Test class calls method parameters

package com.bjpowernode.vo;
public class QueryParam {
 private String queryName;
 private int queryAge;
 //set, get method
}

List<Student> selectMultiObject(QueryParam queryParam);

<select id="selectMultiObject" resultType="com.bjpowernode.domain.Student">
 	select id,name,email,age from student where name=#{queryName} or age =#{queryAge}
</select>

@Test
public void selectMultiObject(){
 QueryParam qp = new QueryParam();
 qp.setQueryName("Li Li");
 qp.setQueryAge(20);
 List<Student> stuList = studentDao.selectMultiObject(qp);
 stuList.forEach( stu -> System.out.println(stu));
}

5) Pass in multiple parameters 3 -- pass parameters by location

• The parameter position starts from 0 and refers to the parameter syntax #{argposition}. The first parameter is #{arg0}, and the second is #{arg1}

List<Student> selectByNameAndAge(String name,int age);

<select id="selectByNameAndAge" resultType="com.bjpowernode.domain.Student">
	 select id,name,email,age from student where name=#{arg0} or age =#{arg1}
</select>

@Test
public void testSelectByNameAndAge(){
	//By location parameter 
	List<Student> stuList = studentDao.selectByNameAndAge("Li Li",20);
	stuList.forEach( stu -> System.out.println(stu));
}

6) Pass in multiple parameters 4 -- use Map

• The Map collection can store multiple values and use Map to pass in multiple parameters to the mapper file at one time
• The Map collection uses the key of String and the value of Object type to store parameters
• The mapper file uses # {key} to reference parameter values

Map<String,Object> data = new HashMap<String,Object>();
data.put("myname",""Li Li");
data.put("myage",20);

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

<select id="selectMultiMap" resultType="com.bjpowernode.domain.Student">
	select id,name,email,age from student where name=#{myname} or age =#{myage}
</select>

@Test
public void testSelectMultiMap(){
	Map<String,Object> data = new HashMap<>();
	data.put("myname","Li Li");// #{myname}
	data.put("myage",20); // #{myage}
	List<Student> stuList = studentDao.selectMultiMap(data);
	stuList.forEach( stu -> System.out.println(stu));
}

6) Placeholder # and$

• #: a placeholder that tells mybatis to use the actual parameter value instead
• The PrepareStatement object is used to execute the SQL statement, #{...} instead of the "?" of the SQL statement, Avoid SQL injection
• $string replacement, tell mybatis to use the "string" contained in $to replace the location
• Use Statement to connect the sql Statement with the contents of ${}. It is mainly used to replace table name, column name, sorting of different columns and other operations.
• It can be used when it can be determined that the data is safe$

      select id,name, email,age from student where id=#{studentId}
	  # Result: select id,name, email,age from student where id =? 
	  
	  select id,name, email,age from student where id=${studentId}
	  $ Results: select id,name, email,age from student where id=1001

#And $difference
1. # use? In sql statements, the PreparedStatement is used to execute sql with high efficiency
2. # it can avoid sql injection and is safer.
3. $does not use placeholders, but is a string connection method. Using Statement object to execute sql is inefficient
4. $has the risk of sql injection and lacks security.
5. $: table name or column name can be replaced

3. MyBatis output - Java object

1)resultType

• Execute sql to get the type of ResultSet conversion, and use the fully qualified name or alias of the type
• If a collection is returned, it should be set to the type contained in the collection, not the collection itself
• Resulttypes can be simple types, object types, and collection types

<select id="countStudent" resultType="int">
	select count(*) from student
</select>

<select id="selectById" resultType="com.bjpowernode.domain.Student">
	select id,name,email,age from student where id=#{studentId}
</select>

<select id="selectReturnMap" resultType="java.util.HashMap">
	select name,email from student where id = #{studentId}
</select>

2)resultMap

• You can customize the mapping relationship between sql results and java object attributes
• It is often used when the column name is different from the attribute name of java object
• Usage:
  1. Define resultMap first and specify the corresponding relationship between column name and attribute.
  2. Replace resultType with resultMap in.

<!-- establish resultMap
	 id:Custom unique name, in<select>use
	 type:Expectation turned into java The fully qualified name or alias of the object 
--> 
<resultMap id="studentMap" type="com.bjpowernode.domain.Student">
	 <!-- Primary key field usage id -->
	 <id column="id" property="id" />
	 <!--Use of non primary key fields result-->
	 <result column="name" property="name"/>
	 <result column="email" property="email" />
	 <result column="age" property="age" />
</resultMap>

<!--resultMap: resultMap In label id Attribute value--> 
<select id="selectUseResultMap" resultMap="studentMap">
	 select id,name,email,age from student where name=#{queryName} or age=#{queryAge}
</select>

3) The class attribute name is different from the data table column name

• Method 1 - the sql statement uses the "as" keyword, that is, the column alias
• Method 2 -- use the resultMap method

4) Two schemes of fuzzy like query

• Method 1 - prepare the content of like in advance

List<Student> selectLikeFirst(String name);

<select id="selectLikeFirst" resultType="com.bjpowernode.domain.Student">
	select id,name,email,age from student
	where name like #{studentName}
</select>

@Test
public void testSelectLikeOne(){
	String name="%power%";
	List<Student> stuList = studentDao.selectLikeFirst(name);
	stuList.forEach( stu -> System.out.println(stu));
}

• Method 2 - use like name "%" #{xxx} "%" in mapper file to splice query

List<Student> selectLikeSecond(String name);

<select id="selectLikeSecond" resultType="com.bjpowernode.domain.Student">
	select id,name,email,age from student
	where name like "%" #{studentName} "%"
</select>

@Test
public void testSelectLikeSecond(){
	String name="power";
	List<Student> stuList = studentDao.selectLikeSecond(name);
	stuList.forEach( stu -> System.out.println(stu));
}

4, Dynamic sql

1. What is dynamic sql

• Dynamic SQL: judge the conditions through various tags provided by MyBatis to realize dynamic splicing of SQL statements
• The expression used for condition judgment is OGNL expression
• Commonly used dynamic SQL tags include < if >, < where >, < choose / >, < foreach >
• It is mainly used to solve uncertain query conditions

2. Dynamic sql tag

1) < if > is the judgment condition

• When the value of test is true, the SQL fragment contained in it will be spliced into its SQL statement
• Syntax: < if test = "condition" > part of SQL statement < / if >

List<Student> selectStudentIf(Student student);

<select id="selectStudentIf" resultType="com.bjpowernode.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 ">
	 	and age &gt; #{age}
	 </if>
</select>

2) < where > is used to contain multiple < if > tags

• If all < if / > conditions after where are false, and if there is no 1 = 1 clause after where, there will be only an empty where in SQL and SQL error
• After where, you need to add the always true Clause 1 = 1 to prevent this from happening. However, when the amount of data is large, it will seriously affect the query efficiency
• Using the < where > tag, when one of multiple IFS is established, a where keyword will be automatically added, and the redundant and, or, etc. in the if will be removed
• Syntax: < where > other dynamic SQL < / where >

List<Student> selectStudentWhere(Student student);

<select id="selectStudentWhere" resultType="com.bjpowernode.domain.Student">
	 select id,name,email,age from student
	 <where>
	 	<if test="name != null and name !='' ">
			 and name = #{name}
		 </if>
		 <if test="age > 0 ">
			 and age &gt; #{age}
		 </if>
	 </where>
</select>

3) < foreach > loop array collection in java

• It is mainly used in the in statement of sql
• Collection indicates the collection type to be traversed, such as list, array, etc
• Open, close and separator are SQL splicing of traversal content
• Syntax:

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

4) Code snippets -- reuse some syntax

• The < SQL / > tag is used to define SQL fragments so that other SQL tags can be reused
• For other tags that use this SQL fragment, you need to use < include / > sub tags
• Use steps:
  1. First define < SQL id = "user defined name unique" > sql statement, table name, field, etc. < / SQL >
  2. Reuse, < include refid = "id value" / >

<!--establish sql fragment id:Custom name of the clip--> 
<sql id="studentSql">
	select id,name,email,age from student
</sql> 

<select id="selectStudentSqlFragment" resultType="com.bjpowernode.domain.Student">
	<!-- quote sql fragment -->
	<include refid="studentSql"/>
	<if test="list !=null and list.size > 0 ">
		where id in
		<foreach collection="list" open="(" close=")" item="stuobject" separator=",">
			#{stuobject.id}
		</foreach>
	</if>
</select>

5, MybBatis profile

Settings are global adjustment settings in MyBatis. They will change the runtime behavior of MyBatis. They should be set carefully

• cache

<!-- This configuration affects the global switch of the cache configured in all mappers. Default value true -->  
<setting name="cacheEnabled" value="true"/>

• Delayed loading (lazy loading)

<!--Global switch for delayed loading. When turned on, all associated objects are loaded late. In a specific association relationship, you can set fetchType Property to override the switch state of the item. Default value false  -->  
<setting name="lazyLoadingEnabled" value="true"/>

• Return multiline data

<!-- Whether to allow a single statement to return multiple result sets (compatible driver is required). Default value true -->  
<setting name="multipleResultSetsEnabled" value="true"/>

• Column labels replace column names

<!-- Use column labels instead of column names. Different drivers will have different performances in this aspect. For details, please refer to the relevant driver documents or observe the results of the drivers used by testing these two different modes. Default value true -->  
<setting name="useColumnLabel" value="true"/>

• JDBC automatically generates primary key

<!-- allow JDBC It supports automatic generation of primary keys and requires driver compatibility. If set to true This setting enforces the automatic generation of primary keys. Although some drivers are incompatible, they can still work normally (for example Derby).  Default value false  -->  
<setting name="useGeneratedKeys" value="false"/> 

• Specifies that the object method triggers deferred loading

<!--   Specifies which object's method triggers a deferred load.  -->  
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>

1. Database properties profile

1) Purpose and function

• Put the database connection information into a separate file
• Separate it from the mybatis main configuration file
• It is convenient to modify, save and process the information of multiple databases

2) Implementation steps

• Define an attribute configuration file in the resources directory, XXXX Properties, such as JDBC properties
• Define data in the attribute configuration file in the format of key=value
• Among them, the key generally uses'. ' To split multi-level directories, such as JDBC mysql. driver

	 jdbc.driver=com.mysql.jdbc.Driver
	jdbc.url=jdbc:mysql//.....
	jdbc.username=root
	jdbc.password=123456

• In the main configuration file of mybatis, use < property > to specify the location of the file

	<properties resource="jdbc.properties" />

• Where a value is required, ${key}

<dataSource type="POOLED">
 <!--use properties file: grammar ${key}-->
	<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. mappers mapper

1) For individual mapping files

• Often use < mapper resource = "" / >
• Using resources relative to the classpath, look for files from the classpath path
• For example: < mapper resource = "COM / bjpowernode / Dao / studentdao. XML" / >
• Disadvantages: when there are too many mapping files, the addition is too miscellaneous

2) For all interfaces and their mapping files under the specified package

• Use < package name = "" / >
• Dao interface name and mapper mapping file name are required to be the same and in the same directory
• For example: < package name = "com. Bjpowernode. Dao" / >

6, MyBatis extension -- PageHelper

PageHelper is used to realize data paging. PageHelper supports a variety of databases

1. Paging based on PageHelper

1) Implementation steps

• maven coordinates

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

• Add plugin configuration

<!--stay<environments>Before joining-->
<plugins>
 	<plugin interceptor="com.github.pagehelper.PageInterceptor" />
</plugins>

• Create PageHelper object: call PageHelper. before paging MyBatis query method. Just use the startpage static method

@Test
public void testSelect() throws IOException {
	//Access, page 3
	PageHelper.startPage(1,3);
	List<Student> studentList = studentDao.selectStudents();
	studentList.forEach( stu -> System.out.println(stu));
}