Java自学之mybatis：一对多查询

学习目的：使用mybatis，进行一对多查询。

Part 1

清空category_和product_表中的数据，修改product_表：

create table vaefun_mybatis.product_
(
    id    int auto_increment
        primary key,
    name  varchar(30)     null,
    price float default 0 null,
    cid   int             null
)
    charset = utf8;

在表中添加数据：

INSERT INTO vaefun_mybatis.product_ (id, name, price, cid) VALUES (1, 'product a', 88.88, 1);
INSERT INTO vaefun_mybatis.product_ (id, name, price, cid) VALUES (2, 'product b', 88.88, 1);
INSERT INTO vaefun_mybatis.product_ (id, name, price, cid) VALUES (3, 'product c', 88.88, 1);
INSERT INTO vaefun_mybatis.product_ (id, name, price, cid) VALUES (4, 'product x', 88.88, 2);
INSERT INTO vaefun_mybatis.product_ (id, name, price, cid) VALUES (5, 'product y', 88.88, 2);
INSERT INTO vaefun_mybatis.product_ (id, name, price, cid) VALUES (6, 'product z', 88.88, 2);
INSERT INTO vaefun_mybatis.category_ (id, name) VALUES (1, 'category1');
INSERT INTO vaefun_mybatis.category_ (id, name) VALUES (2, 'category2');

Part 2

pojo类

Product

package cn.vaefun.pojo;

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

Category

package cn.vaefun.pojo;

import java.util.List;

public class Category {
    private int id;
    private String name;
    List<Product> products;

    public List<Product> getProducts() {
        return products;
    }

    public void setProducts(List<Product> products) {
        this.products = 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;
    }

    @Override
    public String toString() {
        return "Category{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

Part 3

配置pojo.xml，通过left join关联查询，对Category和Product表进行关联查询。

与前面学习的有所区别，这里不是用的resultType, 而是resultMap，通过resultMap把数据取出来放在对应的对象属性里。

注： Category的id 字段 和Product的id字段同名，Mybatis不知道谁是谁的，所以需要通过取别名cid,pid来区分。name字段同理。

新增以下配置：

    <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="listCategoryP" 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>

Part 4

测试代码块

    /**
     * 一对多
     */
    public static void listCategory(SqlSession session){
        List<Category> cs = session.selectList("listCategoryP");
        for (Category c : cs) {
            System.out.println(c);
            List<Product> ps = c.getProducts();
            for (Product p : ps) {
                System.out.println("\t"+p);
            }
        }
    }

测试结果