MyBatis见解3

8.MyBatis的关联查询

8.3.一对多查询

需求：查询所有用户信息及用户关联的账户信息。

分析：用户信息和他的账户信息为一对多关系，并且查询过程中如果用户没有账户信息，此时也要将用户信息查询出来，此时左外连接查询比较合适。

8.3.1.pojo

package com.by.pojo;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    private List<Account> account;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", account=" + account +
                '}';
    }

    public List<Account> getAccount() {
        return account;
    }

    public void setAccount(List<Account> account) {
        this.account = account;
    }

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }


}

8.3.2.mapper

package com.by.dao;

import com.by.pojo.User;

import java.util.List;

public interface UserDao {
    User getUserById(Integer id);
}

<?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">
<resultMap id="getUserByIdResultMap" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="birthday" property="birthday"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
        <!--一对多使用collection标签指定数据的封装规则-->
        <collection property="account" ofType="account">
            <id column="aid" property="id"></id>
            <result column="uid" property="uid"></result>
            <result column="money" property="money"></result>
        </collection>
    </resultMap>

    <select id="getUserById" parameterType="int" resultMap="getUserByIdResultMap">
        SELECT
            u.*,
            a.id aid,
            a.money money,
            a.uid uid
        FROM
            user u
                LEFT JOIN
            account a
            ON
                u.id=a.uid
        WHERE
            u.id=#{id}
    </select>

8.3.3.测试

    /**
     * 一对多：一个user 对 多个Account
     */
    @Test
    public void testGetUsertById() throws IOException {
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = userDao.getUserById(41);
        System.out.println(user);
    }

8.4.多对多查询

需求：查询角色及角色赋予的用户信息。

分析：一个用户可以拥有多个角色，一个角色也可以赋予多个用户，用户和角色为双向的一对多关系，多对多关系其实我们看成是双向的一对多关系。

user(uid, username)：王贺、万通

? user_role(uid, rid)

role(rid, )：校长、老师、学生

8.3.1.pojo

package com.by.pojo;

import java.util.List;
// 一方
public class Role {
    private Integer id;
    private String roleName;
    private String roleDesc;
    // 多方
    private List<User> user;

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                ", user=" + user +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    public List<User> getUser() {
        return user;
    }

    public void setUser(List<User> user) {
        this.user = user;
    }
}

8.3.2.mapper

package com.by.dao;

import com.by.pojo.Role;

public interface RoleMapper {
    Role getRoleById(Integer id);
}

<?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.by.dao.RoleMapper">
    <resultMap id="getRoleByIdResultMap" type="role">
        <id column="rid" property="id"></id>
        <result column="role_name" property="roleName"></result>
        <result column="role_desc" property="roleDesc"></result>
        <!--
            一对多使用collection标签指定数据的封装规则
            property="userList"：pojo的属性
            ofType="com.by.pojo.User"：集合的泛型，等价于resultType
        -->
        <collection property="user" ofType="user">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="birthday" property="birthday"></result>
            <result column="sex" property="sex"></result>
            <result column="address" property="address"></result>
        </collection>
    </resultMap>
    <select id="getRoleById" parameterType="int" resultMap="getRoleByIdResultMap">
        SELECT
            r.id rid,
            role_name,
            role_desc,
            u.*
        FROM
            user_role ur
                LEFT JOIN
            role r
            ON
                ur.rid=r.id
                LEFT JOIN
            user u
            ON
                ur.uid=u.id
        WHERE
            r.id=#{id}
    </select>
</mapper>

8.3.3.测试

   /**
     * 多对多：一个user 对 多个role   一个role 对 多个user
     */
    @Test
    public void testGetRoletById() throws IOException {
        RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
        Role role = roleMapper.getRoleById(1);
        System.out.println(role);
    }

9.MyBatis的延迟加载

创建工程：

9.1.什么是延迟加载？

开启延迟加载后，在真正使用数据的时候才发起级联查询，不用的时候不查询。

9.2.mapper

package com.by.dao;

import com.by.pojo.User;

import java.util.List;

