target

• Be able to understand what a framework is
• Master the quick start of Mybatis framework development
• Master the basic CRUD operation of Mybatis framework
• Master SqlMapConfig.xml configuration file
• Master the configuration of parameterType of Mybatis
• Master the configuration of resultType of Mybatis
• Understand Mybatis connection pool and transaction operations
• Master Mybatis dynamic SQL

1. JDBC problem analysis

1. The frequent creation and release of database links cause a waste of system resources, which affects the system performance. If you use database link pool, you can solve this problem.
2. sql statements are hard coded in the code, which makes the code difficult to maintain. The actual application of sql may change greatly. sql changes need to change the java code.
3. There is hard coding when using preparedStatement to transfer parameters to occupancy symbols, because the where conditions of sql statements are not necessarily, there may be more or less, and the code must be modified when modifying sql, so the system is not easy to maintain.
4. There is hard coding (query column name) for result set parsing. The change of sql leads to the change of parsing code, which is difficult to maintain. It is more convenient to parse if the database records can be encapsulated into pojo objects

2 overview of mybatis framework

mybatis is an excellent persistence layer framework based on java. It encapsulates jdbc internally, so that developers only need to pay attention to the sql statement itself, and do not need to spend energy on the complex processes such as loading drivers, creating connections, creating statements and so on.

Mybatis configures various statements to be executed through xml or annotation, and generates the final executed sql statement through the mapping between java objects and sql dynamic parameters in the statement. Finally, the mybatis framework executes sql, maps the results into java objects and returns them.

The ORM idea is adopted to solve the problem of entity and database mapping, encapsulate jdbc and shield the underlying access details of jdbc api, so that we can complete the persistence operation of database without dealing with jdbc api.

Official website: http://www.mybatis.org/mybatis-3/

Summary

1. MyBatis: a framework of persistence layer, which encapsulates JDBC. Operation database
2. Why learn MyBatis?
   • JDBC and DBUtils have some obvious shortcomings. JDBC and DBUtils are not suitable for projects
   • MyBatis is the mainstream persistence layer framework in the work, which has a high probability of use

1. Demand

• Use MyBatis to query all users and encapsulate them into the List collection

2. Analysis

1. Create Maven project (jar) and add coordinates
2. Create pojo
3. Create UserDao interface
4. Create UserDao mapping file
5. Create MyBatis core configuration file SqlMapConfig.xml
6. Writing java code tests

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <!--introduce lombok Dependence of-->
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.10</version>
      <scope>provided</scope>
    </dependency>
    <!--mysql drive-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>
    <!--mybatis Dependence of-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.3</version>
    </dependency>
  </dependencies>

1.2 create UserDao interface

• The UserDao interface is our persistence layer interface (which can also be written as UserMapper). We write it as UserDao. The specific code is as follows:

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

1.3 create UserDao.xml mapping file

be careful:

1. The path of the mapping configuration file is stored in resources, which should be consistent with the path of the corresponding Dao interface
2. The file name of the mapping configuration file must be consistent with the Dao interface name
3. Be sure to import constraint files
   mapper.xml has the following format
   1. The file name of the mapper file is the same as that of dao. It is better to have the same file name before storing it under resources. Mapper can also be stored directly under src, next to the dao file, but it is not recommended to do so. 2.resources is in the form of a folder, starting with / instead of

<?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">
<!--
    Root label mapper，One mapping configuration file corresponds to one dao Interface
    Root label namespace The value of the property corresponds to dao Fully qualified name of the interface
-->
<mapper namespace="com.weijisheng.dao.UserDao">

    <!--
        Each child tag of the root tag corresponds to dao Each method of the interface:
            select The tag corresponds to the query method

        Tagged id The value of corresponds to the name of the method
        Tagged resultType The value of corresponds to the type of encapsulation result. If the type of encapsulation result is List The type corresponding to its generic type
        The content of the tag body is to be executed SQL sentence
    -->
    <select id="findAll" resultType="com.weijisheng.dao.UserDao">
        select * from t_user
    </select>
</mapper>

#####1.4. Create SqlMapConfig.xml configuration file (core configuration file)
matters needing attention

