建表语句

CREATE TABLE category(
  cid VARCHAR(32) PRIMARY KEY,
  cname VARCHAR(50),
  parent_id VARCHAR(32),
  `desc` VARCHAR(100)
);
INSERT INTO category(cid,cname,parent_id,`desc`) VALUES('c100','童书','0','一级分类');
INSERT INTO category(cid,cname,parent_id,`desc`) VALUES('c200','电子书','0','一级分类');

INSERT INTO category(cid,cname,parent_id,`desc`) VALUES('c101','中国儿童文学','c100','二级分类');
INSERT INTO category(cid,cname,parent_id,`desc`) VALUES('c102','婴儿读物','c100','二级分类');

INSERT INTO category(cid,cname,parent_id,`desc`) VALUES('c201','武侠小说','c200','二级分类');
INSERT INTO category(cid,cname,parent_id,`desc`) VALUES('c202','侦探推理','c200','二级分类');


CREATE TABLE book(
  bid VARCHAR(32) PRIMARY KEY,
  title VARCHAR(50),
  price DOUBLE(6,2),
  author VARCHAR(50),
  cid VARCHAR(32)
);

INSERT INTO book(bid,title,price,author,cid) VALUES('b001','学会管自己（歪歪兔独立成长童话）',64.00,'陈梦敏','c101');
INSERT INTO book(bid,title,price,author,cid) VALUES('b002','写给儿童的中国历史',177.50,'陈卫平','c101');

INSERT INTO book(bid,title,price,author,cid) VALUES('b003','小鸡球球触感玩具书',125.00,'[日]入山智','c102');

INSERT INTO book(bid,title,price,author,cid) VALUES('b004','天龙八部',12.00,'金庸','c201');
INSERT INTO book(bid,title,price,author,cid) VALUES('b005','朱贞木短篇小说集(全套装共8册)',103,'朱贞木','c201');
INSERT INTO book(bid,title,price,author,cid) VALUES('b006','笑傲江湖(新修版)一',1200,'金庸','c201');


INSERT INTO book(bid,title,price,author,cid) VALUES('b007','解忧杂货店',19.99,'（日）东野圭吾','c202');
INSERT INTO book(bid,title,price,author,cid) VALUES('b008','三口棺材',8.99,'（美）约翰·迪克森·卡尔','c202');

实现功能

查询一级分类，同时查询二级分类，以及查询二级分类下的所有图书

代码实现

domain

@Entity(name = "book")
public class Book {
    @Id
    private String bid;
    private String title;
    private Double price;
    private String author;
    private String cid;

@Override
public String toString() {
    return "Book{" +
            "bid='" + bid + '\'' +
            ", title='" + title + '\'' +
            ", price=" + price +
            ", author='" + author + '\'' +
            ", cid='" + cid + '\'' +
            '}';
}

public Book(String bid, String title, Double price, String author, String cid) {
    this.bid = bid;
    this.title = title;
    this.price = price;
    this.author = author;
    this.cid = cid;
}

public Book() {
}

public String getBid() {
    return bid;
}

public void setBid(String bid) {
    this.bid = bid;
}

public String getTitle() {
    return title;
}

public void setTitle(String title) {
    this.title = title;
}

public Double getPrice() {
    return price;
}

public void setPrice(Double price) {
    this.price = price;
}

public String getAuthor() {
    return author;
}

public void setAuthor(String author) {
    this.author = author;
}

public String getCid() {
    return cid;
}

public void setCid(String cid) {
    this.cid = cid;
}

}

@Entity(name = "category")
public class Category {
    @Id
    private String cid;
    private String cname;
    private String parent_id;
    private String desc;
    //一对多
    private ArrayList<Category> categoriesList =new ArrayList<>();
    private ArrayList<Book> booksList =new ArrayList<>();


@Override
public String toString() {
    return "Category{" +
            "cid='" + cid + '\'' +
            ", cname='" + cname + '\'' +
            ", parent_id='" + parent_id + '\'' +
            ", desc='" + desc + '\'' +
            ", categoriesList=" + categoriesList +
            ", booksList=" + booksList +
            '}';
}

public Category(String cid, String cname, String parent_id, String desc) {
    this.cid = cid;
    this.cname = cname;
    this.parent_id = parent_id;
    this.desc = desc;
}

public ArrayList<Category> getCategoriesList() {
    return categoriesList;
}

public void setCategoriesList(ArrayList<Category> categoriesList) {
    this.categoriesList = categoriesList;
}

public ArrayList<Book> getBooksList() {
    return booksList;
}

public void setBooksList(ArrayList<Book> booksList) {
    this.booksList = booksList;
}

public Category() {
}

public String getCid() {
    return cid;
}

public void setCid(String cid) {
    this.cid = cid;
}

public String getCname() {
    return cname;
}

public void setCname(String cname) {
    this.cname = cname;
}

public String getParent_id() {
    return parent_id;
}

public void setParent_id(String parent_id) {
    this.parent_id = parent_id;
}

public String getDesc() {
    return desc;
}

public void setDesc(String desc) {
    this.desc = desc;
}

}

mapper

BookMapper

public interface BookMapper {
public Book book(@Param("cid")String cid);
}

BookMapper.xml

<mapper namespace="com.itcast.mapper.BookMapper">
<select id="book" parameterType="String" resultType="com.itcast.domain.Book">
    select * from book where cid=#{cid}
</select>
</mapper>

Category

public interface CategoryMapper {
    public List<Category>SelectById2(@Param("cid")String cid);
    public List<Category>SelectById(@Param("cid")String cid);
    public List<Category> SelectAll();
}

Category.xml

<mapper namespace="com.itcast.mapper.CategoryMapper">
    <resultMap id="SelectById2" type="com.itcast.domain.Category">
        <id property="cid" column="cid"></id>
        <collection property="booksList" column="cid" select="com.itcast.mapper.BookMapper.book"></collection>
    </resultMap>


    <select id="SelectById" parameterType="String" resultType="com.itcast.domain.Category" resultMap="SelectById2">
        select * from category as c where c.parent_id=#{cid}
    </select>
    <resultMap id="d1" type="com.itcast.domain.Category">
        <id property="cid" column="cid"></id>
        <collection property="categoriesList" column="cid"
                    select="com.itcast.mapper.CategoryMapper.SelectById"></collection>
    </resultMap>
    <select id="SelectAll" resultType="com.itcast.domain.Category" resultMap="d1">
                    select * from category as c1 where c1.parent_id='0'
    </select>

</mapper>

Main

public static void main(String[] args) {
    CategoryMapper categoryMapper = MyBatisUtils.getMapper(CategoryMapper.class);
    List<Category> list = categoryMapper.SelectAll();
    list.forEach(System.out::println);
}
总结:一级和二级自表关联  简称套娃