MyBatis 高级映射

MyBatis 是如何处理多张数据库表之间的关联关系，其中包括：

• 一对一的查询
• 一对多查询
• 多对多查询

mybatis项目中,必须先创建mybatis-config.xml和引入jar包

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <!-- 别名 -->
    <typeAliases>
        <package name="pojo"/>
    </typeAliases>
    <!-- 数据库环境 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 映射文件 -->
    <mappers>
        <mapper resource="pojo/Category.xml"/>
    </mappers>

</configuration>

一对多的查询

1.创建表:

 CREATE TABLE category_ (
            id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            name varchar(32) DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

    INSERT INTO category_ VALUES (null,'category1');
    INSERT INTO category_ VALUES (null,'category2');


        create table product_(
        id int NOT NULL AUTO_INCREMENT  PRIMARY KEY,
        name varchar(30)  DEFAULT NULL,
        price float  DEFAULT 0,
        cid int
        ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

        INSERT INTO product_ VALUES (1,'product a', 88.88, 1);
        INSERT INTO product_ VALUES (2,'product b', 88.88, 1);
        INSERT INTO product_ VALUES (3,'product c', 88.88, 1);
        INSERT INTO product_ VALUES (4,'product x', 88.88, 2);
        INSERT INTO product_ VALUES (5,'product y', 88.88, 2);
        INSERT INTO product_ VALUES (6,'product z', 88.88, 2);

2.创建Product产品实体类

package pojo;

/**
 * @author lenovo
 */
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 + "]";
    }

}

3.创建Category分类实体类

package pojo;


import java.util.List;

/**
 * @author lenovo
 */
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 + "]";
    }

}

4.Category.xml,不创建Product.xml是因为通过分类对产品的一对多，暂时无需 Product.xml

<?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="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 mybatis.category_ c left join mybatis.product_ p on c.id = p.cid
    </select>
</mapper>

5.创建TestMybatis测试类

package test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;


import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import pojo.Category;
import pojo.Product;


/**
 * @author lenovo
 */
public class MybatisTest {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        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);
            }
        }

        session.commit();
        session.close();

    }
}

6.运行测试类:

结果:

Category [id=1, name=category1]
    Product [id=1, name=product a, price=88.88]
    Product [id=2, name=product b, price=88.88]
    Product