1. The storage path must be the root path of resources
2. The name of the configuration file, write it casually
3. Be sure to import constraint files

<?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">
<!--
    The root tag is configuration
-->
<configuration>
    <!--
        Environment information of configuration database:
        environments: It means that multiple environments can be configured,default Indicates which environment to use
            environment: every last environment Represents an environment
        Why configure multiple environments: Because we have multiple environments(Development environment, production environment(Environment to run after a real project),testing environment)
    -->
    <environments default="dev">
        <!--development environment -->
        <environment id="dev">
            <!--
                Transaction manager, type by JDBC Indicates use JDBC Transaction manager(understand)
            -->
            <transactionManager type="JDBC"></transactionManager>
            <!--
                dataSource Represents a data source, 1. POOLED Indicates that the built-in connection pool 2 is used. UNPOOLED Indicates that connection pool 3 is not used. JNDI Indicates use JNDI Connection pool for
            -->
            <dataSource type="POOLED">
                <!--Configuration information of connection pool-->
                <property name="username" value="root"/>
                <property name="password" value=""/>
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value=""/>
            </dataSource>
        </environment>
        <!--production environment -->
        <environment id="pro">
            <transactionManager type=""></transactionManager>
            <dataSource type=""></dataSource>
        </environment>
        <!--testing environment-->
        <environment id="test">
            <transactionManager type=""></transactionManager>
            <dataSource type=""></dataSource>
        </environment>
    </environments>

    <!--
        Specify which mapping profiles to load: mappers
            mapper label: every last mapper The tag is responsible for loading a mapping profile;resource Specifies the path to the mapping profile to load
    -->
    <mappers>
        <mapper resource="com/weijisheng/dao/UserDao.xml"></mapper>
    </mappers>
</configuration>

1.5 testing

package com.weijisheng;

import com.weijisheng.dao.UserDao;
import com.weijisheng.pojo.User;
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.util.List;
import java.io.InputStream;
public class TestMybatis {
  public class TestMybatis {
    @Test
    public void testFindAll() throws Exception {
        //1. Let the mybatis framework load the main configuration file
        //1.1 convert the main configuration file SqlMapConfig.xml into byte input stream
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        //1.2 create a SqlSessionFactoryBuilder
        SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
        //1.3 use factoryBuilder object to load byte input stream and create SqlSessionFactory object
        SqlSessionFactory sessionFactory = factoryBuilder.build(is); //The builder pattern is used
        //1.4 create sqlSession object using sessionFactory object
        SqlSession sqlSession = sessionFactory.openSession(); //Factory mode is used

        //2. Use sqlSession object to create proxy object of UserDao interface
        UserDao userDao = sqlSession.getMapper(UserDao.class); //Dynamic proxy is used

        //3. Call the findAll() method of userDao proxy object to query
        List<User> userList = userDao.findAll();
        for (User user : userList) {
            System.out.println(user);
        }

        //4. Close resources
        sqlSession.close();
        is.close();
}

Summary

step

1. Create maven project (javase)

2. Introduce dependency:mysql,mybatis,Junit,lombok
3. establish POJO Class, create Dao Interface
4. establish Dao Mapping configuration file corresponding to interface
      	1. Path to resources Inside and Dao The path of the interface is consistent, and resources When creating a directory in a directory, use"/"instead of"."
      	2. The file name of the mapping configuration file should match the corresponding Dao The name of the interface is consistent
5. establish mybatis Core profile for
6. to write mybatis Test with the test code 

Properties (import external properties file)

settings (global configuration parameters)

typeAliases

typeHandlers

objectFactory (object factory)

plugins

environments (environment collection property object)

Environment (environment sub attribute object)

Transaction manager (transaction management)

dataSource

mappers

3.2.properties

• jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=
jdbc.user=root
jdbc.password=

• Import to core profile

<configuration>
   <properties resource="jdbc.properties">
    </properties>
    <!--Data source configuration-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="UNPOOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    ....
</configuration>

3.3.typeAliases

3.3.1 defining a single alias

• Core profile

<typeAliases>
      <typeAlias type="com.weijisheng.bean.User" alias="user"></typeAlias>
 </typeAliases>

• Modify UserDao.xml

<select id="findAll" resultType="user">
    SELECT  * FROM  user
</select>

3.3.2 batch definition alias

Alias defined by package: it is the class name of pojo, which can be case sensitive

• Core profile

<typeAliases>
    <package name="com.weijisheng.bean"/>
</typeAliases>

• Modify UserDao.xml

<select id="findAll" resultType="user">
         SELECT  * FROM  user
</select>

3.4.Mapper

3.4.1 method 1: import mapping file path

<mappers>
     <mapper resource="com/weijisheng/dao/UserDao.xml"/>
 </mappers>

3.4.2 mode 2: scan interface

• Configure a single interface

<mappers>
 	<mapper class="com.weijisheng.dao.UserDao"></mapper>
</mappers>

• Batch configuration

<mappers>
   <package name="com.weijisheng.dao"></package>
</mappers>

4. Summary

1. Order of core configuration files

2. Properties introduces the properties file