public interface UserDao {
    User getUserById2(Integer id);
}

<?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.by.dao.UserDao">
    <!--
      id:和接口方法名保持一致
      resultType:和接口返回类型保持一致
     -->
    <select id="findAll" resultType="com.by.pojo.User">
        select * from user
    </select>

    <select id="getUserById" parameterType="int" resultType="user">
        select * from user where id=#{id}
    </select>

    <resultMap id="getUserById2ResultMap" type="user">
        <id column="id" property="id"></id><result column="username" property="username"></result>
        <result column="birthday" property="birthday"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
        <!--
           property="accountList"：pojo的属性
           ofType="account"：集合的泛型
           select="com.by.mapper.AccountMapper.selectAccountByUid"：要调用的select标签的id
           column="id"：传递的参数
           fetchType="lazy"：局部开启延迟加载
       -->
        <collection property="accountList" ofType="account" select="com.by.dao.AccountMapper.selectAccountById" column="id" fetchType="lazy">
        </collection>

    </resultMap>

    <select id="getUserById2" parameterType="int" resultMap="getUserById2ResultMap">
        SELECT * FROM user WHERE id=#{id}
    </select>
</mapper>

9.3.全局开启懒加载

    <!-- 全局配置延迟加载策略 -->
    <settings>
        <!--  打开延迟加载的开关  -->
        <setting name="lazyLoadingEnabled" value="true"/>
    </settings>

9.4.测试

    @Test
    public void testGetUserById2() throws IOException {
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = userDao.getUserById2(41);
        System.out.println(user.getUsername());
        List<Account> accountList = user.getAccountList();
        for (Account account : accountList) {
            System.out.println(account);
        }
    }

10.MyBatis的动态SQL

创建工程：

10.1.什么是动态SQL?

MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作，并动态拼接成完整的SQL之后再执行，以达到SQL复用、简化编程的效果。

10.2.if标签

我们根据实体类的不同取值，使用不同的SQL语句来进行查询。比如在id如果不为空时可以根据 id查询，如果username不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。
mapper接口

package com.by.dao;

import com.by.pojo.User;

import java.util.List;

public interface UserDao {
    User findAll2(User user);
}

mapper映射文件

<?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">
    <select id="findAll2" parameterType="user" resultType="user">
        SELECT * FROM user
        <where>
            <if test="id != null">
                AND id=#{id}
            </if>
            <if test="sex != null and sex != ''">
                AND sex=#{sex}
            </if>
        </where>
    </select>

测试

    public void testFindAll2(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setId(41);
        user.setSex("男");
        System.out.println(userDao.findAll2(user));
    }

10.3.where标签

为了简化上面where 1=1的条件拼装，我们可以使用where标签将if标签代码块包起来，将1=1条件去掉。

若查询条件的开头为 “AND” 或 “OR”，where 标签会将它们去除。
mapper映射文件

<?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">
<!--namespace：隔离sql，一般是接口名称的全类名-->
<mapper namespace="com.by.dao.UserDao">
<select id="findByUser" resultType="User">
        select * from user
        <!--where标签将if标签代码块包起来去掉开头 “AND” 或 “OR”-->
        <where>
            <if test="username!=null and username != ''">
                and username=#{username}
            </if>
            <if test="birthday!=null">
                and birthday=#{birthday}
            </if>
            <if test="sex!=null and sex != ''">
                and sex=#{sex}
            </if>
            <if test="address!=null and address != ''">
                and address=#{address}
            </if>
        </where>
    </select>
  </mapper>

10.4.set标签

set标签用于动态包含需要更新的列，并会删掉额外的逗号
mapper

package com.by.dao;

import com.by.pojo.User;

import java.util.List;

public interface UserDao {
    void updateUserById(User user);
}

    <update id="updateUserById" parameterType="user">
        UPDATE user
        <set>
            <if test="username != null and username != ''">
                username=#{username},
            </if>
        </set>
        WHERE id=#{id}
    </update>

测试

    @Test
    public void testUpdateUserById(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setId(41);
        user.setUsername("杨过");
        userDao.updateUserById(user);
    }