【java框架】查询数据方法： ①关联查询 ②嵌套查询

<?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">
<!--sql映射文件  namespace名字必须与接口的类路径一致-->
<mapper namespace="com.ffyc.ssmback.dao.UserDao">

<!--
    查询数据库数值：
        1、关联查询所有数据
        2、 mybatis中的嵌套查询 分开查询
-->
<!--    <select id="list" resultType="user">
        SELECT * FROM USER  u LEFT JOIN dept d ON u.deptid = d.id
	              LEFT JOIN user_role ur ON u.id = ur.userid
	              LEFT JOIN ROLE r ON ur.roleid = r.id*/
    </select>-->

    <resultMap id="userMap" type="User">
        <id column="id" property="id"></id>
        <result column="account" property="account"></result>
        <result column="gender" property="gender"></result>
        <result column="phone" property="phone"></result>
        <!--查询用户关联的部门-->
        <association property="dept" column="deptid" select="finddepts"></association>
        <!--查询用户分配的角色                                         cloumn="id" 为用户的id-->
        <collection property="roles" javaType="list" ofType="Role" column="id" select="findRole"></collection>
    </resultMap>
    <select id="list" resultMap="userMap">
        SELECT id,account,gender,pho`user`ne,deptid FROM USER WHERE TYPE=1
    </select>
    <select id="finddepts" parameterType="int" resultType="Dept">
        SELECT NAME FROM dept WHERE id=#{id}
    </select>
    <select id="findRole" parameterType="int" resultType="Role">
        SELECT r.name FROM user_role ur LEFT JOIN ROLE r ON ur.roleid = r.id WHERE ur.userid = #{id}
    </select>
</mapper>

-- 1、关联查询所有数据   

-- 2、 mybatis中的嵌套查询 分开查询  后面讲分页功能

-- 查询用户表 关联部门表、用户_角色表、角色表   
SELECT * FROM USER  u LEFT JOIN dept d ON u.deptid = d.id
	              LEFT JOIN user_role ur ON u.id = ur.userid
	              LEFT JOIN ROLE r ON ur.roleid = r.id
	              
-- 只查询普通管理员，
SELECT id,account,gender,pho`user`ne,deptid FROM USER WHERE TYPE=1
-- 查询用户所关联的部门
SELECT NAME FROM dept WHERE id=2
-- 查询用户2所分配的角色名字    
SELECT r.name FROM user_role ur LEFT JOIN ROLE r ON ur.roleid = r.id WHERE ur.userid = 2