Java EE企业级应用与开发实验四 查询商品类别 附源码

1.创建数据表，在数据表中插入category和product表

USE mybatis;
CREATE table product(
   id INT PRIMARY KEY AUTO_INCREMENT,
	 goodsname VARCHAR(32),
	 price DOUBLE,
	 typeid  int(32)
);
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');


USE mybatis;
CREATE TABLE category(
      id int(32)  PRIMARY KEY AUTO_INCREMENT,
			typename VARCHAR(32)
);
insert into category VALUES('1','黑色家电');
insert into category VALUES('2','白色家电');

2.配置核心配置文件，在此省略

3.建立Category和OneProduct的pojo类

（1）Category的pojo

package com.itheima.pojo;

import java.util.List;

public class Category {
    private int id;
    private String typename;
    private List<OneProduct> OneproductList;

    public int getId() {
        return id;
    }

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

    public String getTypename() {
        return typename;
    }

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

    public List<OneProduct> getOneproductList() {
        return OneproductList;
    }

    public void setOneproductList(List<OneProduct> oneproductList) {
        OneproductList = oneproductList;
    }

@Override
    public String toString(){
        return "category [id=" + id + ",typename=" + typename +",OneproductList=" + OneproductList +"]";
}
}

(2)OneProduct的pojo类

package com.itheima.pojo;

import java.util.List;

public class OneProduct {
    private Integer id;                //商品id
    private String goodsname;                //商品名称
    private Double price;//商品单价
    private int typeid;        //与订单关联的属性

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

    public int getTypeid() {
        return typeid;
    }

    public void setTypeid (int typeid){
        this.typeid = typeid;
    }

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

4.建立Category的Mapper映射文件（商品表和商品类别表的关系为多对一，建立一那方的Mapper文件）

<!DOCTYPE mapper PUBLateIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.CategoryMapper">
    <select id="findCategoryWithOneProduct" parameterType="Integer"
            resultMap="categoryWithProduct">
        select
            p.id pid,
            p.goodsname,
            p.price,
            c.id cid,
            c.typename
        from
            category c,
            product p
        where
            c.id = p.typeid
          and c.id=#{cid}
    </select>
    <resultMap id="categoryWithProduct" type="Category">
        <id column="cid" property="id"></id>
        <result column="typename" property="typename"></result>
        <collection property="OneproductList" javaType="list" ofType="OneProduct">
            <id column="pid" property="id"></id>
            <result column="goodsname" property="goodsname"></result>
            <result column="price" property="price"></result>
        </collection>
    </resultMap>
</mapper>

5.在mybatis-config.xml文件的<mappers>元素下，引入CategoryMapper.xml映射文件

 <mapper resource="com/itheima/mapper/CategoryMapper.xml"/>

6.在test/java下建立测试类

package com.itheima;

import com.itheima.pojo.Category;
import com.itheima.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class CategoryTest {
    @Test
    public void findCategoryWithOneProductTest(){
        SqlSession session = MyBatisUtils.getSession();
        Category category = session.selectOne("com.itheima.mapper.CategoryMapper.findCategoryWithOneProduct",2);
        System.out.println(category);
        session.commit();
        session.close();

    }
}

7.附上·运行结果