Let's start with the needs:

Display all the books in the database on the page, and show the categories of the books at the same time (where a book may belong to multiple categories at the same time)

Create table:

The author uses intermediate tables to connect Book tables and book category tables. There is no foreign key to associate book category tables in book tables.

It refers to the primary keys of books and categories in the middle table.

Intermediate tables are used to represent the relationship between books and Book categories.

Establish a book list (book number, book name)

create table book
(
    bid int primary key auto_increment,
    bname varchar(20)                                     
);

Create a Category Table (Category Number, Category Name)

create table category
(
    cid int primary key auto_increment,
    cname varchar(20)
);

Establish intermediate tables (book number, category number)

create table middle
(
    m_bid int,
    m_cid int,
    constraint fk_bid foreign key(m_bid) references book(bid),
    constraint fk_cid foreign key(m_cid) references category(cid)
);

Insert test data

insert into category values (default,'java');
insert into category values (default,'c++');
insert into category values (default,'mysql');

insert into book values (default,'SQL technology');
insert into book values (default,'SSM+MySQL Detailed explanation');
insert into book values (default,'C++and java Contrast');

insert into middle values (1,3);
insert into middle values (2,1);
insert into middle values (2,3);
insert into middle values (3,2);
insert into middle values (3,1);

In the inserted data, the first book has one category, the second book and the third book have two categories.

So far, the database thing is over. Next, generate entity classes, DAO interfaces, and XML mapping files through MyBatis-Generator Not clicking here

For convenience and convenience, the author here demonstrates through the Java project, put the automatically generated files into the new Java project, import the relevant Jar package, the project structure is as follows

Now let's open the generated Book entity class and take a look at it.

public class Book {
private Integer bid;
    private String bname;

    public Integer getBid() {
        return bid;
    }
    public void setBid(Integer bid) {
        this.bid = bid;
    }
    public String getBname() {
        return bname;
    }
    public void setBname(String bname) {
        this.bname = bname == null ? null : bname.trim();
    }
}

There are only two attributes: book number and Book name. Our requirement is to get all categories of the book while getting the book. So we can consider adding a collection of book categories to the book entity class.

The revised book entity classes are as follows

public class Book {
    private Integer bid;
    private String bname;
    private List<Category> categories;

    public Integer getBid() {
        return bid;
    }
    public void setBid(Integer bid) {
        this.bid = bid;
    }
    public String getBname() {
        return bname;
    }
    public void setBname(String bname) {
        this.bname = bname == null ? null : bname.trim();
    }
    public List<Category> getCategories() {
        return categories;
    }
    public void setCategories(List<Category> categories) {
        this.categories = categories;
    }
}

Now let's start writing SQL statements. They look up all books and book categories by connecting queries.

select 
    * 
from 
    book b
inner join  
    middle m
on
    b.bid=m.m_bid
inner join
    category c
on
    m.m_cid=c.cid

The results of the execution show all the books and their categories perfectly as follows

Now let's start with the XML mapping file so that the data can be automatically populated into the book entity class as we expect.

In order to highlight the key points, the mapping files and DAO interfaces of the books are cleared. After clearing, the following is done.

<?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.mybatis.dao.BookMapper" >
    <select id="queryAll">
        
    </select>
</mapper>

public interface BookMapper {
    
   List<Book> queryAll();
   
}

After clearing up, we started to write, and the results are as follows.

<?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.mybatis.dao.BookMapper" >
    
    <resultMap type="com.mybatis.entity.Book" id="bookMap">
        <id property="bid" column="bid" />
        <result property="bname" column="bname" />
        
        <collection  property="categories"  ofType="com.mybatis.entity.Category">
            <id property="cid" column="cid" />
            <result property="cname" column="cname" />    
        </collection>
    </resultMap>

    <select id="queryAll" resultMap="bookMap">
        select 
            * 
        from 
            book b
        inner join  
            middle m
        on
            b.bid=m.m_bid
        inner join
            category c
        on
            m.m_cid=c.cid
    </select>
</mapper>

Finally, we write the main method test

public class MyMain {
    
    public static void main(String[] args) throws IOException {
        
         String resource = "mybatis-config.xml";
         Reader reader = Resources.getResourceAsReader(resource);
         SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
         SqlSession session = factory.openSession();
         
         BookMapper bookMapper = session.getMapper(BookMapper.class);
         for (Book book : bookMapper.queryAll()) {
            System.out.print("["+book.getBname()+"]");
            for(Category category :book.getCategories()){
                System.out.print(category.getCname()+"\t");
            }
            System.out.println("\n");
        }
    }
}

The test results are as follows

Successful output of all books and corresponding categories of books

Complete project download: Click to download

The author's ability is limited, what can be improved or wrong places are welcome to put forward! ___________