   • Create properties file
   • Use < properties resource = "path to file" / >
   • Use ${key}

3. typeAliases directly write the name of the class (pojo) in the Dao mapping file. There is no need to write the fully qualified name of the class

<typeAliases>
    <package name="com.weijisheng.bean"/>
</typeAliases>

1. Mapper introduces Dao mapping file

<mappers>
   <package name="com.weijisheng.dao"></package>
</mappers>

MyBatis advanced

Knowledge points - use of logs (can use)

1. Objectives

When we use MyBatis, in fact, MyBatis framework will print some necessary log information, which is particularly helpful for us to analyze problems and understand code execution in the development stage; Including after the project goes online, we can also collect the error log of the project into the file; We use a special log system to deal with all these problems

2. Steps

1. Import coordinates
2. Copy log profile to project

3. Explanation

• Import coordinates

<!-- log start -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.12</version>
</dependency>

<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.6.6</version>
</dependency>

<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.6.6</version>
</dependency>
<!-- log end -->

• Copy log4j.properties to the resources directory

log4j.rootLogger=DEBUG,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#[%-5p] %t %l %d %rms:%m%n
#%d{yyyy-MM-dd HH:mm:ss,SSS\} %-5p [%t] {%c}-%m%n
log4j.appender.stdout.layout.ConversionPattern=[%-5p] %t %l %d %rms:%m%n
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=D:\\idea_project\\wei_mm_backend.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS\} %-5p [%t] {%c}-%m%n

Level: error > warn > info > debug > trace

Addition, deletion and modification of mybatis

Note: there is no resultType except select
directory structure

1. Add contacts

• Add new method in UserDao

public interface UserDao {
    /**
     * Add user
     * @param user Data to be added to the database
     * @return Number of rows affected
     */
    int addUser(User user);
}

• Add new configuration in UserDao.xml file

