MyBatis Study Guide - result mapping and dynamic SQL

Posted by azaidi on Wed, 19 Jan 2022 16:17:25 +0100

1. Complex mapping

1.1 result mapping

1.1.1 basic concepts

resultType: if the attribute name of the entity is consistent with the field name in the table, the query results will be automatically encapsulated into the entity class
ResutlMap: if the attribute name of the entity is inconsistent with the field name in the table, you can use ResutlMap to manually encapsulate it into the entity class

1.1.2 code cases

UserMapper.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="cn.knightzz.mapper.UserMapper">

    <resultMap id="UserResultMap" type="cn.knightzz.entity.User">

        <!-- Encapsulate primary key fields -->
        <id property="uid" column="id"></id>
        <!-- Configure common fields -->
        <result property="usernameABC" column="username"></result>
        <result property="birthdayABC" column="birthday"></result>
        <result property="sexABC" column="sex"></result>
        <result property="addressABC" column="address"></result>
    </resultMap>

    <select id="findUserById" parameterType="int" resultMap="UserResultMap">
        select *
        from user
        where id = #{id};
    </select>
</mapper>

Entity class

package cn.knightzz.entity;

import java.util.Date;

/**
 * @author Wang Tianci
 * @title: User
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/15 22:00
 */
public class User {
    /**
     * User id
     */
    private Integer uid;

    /**
     * User name
     */
    private String usernameABC;

    /**
     * User birthday
     */
    private Date birthdayABC;

    /**
     * Gender
     */
    private String sexABC;

    /**
     * address
     */
    private String addressABC;
	
    // ....
}

Test code

package cn.knightzz;


import cn.knightzz.entity.User;
import cn.knightzz.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

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

public class MybatisTest {

