MyBatis实现多表查询——案例：订单

一、数据库表创建

• users用户表、products商品表、types商品类型表、orders订单表、details订单详情表
• 表结构.png
• details.png
• orders.png
• products.png
• types.png
• users.png

二、使用MyBatis完成相应的查询功能

• pom.xml 文件添加 junit，mysql， mybatis 依赖。

  <?xml version="1.0" encoding="UTF-8"?>
  <project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
      <groupId>com</groupId>
      <artifactId>Mybatis</artifactId>
      <version>1.0-SNAPSHOT</version>
      <dependencies>
          <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
              <version>8.0.18</version>
          </dependency>
          <dependency>
              <groupId>junit</groupId>
              <artifactId>junit</artifactId>
              <version>4.12</version>
          </dependency>
          <dependency>
              <groupId>org.mybatis</groupId>
              <artifactId>mybatis</artifactId>
              <version>3.4.6</version>
          </dependency>
      </dependencies>
  </project>
  

• 设置MyBatis的配置文件 mybatis.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="db.properties">
          <property name="pass" value="123456"></property>
      </properties>
      <settings>
          <setting name="logImpl" value="STDOUT_LOGGING"/>
      </settings>
      <typeAliases>
          <package name="com.pojo" />
      </typeAliases>
      <environments default="development">
          <environment id="development">
              <transactionManager type="JDBC"/>
              <dataSource type="POOLED">
                  <property name="driver" value="${driver}"/>
                  <property name="url" value="${url}"/>
                  <property name="username" value="${user}"/>
                  <property name="password" value="${pass}"/>
              </dataSource>
          </environment>
      </environments>
      <mappers>
          <mapper resource="com/mapper/OrderMapper.xml"/>
          <mapper resource="com/mapper/UserMapper.xml"/>
          <mapper resource="com/mapper/DetailMapper.xml"/>
          <mapper resource="com/mapper/ProductMapper.xml"/>
          <mapper resource="com/mapper/TypeMapper.xml"/>
      </mappers>
  </configuration>
  

• db.properties数据库的配置文件
  • mysql版本不同driver要修改

  driver=com.mysql.cj.jdbc.Driver
  url=jdbc:mysql://localhost:3307/mysqls?serverTimezone=UTC
  user=root
  pass=123456
  

• 设计 pojo 相关类
  • Users.java

    public class Users {
        private int uid;
        private String name;
        private String pass;
        private String phone;
        public int getUid() { return uid; }
        public void setUid(int uid) { this.uid = uid; }
        public String getName() { return name; }
        public void setName(String name) { this.name = name; }
        public String getPass() { return pass; }
        public void setPass(String pass) { this.pass = pass; }
        public String getPhone() { return phone; }
        public void setPhone(String phone) { this.phone = phone; }
        @Override
        public String toString() {
            final StringBuffer sb = new StringBuffer("Users{");
            sb.append("uid=").append(uid);
            sb.append(", name='").append(name).append('\'');
            sb.append(", pass='").append(pass).append('\'');
            sb.append(", phone='").append(phone).append('\'');
            sb.append('}');
            return sb.toString();
        }
    }
    

  • Order.java

    public class Order {
        private String oid;
        private double price;
        private String addr;
        private String payType;
        private Users u;
        private List<Detail> details;
        public String getOid() { return oid; }
        public void setOid(String oid) { this.oid = oid; }
        public double getPrice() { return price; }
        public void setPrice(double price) { this.price = price; }
        public String getAddr() { return addr; }
        public void setAddr(String addr) { this.addr = addr; }
        public String getPayType() { return payType; }
        public void setPayType(String payType) { this.payType = payType; }
        public Users getU() { return u; }
        public void setU(Users u) { this.u = u; }
        public List<Detail> getDetails() { return details; }
        public void setDetails(List<Detail> details) { this.details = details; }
        @Override
        public String toString() {
            final StringBuffer sb = new StringBuffer("Order{");
            sb.append("oid='").append(oid).append('\'');
            sb.append(", price=").append(price);
            sb.append(", addr='").append(addr).append('\'');
            sb.append(", payType='").append(payType).append('\'');
            sb.append(", u=").append(u);
            sb.append(", details=").append(details);
            sb.append('}');
            return sb.toString();
        }
    }
    

  • Product.java

    public class Product {
        private String pid;
        private String name;
        private String img;
        private double price;
        private Types t;
        public String getPid() { return pid; }
        public void setPid(String pid) { this.pid = pid; }
        public String getName() { return name; }
        public void setName(String name) { this.name = name; }
        public String getImg() { return img; }
        public void setImg(String img) { this.img = img; }
        public double getPrice() { return price; }
        public void setPrice(double price) { this.price = price; }
        public Types getT() { return t; }
        public void setT(Types t) { this.t = t; }
        @Override
        public String toString() {
            final StringBuffer sb = new StringBuffer("Product{");
            sb.append("pid='").append(pid).append('\'');
            sb.append(", name='").append(name).append('\'');
            sb.append(", img='").append(img).append('\'');
            sb.append(", price=").append(price);
            sb.append(", t=").append(t);
            sb.append('}');
            return sb.toString();
        }
    }
    

  • Types.java

    public class Types {
        private String tid;
        private String name;
        public String getTid() { return tid; }
        public void setTid(String tid) { this.tid = tid; }
        public String getName() { return name; }
        public void setName(String name) { this.name = name; }
        @Override
        public String toString() {
            final StringBuffer sb = new StringBuffer("Types{");
            sb.append("tid='").append(tid).append('\'');
            sb.append(", name='").append(name).append('\'');
            sb.append('}');
            return sb.toString();
        }
    }
    

  • Detail.java

    public class Detail {
        private String did;
        private int count;
        private Product pro;
        public String getDid() { return did; }
        public void setDid(String did) { this.did = did; }
        public int getCount() { return count; }
        public void setCount(int count) { this.count = count; }
        public Product getPro() { return pro; }
        public void setPro(Product pro) { this.pro = pro; }
        @Override
        public String toString() {
            final StringBuffer sb = new StringBuffer("Detail{");
            sb.append("did='").append(did).append('\'');
            sb.append(", count=").append(count);
            sb.append(", pro=").append(pro);
            sb.append('}');
            return sb.toString();
        }
    }
    

• 设置映射文件mapper
  • namespace和id组合唯一,可自定义
  • 使用resultMap进行联合查询，对应pojo内的属性名(property)和数据库表中的列名相同,可省略不写result标签
  • 使用association标签进行对一的表结构联合查询
  • 使用collection标签进行对多的表结构联合查询
  • UserMapper.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="com.pojo.UserMapper">
        <select id="getUserByUid" resultType="Users">
          select * from users where uid = #{uid}
        </select>
    </mapper>
    

  • OrderMapper.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="com.pojo.OrderMapper">
        <select id="getOrderByOid" resultMap="orderMap">
          select * from orders where oid = #{oid}
        </select>
        <resultMap id="orderMap" type="com.pojo.Order">
            <id property="oid" column="oid" ></id>
            <result column="price" property="price" />
            <result column="addr" property="addr" />
            <result column="payType" property="payType" />
            <association property="u" column="uid" select="com.pojo.UserMapper.getUserByUid"></association>
            <collection property="details" column="oid" select="com.pojo.DetailMapper.getDetailsByOid" ofType="detail" />
        </resultMap>
    </mapper>
    

  • DetailMapper.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="com.pojo.DetailMapper">
        <select id="getDetailsByOid" resultMap="detailMap">
          select * from details where oid = #{oid}
        </select>
        <resultMap id="detailMap" type="com.pojo.Detail">
            <id column="did" property="did"></id>
            <result property="count" column="count" />
            <association property="pro" column="pid" select="com.pojo.ProductMapper.getProductByPid" />
        </resultMap>
    </mapper>
    

  • TypeMapper.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="com.pojo.TypeMapper">
        <select id="getTypesByTid" resultType="com.pojo.Types">
          select * from types where tid = #{tid}
        </select>
    </mapper>
    

  • ProductMapper.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="com.pojo.ProductMapper">
        <select id="getProductByPid" resultMap="productMap">
          select * from products where pid = #{uid}
        </select>
        <resultMap id="productMap" type="com.pojo.Product">
            <id column="pid" property="pid"></id>
            <association property="t" column="tid" select="com.pojo.TypeMapper.getTypesByTid" />
        </resultMap>
    </mapper>
    

• 测试类 Test.java

  public class TestOrders {
      private SqlSessionFactory sf = null;
      private SqlSession session = null;
      @Before
      public void setUp(){
          try {
              sf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
              session = sf.openSession();
          } catch (IOException e) {
              e.printStackTrace();
          }
      }
      @After
      public void tearDown(){
          if(session != null){
              session.close();
              session = null;
          }
      }
      @Test
      public void testGetOrderByOid(){
          Order order = session.selectOne("com.pojo.OrderMapper.getOrderByOid", "bfa2974d62cc11eaa62b8cec4b26e06a");
          System.out.println(order);
      }
  }
  

• 测试结果

  Opening JDBC Connection
  Created connection 1881561036.
  Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@702657cc]
  ==>  Preparing: select * from orders where oid = ? 
  ==> Parameters: bfa2974d62cc11eaa62b8cec4b26e06a(String)
  <==    Columns: oid, uid, price, payType, addr
  <==        Row: bfa2974d62cc11eaa62b8cec4b26e06a, 1, 4799.0, AAA, 支付宝
  ====>  Preparing: select * from users where uid = ? 
  ====> Parameters: 1(Integer)
  <====    Columns: uid, name, pass, phone
  <====        Row: 1, LKT, 123456, 18111111111
  <====      Total: 1
  ====>  Preparing: select * from details where oid = ? 
  ====> Parameters: bfa2974d62cc11eaa62b8cec4b26e06a(String)
  <====    Columns: did, count, oid, pid
  <====        Row: 304adbc162cd11eaa62b8cec4b26e06a, 2, bfa2974d62cc11eaa62b8cec4b26e06a, 4445ff1c62cc11eaa62b8cec4b26e06a
  ======>  Preparing: select * from products where pid = ? 
  ======> Parameters: 4445ff1c62cc11eaa62b8cec4b26e06a(String)
  <======    Columns: pid, name, img, price, tid
  <======        Row: 4445ff1c62cc11eaa62b8cec4b26e06a, BlackShark2pro, BlackShark.jpg, 3299.0, baa46abb62cb11eaa62b8cec4b26e06a
  ========>  Preparing: select * from types where tid = ? 
  ========> Parameters: baa46abb62cb11eaa62b8cec4b26e06a(String)
  <========    Columns: tid, name
  <========        Row: baa46abb62cb11eaa62b8cec4b26e06a, AAA
  <========      Total: 1
  <======      Total: 1
  <====        Row: 304bca1b62cd11eaa62b8cec4b26e06a, 1, bfa2974d62cc11eaa62b8cec4b26e06a, 70106ee762cc11eaa62b8cec4b26e06a
  ======>  Preparing: select * from products where pid = ? 
  ======> Parameters: 70106ee762cc11eaa62b8cec4b26e06a(String)
  <======    Columns: pid, name, img, price, tid
  <======        Row: 70106ee762cc11eaa62b8cec4b26e06a, BlackShark3pro, BlackShark3pro.jpg, 4799.0, baa49b9762cb11eaa62b8cec4b26e06a
  ========>  Preparing: select * from types where tid = ? 
  ========> Parameters: baa49b9762cb11eaa62b8cec4b26e06a(String)
  <========    Columns: tid, name
  <========        Row: baa49b9762cb11eaa62b8cec4b26e06a, BBB
  <========      Total: 1
  <======      Total: 1
  <====        Row: 88d56e6a62cd11eaa62b8cec4b26e06a, 1, bfa2974d62cc11eaa62b8cec4b26e06a, 70106ee762cc11eaa62b8cec4b26e06a
  <====      Total: 3
  <==      Total: 1
  Order{oid='bfa2974d62cc11eaa62b8cec4b26e06a', price=4799.0, addr='支付宝', payType='AAA', u=Users{uid=1, name='LKT', pass='123456', phone='18111111111'}, details=[Detail{did='304adbc162cd11eaa62b8cec4b26e06a', count=2, pro=Product{pid='4445ff1c62cc11eaa62b8cec4b26e06a', name='BlackShark2pro', img='BlackShark.jpg', price=3299.0, t=Types{tid='baa46abb62cb11eaa62b8cec4b26e06a', name='AAA'}}}, Detail{did='304bca1b62cd11eaa62b8cec4b26e06a', count=1, pro=Product{pid='70106ee762cc11eaa62b8cec4b26e06a', name='BlackShark3pro', img='BlackShark3pro.jpg', price=4799.0, t=Types{tid='baa49b9762cb11eaa62b8cec4b26e06a', name='BBB'}}}, Detail{did='88d56e6a62cd11eaa62b8cec4b26e06a', count=1, pro=Product{pid='70106ee762cc11eaa62b8cec4b26e06a', name='BlackShark3pro', img='BlackShark3pro.jpg', price=4799.0, t=Types{tid='baa49b9762cb11eaa62b8cec4b26e06a', name='BBB'}}}]}
  Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@702657cc]
  Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@702657cc]
  Returned connection 1881561036 to pool.