    <insert id="insertUser" parameterType="User">
        insert into t_user (username,sex,birthday,address) values (#{username},#{sex},#{birthday},#{address})
    </insert>

	<!--We can find that this sql Used in statements#{}Characters, #{} stands for placeholder. We can understand that it is learned from the original jdbc part?, They all represent placeholders, and the specific value is determined by the username attribute of the User class.
	parameterType Attribute: represents the type of the parameter. Because we want to pass in the object of a class, the type is written to the object of the class
 Full name.-->

• Add test method in test class

public class TestMybatis {

    private UserDao userDao;
    private SqlSession sqlSession;
    private InputStream is;

    @Before
    public void init() throws Exception {
        //1. Let the mybatis framework load the main configuration file
        //1.1 convert the main configuration file SqlMapConfig.xml into byte input stream
        is = Resources.getResourceAsStream("SqlMapConfig.xml");
        //1.2 create a SqlSessionFactoryBuilder
        SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
        //1.3 use factoryBuilder object to load byte input stream and create SqlSessionFactory object
        SqlSessionFactory sessionFactory = factoryBuilder.build(is); //The builder pattern is used
        //1.4 create sqlSession object using sessionFactory object
        //Factory mode is used
        sqlSession = sessionFactory.openSession();

        //2. Use sqlSession object to create proxy object of UserDao interface
        //Dynamic proxy is used
        userDao = sqlSession.getMapper(UserDao.class);
    }
 @Test
    public void testinsert() throws IOException, ParseException {
        String date="1997-04-02";
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date parse = dateFormat.parse(date);
        User user = new User(null,"Faye Wong","female",parse,"earth");
        int insertUser = mapper.insertUser(user);
        //Focus on obtaining uid. For details, see the mapper.xml file below
        System.out.println(user.getUid());
        System.out.println(insertUser);
    }
    @After
    public void destroy() throws IOException {
        //Commit transaction
        sqlSession.commit();

        //4. Close resources
        sqlSession.close();
        is.close();
    }
}

2 return value of new user id

After adding a new user, we also need to return the id value of the current new user. Because the id is realized by the automatic growth of the database, it is equivalent to that we will automatically grow auto after adding a new user_ The value of increment is returned.

• SelectKey get primary key

attribute	describe
keyProperty	The target attribute of the result of the selectKey statement should be set.
resultType	Type of result. MyBatis can usually be calculated, but there is no problem writing it. MyBatis allows any simple type to be used as the type of primary key, including string.
order	This can be set to BEFORE or AFTER. If it is set to BEFORE, it will first select the primary key, set the keyProperty, and then execute the insert statement. If it is set to AFTER, the insert statement is executed first, followed by the selectKey element - similar to Oracle database, sequence calls can be embedded in the insert statement.

UserDao.xml

	<!--
        resultType only select Only labels

        We need to be in the label body SQL Statement, get pojo Properties of parameters of type: #{property name}


        selectKey label: Query primary key
            keyColumn Represents the column name to query
            keyProperty Represents the name of the property to be assigned
            resultType Indicates the type of query result
            order Indicates execution before or after
        select last_insert_id() Query the last self growing id Value of
    -->
<insert id="insertUser" parameterType="User">
    <selectKey resultType="int" order="AFTER" keyProperty="uid" keyColumn="uid">
        select last_insert_id()
    </selectKey>
    insert into t_user (username,sex,birthday,address) values (#{username},#{sex},#{birthday},#{address})
</insert>

3 writing method of fuzzy query

• Add new method in UserDao

/**
     * Fuzzy query based on user name
     */
    List<User> searchByUsername(String username);

• Add new configuration in UserMapper.xml file

 <select id="searchByUsername" resultType="User" parameterType="String">
          select * from t_user where username like "%"#{username}"%"
    </select>

concat is a connection string in mybatis, so the above statement can be written as

select *from t_user where username like concat("%",#{username},"%");

The other is ${}, and the code is as follows:

select * from t_user where username like '%${value}%'

#Difference between {} and ${} [interview]

1. #{} must not be written in quotation marks, ${} must be written in quotation marks
2. If it is a pojo or map type parameter, there are attribute names in #{} or ${}
3. If it is a simple type parameter, #{} can write any string, but ${} can only write value (previous version)
4. If you use #{} import parameters, you need to use "import parameters" first? Placeholder, and then set parameters; If you use ${} to introduce parameters, you can directly splice SQL statements

Encapsulation of mybatis tool class

SqlSessionFactoryUtil.java

package com.weijisheng.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;

public class SqlSessionFactoryUtil {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            //1 convert the main configuration file SqlMapConfig.xml into a byte input stream
            InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
            //2 create a SqlSessionFactoryBuilder
            SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
            //3 use factoryBuilder object to load byte input stream and create SqlSessionFactory object
            sqlSessionFactory = factoryBuilder.build(is); //The builder pattern is used
            System.out.println(sqlSessionFactory);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Create SqlSession object
     * @return
     */
    public static SqlSession openSqlSession(){
        System.out.println(sqlSessionFactory.openSession());
        return sqlSessionFactory.openSession();
    }

    /**
     * Commit the transaction and close sqlSession
     * @param sqlSession
     */
    public static void commitAndClose(SqlSession sqlSession){
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * Roll back the transaction and close sqlSession
     * @param sqlSession
     */
    public static void rollbackAndClose(SqlSession sqlSession){
        sqlSession.rollback();
        sqlSession.close();
    }
}

parameterType parameter type of mapping configuration file of mybatis (important):

1. For a single parameter, the method can be passed in as a simple type, and the value of parameterType in the mapping configuration file is the alias of this simple type
   Introduce simple type parameter #{arbitrary string} into SQL statement

2. Multiple parameters:

3. Pass in multiple simple type parameters (not recommended). We need to name each parameter through the Param annotation, so the parameterType will not be written in the mapping configuration file
   Introduce the value of parameter #{Param annotation in SQL statement}

4. If multiple parameters are encapsulated into a POJO, the value of parameterType in the mapping configuration file is the fully qualified name or alias of the POJO
   Introduce parameter #{property name of POJO} or '${property name of POJO}' into SQL statement

5. Encapsulate multiple parameters into a map (the parameter to be encapsulated has no corresponding POJO), then the value of parameterType in the mapping configuration file is map,
   Introduce the parameter #{key} of map or '${key}' of map into the SQL statement

6. Encapsulate multiple parameters into the wrapper object of a POJO
   What is the wrapper object of POJO, that is, the attribute type in POJO is another POJO type, so the value of parameterType in the mapping configuration file is the alias of the wrapper type
   Introduce parameter #{property name. Property name}
   The fourth code is demonstrated as follows:
   QueryVo.java

package com.weijisheng.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author weijisheng
 * @create 2021-12-04 18:32
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class QueryVo {
    /**
     * The last attribute is not required as a parameter
     * @param currentPage
     * @param pageSize
     * @param queryCondition
     */
    public QueryVo(Long currentPage, Integer pageSize, User queryCondition) {
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        this.queryCondition = queryCondition;
    }

    private Long currentPage;
    private Integer pageSize;
    /**
     * Query criteria, multiple POJOs
     */
    private User queryCondition;

    private Long offset;

    /**
     * The following is to skip the first parameter of limit.
     * @return
     */
    public Long getOffset() {
        return (currentPage - 1)*pageSize;
    }
}

UserDao.java

package com.weijisheng.dao;

import com.weijisheng.pojo.QueryVo;
import com.weijisheng.pojo.User;

import java.util.List;

/**
 * @author weijisheng
 * @create 2021-12-04 18:09
 */
public interface UserDao {
    List<User> findAll();

    /**
     * Incoming pojo
     * @param queryVo
     * @return
     */
    List<User> findPage(QueryVo queryVo);
}

UserDao.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.weijisheng.dao.UserDao">
    <select id="findAll" resultType="User">
        select *from t_user;
    </select>
    <select id="findPage" parameterType="QueryVo" resultType="User">
      select *from t_user where sex=#{queryCondition.sex} and address=#{queryCondition.address}
      limit #{offset},#{pageSize}
     </select>
</mapper>

Test.java

 @Test
    public void findPage(){
        SqlSession sqlSession = SqlFactoryUtils.openSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setSex("female");
        user.setAddress("Wuhan");
        QueryVo queryVo = new QueryVo(1l, 3, user);
        List<User> page = mapper.findPage(queryVo);
        for (User u :
                page) {
            System.out.println(u);
        }
        SqlFactoryUtils.commitAndClose(sqlSession);
    }

The parameter result type of the mapping configuration file of mybatis is important:

1. Output simple type

2. Output pojo object

3. Output pojo list

4. resultMap result type

3.1 output simple type

The result of the query is a single data, and the value of the resultType attribute in the mapping configuration file is the type of this data

/**
  * Total number of query users
  * @return
  */
Long findTotal();

<select id="findTotal" resultType="long">
    select count(*) from t_user
</select>

3.2 output a pojo object (a pojo object corresponds to a row of data) or a Map

The result of the query is a row of data:

• Store this row of data in the POJO object. The value of resultType in the mapping configuration file is the fully qualified name or alias of the POJO. At this time, it is required that the field name and type of the query result should be consistent with the property name and type of the POJO
• Store this row of data in the map object, and the value of resultType in the mapping configuration file is map. At this time, the field name in the query result is the key of map, and the field value is the value of map

/**
  * Query a piece of data by id
  * @param id
  * @return
  */
User findById(int id);

/**
  * Query users by user name
  * @param username
  * @return
  */
Map findByUsername(String username);

<select id="findById" parameterType="int" resultType="User">
    select * from t_user where uid=#{id}
</select>

<select id="findByUsername" parameterType="string" resultType="map">
    select * from t_user where username=#{username}
</select>

3.3 output pojo list (a pojo list corresponds to multiple rows of data) or Map list

The result of the query is multi row data:

• Store multiple pieces of data in the List, and the value of resultType in the mapping configuration file is the alias of POJO

• Store multiple pieces of data in the List, and the value of resultType in the mapping configuration file is map

3.4 resultMap result type

   resultType Can specify pojo Map query results to pojo，But need pojo Property name and sql Only when the column names of the query are consistent can the mapping be successful.

   If sql Query field name and pojo The attribute names of are inconsistent. You can use resultMap Make a corresponding relationship between the field name and the attribute name, resultMap In essence, you also need to map the query results to pojo Object.

	resultMap You can map query results to complex types pojo，For example, the query result mapping object includes pojo and list Realize one-to-one query and one to many query. (See Notes in the second paragraph for details)

Let's take a look at the situation when the returned column name is inconsistent with the attributes of the entity class. Next time, we will study the complex encapsulation (multi table query) and query the t_ The information of the user table is encapsulated in the UserInfo object

• UserInfo.java

package com.weijisheng.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;


@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserInfo implements Serializable {
    private Integer userId;
    private String username;
    private String userSex;
    private String userBirthday;
    private String userAddress;
}

• UserDao.java

public interface UserDao {
    /**
     * Query all user information and encapsulate it into UserInfo object
     * @return
     */
    List<UserInfo> findAllUserInfo();
}

• UserDao.xml

<!--
        resultType Properties are automatically mapped: Based on the field name and of the result set POJO Mapping the corresponding relationship between the attribute names of

        resultMap attribute: Result set mapping(Manual mapping),We need to use it first resultMap Tag, write a manual mapping rule, and then use the mapping rule
    -->
<!--
        id Is the unique identification of this mapping rule
        type Is the type to be manually mapped:UserInfo

        autoMapping="true" Indicates that the attributes that can be mapped automatically will be mapped automatically, and the attributes that cannot be mapped automatically will be mapped manually
    -->
<resultMap id="userInfoMap" type="UserInfo" autoMapping="true">
    <!--
            id The label represents the mapping of the primary key
                column Property is the field name of the primary key to be mapped(Listing)
                property Is to be mapped POJO Property name for
        -->
    <id column="uid" property="userId"></id>
    <!--
            result A tag is a mapping of other non primary keys
        -->
    <result column="sex" property="userSex"></result>
    <result column="birthday" property="userBirthday"></result>
    <result column="address" property="userAddress"></result>
</resultMap>

<select id="findAllUserInfo" resultMap="userInfoMap">
    select * from t_user
</select>

Summary

1. Output a simple type and directly write the java type name eg: int
2. The output pojo object directly writes the pojo type name eg: User
3. Output pojo list type write the generic type in the list eg: List write User
4. ResultMap
   • Resolve the request that the column name of the query result is inconsistent with the javaBean attribute
   • Complex pojo (tomorrow)

SQL depth of Mybatis mapping file (important ----- > dynamic sql):

In the mapping file of Mybatis, our SQL is relatively simple. Sometimes when the business logic is complex, our SQL changes dynamically. At this time, our SQL can not meet the requirements in the previous study.

Knowledge point - if tag of dynamic SQL

1. Objectives

We use different SQL statements to query according to different values of entity classes.

For example, if the id is not empty, you can query according to the id. if the username is not empty, you need to add the user name as a condition. This situation is often encountered in our multi condition combined query.

2. Explanation

• UserDao.java

package com.weijisheng.dao;

import com.weijisheng.pojo.QueryVo;
import com.weijisheng.pojo.User;
import com.weijisheng.pojo.UserInfo;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * @author weijisheng
 * @create 2021-12-04 18:09
 */
public interface UserDao {
    /**
     * Query all users according to dynamic sql fragments
     * @return
     */
    List<User> findUsers();
    /**
     * Query by user address and gender
     * @param address
     * @param sex
     * @return
     */
    List<User> findUserListByAddressAndSex(@Param("addr") String address, @Param("sex") String sex);

    /**
     * Batch deletion according to id, similar to shopping cart
     * @param ids
     */
    void deleteByIds(List<Integer> ids);
}

• UserDao.xml

<!--
            Add a judgment to judge the incoming address Is it empty,use if Label for judgment,In this label test Attribute to write judgment conditions
        -->
  <select id="findUserListByAddress" parameterType="string" resultType="User">
        select * from t_user
        <!--
            Add a judgment to judge the incoming address Is it empty,use if Label for judgment,In this label test Attribute to write judgment conditions
        -->
        <if test="address != null">
            where address=#{address}
        </if>
    </select>

    <select id="findUserListByAddressAndSex" parameterType="User" resultType="User">
        select * from t_user
        where 1=1
        <if test="address != null">
            and address=#{address}
        </if>

        <if test="sex != null">
            and sex=#{sex}
        </if>
    </select>

Knowledge point - where label of dynamic SQL

1. Objectives

In order to simplify the above conditional assembly of where 1=1, we can use labels to simplify development.

2. Explanation

Modify the UserDao.xml mapping file as follows:

Note: the first and can be processed automatically

<select id="findUserListByAddressAndSex" parameterType="User" resultType="User">
    <include refid="select_all"/>

    <!--
            where Role of labels:
                1. You can add before a condition where keyword
                2. You can remove the before the first condition and
        -->
    <where>
        <if test="address != null">
            and address=#{address}
        </if>

        <if test="sex != null">
            and sex=#{sex}
        </if>
    </where>
</select>

3. Summary

1. The where tag is used to replace where '1' = '1' when writing sql statements
2. The first and can be processed automatically. It is recommended to add and to all

Knowledge point - foreach label of Dynamic Label

1. Objectives

• Master the use of foreach tag

2.1 requirements

• Batch delete: deletes all elements according to the set of IDS

2.2 LinkManDao code

/**
  * Batch delete
  * @param ids
  */
void deleteByIds(List<Integer> ids);

2.3 LinkManDao mapping profile

<delete id="deleteByIds" parameterType="int">
    delete from t_user

    <!--
            Traverse the data in the incoming collection and put it into()inside
            use foreach Tag traversal
                collection attribute:To traverse the collection, if you want to traverse a List Is written as list
                item attribute: Each element traversed out
                separator attribute: The separator between each element traversed
                index attribute: Index of each element traversed
                open attribute: Concatenate the string before the first element traversed
                close attribute: Concatenate the string after the last element traversed
        -->
    <foreach collection="list" item="id" separator="," open="where uid in(" close=")">
        #{id}
    </foreach>

</delete>

Knowledge point - Sql fragment of Dynamic Label

1. Objectives

Duplicate SQL can be extracted from SQL and referenced with include when used, so as to achieve the purpose of SQL reuse. Let's use the tag in the UserDao.xml file to define the public part

2. Explanation

• Using sql tag extraction

<!--
    use sql The label will repeat sql The statement part is encapsulated

    Use this when needed sql Where the clip is, use it include Just import the label
 -->
<sql id="select_all">
    select uid,username,sex,address,birthday from t_user
</sql>

• Using the include tag

<select id="findUserListByAddress" parameterType="string" resultType="User">
    <include refid="select_all"/>
    <!--
            Add a judgment to judge the incoming address Is it empty,use if Label for judgment,In this label test Attribute to write judgment conditions
        -->
    <if test="address != null">
        where address=#{address}
    </if>
</select>

<select id="findUserListByAddressAndSex" parameterType="User" resultType="User">
    <include refid="select_all"/>

    <!--
            where Role of labels:
                1. You can add before a condition where keyword
                2. You can remove the before the first condition and
        -->
    <where>
        <if test="address != null">
            and address=#{address}
        </if>

        <if test="sex != null">
            and sex=#{sex}
        </if>
    </where>
</select>

3. Summary

1. The sql tag can extract public sql statements and then use the include tag to introduce them. Benefits: easy maintenance and prompt efficiency