    @Test
    public void findUserById() throws IOException {

        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(stream);
        // Turn on auto submit
        SqlSession sqlSession = factory.openSession(true);

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

1.2 parameter injection

1.2.1 three methods of parameter injection

Use #{arg0}-#{argn} or #{param1}-#{paramn} to get parameters

<mapper namespace="com.lagou.mapper.UserMapper"> 
    <select id="findByIdAndUsername1" resultType="user"> 
        <!-- select * from user where id = #{arg0} and username = #{arg1} --> 
        select * from user where id = #{param1} and username = #{param2} 
    </select> 
</mapper>

Use annotation and introduce @ Param() annotation to obtain parameters

public interface UserMapper { 
    public List<User> findByIdAndUsername2(@Param("id") Integer id,
                                           @Param("username") String username); }

Passing parameter recommendations using pojo objects

User entity class

package cn.knightzz.entity;

import java.util.Date;

public class User {
    /**
     * User id
     */
    private Integer id;

    /**
     * User name
     */
    private String username;

    /**
     * User birthday
     */
    private Date birthday;

    /**
     * Gender
     */
    private String sex;

    /**
     * address
     */
    private String address;
}

UserMapper interface

public List<User> findByIdAndUsername(User user);

UserMapper.xml

<mapper namespace="com.lagou.mapper.UserMapper"> 
    <select id="findByIdAndUsername" parameterType="cn.knightzz.entity.User" resultType="cn.knightzz.entity.User"> 
        select * from user where id = #{id} and username = #{username} </select> </mapper>

1.3 fuzzy query

1.3.1 requirements

Requirement: query the user table according to username

1.3.2 code cases

UserMapper.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="cn.knightzz.mapper.UserMapper">

    <resultMap id="UserResultMap" type="cn.knightzz.entity.User">

        <!-- Encapsulate primary key fields -->
        <id property="uid" column="id"></id>
        <!-- Configure common fields -->
        <result property="usernameABC" column="username"></result>
        <result property="birthdayABC" column="birthday"></result>
        <result property="sexABC" column="sex"></result>
        <result property="addressABC" column="address"></result>
    </resultMap>

    <select id="findUserById" parameterType="int" resultMap="UserResultMap">
        select *
        from user
        where id = #{id};
    </select>

    <select id="findUserByName" parameterType="string" resultMap="UserResultMap">
        select *
        from user
        where username like #{username};
    </select>
</mapper>

UserMapper

package cn.knightzz.mapper;

import cn.knightzz.entity.User;

import java.util.List;


public interface UserMapper {

    /**
     * Query user by id
     * @param id User id
     * @return
     */
    public User findUserById(int id);

    /**
     * Query user by user name
     * @param username
     * @return
     */
    public User findUserByName(String username);
}

Test code

    @Test
    public void findUserByName() throws IOException {

        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(stream);
        // Turn on auto submit
        SqlSession sqlSession = factory.openSession(true);

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.findUserByName("%answer%");
        System.out.println(user);
        sqlSession.close();
    }

1.3.3 difference between ${} and #{} [written test questions]

#{}: represents a placeholder
- Through #{} the preparedStatement, you can set the value in the placeholder and automatically convert java type and jdbc type,
- #{} can effectively prevent sql injection.
- #{} can receive simple type values or pojo property values.
- If parameterType transfers a single simple type value, #{} the name in parentheses is written casually.
${}: indicates a concatenated sql string
- Through ${}, the contents passed in by parameterType can be spliced into sql without jdbc type conversion, and sql injection will occur
  Question.
- ${} can receive simple type values or pojo attribute values. If parameterType transfers a single simple type value,
- ${} can only be value in parentheses

2. Mapping file

2.1 return primary key

2.1.1 basic concepts

In many cases, we need to insert a piece of data into the database and hope to get the data immediately

2.1.2 useGeneratedKeys

We can set the value of the primary key returned by the query by setting the value of useGeneratedKeys
useGeneratedKeys="true" declare the returned primary key. keyProperty="id" encapsulate the returned primary key value into the id property of the entity. Note that this entity refers to the id value of the parameter object, not the directly returned value
Note: it is only applicable to databases with self incremented primary keys. mysql and sqlserver support it, but oracle does not

Entity class User

package cn.knightzz.entity;

import java.util.Date;

/**
 * @author Wang Tianci
 * @title: User
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/15 22:00
 */
public class User {
    /**
     * User id
     */
    private Integer id;

    /**
     * User name
     */
    private String username;

    /**
     * User birthday
     */
    private Date birthday;

    /**
     * Gender
     */
    private String sex;

    /**
     * address
     */
    private String address;

	// .... get / set
}

UserMapper.xml configuration

<?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.knightzz.mapper.UserMapper">

    <!--
        useGeneratedKeys="true" Declare return primary key keyProperty="id" Encapsulate the returned primary key value into the entity's id Attribute,
        Note that this entity refers to the object of the parameter
    -->
    <insert id="saveUser" parameterType="cn.knightzz.entity.User" useGeneratedKeys="true" keyProperty="id">
        insert into user(username, birthday, sex, address)
        values (#{username}, #{birthday}, #{sex}, #{address})
    </insert>
</mapper>

Test code

package cn.knightzz.test;

import cn.knightzz.entity.User;
import cn.knightzz.mapper.UserMapper;
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.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;


public class MyBatisMapperTest {

    private SqlSession sqlSession;

    @Before
    public void init() throws IOException {
        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        this.sqlSession = build.openSession(true);
    }

    @Test
    public void saveUser() {

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = new User();
        user.setUsername("KKK");
        user.setSex("male");
        user.setBirthday(new Date());
        user.setAddress("Henan");
        mapper.saveUser(user);
        System.out.println(user);
        sqlSession.close();
    }
}

2.1.3 selectKey

selectKey has a wide range of applications and supports all types of databases
The principle is: use SELECT LAST_INSERT_ID(); SQL built-in function, which can query the primary key id of the last inserted data
Common configuration
- keyColumn="id" specifies the primary key column name
- keyProperty="id" specifies that the primary key is encapsulated in the id property of the entity
- resultType="int" specifies the primary key type
- order="AFTER" is set before (after) the sql statement is executed

UserMapper.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="cn.knightzz.mapper.UserMapper">
    <!--
            selectKey It has a wide range of applications and supports all types of databases keyColumn="id" Specifies the primary key column name keyProperty="id"
            Specifies how the primary key is encapsulated into the entity id Attribute resultType="int"
            Specify primary key type order="AFTER" Set in sql Execute the statement before (after) the statement is executed
    -->
    <insert id="saveUserBySelectKey" parameterType="cn.knightzz.entity.User" useGeneratedKeys="true" keyProperty="id">

        <selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
            SELECT LAST_INSERT_ID();
        </selectKey>

        insert into user(username, birthday, sex, address)
        values (#{username}, #{birthday}, #{sex}, #{address})
    </insert>
</mapper>

UserMapper

package cn.knightzz.mapper;

import cn.knightzz.entity.User;

import java.util.List;

public interface UserMapper {

    /**
     * Insert user data
     * @param user
     * @return Return primary key
     */
    public void saveUser(User user);

    /**
     * Return the primary key by selectKey
     * @param user
     */
    public void saveUserBySelectKey(User user);
}

Test code

package cn.knightzz.test;

import cn.knightzz.entity.User;
import cn.knightzz.mapper.UserMapper;
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.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;


public class MyBatisMapperTest {

    private User user = new User();
    private SqlSession sqlSession;
    private UserMapper mapper;

    @Before
    public void init() throws IOException {
        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void saveUserBySelectKey() {

        user.setUsername("KKK222");
        user.setSex("female");
        user.setBirthday(new Date());
        user.setAddress("Henan");
        mapper.saveUserBySelectKey(user);
        System.out.println(user);
        sqlSession.close();
    }
}

2.2 dynamic SQL

2.2.1 basic concepts

When we want to execute different sql statements according to different conditions, we need to use dynamic sql

2.2.2 dynamic SQL < if >

UserMapper interface

    /**
     * According to id, the latter
     * @param user
     * @return
     */
    public User findUserByIdAndUsernameIf(User user);

UserMapper.xml

    <select id="findUserByIdAndUsernameIf" parameterType="cn.knightzz.entity.User" resultType="cn.knightzz.entity.User">
        select * from user
        <where>
            <if test="id != null">
                AND id = #{id}
            </if>
            <if test="username != null">
                AND username = #{username}
            </if>
        </where>
    </select>

Test code

package cn.knightzz.test;

import cn.knightzz.entity.User;
import cn.knightzz.mapper.UserMapper;
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.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;


/**
 * @author Wang Tianci
 * @title: MyBatisMapperTest
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/17 18:51
 */
public class MyBatisMapperTest {

    private User user = new User();
    private SqlSession sqlSession;
    private UserMapper mapper;

    @Before
    public void init() throws IOException {
        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }
    @Test
    public void findUserByIdAndUsernameIf() {
        user.setUsername("KKK222");
        User userByIdAndUsernameIf = mapper.findUserByIdAndUsernameIf(user);
        System.out.println(userByIdAndUsernameIf);
        sqlSession.close();
    }
}

2.2.3 dynamic SQL < set >

Dynamically update the user table data. If the attribute has a value, it will be updated. If there is no value, it will not be processed.

UserMapper interface

    /**
     * Update user information and use the set tag
     * @param user
     */
    public void updateUserBySet(User user);

UserMapper.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="cn.knightzz.mapper.UserMapper">
    <update id="updateUserBySet" parameterType="cn.knightzz.entity.User">
        update user
        <set>
            <if test="username != null">
                username = #{username}
            </if>
            <if test="birthday != null">
                birthday = #{birthday}
            </if>
            <if test="sex != null">
                sex = #{sex}
            </if>
            <if test="address != null">
                address = #{address}
            </if>
        </set>
        where id = #{id}
    </update>
</mapper>

Test code

package cn.knightzz.test;

import cn.knightzz.entity.User;
import cn.knightzz.mapper.UserMapper;
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.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;


/**
 * @author Wang Tianci
 * @title: MyBatisMapperTest
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/17 18:51
 */
public class MyBatisMapperTest {

    private User user = new User();
    private SqlSession sqlSession;
    private UserMapper mapper;

    @Before
    public void init() throws IOException {
        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }
    @Test
    public void updateUserBySet() {
        user.setId(1);
        user.setUsername("KKK222");
        mapper.updateUserBySet(user);
        sqlSession.close();
    }
}

2.2.4 dynamic SQL < foreach >

foreach is mainly used for loop traversal of data
For example: select * from user where id in (1,2,3) in such a statement, the passed in parameter part must depend on
foreach traversal can be realized.
The < foreach > tag is used to traverse the collection. Its properties are:
Collection: represents the collection elements to traverse
open: represents the beginning of the statement
close: represents the end part
item: represents the variable name generated by traversing each element of the collection
sperator: represents the separator

UserMapper interface

    /**
     * Query user list by id
     * @param ids
     * @return
     */
    public List<User> findAllUserById(List<Integer> ids);

UserMapper.xml configuration

    <select id="findAllUserById" parameterType="java.util.List" resultType="cn.knightzz.entity.User">
        select * from user
        <where>
            /*
                collection: Represents the collection element to be traversed, usually written as collection or list
                open: Represents the beginning of a statement
                close: Representative closing part
                item: Represents the variable name generated by traversing each element of the collection
                sperator: Delegate separator
            */
            <foreach collection="collection" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

Test code

package cn.knightzz.test;

import cn.knightzz.entity.User;
import cn.knightzz.mapper.UserMapper;
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.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * @author Wang Tianci
 * @title: MyBatisMapperTest
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/17 18:51
 */
public class MyBatisMapperTest {

    private User user = new User();
    private SqlSession sqlSession;
    private UserMapper mapper;

    @Before
    public void init() throws IOException {
        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void findAllUserById() {

        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(2024);

        List<User> userList = mapper.findAllUserById(ids);
        for (User user1 : userList) {
            System.out.println(user1);
        }
        sqlSession.close();
    }
}

2.2.5 dynamic SQL < SQL >

Duplicate sql can be extracted from the mapping file and referenced with include when used, so as to achieve the purpose of sql reuse

usage method

Extract common code

    <sql id="selectUser">
        select * from user
    </sql>

Introducing SQL code

 /* Introducing SQL fragments*/
<include refid="selectUser"></include>

Code example

    <sql id="selectUser">
        select * from user
    </sql>

    <select id="findAllUserById" parameterType="java.util.List" resultType="cn.knightzz.entity.User">
        /* Introducing SQL fragments*/
        <include refid="selectUser"></include>
        <where>
            /*
                collection: Represents the collection element to be traversed, usually written as collection or list
                open: Represents the beginning of a statement
                close: Representative closing part
                item: Represents the variable name generated by traversing each element of the collection
                sperator: Delegate separator
            */
            <foreach collection="collection" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

2.3 paging plug-in

2.3.1 plugins plug-in

MyBatis can use third-party plug-ins to extend its functions. The paging assistant PageHelper encapsulates the complex operations of paging
Loading, you can obtain paging related data in a simple way
Development steps:
1. Import the coordinates of the generic PageHelper
2. Configure the PageHelper plug-in in the mybatis core configuration file
3. Test paging data acquisition

2.3.2 case realization

Introduce Maven dependency

        <!-- Paging assistant -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>3.7.5</version>
        </dependency>
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>0.9.1</version>
        </dependency>

In sqlmapconfig Configuring plug-ins in XML

Special note: note that < plugins > should be placed in front of < environment > and behind < typealiases >

    <!-- Configure plug-ins -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!--
                1. Designated dialect, Because different databases have different paging syntax, Database needs to be specified
            -->
            <property name="dialect" value="mysql"/>
        </plugin>
    </plugins>

UserMapper interface

    /**
     * Query all user information
     * @return
     */
    public List<User> findAll();

Test code

package cn.knightzz.test;

import cn.knightzz.entity.User;
import cn.knightzz.mapper.UserMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
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.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * @author Wang Tianci
 * @title: MyBatisMapperTest
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/17 18:51
 */
public class MyBatisMapperTest {

    private User user = new User();
    private SqlSession sqlSession;
    private UserMapper mapper;

    @Before
    public void init() throws IOException {
        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void pageHelper() {

        // Set paging parameters:
        // pageNum: current page number
        // pageSize: number of data pieces displayed per page
        PageHelper.startPage(2, 2);
        List<User> allUsers = mapper.findAll();

        for (User allUser : allUsers) {
            System.out.println(allUser);
        }

        PageInfo<User> pageInfo = new PageInfo<User>(allUsers);
        System.out.println("Total number of pieces:" + pageInfo.getTotal());
        System.out.println("Total pages:" + pageInfo.getPages());
        System.out.println("Current page:" + pageInfo.getPageNum());
        System.out.println("Display length per page:" + pageInfo.getPageSize());
        System.out.println("First page:" + pageInfo.isIsFirstPage());
        System.out.println("Last page:" + pageInfo.isIsLastPage());

        sqlSession.close();
    }
}

3. MyBatis multi table query

3.1 database relationship

3.1.1 relationship table type

Relational database tables are divided into
- one-on-one
- One to many
- Many to many

3.1.2 relationship table cases

The ID number is one to one.
- A person can only have one ID number.
- An ID number can only belong to one person.
Users and orders are one to many, and orders and users are many to one
- One user can place multiple orders
- Multiple orders belong to the same user
Students and courses are many to many
- A student can take multiple courses
- A course can be taken by more than one student
In particular, an order is subordinate to only one user, so mybatis regards many to one as one to one

3.2 case environment

3.2.1 database table

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`
(
    `id`        INT(11) NOT NULL AUTO_INCREMENT,
    `ordertime` VARCHAR(255) DEFAULT NULL,
    `total`     DOUBLE       DEFAULT NULL,
    `uid`       INT(11)      DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `uid` (`uid`),
    CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE = INNODB
  AUTO_INCREMENT = 4
  DEFAULT CHARSET = utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders`
VALUES ('1', '2020-12-12', '3000', '1');
INSERT INTO `orders`
VALUES ('2', '2020-12-12', '4000', '1');
INSERT INTO `orders`
VALUES ('3', '2020-12-12', '5000', '2');
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role`
(
    `id`       INT(11) NOT NULL AUTO_INCREMENT,
    `rolename` VARCHAR(255) DEFAULT NULL,
    `roleDesc` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB
  AUTO_INCREMENT = 3
  DEFAULT CHARSET = utf8;
-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role`
VALUES ('1', 'CTO', 'CTO');
INSERT INTO `sys_role`
VALUES ('2', 'CEO', 'CEO');
-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role`
(
    `userid` INT(11) NOT NULL,
    `roleid` INT(11) NOT NULL,
    PRIMARY KEY (`userid`, `roleid`),
    KEY `roleid` (`roleid`),
    CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `sys_role` (`id`),
    CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `user` (`id`)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8;
-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
INSERT INTO `sys_user_role`
VALUES ('1', '1');
INSERT INTO `sys_user_role`
VALUES ('2', '1');
INSERT INTO `sys_user_role`
VALUES ('1', '2');
INSERT INTO `sys_user_role`
VALUES ('2', '2');

3.3 one to one

3.3.1 data model

The relationship between user table and order table is that a user has multiple orders, and an order belongs to only one user
One to one query requirement: query all orders and find out the users of each order at the same time

Query sql

select * from orders join user u on u.id = orders.uid

3.3.2 code cases

OrderMapper interface

package cn.knightzz.mapper;

import cn.knightzz.entity.Order;

import java.util.List;

/**
 * @author Wang Tianci
 * @title: OrderMapper
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/19 11:24
 */
public interface OrderMapper {

    /**
     * Query order information and user information corresponding to the order
     * @return
     */
    public List<Order> findAllWithUser();
}

Order entity class

package cn.knightzz.entity;

import java.util.Date;

/**
 * @author Wang Tianci
 * @title: Oder
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/19 11:19
 */
public class Order {

    /**
     * Order id
     */
    private Integer id;

    /**
     * Order time
     */
    private Date orderTime;

    /**
     * amount of money
     */
    private double money;

    /**
     * User information to which the order belongs
     */
    private User user;
	
    // ... get / set
}

OrderMapper.xml

It should be noted that the User of < association property = "User" refers to the property value of the User object of the entity class Order

Mapping entity class attributes can use < association property = "user" javatype = "CN. Knightzz. Entity. User" >

<?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.knightzz.mapper.OrderMapper">

    <!--
        One to one (many to one) use association Label Association
        property="user" Encapsulates the attribute name of the entity
        javaType="user" Encapsulates the attribute type of the entity
    -->
    <resultMap id="orderReturnMap" type="cn.knightzz.entity.Order">
        <!-- Primary key  -->
        <id property="id" column="id"></id>
        <result property="orderTime" column="ordertime"></result>
        <result property="money" column="total"></result>

        <!--
            property : Order In the table User Property name of the object
            javaType : User Object's property type full path
        -->
        <association property="user" javaType="cn.knightzz.entity.User">
            <!-- Encapsulate primary key -->
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="birthday" column="birthday"></result>
            <result property="sex" column="sex"></result>
            <result property="address" column="address"></result>
        </association>

    </resultMap>

    <select id="findAllWithUser" resultMap="orderReturnMap">
        select *
        from orders
                 join user u on u.id = orders.uid
    </select>

</mapper>

Test code

package cn.knightzz.test;

import cn.knightzz.entity.Order;
import cn.knightzz.entity.User;
import cn.knightzz.mapper.OrderMapper;
import cn.knightzz.mapper.UserMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * @author Wang Tianci
 * @title: MyBatisMapperTest
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/17 18:51
 */
public class MyBatisMapperTest {

    private User user = new User();
    private SqlSession sqlSession;
    private UserMapper mapper;
    private OrderMapper orderMapper;

    @Before
    public void init() throws IOException {
        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
        orderMapper = sqlSession.getMapper(OrderMapper.class);
    }

    @Test
    public void findAllWithUser(){
        List<Order> allWithUsers = orderMapper.findAllWithUser();
        for (Order allWithUser : allWithUsers) {
            System.out.println(allWithUser);
        }
    }

    @After
    public void destroy(){
        sqlSession.close();
    }
}

3.4 one to many

3.4.1 data model

The relationship between user table and order table is that a user has multiple orders, and an order belongs to only one user
One to many query requirements: query all users and find the orders that the user has at the same time

Query sql

select *, o.id as oid from user left join orders o on user.id = o.uid

3.4.2 code cases

UserMapper interface

    /**
     * Query all user information and their order information
     * @return
     */
    public List<User> findAllWithOrder();

User entity class

package cn.knightzz.entity;

import java.util.Date;
import java.util.List;

/**
 * @author Wang Tianci
 * @title: User
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/15 22:00
 */
public class User {
    /**
     * User id
     */
    private Integer id;

    /**
     * User name
     */
    private String username;

    /**
     * User birthday
     */
    private Date birthday;

    /**
     * Gender
     */
    private String sex;

    /**
     * address
     */
    private String address;

    /**
     * Order list
     */
    private List<Order> orderList;
}

UserMapper.xml configuration

    <resultMap id="userReturnMap" type="cn.knightzz.entity.User">
        <!-- Set user table primary key and attribute mapping-->
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>

        <collection property="orderList" ofType="cn.knightzz.entity.Order">
            <!-- Primary key  -->
            <id property="id" column="oid"></id>
            <result property="orderTime" column="ordertime"></result>
            <result property="money" column="total"></result>
        </collection>
    </resultMap>

    <select id="findAllWithOrder" resultMap="userReturnMap">
        select *, o.id as oid from user left join orders o on user.id = o.uid
    </select>

Test code

package cn.knightzz.test;

import cn.knightzz.entity.Order;
import cn.knightzz.entity.User;
import cn.knightzz.mapper.OrderMapper;
import cn.knightzz.mapper.UserMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * @author Wang Tianci
 * @title: MyBatisMapperTest
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/17 18:51
 */
public class MyBatisMapperTest {

    private User user = new User();
    private SqlSession sqlSession;
    private UserMapper mapper;
    private OrderMapper orderMapper;

    @Before
    public void init() throws IOException {
        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
        orderMapper = sqlSession.getMapper(OrderMapper.class);
    }

    @Test
    public void findAllWithOrder(){
        List<User> allWithOrders = mapper.findAllWithOrder();
        for (User allWithOrder : allWithOrders) {
            System.out.println(allWithOrder);
        }
    }

    @After
    public void destroy(){
        sqlSession.close();
    }
}

3.5 many to many

3.5.1 data model

The relationship between user table and role table is that a user has multiple roles and a role is used by multiple users
Many to many query requirements: query all users and find all roles of the user at the same time

Query SQL

        select *
        from user
                 left join sys_user_role sur on user.id = sur.userid
                 inner join sys_role sr on sur.roleid = sr.id

3.5.2 code cases

User and SysRole entity classes

package cn.knightzz.entity;

import java.util.Date;
import java.util.List;

public class User {

    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    /**
     * Represents the list of roles associated with the current user
     */
    private List<SysRole> roleList;
	
}

package cn.knightzz.entity;

public class SysRole {

    private Integer id;
    private String roleName;
    private String roleDesc;
	
    // get /set ...
}

UserMapper interface

    /**
     * Find users and their permission list
     * @return
     */
    public List<User> findAllWithRole();

UserMapper.xml configuration`

    <resultMap id="userRoleListMap" type="cn.knightzz.entity.User">
        <!-- Set user table primary key and attribute mapping-->
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>

        <collection property="roleList" ofType="cn.knightzz.entity.SysRole">
            <!-- Primary key  -->
            <id property="id" column="roleid"></id>
            <result property="roleName" column="rolename"></result>
            <result property="roleDesc" column="roleDesc"></result>
        </collection>
    </resultMap>

    <select id="findAllWithRole" resultMap="userRoleListMap">
        select *
        from user
                 left join sys_user_role sur on user.id = sur.userid
                 inner join sys_role sr on sur.roleid = sr.id
    </select>

Test code

package cn.knightzz.test;

import cn.knightzz.entity.Order;
import cn.knightzz.entity.User;
import cn.knightzz.mapper.OrderMapper;
import cn.knightzz.mapper.UserMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * @author Wang Tianci
 * @title: MyBatisMapperTest
 * @projectName spring-aop-01
 * @description:
 * @website http://knightzz.cn/
 * @github https://github.com/knightzz1998
 * @date 2022/1/17 18:51
 */
public class MyBatisMapperTest {

    private User user = new User();
    private SqlSession sqlSession;
    private UserMapper mapper;
    private OrderMapper orderMapper;

    @Before
    public void init() throws IOException {
        InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
        sqlSession = build.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
        orderMapper = sqlSession.getMapper(OrderMapper.class);
    }
    @Test
    public void findAllWithRole(){
        List<User> allWithRoles = mapper.findAllWithRole();
        for (User allWithRole : allWithRoles) {
            System.out.println(allWithRole);
        }
    }

    @After
    public void destroy(){
        sqlSession.close();
    }
}

4. MyBatis nested query

4.1 basic concepts

Nested query is to split the joint query statements in the original multi table query into a single table query, and then nest them in one table using the syntax of mybatis

Get up.

4.2 advantages and disadvantages

Advantages: simplify multi table query operations
Disadvantages: execute multiple sql statements, wasting database performance

Topics: Java Database Mybatis

Programmer Think

MyBatis Study Guide - result mapping and dynamic SQL

1. Complex mapping

1.1 result mapping

1.1.1 basic concepts

1.1.2 code cases

1.2 parameter injection

1.2.1 three methods of parameter injection

1.3 fuzzy query

1.3.1 requirements

1.3.2 code cases

1.3.3 difference between ${} and #{} [written test questions]

2. Mapping file

2.1 return primary key

2.1.1 basic concepts

2.1.2 useGeneratedKeys

2.1.3 selectKey

2.2 dynamic SQL

2.2.1 basic concepts

2.2.2 dynamic SQL < if >

2.2.3 dynamic SQL < set >

2.2.4 dynamic SQL < foreach >

2.2.5 dynamic SQL < SQL >

2.3 paging plug-in

2.3.1 plugins plug-in

2.3.2 case realization

3. MyBatis multi table query

3.1 database relationship

3.1.1 relationship table type

3.1.2 relationship table cases

3.2 case environment

3.2.1 database table

3.3 one to one

3.3.1 data model

3.3.2 code cases

3.4 one to many

3.4.1 data model

3.4.2 code cases

3.5 many to many

3.5.1 data model

3.5.2 code cases

4. MyBatis nested query

4.1 basic concepts

4.2 advantages and disadvantages

Hot Topics