MyBatis嵌套结果查询

J2EE课老师让我们完成课后的练习，内容是分别使用嵌套结果的方式和嵌套查询的方式查询商品表和分类表。

表的内容如下:

首先我们建库建表嘛。

create DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
use shop;
CREATE TABLE category
(
 id int(32) PRIMARY KEY auto_increment,
 typename VARCHAR(56)
)CHARACTER set utf8 COLLATE utf8_general_ci;

INSERT INTO category VALUES(1, '黑色家电');

INSERT INTO category VALUES(2, '白色家电');

CREATE TABLE product
(
 id int(32) PRIMARY KEY auto_increment,
 goodsname VARCHAR(56),
 price DOUBLE,
 category_id int(32) not null,
 FOREIGN key(category_id) REFERENCES category(id)
)CHARACTER set utf8 COLLATE utf8_general_ci;

INSERT INTO product
VALUES(1,'电视机', 5000,1);

INSERT INTO product
VALUES(2,'冰箱', 4000,2);

INSERT INTO product
VALUES(3,'空调', 3000,2);

INSERT INTO product
VALUES(4,'洗衣机', 2000,2);

 然后建实体类

package pojo;

import java.util.List;

public class Category {
    private Integer id;
    private String typename;
    private List<Product> productList;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTypename() {
        return typename;
    }

    public void setTypename(String typename) {
        this.typename = typename;
    }

    public List<Product> getProductList() {
        return productList;
    }

    public void setProductList(List<Product> productList) {
        this.productList = productList;
    }

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

package pojo;

public class Product {
    private Integer id;
    private String goodsname;
    private double price;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getGoodsname() {
        return goodsname;
    }

    public void setGoodsname(String goodsname) {
        this.goodsname = goodsname;
    }

    public double getPrice() {
        return price;
    }

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

    @Override
    public String toString() {
               return "Product{" +
                                "id=" + id + ", goodsname='" + goodsname +
                                ", price=" + price + '}';
            }

}

在Resources下mapper编写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为映射的根节点-->
<!-- mapper为映射的根节点，namespace指定Dao接口的完整类名
mybatis会依据这个接口动态创建一个实现类去实现这个接口，
而这个实现类是一个Mapper对象-->
<mapper namespace="pojo.CategoryMapper">
    <!--id ="接口中的方法名"
　　parameterType="传入的参数类型"
　　resultType = "返回实体类对象，使用包.类名"-->
    <!--    一对多：查看某一商品的类别及其关联的商品信息-->
    <select id="findCategoryWithProduct" parameterType="Integer"
            resultMap="CategoryWithProductResult">
        SELECT c.*, p.id as category_id, p.goodsname, p.price
        from category c,
             product p
        WHERE c.id = p.category_id
          and c.id = #{id}

    </select>
    <resultMap type="pojo.Category" id="CategoryWithProductResult">
        <id property="id" column="id"/>
        <result property="typename" column="typename"/>
        <!-- 一对多关联映射：collection
  ofType表示属性集合中元素的类型，List<Product>属性即Product类 -->
        <collection property="productList"
                    ofType="pojo.Product">
            <id property="id" column="category_id"/>
            <result property="goodsname" column="goodsname"/>
            <result property="price" column="price"/>
        </collection>
    </resultMap>


</mapper>

<?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为映射的根节点-->
<!-- mapper为映射的根节点，namespace指定Dao接口的完整类名
mybatis会依据这个接口动态创建一个实现类去实现这个接口，
而这个实现类是一个Mapper对象-->
<mapper namespace="pojo.ProductMapper">
    <!--id ="接口中的方法名"
　　parameterType="传入的参数类型"
　　resultType = "返回实体类对象，使用包.类名"-->
    <!--    一对多：查看某一商品的类别及其关联的商品信息-->

    <select id="findProductById" parameterType="Integer" resultMap="Product">
        select * from product where category_id IN(select id from category where id=#{id})

    </select>
    <resultMap type="pojo.Product" id="Product">
        <id property="id" column="id"/>
        <result property="typename" column="typename"/>
        <!-- 一对多关联映射：collection
  ofType表示属性集合中元素的类型，List<Product>属性即Product类 -->
    </resultMap>

</mapper>

然后在测试类编写查询语句

public class findCategoryTest {
    @Test
    public void findCategoryTest() {
        String resources = "mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resources);
        } catch (IOException e) {
            e.printStackTrace();
        }
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        /**
         * author 诉衷情の麻雀
         * date 2023/3/24
         * 查询id为2的商品类信息
         */
        String statement = "findCategoryWithProduct";

        Category category = new Category();
        category.setId(2);
        category = sqlSession.selectOne(statement, category);

       
        System.out.print(category);
        sqlSession.close();
    }

    @Test
    public void findProductTest() {
        String resources = "mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resources);
        } catch (IOException e) {
            e.printStackTrace();
        }
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        String statement = "findProductById";

        Product product = new Product();
//        product.setId(1);
        product= sqlSession.selectOne(statement, 1);

        System.out.print(product);
        sqlSession.close();

    }
}

 

 

需要注意的是在xml文件中 以前映射的语句是resultType但映射为实体类时是ResultMap

如果你出现了以下的报错除了要检查mybatis_config配置外，那就是有可能这里出现了问题