mybatis多表查询

---

一对多

---

通过查询category分类对应多个产品product

1.先创建第普通的product实体类

public class Product {
    private int id;
    private String name;
    private float price;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }
    @Override
    public String toString() {
        return "Product [id=" + id + ", name=" + name + ", price=" + price + "]";
    }
 
}

2.创建Category实体类，提供products的集合

public class Category {
    private int id;
    private String name;
    
    List<Product> products;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public List<Product> getProducts() {
        return products;
    }
    public void setProducts(List<Product> products) {
        this.products = products;
    }
    @Override
    public String toString() {
        return "Category [id=" + id + ", name=" + name + "]";
    }
     
}

3.修改Category.xml

column对应sql字段列名，property对应实体类名，因为Category的id 字段 和Product的有些字段如id字段同名所以，需要通过取别名cid,pid来区分。修改在sql中修改

sql语句： select c., p., c.id ‘cid’, p.id ‘pid’, c.name ‘cname’,
p.name ‘pname’ from category_ c left join product_ p on c.id = p.cid

  <mapper namespace="com.how2java.pojo">
            <resultMap type="Category" id="categoryBean">
                <id column="cid" property="id" />
                <result column="cname" property="name" />
         
                <!-- 一对多的关系 -->
                <!-- property: 指的是集合属性的值, ofType：指的是集合中元素的类型 -->
                <collection property="products" ofType="Product">
                    <id column="pid" property="id" />
                    <result column="pname" property="name" />
                    <result column="price" property="price" />
                </collection>
            </resultMap>
         
            <!-- 关联查询分类和产品表 -->
            <select id="listCategory" resultMap="categoryBean">
                select c.*, p.*, c.id 'cid', p.id 'pid', c.name 'cname', p.name 'pname' from category_ c left join product_ p on c.id = p.cid
            </select>   
        </mapper>

test

List<Category> cs = session.selectList("listCategory");
        for (Category c : cs) {
            System.out.println(c);
            List<Product> ps = c.getProducts();
            for (Product p : ps) {
                System.out.println("\t"+p);
            }
        }

---

多对多

---

当我们查询角色时，可以同时得到角色所包含的用户信息
Role:角色表
USER_ROLE ：中间表

1.编写角色实体类

public class Role implements Serializable {
	    private Integer roleId;
	    private String roleName;
	    private String roleDesc;
//多对多的关系映射：一个角色可以赋予多个用户
	 private List<User> users;
	 
	public List<User> getUsers() {
			    return users;
}
	 public void setUsers(List<User> users) {
	    this.users = users;
	    }

2.编写 Role 持久层 接口

public interface IRoleDao {
/**
* 查询所有角色
* @return
*/
List<Role> findAll();
}

3. 编写Role映射文件xml
4. sql语句：

select u.*,r.id as rid,r.role_name,r.role_desc from role r
left outer join user_role ur on r.id = ur.rid
left outer join user u on u.id = ur.uid

<mapper namespace="com.itheima.dao.IRoleDao">
<!--定义 role 表的 ResultMap-->
	<resultMap id="roleMap" type="role">
		<id property="roleId" column="rid"></id>
		<result property="roleName" column="role_name"></result>
		<result property="roleDesc" column="role_desc"></result>
		<collection property="users" ofType="user">
			<id column="id" property="id"></id>
			<result column="username" property="username"></result>
			<result column="address" property="address"></result>
			<result column="sex" property="sex"></result>
			<result column="birthday" property="birthday"></result>
		</collection>
	</resultMap>
	<!--查询所有-->
	<select id="findAll" resultMap="roleMap">
			select u.*,r.id as rid,r.role_name,r.role_desc from role r
			left outer join user_role ur on r.id = ur.rid
			left outer join user u on u.id = ur.uid
	</select>
</mapper>

test:

@Test
public void testFindAll(){
List<Role> roles = roleDao.findAll();
 for(Role role : roles){
	    System.out.println("---每个角色的信息----");
	    System.out.println(role);
	    System.out.println(role.getUsers());
}
}