• 中文
    • English
  • 注册
  • 查看作者
  • Mybatis:一对多查询

    一.  数据准备

    首先我们的数据表还是采用《Mybatis:一对一查询》一文中第一节创建的两个数据表,但是实体类要稍作修改

    将Product 中的Brand属性去掉:

    package io.zhangjia.entity;
    
    import java.sql.Timestamp;
    
    public class Product {
        private Integer pid;
        private String name;
        private Double price;
        private Integer status;
        private Timestamp createTime;
    
        public Integer getPid() {
            return pid;
        }
    
        public void setPid(Integer pid) {
            this.pid = pid;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Double getPrice() {
            return price;
        }
    
        public void setPrice(Double price) {
            this.price = price;
        }
    
        public Integer getStatus() {
            return status;
        }
    
        public void setStatus(Integer status) {
            this.status = status;
        }
    
        public Timestamp getCreateTime() {
            return createTime;
        }
    
        public void setCreateTime(Timestamp createTime) {
            this.createTime = createTime;
        }
    
    
        @Override
        public String toString() {
            return "Product{" +
                    "pid=" + pid +
                    ", name='" + name + '\'' +
                    ", price=" + price +
                    ", status=" + status +
                    ", createTime=" + createTime +
                    '}';
        }
    }

    给Brand添加products属性:

    package io.zhangjia.entity;
    
    
    import java.sql.Timestamp;
    import java.util.List;
    
    public class Brand {
        private Integer bid;
        private String name;
        private Integer status;
        private Timestamp createTime;
        private List<Product> products;
    
        public Integer getBid() {
            return bid;
        }
    
        public void setBid(Integer bid) {
            this.bid = bid;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Integer getStatus() {
            return status;
        }
    
        public void setStatus(Integer status) {
            this.status = status;
        }
    
        public Timestamp getCreateTime() {
            return createTime;
        }
    
        public void setCreateTime(Timestamp createTime) {
            this.createTime = createTime;
        }
    
        public List<Product> getProducts() {
            return products;
        }
    
        public void setProducts(List<Product> products) {
            this.products = products;
        }
    
        @Override
        public String toString() {
            return "Brand{" +
                    "bid=" + bid +
                    ", name='" + name + '\'' +
                    ", status=" + status +
                    ", createTime=" + createTime +
                    ", products=" + products +
                    '}';
        }
    }

    二.  功能实现

    先来说一下我们要实现的功能:

    根据任意品牌的ID,查询该品牌的全部信息和该品牌所对应的全部商品信息,所以在上面的Brand实体类中,将List<Product>作为了其私有属性,也就是说,一个品牌和商品是一对多的关系

    1.  编写对应接口

    public interface BrandMapper {
        Brand queryByBid(Integer bid);
    }

    2.  编写BrandMapper.xml

    因为Brand类中含有List<Product>属性,所以我们需要使用集合元素collection来处理。collection除了新增的 “ofType” 属性外,其他地方和关联(association)是完全相同的。其中:

    property的值就是Brand类中的List<Product> products,而ofType就是List<Product> 中的Product的全类名。

    <?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="io.zhangjia.mapper.BrandMapper">
        <resultMap id="brandResultMap" type="brand">
            <id column="bid" property="bid"/>
            <result column="brand_name" property="name"/>
            <result column="brand_status" property="status"/>
            <result column="brand_create_time" property="createTime"/>
            <collection property="products" ofType="product">
                <id column="pid" property="pid"/>
                <result column="product_name" property="name"/>
                <result column="price" property="price"/>
                <result column="product_status" property="status"/>
                <result column="product_create_time" property="createTime"/>
            </collection>
        </resultMap>
    
        <select id="queryByBid" parameterType="int" resultMap="brandResultMap">
            SELECT brand.bid,
                   brand.name          brand_name,
                   brand.status        brand_status,
                   brand.create_time   brand_create_time,
                   pid,
                   product.name        product_name,
                   price,
                   product.status      product_status,
                   product.create_time product_create_time
            FROM product,
                 brand
            WHERE brand.bid = product.bid
              AND brand.bid = #{bid}
        </select>
    </mapper>

    接下来配置mybatis-config.xml,配置内容依旧和《Mybatis:增删改查》一文中的第四节内容基本相同,不同之处只是需要在mappers标签中将新添加的BrandMapper.xml注册即可:

    <?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>
        <properties resource="jdbc.properties" />
            <settings>
            <setting name="mapUnderscoreToCamelCase" value="true"/>
        </settings>
        
        <typeAliases>
            <package name="io.zhangjia.entity"/>
        </typeAliases>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driver}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.username}"/>
                    <property name="password" value="${jdbc.password}"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <mapper resource="BrandMapper.xml"/>
        </mappers>
    </configuration>

    3.  最后编写测试类:

    package io.zhangjia.util;
    
    import com.alibaba.fastjson.JSON;
    import io.zhangjia.entity.Brand;
    import io.zhangjia.mapper.BrandMapper;
    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 java.io.IOException;
    import java.io.InputStream;
    
    public class Main {
        public static void main(String[] args) throws IOException {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = build.openSession(true);
            BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
    
            Brand brand = mapper.queryByBid(1);
            System.out.println("(product) = " + JSON.toJSONString(brand));
    
            sqlSession.close();
            inputStream.close();
        }
    }
    输出:
    (product) = {"bid":1,"createTime":1562050565000,"name":"苹果","products":[{"createTime":1562050565000,"name":"iPhone XS","pid":1,"price":10.0,"status":1},{"createTime":1562050565000,"name":"iPhone XS Max","pid":2,"price":20.0,"status":1}],"status":1}


  • 0
  • 0
  • 0
  • 3.2k
  • 请登录之后再进行评论

    登录
    单栏布局 侧栏位置: