MyBatis的关联查询

MyBatis的关联查询

• （1）项目不可能只有一个表，一般是多表
• （2）多表关系为，一对一，一对多，多对多
• （3）查询的数据来自多个表，要使用多表查询
  笛卡尔集，显示内连接inner join，左外连接left outer join，右外连接right outer join，子查询select 嵌套select
• （4）查询的结果要封装成javaBean对象 ，在Mybatis中重点掌握resultMap

关联查询三种方法：

1. 一对一查询

（1）关联查询的中的一对一就是指 一个账号存在一个用户
（2）数据来自两个表，使用连接查询，需要输出所有信息，使用左外连接

• association 标签 用于 成员变量的类型为自定义实体类型

 <resultMap id="" type="">
            ....
            <association property="user" javaType="com.by.pojo.User">
                ....
            </association>
        </resultMap>

案例：一个账号对应一个用户

• （1） 先写一个User对象和Account对象：写在pojo包

public class Account {
    private Integer id;
    private Integer uid;
    private Double money;

    //对应一个 用户
    private User user;

    public Integer getId() {
        return id;
    }

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

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    public User getUser() {
        return user;
    }

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

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                ", user=" + user +
                '}';
    }
}

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

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

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

• （2） 在创建接口是实现方法 和sql结果映射 并使用association标签

public interface AccountDao {
   /**
    * 一对一
    * @param id
    * @return
    */
   Account getAccountById(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.AccountDao">
    <resultMap id="getAccountByIdResultMap" type="account">
        <id column="aid" property="id"></id>
        <result column="uid" property="uid"></result>
        <result column="money" property="money"></result>
        <!--
               一对一查询使用association标签
               property="user" :Account的属性
               javaType="user" :等价于resultType
               -->
        <association property="user" javaType="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>
        </association>
    </resultMap>
<select id="getAccountById" parameterType="int" resultMap="getAccountByIdResultMap" >
    select a.id as aid,a.uid,a.money,u.* from account a left join user u on a.uid=u.id where a.id=#{id}
</select>

• （3）最后测试

public class MyBatisTest {

    private InputStream inputStream;
    private SqlSession sqlSession;

    @Before
    public void info() throws IOException {
        // 加载配置文件
        String resource = "mybatis-config.xml";
        inputStream = Resources.getResourceAsStream(resource);

        // 创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(inputStream);
        //获得数据库会话实例
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void testFindAll() {
        AccountDao accountDao=sqlSession.getMapper(AccountDao.class);
        Account account=accountDao.getAccountById(1);
        System.out.println(account);
    }
     @After
    public void close() throws IOException {
    	//关闭资源
        sqlSession.close();
        inputStream.close();
    }

}

2. 一对多查询：

• （1）关联查询的中的一对多是指，一个用户可以拥有多个账号
• （2）数据来自两个表，使用连接查询，需要输出每一个用户有多少个账号
• （3）查询结果使用collection标签 映射List<元素>

<resultMap id="" type="">
            ....
            <collection property="user" ofType="com.by.pojo.Account">
                ....
            </collection>
        </resultMap>

3. 多对多查询

        <resultMap id="" type="">
            ....
            <collection property="user" ofType="com.by.pojo.Role">
                ....
            </collection>
        </resultMap>

<?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.RoleDao">
    <resultMap id="getRoleByIdResult" type="role">
        <id column="rid" property="id"></id>

        <result column="role_name" property="roleName"></result>
        <result column="role_desc" property="roleDesc"></result>
        <collection property="users" 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" resultMap="getRoleByIdResult">
        SELECT
            r.id as rid,r.role_name,r.role_desc,
            u.*
        FROM
            user_role ur
                JOIN role r ON ur.rid=r.id
                JOIN user u ON ur.uid=u.id
        WHERE
            r.id=#{id}
    </select>
</mapper>