• 中文
    • 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
    • 3k
    • 请登录之后再进行评论

      登录

      赞助本站

      • 支付宝
      • 微信
      • QQ

      感谢一直支持本站的所有人!

      单栏布局 侧栏位置: