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

2020-03-12 本文已影响0人低调的灬攻城狮

一、数据库表创建

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.

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

一、数据库表创建

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

猜你喜欢

热点阅读